By tfrech on

I have been having excessively slow performance on various updates on my Drupal website. It happens most notably when I add a field to a node or when I save a view. It also happens when updating or adding taxonomy terms. I decided to try to figure out what was going on so I logged the MySQL queries. I found an enormous number of insert statements for the menu_router table I couldn't possibly post them here because the file was 38 mb in size within the span of the 60-odd seconds that it took to add one field to a node.

I also periodically see Duplicate Key errors for that same table by the hundreds being messaged on the site. This usually comes after a particularly long delay after saving a view.

I've tried looking up the error and have found that other people have seen it as well but I haven't found anything that resembles a solution yet.

One other thing I tried as a test was to comment out the menu_router_build function. After doing that, the time was reduced substantially but of course, this had it's own set of problems. I just wanted to confirm that it was indeed whatever that function does that was causing my slowdown.

Does anyone know how to eliminate or reduce this problem?

⋅ Categories: Drupal 6.x

Comments

tfrech’s picture

tfrech commented

I just ran another test. I had phpMyadmin open to view the menu_router table. There are 3700 items in it. On another tab, I started to add a field to a node. I went back to look at the menu_router table. Within 10 seconds, the number of records dropped to zero and then began to climb back up until it hit the 3700 again. Then there was another 10 to 15 second pause and it did it all over again. This clearly isn't supposed to do this.

shady_gun’s picture

shady_gun commented

Try clearing your cache or set minimum cache life time in performance based on the traffic on your website .. Also try increasing your php memory limit .. If your server supports memcache, please configure and install it and finally do not forget to run cron ..

tfrech’s picture

tfrech commented

Thanks, I will look into these suggestions. I can tell you now though that 1) I run cron ever 15 minutes. 2) My memory limits are already what I think are extreme compared to what I have seen suggested elsewhere and 3) I haven't cleared the cache recently, but I am pretty sure that I had the problem prior to and after the last time I did. Also, clearing the cache requires rebuilding the search index which, as I recall, took a LONG time. Possibly a related issue.

tfrech’s picture

tfrech commented

I found a two-part solution that worked for me.

First of all, apply this patch to make menu_router_build more efficient:
http://drupal.org/files/issues/drupal.menu_router_build.SELECT.d6.test-me_0.patch

Secondly, I found that the menu_router_build function was rebuilding the menu_router table even when the argument telling it to do so was set to false. The code in that function is like this:

function menu_router_build($reset = FALSE) {
  static $menu;
  if (isset($menu) || $reset) {
.
.
.
.

Well, I was finding that $menu was always coming up not set for some reason. Maybe someone here has an idea why this is. Anyway, I got around that issue by changing it to:

function menu_router_build($reset = FALSE) {
  $menu=variable_get('menu_router_menu',null);
  if (!$menu || $reset) {
.  
. 
. 
    variable_set('menu_router_menu',$menu);
 }

I left out the code in between because it doesn't get changed and is not relevant.

I found that where it was taking me over two minutes to add a cck field, it is now taking less than 20 seconds. Saving views used to take several minutes and now it takes about 15 to 20 seconds. Flushing the cache has had a similar improvement in speed.

kpastore’s picture

kpastore commented

Thanks tfrech for the code. I have tested this out on a multisite setup (6.22 / Windows, IIS, PHP, MySQL), but did not notice any improvement in speed for saving views. Fields for cck did seem to add more quickly, however. Unfortunately, I don't have a means to measure response times, but thought the anecdotal response would help.

tfrech’s picture

tfrech commented

I can't give exact numbers but it was a notable improvement throughout our site. Also, one thing I can say is that the dumping and rebuilding of the menu_router table doesn't happen as often as it did. I was able to physically watch it before and on adding a cck field the the number of records in the table dropped to zero, climbed to 3+ thousand, paused and then did it all over again before the process was done. Now it only does it once (which is still excessive in my opinion but I don't know enough to understand why it is doing that).