MySQL, very high server load -- how-to...

[JohnB@]

Hello All:

I am seeing, since over a week, a very high server load on one of our Linux/Plesk 8.2 servers. This comes from a MySQL process. Doing a "top" I see this:

22795 mysql 15 0 186m 35m 4500 S 93.6 1.8 2:41.93 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --u ....

So, MySQL is reading the /etc/my.cnf config file, that's all I see here.
When I look at the processes -- e.g. via "mytop" I see it all seems to be emails?? But I am not really sure ... I do not see that many emails in the main queue. Can someone help me to understand what's going on, and to find the source?

Here is what "mytop" produces:

464 admin localhost test 0 Query show full processlist
463 admin localhost psa 2 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
353 admin localhost psa 6 Query select ip_address, ip_mask from smtp_poplocks where lock_time is NULL
364 admin localhost psa 6 Query select ip_address, ip_mask from smtp_poplocks where lock_time is NULL
451 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
452 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
453 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
454 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
455 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
456 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
457 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
458 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l
459 admin localhost psa 6 Query delete from smtp_poplocks where ((lock_time < DATE_SUB(NOW(), INTERVAL 21 MINUTE)) and (l

(...... and on and on and on .....)



John

 

[yurtesen]

You might want to check how many entries are in this table and also check the table for corruption and fix.

Please let us know if the table is not corrupted and there are not so many entries in it yet you are seeing the problem.

Thanks,
Evren

 

[JohnB@]

Hello All:

I got this solved successfully by "optimizig" the smtp_poplocks table.
Here all steps:

Login as root via SSH:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`
use psa;
OPTIMIZE NO_WRITE_TO_BINLOG TABLE smtp_poplocks;
exit

(see here for an explanation: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html )


John