Memory & Performance problems using Category module

Hi David, and thanks for submitting this issue.

Can i ask how did your production site based on Category behave after your patch to category.inc?

I am close to be online with a quite big project based on Category too, and if things go as expected it will be easy to reach 5000-10000 categories.

How close to Taxonomy performances would be the Category module after this patch? What other potential slow-downs did you find in the module? Are there other potential problems in using Category for big production sites? Is still core Taxonomy a better choice (if yes why)?

These would be extremely valuable informations for all the people following this mod, and for Jaza hoping he will be committed to Category once he will be back from Summer of Code!

Thank you very much in advance

I am also interested in this!
What do you mean by "not working at all", but makes your site load faster?

this problem was rised also some time ago in this other issue.

Here is the solution proposed there in brief, but please refer to the full issue for further comments:

One possible work around would be to cache the variable in the database beetween calls, another possibility would be to reduce the scope of the array to build only the usefull part of the tree for the request.

i would set this to 'critical', since this is one of those issues that make the difference in choosing Category instead of Taxonomy.

David, there's another small patch about performance at http://drupal.org/node/84424

meantime let's hope Jaza (or some other savvy coder ;) will find some time to dedicate to this issue...

i am also thinking about trying rising some funds to help Category module get a serious bug-fix round...mmm...a thread about this will probably follow soon..

setting this as 'code needs work'

http://drupal.org/node/84424 has been marked duplicate of this thread

I think the 84424 patch may cause other issues.

Issue?

I've committed a fix to HEAD and 4.7, that improves the performance of category_get_category() and category_is_cat_or_cont() a fair bit (uses much more intelligent caching). However, when testing on a site with 4,000+ categories, the module is still extremely slow. More work needs to be done to improve performance.

Leaving this issue at present status.

I have an idea regarding the fixes committed.

I was analyzing the cached queries, and I noticed some problems with the way category_get_cached_item uses the database.

I'll post a patch shortly incorporating my ideas for speedup.

--Brandon

I did a profiling run, and I highly suspect _category_category_select_check_category of being a major part of the slowdowns.

It is node_loading EVERY CATEGORY to check whether the user is allowed to access it.

I finally pounded away at this thing for a few hours and found the issue. There are two instances of node_load which are used at a per category basis, determining whether they view or not. This slow it down grotesquely. I'm not sure but maybe on query to all the categories and then parse it after might be better.

But anyway the two that are a problem are:

function _category_category_select_check_category
*****
  if (!node_access('view', node_load($category->cid))) { //This line
    return FALSE;
  }

and

function _category_node_select_options($cnid, $blank, &$value, $exclude = array()) {
************
  if ($tree) {
    foreach ($tree as $category) {
      if (!in_array($category->cid, $exclude) && node_access('view', node_load($category->cid))) { //and this line
        $options[$category->cid] = _category_depth($category->depth, '-') . $category->title;
      }
    }

Until a better solution presents itself my quick fix has been commenting out the entire first one -

  //if (!node_access('view', node_load($category->cid))) { //This line
  //  return FALSE;
  //}
  

and editing the line in the second to remove the node_load, it's less secure, but at least it speeds it up, A LOT.

  if ($tree) {
    foreach ($tree as $category) {
      //if (!in_array($category->cid, $exclude) && node_access('view', node_load($category->cid))) {
      if (!in_array($category->cid, $exclude)) { //replaced with me!!
        $options[$category->cid] = _category_depth($category->depth, '-') . $category->title;
      }
    }

I hope this information is helpful to everyone!

It's not so much a security thing, really. People who don't limit access to categories won't even need this ability..

For now, here's a patch to add a toggle in the settings page.

In the future, perhaps adding a flag to categories or containers to selectively run the check only for them may be worth implementing. (checkbox -- Enable advanced access control for this category or something...)

Here's one that does the same thing for both cases pointed out in #15.

Both of these patches were against DRUPAL-4-7, btw.

And then it occurs to me...

WHY THE HELL AREN'T WE USING db_rewrite_sql() HERE??!?!??

category_get_tree is already db_rewrite_sql()ing.....

If it is working properly, both those node_load()s should be completely redundant.....

Unfortunately, that appears to not be the case...
I traced the return values from db_rewrite_sql for a normal user and got:
"SELECT c.cid, c.*, h.parent, n.title, cn.admin_title FROM {category} c INNER JOIN {category_hierarchy} h ON c.cid = h.cid INNER JOIN {node} n ON c.cid = n.nid LEFT JOIN {category_cont} cn ON c.cid = cn.cid LEFT JOIN {category} c2 ON h.parent = c2.cid WHERE (c2.cid IS NOT NULL OR h.parent = 0) AND n.status = 1 AND n.moderate = 0 AND c.cnid = %d ORDER BY c.weight, n.title"
"SELECT c.cid, c.*, h.parent, n.title, cn.admin_title FROM {category} c INNER JOIN {category_hierarchy} h ON c.cid = h.cid INNER JOIN {node} n ON c.cid = n.nid LEFT JOIN {category_cont} cn ON c.cid = cn.cid LEFT JOIN {category} c2 ON h.parent = c2.cid WHERE (c2.cid IS NOT NULL OR h.parent = 0) AND n.status = 1 AND n.moderate = 0 AND c.cnid = %d ORDER BY c.weight, n.title "

Looking into why db_rewrite_sql isn't kicking in properly....

Hmm, it appears I should install a node access module of some sort before trying to test this XD

With cac_lite, I get:

SELECT c.cid, c.*, h.parent, n.title, cn.admin_title FROM {category} c INNER JOIN {category_hierarchy} h ON c.cid = h.cid INNER JOIN {node} n ON c.cid = n.nid LEFT JOIN {category_cont} cn ON c.cid = cn.cid LEFT JOIN {category} c2 ON h.parent = c2.cid INNER JOIN {node_access} na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 2340 AND na.realm = 'cac_lite') OR (na.gid = 0 AND na.realm = 'cac_lite'))) AND (c2.cid IS NOT NULL OR h.parent = 0) AND n.status = 1 AND n.moderate = 0 AND c.cnid = %d ORDER BY c.weight, n.title

There's definately something wrong with the join order....

And then I read that joins are commutative. Meh.
Sorry about the trackerspam, it appears to work....

Here's the issue that lead to the code in question:
http://drupal.org/node/52552

So, OG doesn't use the node_access table?

Looks like Drupal 5 will be much better with access controls:
http://drupal.org/node/71420
http://drupal.org/node/75395

In any case, it appears that sites using regular node_access style access control already work fine, so I'd recommend having the two cases in #15 at the very least least be a toggle, so that people using no access control plugin / "normal" access control plugin don't have to take the node_load hit.

--Brandon

Sorry when I get a bit offtopic, but what kind of tree structure does category.module use? Perhaps it could be a speedup to use e.g. the Nested Set model instead of a parent relationship.

I wonder if it would be faster to create a new function in category called "category_load" based off of node load, but the way the data is processed and accessed would be different. Firstly the function grabs the entire tree structure of categories, not just the one at a time method. Secondly the data grabbed via the query is ONLY what is needed. Node_load grabs everything, not cool. Lastly, and this should be obvious by now, the use of this function wouldn't be looped like it is currently, it would be loaded once and parsed against the current tree.

Anyone else think this could be a sound solution?

scroogie:

Is THAT the name of what I'm trying to invent in http://drupal.org/node/87918 ?

FWIW, I'll be recieving some Joe Celko books in the post shortly, I'm sure they will help me in my quest to make Category faster ;)

--Brandon

bdragon: I will reply in that issue you pointed me to when i had time to read and understand your idea. On first sight, i dont think its the same. Actually, the Nested Sets Model is an idea of Joe Celko, too.

I am having some serious performance issues with a site that now has several thousand categories. I have been keeping up with the latest versions of the category module, and I was wondering if anyone else has been as well, and could post a new patch for the latest category.inc??

I did manage to get this patched category.inc working by checking out the version of category module from July 11, but I'd prefer to use a more recent version of category.

Thanks,
Colin

Hi David,

I for one would be very interested in getting a hold of your latest changes. Any chance you can post your category module (all files) in its entirety? Also interested in your other patches to core.

Many thanks,
Colin

This thread is silent for almost a year. Does this mean that category module is now free of any performance problems.

I am using categories module to provide free tagging function for this site for submitting free press release. This site runs on Drupal 5.1 and it uses Category module version 5.x-1.1 . There are more than 3000 Categories. If some one tries to submit a press release(article) with some free tags, PHP throws fatal error because of insufficient memory. It asks for more than 32MB.

So is there any patch to improve the performance of Category 5..x-1.1 module. I don't know whether this patch for Drupal 4.7 version of category module is already incorporated in the new version of category module for 5,x. Some one who knows more about category mode help me please.

Category still has performance issues, and I am currently swamped with other work and really need to find a block of time to spend on things... :-/

Wow, wish i had seen this sooner. Just launching 2 sites this week which could end up being very large sites - and of course both use category module. I have never done one yet that doesn't use this module.

The main thing i use Cat for is the ability to categorize with nodes. Am i correct in thinking this is cleanest solution for doing this? I am pretty sure i can't do this with taxonomy module??

So, if i am right about needing category module to be able to tag something with a thing that also has image fields, url fields, etc - then we need to get performance issue sorted out. Personally i don think i care how much memory is being used -if the site is big it likely shouldn't be running on a low end server anyway - BUT... page loads taking 3 times as long is kind of scary.

But did i read this right??? If i use cat module but only have a few categories.. but 100,000 nodes that are tagged with those categories - is this a performance issue?? Regardless i have no issue throwing some money into a bounty to get category module issues sorted out - anyone else. Brandon can't be expected to work for free - i don't.. well trying not to more these days.. :)

Peter Lindstrom
LiquidCMS - Content Management Solution Experts

ps - stay tuned for major site release notice tomorrow - and this site uses category module

No, a bunch of nodes being tagged should not cause performance issues. Only the creation of lots of categories has caused any issues in my experience.

So, does this mean that so-called "free tagging" is a kiss of death?

Thanks, David! - Wow, even I get it now!
Good karma!!

I'd like to try some of the suggestions here, but i'm not sure which is the patch and there seems to be several problems addressed.

I have an issue with submitting categories, using drupal 5.
Some performance reports:

I have a moderate traffic site where submitting a new category creates a spike that ends crashing eaccelerator.
There's about 5000 categories, but these are accessed all the time by a hundred visitors.
The spike is not too big, and the server is only on 1/3 of max load, but mysql runs over the 32m memory_limit by the time watchdog entries are written.
(it often throws mysql errors, like watchdog tables locked or mysql gone away).
I also suspect it also triggers a drupal cache clear, which might be the cause for the actual crashes on eaccelerator.
(eaccelerator is known to be sensitive to sudden loads, but gets the work done).

So far, the troubleshoot tracing points to category nodes being both edited or added.
Devel and mysql slow queries are still being examined, without conclusive findings.
However, if the site is put into maintenance mode, there's no bottleneck and while the spike is still observed it is not noticeable by users and there's no crash.
In this case the brief spike happens after the site is put back online, but drupal cache is not always cleared.
The result is that if you send 1 container and 10 categories in a row you have 11 chances of crash.
With site offline you send the 11 nodes with only a single spike, without bottleneck because the site is idle.

The site in question has undergone patching for the tid issue, exposed cid in views and have heavy views optimization via theming.
It began with drupal 4.7 and now uses drupal 5.7, so it is hard to tell how old is the problem, it has only worsened recently as the site growed.
An exact setup on another site, with only a couple hundred categories and few visitors have no such problem.
So far, only regular categories are used, no book style articles with multi pages transformed to categories.
Users are unable to post this kind of nodes, and use the outline module instead.

This is silent for a year and a half, and 4.7.x is unsupported now - but still, there's a lot of interesting reports in the above discussion.

As of 6.x-2.0-rc1, my performance patch got in (#501378: PERFORMANCE! Central caching for category API functions) - that's focused on slow queries, though, solving them with caching. It helped a lot on my site, which have some 100-150 categories, thousands of tagged nodes, 64M memory limit, and traffic just big enough to turn slow queries into a problem on a shared host (difficult to estimate any numbers on that part). Now that it's in, it'll get real "testing" in the wild, and we can further improve/build on that. But apart from that, there are areas unrelated to that patch:

The status of 6.x version is most probably still not examined yet, regarding these points mentioned here:

- memory footprint of category (do we avoid loading of unnecessary data?)
- scalability to thousands of categories
- whether we still have performance hit on access checks (the extra node_load() calls are gone now, but I suspect there might be some indirect replacement in use?)
- whether it makes sense to extend caching to wrapper modules (for example legacy taxonomy queries - my experience from live site says yes, but possible side-effects must be carefully examined in this legacy code).

So, while the 4.7.x patching attempts are obsolete now, it's entirely possible that some of the underlying problems still stand, so I'm turning this issue to a 6.x task, in hope that it gets a bit of research.

(Edit - also marking as duplicate: #245897: When trying to edit a category: "Fatal error: Allowed memory size of...")