System Search Timing Out

(Tbaatar) #1

Hi,

 

Our Matrix build has almost 400,000 assets and now the site is timing out for normal string searches in the back end administration panel.

 

Is this common issue with Matrix and other CMS? or can we do something to fix the problem?

 

 

Tuguldur

(Micky Gough) #2

Hi tbaatar,

 

What version of Postgresql are you running?

 

There are a few things that can be done to speed up the search time for the backend.

 

* cluster the sq_sch_idx table. This will take a while if it hasn't been done before, and will probably cause some heavy load. The syntax you're after from the psql prompt is:
  CLUSTER sq_sch_idx USING sch_idx_pk;

 

Rationale: The search index table frequently deletes large numbers of rows. While the autovacuum process will mark rows as deleted and reusable, when you do a lot of them at once you can end up with a lot of deleted rows taking up space on the filesystem. That space takes up a lot of the OS filesystem cache and gets swapped out and in when needed, which makes it very slow.

The cluster command rebuilds the table in the order of the specified index, so the table size is reduced and the index becomes more effective.

 

* set the "Backend Search Result Limit" in Global Preferences

 

Rationale: Parsing and paginating the quick search results uses a lot of CPU and memory. By default it'll find every single instance of the search phrase. Setting this value means it'll stop rendering the search popup after the top "x" results. A value like 20 or so will likely give you the results you need but cut down your search time.

 

* use the system_integrity_check_indexes.php script to make sure all of the indexes on the search table are correct. http://manuals.matrix.squizsuite.net/server-administrator/chapters/system-integrity-scripts/#system_integrity_check_indexes.php

 

There may or may not be other postgresql tuning that could be done, let me know if you don't get any joy from these.

(Tbaatar) #3

Thanks for the tips.

 

I have forwarded your reponse to Squiz UK support as we don't have access to root.

 

 

Tuguldur

(Bart Banda) #4

Also review the search manager settings for minimum word to be indexed, ideally this should be set to 4. And if search contains setting is turned on, this will also reduce performance. 

(Dan Graver) #5

Bit late to the party here...

 

The setting Backend Search Result Limit can be very handy in this situation too. http://manuals.matrix.squizsuite.net/system-configuration/chapters/global-preferences#Search-Manager-Preferences

(Tbaatar) #6

Thanks Dan, will give this a go and see how it improves the performance.

 

Our search is now working again, thanks to Squiz. The system search/table had to be re-indexed and re-clustered. No idea what this means, probably something to do with the database.