CPU usage at 90%+

Asked by haddow777 on 2012-07-01

Hi there. I am kind of new to MariaDB. I had it install on my virtual server for about a month now. I am using it for my wordpress site. Recently I went to install something on my server and I noticed that mariadb is using almost all of one of the CPU's. I am using the latest debian package release of mariadb. I think it is 5.5.24.

I didn't change any settings on it. I checked the syslog and it mentioned that a bunch of my wordpress tables had crashed and needed to be fixed. Also, some debian maintenance account had been denied access a number of times. The site is working, although quite slowly.

What procedure do I use to find out why the database is killing my CPU? Is this a possible bug?

Thank you.

Question information

Language:
English Edit question
Status:
Solved
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Solved by:
haddow777
Solved:
2012-07-03
Last query:
2012-07-03
Last reply:
2012-07-02
haddow777 (haddow777) said : #1

A bit more information. I tried to restart the server. All attempts to shut it down failed with the exception of kill -9. Actually, I didn't try kill by itself. Anyways, the log messages are from after I killed the server, so they are probably related to my killing it.

The problem persisted even after restarting it. CPU still in high usage. Server can't be shutdown through service command. The website still works (slowly) and phpMyAdmin works. I killed anything that has access to the database (webmin and php-fpm) and still it continued like before.

Arjen Lentz (arjen-lentz) said : #2

A "crashed table" needs to be repaired, until then MySQL will mark it as read-only.
Restarting or even worse, murdering mysqld with -9, will not actually fix your problem.

Regarding the Debian maintenance account, you may have changed or removed the password for that login. On Debian and Ubuntu based systems, that account is important as it allows startup scripts to care of upgrade and recovery tasks. Since I don't know exactly what you did in that arena, I can't provide you with an exact answer on how to fix it.

I presume that you are using MyISAM tables, and at some point rebooted your server without shutting down mysqld. Consequentially, the MyISAM index files will be corrupted - that's entirely fixable.
See REPAIR TABLE (and the mysqlcheck command line tool) for sorting out the actual "crashed tables" issue directly.

haddow777 (haddow777) said : #3

Okay, I've been reading the docs and coming up with nothing so far. I can shut the server down through mysqladmin. I found how to read the binary logs, but I couldn't find anything obvious there. Loads of Wordpress queries, but it all seemed fine. I did skim them though.

I check the process list and the only process was me checking the process list. The CPU has remained constantly high for at a minimum of hours now. I cannot find even what is causing the server to be working hard. One funny thing is that the database server uses 19.1% of my system memory (512MB server) even though the only thing in the database is a Wordpress site with a handful of posts and plugins. I have a hard time believing that the memory usage is normal.

< This next part is after I read Arjen Lentz's message >

Okay, so I used the mysqlcheck command and it states that all the tables are ok. I had previously tested the database by writing a comment on my WordPress site and it worked fine (although slowly).

So the tables seem to be fine (not sure why though if I crashed the server) and the tables aren't read only. I used the repair table command an a bunch of the tables and it just returns okay. I even checked the databases that comes with the DB installation and they were fine.

Also, since yesterday, the memory footprint of MariaDB is now 19.4%. It's not a huge increase, but I don't know why it is going up when nothing really has been done on my website.

Also, I didn't change any user information. I only added the user for my WP site.

In case you are wondering, I do have a firewall up. I only allow a few connections from outside the firewall through. Varnish on port 80, webmin and sshd on non-standard ports. That's about it.

Thanks for the help.

haddow777 (haddow777) said : #4

Okay, so I don't know how smart this was. I uninstalled MariaDB by removing all the packages. I then reinstalled it. The database tables are all still there. The server is now 5.5.25 and the debian-start account is working again. Well, at least it was according to syslog.

The problem persists. The CPU is still being used up and I can't for the life of me see why. There aren't any problems with the tables. There doesn't seem to be any data sent in or out of the DB. It is just spinning its wheels for some reason. I have been trying some different tools, but I still haven't found a way to even see what the server is doing. According to everything it is just sitting there silent, except top shows it working the CPU over.

I've even got an email from my server provider saying my server is tripping alarms because it has had such high CPU usage for so long.

Is there some tool where I can see what the heck the server is doing? I have checked the processlist in mysqladmin, but it simply says that the only process is me checking for processes.

Any help would be appreciated.

Elena Stepanova (elenst) said : #5

Just to rule out the obvious, since the timing of the question is remarkable -- can it have something to do with the leap second problem? (http://www.sheeri.com/content/mysql-and-leap-second-high-cpu-and-fix)

haddow777 (haddow777) said : #6

Thank goodness. What a nightmare. I restarted my server and now it seems to be fine. I guess it wasn't the DB server. So annoying.

I am going to keep that link on file.

Thanks.