You are here: Foswiki>Tasks Web>Item14037 (11 Aug 2016, GeorgeClark)Edit Attach

Item14037: PageCache needs an index on the to_topic field.

pencil
Priority: Urgent
Current State: Closed
Released In: 2.1.1
Target Release: patch
Applies To: Engine
Component: PageCache, Performance
Branches: Release02x01 master Item14033 Item13897
Reported By: GeorgeClark
Waiting For:
Last Change By: GeorgeClark
Very long topic save times on foswiki.org was traced to very long cache update times.
  • foswiki_cache_pages: 55,457 rows
  • foswiki_cache_deps: 7,050,189 rows

The only index on the deps table is: KEY `foswiki_cache_deps_index` (`from_topic`,`to_topic`)

However on topic update, the deps table is joined with the pages table, =where to_topic='the saved topic'; And is referenced again, to delete all the deps rows for that to_topic.

Adding a simple index on to_topic reduced a simple count(*) for a unique to_topic from 16 seconds to sub-second. This will have similar impact on the join and delete operation. Index has been added to foswiki.org, but we need to create this index by default.

-- GeorgeClark - 03 Apr 2016

This index should be created by

  • Foswiki::PageCache::DBI::_createPagesTable
  • Foswiki::PageCache::DBI::_createDepsTable
  • Foswiki::PageCache::DBI::MySQL::_createPagesTable
  • Foswiki::PageCache::DBI::MySQL::_createDepsTable

Which patch did you apply to them?

-- MichaelDaum - 04 Apr 2016
 
Topic revision: r8 - 11 Aug 2016, GeorgeClark - This page was cached on 27 Mar 2017 - 10:41.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License