crazy mysql overload

Status
Not open for further replies.
S

Secret Agent

Guest
My server hit an overload of 140+. This is crazy. I checked the cpu / memory / mysql usage and showed this in red:

mysql 141.25 24.30 0.0
Top Process %CPU 99.9 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306
Top Process %CPU 99.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306
Top Process %CPU 97.4 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.domain.com.pid --skip-locking --port3306

What is that stuff?
 
S

Secret Agent

Guest
Can someone please help. Still happening right now and causing Exim to crash as well
 
S

Secret Agent

Guest
These are the running MySQL processes as well:

+------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1069 | eximstats | localhost | eximstats | Sleep | 42 | | |
| 2171 | eximstats | localhost | eximstats | Sleep | 369 | | |
| 2306 | leechprotect | localhost | leechprotect | Sleep | 331 | | |
| 3091 | studio | localhost:49923 | hosting | Sleep | 96 | | |
| 3196 | antro_agbook1 | localhost | antro_agbook1 | Query | 96 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3253 | antro_agbook1 | localhost | antro_agbook1 | Query | 79 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3255 | antro_agbook1 | localhost | antro_agbook1 | Query | 78 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3275 | antro_agbook1 | localhost | antro_agbook1 | Query | 73 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3277 | antro_agbook1 | localhost | antro_agbook1 | Query | 73 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3286 | antro_agbook1 | localhost | antro_agbook1 | Query | 71 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3342 | antro_agbook1 | localhost | antro_agbook1 | Query | 55 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3360 | antimon_genel | localhost | antimon_vbulletin | Sleep | 46 | | |
| 3379 | antro_agbook1 | localhost | antro_agbook1 | Query | 43 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3394 | antro_agbook1 | localhost | antro_agbook1 | Query | 39 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3395 | antro_agbook1 | localhost | antro_agbook1 | Query | 39 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3477 | kalistyl_admin | localhost | kalistyl_community | Sleep | 21 | | |
| 3500 | antro_agbook1 | localhost | antro_agbook1 | Query | 13 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3512 | antro_agbook1 | localhost | antro_agbook1 | Query | 11 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3518 | antro_agbook1 | localhost | antro_agbook1 | Query | 10 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3519 | antro_agbook1 | localhost | antro_agbook1 | Query | 10 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
| 3554 | root | localhost | | Query | 0 | | show processlist |
+------+----------------+-----------------+--------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
 

Leandro

Well-Known Member
Sep 23, 2003
48
0
156
Argentin
cPanel Access Level
DataCenter Provider
I have the same problem in two diferentt servers,

Top Process %CPU 99.9 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking
Top Process %CPU 99.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking
Top Process %CPU 80.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/domain.net.pid --skip-locking

Any fix ?? :confused:
 
Last edited:

eger

Well-Known Member
Feb 28, 2003
128
0
166
I am also having the same issue on one of my servers. A simple query is stuck on "Copying to tmp table" for about 10+ minutes while mysql is using 99% CPU.

I found a big article on mysql.com about it. But there is no relevant fix anywhere in the posts as far as I can tell. Just a bunch more confirming the problem: http://forums.mysql.com/read.php?24,39066,39066#msg-39066
 

eger

Well-Known Member
Feb 28, 2003
128
0
166
I haven't found a fix for this. The only thing I could do was disable the advanced guestbook and contact a customer also having this problem in his mysql application.
 

kship

Member
Oct 29, 2004
23
0
151
CROCKETT, TEXAS
cPanel Access Level
Root Administrator
advanced guestbook

I removed all instances from hosts directories and completely uninstalled the advanced guestbook and the levels seemed to have dropped. The guestbook is something that should not be used!
 

Manuel_accu

Well-Known Member
Jun 19, 2005
191
0
166
Yes, this type of issue I have faced

You make have select or update query running with many distinct and join which is hanging while fetching data from your database. Either you can alter this query or kill that particular mysql processes.
 

wardv

Member
Aug 10, 2005
15
0
151
I think I've got a fix!

For every agbook database, add an extra INDEX:

ALTER TABLE `book_com` ADD INDEX `id` ( `id` )

(for phpmyadmin users: go to the table boom_com, create an index with the name 'id' on the column 'id').

When an extra INDEX is added, the MySQL Bug isnt triggered (tested using mysql 4.1.21).
 
Status
Not open for further replies.