You are here: Foswiki>Tasks Web>Item14037 (31 Jan 2018, 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 Item14380 Item14537
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: r10 - 31 Jan 2018, GeorgeClark
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy