Re: [Tiki-devel] Serious performance killer in categlib.php
On 2017-01-25 13:16, 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
> 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 don't, but before getCategories() was created this code was in build_cache(). build_cache() was split from 2 callers in r6863. Before that, the query was in get_all_categories_ext(). get_all_categories_ext() was itself split from get_all_categories() in r5670.
I do not have such an old checkout, but checking get_all_categories() callers back then should reveal a large part of today's users of the objects element (number of objects).
> 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?
Is the database server on the same OS as the HTTP server? If so, if it is query execution proper which is slow, Victor's suggestion should help.