Today I had very strange problem with MySql on my VPS ( ubuntu 64 9.10 )

Server stoped CPU usage 120% and Disk IO 35k

Only shutdown helped. After rebooting everything was ok.

In log:
Apr 8 10:24:42 localhost dhclient: Listening on LPF/eth0/fe:fd:6d:4a:cc:ad
Apr 8 10:24:42 localhost dhclient: Sending on LPF/eth0/fe:fd:6d:4a:cc:ad
Apr 8 10:24:42 localhost dhclient: Sending on Socket/fallback
Apr 8 10:24:43 localhost mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [Note] Plugin 'FEDERATED' is disabled.
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [Note] Plugin 'InnoDB' is disabled.
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [Warning] Checking table: './mysql/db'
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [ERROR] 1 client is using or hasn't closed the table properly
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [Note] Event Scheduler: Loaded 0 events
Apr 8 10:24:43 localhost mysqld: 100408 10:24:43 [Note] /usr/sbin/mysqld: ready for connections.
Apr 8 10:24:43 localhost mysqld: Version: '5.1.37-1ubuntu5.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
Apr 8 10:24:44 localhost dhclient: DHCPREQUEST of 109.74.204.173 on eth0 to 255.255.255.255 port 67
Apr 8 10:24:44 localhost dhclient: DHCPACK of 109.74.204.173 from 109.74.207.18
Apr 8 10:24:44 localhost dhclient: bound to 109.74.204.173 -- renewal in 42288 seconds.
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2313]: Upgrading MySQL tables if necessary.
Apr 8 10:24:44 localhost ntpdate[2275]: adjust time server 91.189.94.4 offset 0.021933 sec
Apr 8 10:24:44 localhost ntpd[2358]: ntpd 4.2.4p6@1.1549-o Fri Dec 4 19:03:28 UTC 2009 (1)
Apr 8 10:24:44 localhost ntpd[2361]: precision = 1.000 usec
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #0 wildcard, 0.0.0.0#123 Disabled
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #1 wildcard, ::#123 Disabled
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #2 lo, ::1#123 Enabled
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #3 eth0, fe80::fcfd:6dff:fe4a:ccad#123 Enabled
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #4 lo, 127.0.0.1#123 Enabled
Apr 8 10:24:44 localhost ntpd[2361]: Listening on interface #5 eth0, 109.74.204.173#123 Enabled
Apr 8 10:24:44 localhost ntpd[2361]: kernel time sync status 2040
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2317]: Looking for 'mysql' as: /usr/bin/mysql
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2317]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2317]: This installation of MySQL is already upgraded to 5.1.37, use --force if you still need to run mysql_upgrade
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2363]: Checking for insecure root accounts.
Apr 8 10:24:44 localhost ntpd[2361]: frequency initialized -2.312 PPM from /var/lib/ntp/ntp.drift
Apr 8 10:24:44 localhost /etc/mysql/debian-start[2378]: Triggering myisam-recover for all MyISAM tables
Apr 8 10:24:47 localhost snmpd[2670]: NET-SNMP version 5.4.1
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [ERROR] /usr/sbin/mysqld: Table './blogovet/wp_ak_popularity' is marked as crashed and should be repaired
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [Warning] Checking table: './blogovet/wp_ak_popularity'
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [ERROR] /usr/sbin/mysqld: Table './blogovet/wp_comments' is marked as crashed and should be repaired
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [Warning] Checking table: './blogovet/wp_comments'
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [ERROR] /usr/sbin/mysqld: Table './blogovet/wp_options' is marked as crashed and should be repaired
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [Warning] Checking table: './blogovet/wp_options'
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [ERROR] /usr/sbin/mysqld: Table './blogovet/wp_sabre_table' is marked as crashed and should be repaired
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [Warning] Checking table: './blogovet/wp_sabre_table'
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [ERROR] /usr/sbin/mysqld: Table './cacti/host' is marked as crashed and should be repaired
Apr 8 10:24:48 localhost mysqld: 100408 10:24:48 [Warning] Checking table: './cacti/host'

Did anybody have such problem?

Looks like your mysql server crashed and didn't close tables properly run this command


mysqlcheck -A -a -c -o -g --auto-repair -u root -p

It checks all tables, analyses them, checks them, optimises them, checks if they need upgrading, repairs them and you log in as root with your mysql password.

MySQL needs to be running at the time, but you should stop any applications from accessing it until you've run that.

Thanks for reply!

I've run this command And I hope mysql will be works correctly now.

(But When I start mysql I see this message still:
* Checking for corrupt, not cleanly closed and upgrade needing tables.
)

Maybe it is not big problem now.

That's normal it just means your init script checks tables at server start (which is a good idea)

I have the exact same problem however in my case a reboot wont help and cpu is still at 100% since hours.
I refrain to run the fix command till it stops if will never stop...
what do you advice? (I've just upgraded to lucid but that didn't help)

i found this bug and indeed and that solution worked
https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/105457/comments/14
incredible it's still not fixed

Weird, thank god I compile my own.

err actually I said it to early... cpu up to 100% again and it's not mysql_safe it's mysqld...
what do you suggest? really compile my own???

how do I know if is going loop for a bug or it's actually doing leggitimate work? the problem is that it wont even let me login to the mysql console

No you shouldn't have to compile your own.

What's your load average (use the uptime command to find out).

What happens when you try to log in via the mysql cli?

so I found out it's actually postfix causing problems I see
mysql:/etc/postfix/mysql_virtual_domains_maps.cf(0,lock|fold_fix): table lookup problem
in mail.log. don't know if is this is causing problem.. I've read that it could because postfix run chrooted, but it was working fine...
(removing chroot didn't help)
what to try next?

Try running "flush privileges" on your mysql cli, your postfix error means it can't connect to the server, also check the login info you've given postfix for mysql is correct.

thanks for following..
flushing privileges doesn't change nothing mysql goes 100% as soon as I start postfix
I verified the settings I can make the same query on the mysql client with these permission/user and was working *before*

OK not sure then I don't use postfix with mysql, try asking on serverfault.com

I've found the problem.
I've used Nginx + Apache and some times I've the problem "Server stoped CPU usage 120% and Disk IO 35k"
Looks like it is bug. Apache create too many processes.

Now I use only Nginx + FastCGI and have no problems.

Apache creating lots of processes is normally due to a high MaxClients in the apache config, 10 should be sufficient.