By Anonymous (not verified) on

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  F10Quit

Does anyone have any suggestions to further diagnose problem? - our Drupal provider hasn't been must help...

⋅ Categories: Drupal 6.x

Comments

coreyp_1’s picture

coreyp_1 commented

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

Anonymous’s picture

Anonymous (not verified) commented

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?

vm’s picture

vm commented

To check, disble the block and if necessary the module that is producing that block and check site and log

nerkn’s picture

nerkn commented

+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