Hello Everyone;
We've just recently deployed our Drupal website to a hosting service. Its not a particularly large Drupal site(a few hundred nodes) - and doesn't get more than 200 visitors a day.
Our hosting package include: VPS running ubuntu-8.04-x86 server - 1GIG of RAM and has 1000 Mhz CPU allocated to it. With 1000 GB Traffic limit.
Its only been a few days and we are having a difficult time keeping the our Drupal site up at all. The site runs fine for 10mins or so then comes to a come crawl.
Just watching htop - there are mysql processes that come up that never complete and after awhile they eat up all the CPU to 100%.
Below is a capture of the processes:
root:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
1 [||||||||||||||||||||||||||||||||||||||||||||100.0%] Tasks: 61 total, 6 running
2 [||||||||||||||||||||||||||||||||||||||||||||100.0%] Load average: 4.52 3.38 3.68
Mem[||||||||||||| 249/1024MB] Uptime: 21:43:45
Swp[ 0/0MB]
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
20105 mysql 15 0 131M 22080 5280 S 100. 2.1 4:42.58 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld
25932 mysql 19 0 131M 22080 5280 R 98.4 2.1 0:33.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --
20120 mysql 17 0 131M 22080 5280 R 59.0 2.1 2:45.98 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --
23782 mysql 17 0 131M 22080 5280 R 45.9 2.1 1:23.47 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --
7196 root 15 0 10312 2936 2328 S 0.0 0.3 0:01.69 sshd: root@pts/0
10065 root 15 0 2252 1136 888 R 0.0 0.1 0:59.79 htop
28216 root 18 0 2692 1304 1052 S 0.0 0.1 0:00.03 vzl [servicem]
27849 www-data 16 0 58636 35660 3880 S 0.0 3.4 0:00.70 /usr/sbin/apache2 -k start
27852 www-data 15 0 50612 28548 4112 S 0.0 2.7 0:00.81 /usr/sbin/apache2 -k start
22476 www-data 15 0 48684 27412 4904 S 0.0 2.6 0:16.78 /usr/sbin/apache2 -k start
21586 www-data 15 0 46844 24916 4232 S 0.0 2.4 0:06.83 /usr/sbin/apache2 -k start
12031 www-data 16 0 58600 36928 4900 S 0.0 3.5 0:08.97 /usr/sbin/apache2 -k start
20116 mysql 15 0 131M 22080 5280 S 0.0 2.1 0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --
25886 www-data 16 0 47040 24932 4064 S 0.0 2.4 0:01.26 /usr/sbin/apache2 -k start
25884 www-data 15 0 46768 24636 4068 S 0.0 2.3 0:00.66 /usr/sbin/apache2 -k start
5908 www-data 16 0 59096 37404 4884 S 0.0 3.6 0:16.07 /usr/sbin/apache2 -k start
9264 root 15 0 10316 2936 2328 S 0.0 0.3 0:00.48 sshd: root@pts/1
20106 root 24 0 1612 512 440 S 0.0 0.0 0:00.00 logger -p daemon.err -t mysqld_safe -i -t mysqld
9286 root 15 0 2760 1548 1216 S 0.0 0.1 0:00.01 -bash
15784 root 18 0 31660 12400 7204 R 0.0 1.2 0:00.33 /usr/sbin/apache2 -k start
5784 bind 18 0 41900 8120 1896 S 0.0 0.8 0:00.03 /usr/sbin/named -t /var/named/run-root -c /etc/named.conf -
24520 syslog 17 0 1844 648 500 S 0.0 0.1 0:00.74 /sbin/syslogd -u syslog
15800 www-data 18 0 22144 3296 476 S 0.0 0.3 0:00.03 /usr/sbin/apache2 -k start
9886 root 17 0 2008 836 656 S 0.0 0.1 0:00.03 /usr/sbin/cron
7849 qmails 15 0 1672 504 408 S 0.0 0.0 0:00.07 qmail-send
1 root 15 0 1960 684 584 S 0.0 0.1 0:00.04 init [2]
F1Help F2Setup F3SearchF4InvertF5Tree F6SortByF7Nice -F8Nice +F9Kill F10QuitDoes anyone have any suggestions to further diagnose problem? - our Drupal provider hasn't been must help...
Comments
I am the first to admit that
I am the first to admit that I am not a server optimization guru, but I do have a few questions:
1. do you have access to the slow query log?
2. what versions of php/mysql are we dealing with?
3. are you connecting using mysql or mysqli?
4. have you repaired the database?
Maybe something in the answers to these questions will help out.
-Corey
- Corey
http://www.thefreecollege.com
More Info.
Hey Corey:
Here is what slow query log is showing:
User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 286 Lock_time: 0 Rows_sent: 5 Rows_examined: 3355663
/* Anonymous : similarterms_list */ SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'es' WHERE (n.language ='es' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( tn.tid IN (10,67,105,125,138,139) AND n.type IN ('event','link_directory_item','news','teaching_resource') AND n.nid != 4868 AND n.status = 1 AND n.moderate = 0 )GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 5;
# User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 640 Lock_time: 0 Rows_sent: 5 Rows_examined: 8120107
/* Anonymous : similarterms_list */ SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'es' WHERE (n.language ='es' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( tn.tid IN (11,15,68,78,108,135,138) AND n.type IN ('event','link_directory_item','news','teaching_resource') AND n.nid != 4639 AND n.status = 1 AND n.moderate = 0 )GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 5;
# User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 353 Lock_time: 0 Rows_sent: 5 Rows_examined: 8149966
/* Anonymous : similarterms_list */ SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'fr' WHERE (n.language ='fr' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( tn.tid IN (168,44,15,50,109,135,138) AND n.type IN ('event','link_directory_item','news','teaching_resource') AND n.nid != 4754 AND n.status = 1 AND n.moderate = 0 )GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 5;
# User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 439 Lock_time: 0 Rows_sent: 5 Rows_examined: 8756807
/* Anonymous : similarterms_list */ SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'fr' WHERE (n.language ='fr' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( tn.tid IN (11,15,50,92,128,138) AND n.type IN ('event','link_directory_item','news','teaching_resource') AND n.nid != 4794 AND n.status = 1 AND n.moderate = 0 )GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 5;
# User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 329 Lock_time: 0 Rows_sent: 1 Rows_examined: 1960564
/* Anonymous : pager_query */ SELECT COUNT(DISTINCT(n.nid)) FROM node n INNER JOIN term_node tn0 ON n.vid = tn0.vid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'es' WHERE (n.language ='es' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( n.status = 1 AND tn0.tid IN (138));
# User@Host: cigi_pna[cigi_pna] @ localhost []
# Query_time: 1240 Lock_time: 0 Rows_sent: 5 Rows_examined: 8120107
/* Anonymous : similarterms_list */ SELECT n.nid, n.title, COUNT(n.nid) AS ncount FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'fr' WHERE (n.language ='fr' OR n.language ='' OR n.language IS NULL OR (n.language = 'en' AND i18n.nid IS NULL)) AND ( tn.tid IN (11,15,68,78,108,135,138) AND n.type IN ('event','link_directory_item','news','teaching_resource') AND n.nid != 4639 AND n.status = 1 AND n.moderate = 0 )GROUP BY n.nid, n.title, n.created ORDER BY ncount DESC, n.created DESC LIMIT 5;
I'm only showing a few - but there are many more of these entries.
We are running PHP Version:
PHP 5.2.4-2ubuntu5.3 with Suhosin-Patch 0.9.6.2 (cli) (built: Jul 23 2008 06:44:49)
And MySql:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
For questions:
3. are you connecting using mysql or mysqli?
(How do I find this information?)
4. have you repaired the database?
(How do I go about this?)
Thank-you for your help so far - there seems to be an obvious problem with "similarterms" block we are using. Isn't the "Rows_examined" seem exuberantly high to anyone?
=-=
To check, disble the block and if necessary the module that is producing that block and check site and log
I have the same problem too.
+1
with 22000 nodes and 10+ users at the same time.
I got this query
SELECT COUNT(*) FROM node n INNER JOIN users u ON n.uid = u.uid LEFT JOIN node i18n ON n.tnid > 0
şarkı sözleri