Drupal 4.7.2
MySQL 4.1
Php 4.3
Server: linux
Hosting Services: VPS account @ user: bfnnetwo_dpl1
Hi All,
Thank you for taking a minute of your time to review my post.
Question = How do we eliminate open Query processes which seem to lock/shut down our MySQL server & SITE (see copy of open processes below as provided by host)
Brief Issue Background:
Experiencing slow site processing which quickly escalates, after adding a banner for instance, to the site not displaying at all (i.e. blank screen) with our Mysql database server seeming to be locking.
Initially after contacting host of site problem, host informed that our Mysql server was down and would require a restart. After restart of Mysql server and after executing another "banner add", the site again returns a blank white page.
This seems to happen each time now, so much so, now I just open a ticket with host telling them to restart MySQL server.
Increasingly, the necessity of having to open a ticket with host each time we add a banner or some other configuration action seemed abnormal. As such, I informed the host that our site was not "live" at this point and should not be suffering from any congestion issues!!!
The host stated that our site was really heavy and had many open Query processes. The open Query processes have me a bit puzzled as not sure where/how they are coming about and why with a "non-live" site and VPS service, our MySQL shouts down as a result.
(1) Action Taken:
Disabled locale module as it appears there have been some past issues with this module using an high level of CPU in its translation processes.
----- Result: Our MySQL database server continues to shout down/lock
HOST COMPANY HAS PROVIDED THE BELOW DETAILED LISTING OF OPEN QUERY FROM SERVER LOG:
========== MESSG/HOST SERVER LOGS PROVIDED BY HOST========
The issue is still due to the heavy usage of the mySQL server. You can find below the mySQL processes currently running on your VPS:
root@host [~]# ps -aux | grep "mysql"
Warning: bad syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
root 15463 0.0 0.0 2000 980 ? S 04:59 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid
mysql 15485 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15487 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15489 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15520 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15553 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15584 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15616 0.0 0.3 133304 29532 ? S 04:59 0:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15617 0.0 0.3 133304 29532 ? S 04:59 0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15618 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15619 0.0 0.3 133304 29532 ? S 04:59 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15852 1.9 0.3 133304 29532 ? R 04:59 7:51 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 32247 2.5 0.3 133304 29532 ? R 06:44 7:20 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 4030 8.6 0.3 133304 29532 ? R 10:14 7:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 7826 8.5 0.3 133304 29532 ? R 10:14 6:51 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 10317 8.3 0.3 133304 29532 ? R 10:15 6:40 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 11187 8.2 0.3 133304 29532 ? R 10:15 6:32 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 11473 8.0 0.3 133304 29532 ? R 10:16 6:25 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 13044 8.0 0.3 133304 29532 ? R 10:16 6:19 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 14155 7.9 0.3 133304 29532 ? R 10:17 6:13 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 15922 7.9 0.3 133304 29532 ? R 10:18 6:09 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 16631 7.0 0.3 133304 29532 ? R 10:18 5:24 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 17839 7.2 0.3 133304 29532 ? R 10:19 5:31 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 30994 7.6 0.3 133304 29532 ? R 10:23 5:28 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 3264 7.6 0.3 133304 29532 ? R 10:24 5:21 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 6434 7.6 0.3 133304 29532 ? R 10:25 5:18 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 7006 7.5 0.3 133304 29532 ? R 10:25 5:16 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 8283 0.0 0.3 133304 29532 ? S 10:26 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 8450 0.0 0.3 133304 29532 ? S 10:26 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 10499 0.0 0.3 133304 29532 ? S 10:27 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 12620 0.0 0.3 133304 29532 ? S 10:29 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 13211 0.0 0.3 133304 29532 ? S 10:29 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 18028 0.0 0.3 133304 29532 ? S 10:30 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 25002 0.0 0.3 133304 29532 ? S 10:33 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 26927 0.0 0.3 133304 29532 ? S 10:33 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 26954 0.0 0.3 133304 29532 ? S 10:33 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 4858 0.0 0.3 133304 29532 ? S 10:38 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 5649 0.0 0.3 133304 29532 ? S 10:38 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 7778 0.0 0.3 133304 29532 ? S 11:19 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 8768 0.0 0.3 133304 29532 ? S 11:19 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 10850 0.0 0.3 133304 29532 ? S 11:20 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 12245 0.0 0.3 133304 29532 ? S 11:20 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 12337 0.0 0.3 133304 29532 ? S 11:20 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 12584 0.0 0.3 133304 29532 ? S 11:20 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 14132 0.0 0.3 133304 29532 ? S 11:21 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 14815 0.0 0.3 133304 29532 ? S 11:33 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
mysql 16447 0.0 0.3 133304 29532 ? S 11:34 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.siteground-vps10.com.pid --skip-locking
root 19397 0.0 0.0 1508 480 pts/0 R 11:35 0:00 grep mysql
-------
The processes are spawned because of the mySQL queries that are being made to the server. I've managed to list them:
root@host [~]# mysqladmin processlist
+------+----------------+-----------+----------------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------------+-----------+----------------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| 1747 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 5101 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1750 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 5069 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1755 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 5037 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1759 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 5005 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1763 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4971 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1774 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4936 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1778 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4904 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1781 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4872 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1785 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4831 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1791 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4798 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1824 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4494 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1827 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4463 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1831 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4430 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1835 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4396 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1838 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4360 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1841 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4328 | statistics | SELECT DISTINCT(b.vid), b.nid, b.chance, b.cache, b.views, b.views_max, b.views_day, b.views_day_max |
| 1844 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4296 | Locked | UPDATE node SET nid = 15, vid = 15, title = 'CareerBuilder-468x60-cj', type = 'banner', uid = 1, sta |
| 1850 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4234 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1856 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4173 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1861 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4141 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1869 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 4071 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1880 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 3926 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1884 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 3872 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1885 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 3871 | Locked | SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moder |
| 1907 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 3623 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 1911 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 3591 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2104 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1160 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2107 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1130 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2111 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1115 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2116 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1082 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2118 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1066 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2119 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1066 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2122 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 1033 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2179 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 296 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2181 | bfnnetwo_drpl1 | localhost | bfnnetwo_drpl1 | Query | 264 | Locked | SELECT DISTINCT(n.nid), n.title, n.type, n.status, n.moderate, e.event_start FROM node n INNER JOIN |
| 2207 | root | localhost | | Query | 0 | | show processlist |
+------+----------------+-----------+----------------+---------+------+------------+------------------------------------------------------------------------------------------------------+
As you can see, the issue is due to the fact that those queries are way too heavy for the VPS and the execution of each is either stalled or is taking way too much. The first query at this moment is taking ~5000 secs. and is not yet completed.
There's definitely something wrong with the script, but since I'm not a developer myself, I can only recommend you to contact the Drupal developers and ask them for help. I'm sure they will be able to assist you.
========== MESSG FROM HOST HOST==========
‹ Segmentation Fault with 4.7 FileMaker Launched Step One Towards PHP Support ›
» add new comment
