MySQL hogs CPU in Firefox and Netscape with Drupal 4.5

By Angelo Bonadonna on 14 Nov 2004 at 06:05 UTC

I've upgraded from 4.4 to 4.5 on a Debian system running kernel 2.4.18. Just about everything seems to be working well, with one exception: In Firefox (and Netscape--the Macintosh version for both), pages take "forever" to load. They do eventually load. I monitored CPU activity by logging into a terminal and using the "top" command, and I noticed that a click on a browser link to a drupal page results in the Web server jumping to about 40% of the CPU and mysqld going first to 40%, and then working its way up to 99% (for mysqld). In Internet Explorer, the problem sporadically pops up. In Safari, the problem doesn't happen at all ( the Web server jumps to about 20% of CPU capacity and mysqld barely registers 10%, if anything, if at all.) I've duplicated the problem on two separate machines installed with the same version of Debian Linux (one machine is a backup and offline; the other is a production machine). I never had this problem with 4.4. Any ideas?

Comments

I have kind of the same problem

watson-1 commented 14 November 2004 at 13:32

I have only checked with Firefox 1.0, but it seems that the pages sometimes load half and then hangs for about 5 or 10 seconds until the rest of them are being send to the browser - but it's not all the time - just enough to get me very irritated.

Are you using normal templates or PHP enabled templates?

update: Just forgot to say this... I have not upgraded - This is a fresh installation of 4.5.0

/watson

Probably not Firefox

Angelo Bonadonna commented 14 November 2004 at 14:37

My pages always load all the way, but it takes forever. I'm running on really, really old hardware--a 333 MHz Pentium II with 384 MB of RAM--but I had no problem at all with Drupal 4.4. I'm pretty sure it's not a Firefox problem, because I've now duplicated the problem in four different browsers. I had someone else log in to the site, and Safari was just as afflicted. There must be something in 4.5 that doesn't react well to ancient hardware. I'm really thinking that the Drupal site's problem--which they fixed by getting some new hardware--was the same as mine.... I've got to downgrade till I can get new hardware.... Thanks--Angelo

Thank God for Backups and Quick Restoration...

Angelo Bonadonna commented 14 November 2004 at 16:58

I just downgraded back to 4.4, and my old Pentium II is screaming fast (kinda) in all browsers. I've got to think there's a problem with 4.5. I am, however, looking through the Sunday Best Buy ads for one of those 3 MHz processors machines.... I know I'm overdue for a hardware upgrade. But the old machine handles the load so well in the old version....

Interesting

Dries commented 14 November 2004 at 18:24

Interesting. Any chance you can analyze/debug this further? I'd be happy to cure the problem if you can (help) find out the cause.

Willing to help debug

boreal commented 24 November 2004 at 20:08

I am experiencing the same behaviour with my Drupal system. I'm almost sure it has nothing to do with the browser that's accessing the pages since I've tested it with Mozilla, Firefox and Konqueror.

I started noticing the slowness when I was editting my RSS feeds under the aggregator admin section. The loading of the main aggregator admin page is consistently slow and running top on my server shows the same thing as written above. Basically mysqld uses between 90 and 99% of the CPU for about 8 seconds. That's a long wait.

What can I do to help debug this issue?

update: my server is a 2.4Ghz P4 with 512MB RAM so performance of the hardware shouldn't be the issue.

RSS Feeds Slowing Anyone Down in 4.5?

Angelo Bonadonna commented 29 November 2004 at 05:46

Hmm...I was coming here to apologize for not responding to Dries's generous offer of help, but I'm not all that well-versed in these matters, and I wanted to do whatever sleuthing I could to help out. Typically, I plunge on in and hope things work, and usually they do--eventually--with some critical help here and there.

Anyway, up to a few moments ago when I saw boreal's post, I thought I had narrowed things down a bit, but now I realize I've probably got it wrong. Here's what I did:

I had the opportunity to test three different installations (all Debian Linux systems running Apache 1.29, MySQL 3.23.49, and PHP 4.3.7). The one variable I thought I was testing for was processor speed: one machine had a 333 MHz processor, another a 550 MHz, and the third a 1 GB processor. Well, the problem (MySQL bogging down the process and drupal slowing down but eventually working) occurred on the two slower machines, but not the 1 GB machine.

So I had all the excuse I needed: I rounded up the funds and purchased some new hardware. My main machine (the 333 MHz one) has really needed replacement, so I'm not distressed if its age, in fact, wasn't the problem in this matter. I haven't swapped out the machines yet, so I don't know--maybe my problem IS fixed...

But what got my attention is boreal's mentionof RSS.

When I upgraded to 4.5, I also started using RSS feeds for the first time. Coud RSS or the news aggregator somehow be tied to the problem? In any event, it was a variable I wasn't even considering back when I had 4.5 running. And for some reason, when I tested, I had the two slower machines configured to receive RSS feeds, but not the 1 GB machine. I do have to add, however, that I am now using RSS feeds with the 4.4.2 installation on the 333 MHz machine, and there's no noticeable problem.

Sorry for going on, and not really saying that much. But the RSS feeds issue seems to be a good lead. Once I get my machines swapped out and the drupal installation upgraded, I'll see if I can narrow things down further.

Thanks--as always--really, profound thanks (I sometimes read with sadness some of the impatient or less than kind comments that occasionally (but fortunately not terribly often) get posted in the forums here; there is such generosity behind this project; so many have been helped by it, and I know I speak for many of them when I say bravo and our deepest thanks to all who have contributed to this project.)--Angelo

Some tests to run

boreal commented 30 November 2004 at 04:05

Hi Angelo! Could you explain where you were experiencing the poor performance? I can reproduce my issue every time I access the aggregator admin page. Is there a part in your Drupal system that consistently runs slowly that I could try and test on my system?

Thanks,

boreal

Will get back...

Angelo Bonadonna commented 1 December 2004 at 14:22

Unfortunately, my problem was so extreme, I had to uninstall 4.5 immediately. The loading of MOST pages (but oddly not all) took 10 to 20 seconds. But I have my new hardware just about all configured, and will be doing the switchover this weekend. So I'll definitely be testing then, and will report back. I'm encouraged by your posting, for it suggests that the problem, if it comes back for me, might be solved (at least temporarily) by shutting down the news aggregator and RSS feeds. Those are nice features, but not essential for my setup.

I'm wondering if our problem might be connected to other services we are running out of the same box. The server running drupal for me is a Web server (of course), but it also runs a MOO, POP, FTP, Mailman, and various other incidental services (including bBlog installations for all users and various other PHP-MySQL systems). Its traffic is very light, though (it's used for educational purposes, and at most there are 20 users logged in at peak traffic times. But I do know that when the problem arose, just about all other activities were nil.) Anyway, good luck! (to both of us :) --Angelo

Speed Issues w Drupal 4.5 - Anyone else?

tulula commented 7 January 2005 at 20:57

All of the above posts regarding speed has been my issue for quite some time since upgrading to 4.5 from 4.4.
I initially blamed my host company where they claimed it was nothing on their end.
All browsers I've tested with friends on different computers - new/old, and all claim all of my pages "hang" after loading about 50% and then it takes 5-10 seconds or more to load up thereafter.
I do have a number of modules and tables in my mysql database as still in the inception stage of my site prior to "going live". SO I have no users at his point except myself for testing purposes solely.
I can't afford to lose more speed especially after I plan to go live with my site.

The weather block retrieves

killes@www.drop.org commented 7 January 2005 at 23:31

The weather block retrieves data from an external site. If that site is slow, your site will be slow, too.
--
If you have troubles with a particular contrib project, please consider filing a support request. Thanks. And, by the way, Drupal 4.5 does not work with PHP 5.

--
Drupal services
My Drupal services

It's why I stopped using it.

sepeck commented 8 January 2005 at 01:22

From personal experiance I know that the weather block has caused my entire site to hang. The weather module really needs to have some timeouts added to it so that it fails gracefully without taking out the site.

I should probably submit a feature request or something.

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide}

Culprit was the weather block

Rick Cogley commented 26 January 2005 at 07:27

I was getting really slow performance for some reason, and it turned out to be the weather block. Thanks for the tips.

I also:

* added to my .htaccess:
php_value memory_limit "32M"

* enabled caching

We'll see how those go.

Enabling Weather after the two tweaks makes the site slow again, so I think that is a key thing.

I thought it might be the fact that I loaded 3000 fortune entries into the quotes module, but that does not seem to be the case. ;-)

Regards
Rick Cogley :: Tokyo

Rick Cogley :: rick.cogley@esolia.co.jp
Tokyo, Japan

Not replicable for me.

bryan kennedy commented 7 January 2005 at 22:05

I am not able to generate any signifigant slowdown on my system like people have described here. I have a fairly large drupal site (400+ nodes). I also have 5 different news feeds and do not get any problem on any of the aggregator pages. Now that being said, I am serving on a fairly fast machine:
PowerMac G4 735MHz (or real close to that...can't remember right now) with 512MB RAM. I am runing MySQL also with Apache. Hope this helps clarify this murky problem a little. Is anyone experiencing the slowdown with other DBs?

I am having problems

lunas commented 7 January 2005 at 22:13

I, on the other hand, am experiencing severe problems. I am using Firefox and host my Drupal site on a shared hosting server at Host For Web. My site is quite large, over 10000 nodes and last night had approximately 200 visitors when host for web decided that I was bogging down the server and suspended the account. I don't know if it is related to the hardware or what, but I notice many times when I log in that it will just hang. When I click on another link, the new page will come up (as long as it is not the blog page, or front page)) and I'll be logged in. It seems it hangs whenever it has to try and load pages with a lot of nodes. Sometimes it does it and sometimes it doesn't. Anyways, I realize my slowdown is probably due to my hosting environment and the site is currently being moved to a VPS which hopefully is going to solve my speed problems. I'll have to let you know if it works if they ever get the site moved over. (I really hate when people advertise live help, you use it and they tell you to submit a ticket...)

It seems it hangs whenever

killes@www.drop.org commented 7 January 2005 at 22:16

It seems it hangs whenever it has to try and load pages with a lot of nodes.

This is almost certainly an issue with not enough memory allocated to php.
--
If you have troubles with a particular contrib project, please consider filing a support request. Thanks. And, by the way, Drupal 4.5 does not work with PHP 5.

--
Drupal services
My Drupal services

and

bertboerland commented 8 January 2005 at 08:29

It might be the case that drupal 4.5 hits harder on php's memory. My settings (dedicated for 1 website) in /etc/php.ini is:
memory_limit = 16777216

Check with your hosting provider to let them put the default 8MB to for example 16 (or higher)
--

groets

bertb

--
groets
bert boerland

Okay, site is moved, but...

lunas commented 8 January 2005 at 21:02

I seem to have the same speed issues. I did as suggested and increased the php memory limit (to 32M). Guess it hasn't helped. I hit logout and it hangs. Seeing as it is now on a VPS I checked the CPU usage and the following was listed:

28032 mysql 0 99.9 0.0 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.freejournal.net.pid --skip-locking
13998 mysql 0 99.9 0.0 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.freejournal.net.pid --skip-locking
608 root 0 2.7 0.0 top -n 2 -b -c
1 root 0 0.3 0.0 init

Not sure if that is readable or not, but it says CPU is at 99.9% for whatever process that is. I'm not up on this stuff so if anyone can decipher for me that would be great.

I was hoping the move to the VPS was the answer, guess not. I see other people with busier sites running on much less hardware seemingly without problems. Hopefully someone can help me get my site up to speed. Thanks.

Now that is getting really

killes@www.drop.org commented 8 January 2005 at 21:13

Now that is getting really strange. Apparently some sql queries make the site hang. I suggest to run mysql with slow quereis log enabled in order to find out about slow queries. As an alternative you can install devel module.
--
If you have troubles with a particular contrib project, please consider filing a support request. Thanks. And, by the way, Drupal 4.5 does not work with PHP 5.

--
Drupal services
My Drupal services

Here are the results

lunas commented 9 January 2005 at 05:09

I've installed the devel module. Every page on the site I've tried now takes forever to load.

I am unable to post results here as the comment is terminated because of suspicious data, but the devel module is running on my site at www.freejournal.net so if someone wants to check out the query times, please do.

Any ideas? I see some long query times, not really sure what normal is?

it sped up

lunas commented 9 January 2005 at 06:46

It suddently started speeding up. I disabled the cache and it seems to be coming back to life. Maybe there is a problem with the caching?

memory_limit

lunas commented 9 January 2005 at 08:19

Is memory_limit supposed to show up in php info? In the php.ini file it is right after max execution time but when I run a php info memory_limit is not listed anywhere. I know I'm changing the right php.ini because I can change other values and the changes show up...

I got pre-4.5 CVS to hang

kbahey commented 8 January 2005 at 23:08

I got the same symptom (pages take forever to load) when I upgraded from 4.4.1 to a pre-4.5 CVS version.

It had to do with the new menu tables causing this.

Although it is inconclusive, you can see the details here, and check if it is similar to what you are seeing:

http://drupal.org/node/11115

--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba

More info

lunas commented 9 January 2005 at 17:04

I talked to my hosting provider and he showed me how to enable the slow-server.log. He also noted that mysqld was under severe load. We restarted the mysql and httpd services and the site started to respond very fast. I then hit logout and it hung. Looking at the slow-server.log it indicates:
use freejour_drupal;
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:44:15
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 617 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:46:06
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 709 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:51:25
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 865 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:52:07
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 905 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:52:38
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 925 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:55:06
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 901 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:56:29
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 910 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');
# Time: 050109 10:58:47
# User@Host: freejour_lunas15[freejour_lunas15] @ localhost []
# Query_time: 942 Lock_time: 0 Rows_sent: 1 Rows_examined: 8831
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.type = 'blog' AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0');

I have node_privacy_by_role installed - seems like it may be causing the problem. I will disable and see if it helps.

Yep, Node_privacy_byrole is the problem

lunas commented 9 January 2005 at 17:15

Finally getting somewhere. I disabled node_privacy_byrole and emptied the node_access table setting it back to its default drupal install setting, and low and behold, I hit logout and it logs out. I hit login and it logs in. No hanging, no nothing. So, I re enable the privacy module and load up the front page, hit logout and BANG - hangs again.

Now, what is up with the privacy module because it is kind of necessary on my site...

I'm also seeing this, it's

pyromanfo commented 1 February 2005 at 14:04

I'm also seeing this, it's with taxonomy access control but it's the same result. When node_access is being used the site hangs on those queries, trying to find the total number of nodes in a taxonomy.

Anybody have any ideas on this one? Is there some MySQL settings I haven't tweaked?

I'm running PHP 4.3 and MySQL 4.1.7. Everything is pretty zippy except the above query, which takes literally minutes to complete. All tables are optimized with mysqlcheck to boot. Anything that joins nodes, term_node and node_access seems to take forever.

Me too

Timg-1 commented 14 March 2005 at 03:35

I am running Drupal 4.5.2 with Apache 2 and PHP 4.3.4 on Fedora Core 1.
The box is a 1ghz machine with 384 mb of RAM.
I have a small drupal installation with 1500+ nodes and it seems that the node_provacy_byrole modules just kills my page loads.
My loadavg is 0.00 most of the time so I know my machine is just fine. When I enable the node_privacy_byrole module it jumps to .27 loadavg and the pages seem to load entirely too slowly. They do eventually load, but no where near fast enough. Even when there are relatively few users, i.e. 2 or 3, the loadavg stays around .14. It seems to be tied to whatever the node_privacy_byrole module is doing in the background. Does this module have to run a proc on each node being accessed, and if so, does it store it in the cache. Do I have to empty and renew the cache to speed up the page loads? Whatever it is, it is definetly an issue for me. I would love to use this module, but cannot slow down my site that much.
Any ideas?