[Tiki-devel] Serious performance killer in categlib.php

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Tiki-devel] Serious performance killer in categlib.php

Nelson Ko-3
In CategLib, getCategories() (line 872 in trunk, but similar in older Tiki too) loads all the categories then loops through all the categories to count the number of objects in each one. This is really slow and pretty much kills the server if your site has many categories.

This happens whenever the categories cache needs to be refreshed:
- when a category is added/removed/edited
- if pref categories_cache_refresh_on_object_cat is on (which I think is default), everytime something is added/removed from a cat.

This must be causing a real slowdown on dev.tiki.org, esp when bug items are saved, so I turned off the pref categories_cache_refresh_on_object_cat there. Hope that's ok.

But since that is just one of the 2 causes above that can be avoided, on one of our sites I had to temporarily hack the code to not fetch the number of objects at all (which is not a solution but a workaround). Anyone knows where (other than in browse categories) is the number of items in a category important/used?

I am trying to think of a way to solve this cleanly. I mean, if I am changing just one or two categories I shouldn't have to invalidate the entire cache for all the categories right? 

Any ideas?

Nelson



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
TikiWiki-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Tiki-devel] Serious performance killer in categlib.php

Victor Emanouilov

Hi Nelson,

Probably not answering your question directly but instead of dealing with partial cache invalidation (which is complex), why not optimize the mysql query a bit. I notice 2 things:

1. No need to run count query for every category. Just use a simple join:

SELECT tc.*, count(*) FROM `tiki_categories` tc LEFT JOIN tiki_category_objects tco ON tc.categId = tco.categId GROUP BY tc.categId

2. tiki_category_objects is missing an index and that's why count queries are slow. Above query will be slow unless we add the missing index on categId column. There is a PRIMARY composite index on catObjectId, categId but mysql cannot use it when searching for categId which is the case with the count queries. So, adding an index to categId should solve the performance issue.

I just tested this locally and query analyser gives very promising results.

Regards,
Victor


On 01/25/2017 08:16 PM, Nelson Ko wrote:
In CategLib, getCategories() (line 872 in trunk, but similar in older Tiki too) loads all the categories then loops through all the categories to count the number of objects in each one. This is really slow and pretty much kills the server if your site has many categories.

This happens whenever the categories cache needs to be refreshed:
- when a category is added/removed/edited
- if pref categories_cache_refresh_on_object_cat is on (which I think is default), everytime something is added/removed from a cat.

This must be causing a real slowdown on dev.tiki.org, esp when bug items are saved, so I turned off the pref categories_cache_refresh_on_object_cat there. Hope that's ok.

But since that is just one of the 2 causes above that can be avoided, on one of our sites I had to temporarily hack the code to not fetch the number of objects at all (which is not a solution but a workaround). Anyone knows where (other than in browse categories) is the number of items in a category important/used?

I am trying to think of a way to solve this cleanly. I mean, if I am changing just one or two categories I shouldn't have to invalidate the entire cache for all the categories right? 

Any ideas?

Nelson




------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot


_______________________________________________
TikiWiki-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
TikiWiki-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-devel
Loading...