Page 1 of 1

MyIsam partition database crash

Posted: Wed Aug 26, 2009 4:07 am
by jaoudestudios
I am doing a massive overhaul and moving to partition tables. It all runs fine, but when the connections get near to 200 they start rising exponentially and then the mysql server dies. After I restart it, it seems fine for a while then it starts to build up again and boom same old.

The box is not under much load, there is plenty of resources available.

Any ideas would be much appreciated.
Thanks.

Re: MyIsam partition database crash

Posted: Mon Aug 31, 2009 4:10 am
by Benjamin
Well this could be tricky but I would start with the basics. Have you tried increasing the key_buffer_size in my.cnf? I'm guessing that queries are locking the table, causing subsequent queries to stack. Since the queries are coming in faster than they can be processed, the server will eventually become overloaded.

What is the CPU usage of MySQL while this occurs?
Have you checked the MySQL error log to see if anything is in there?
Have you monitored the server via MySQL Administrator to see what queries are locking the tables?

Re: MyIsam partition database crash

Posted: Mon Aug 31, 2009 12:00 pm
by jaoudestudios
Thanks Astions.

I appreciate it could be a tricky one.

Key_buffer_size is pretty big. The box is doing nothing else, just that 1 database - and there is plenty of resources left. CPU usage of MySQL is about 20%. Nothing in the error logs :(. I will have to install MySQL administrator to see if it can provide anymore information.

Re: MyIsam partition database crash

Posted: Mon Aug 31, 2009 12:04 pm
by John Cartwright
Have you tried enabling the slow log to detect any queries taking too long?

Re: MyIsam partition database crash

Posted: Mon Aug 31, 2009 1:49 pm
by Benjamin
jaoudestudios wrote:CPU usage of MySQL is about 20%.
If MySQL is at 20% CPU usage under a heavy load it's maxing out disk reads. Either MySQL or the box itself may be running out of ram. MySQL under a heavy load should have a high CPU usage. There may be other my.cnf settings that need to be changed to increase memory.

Re: MyIsam partition database crash

Posted: Tue Sep 01, 2009 5:34 am
by Benjamin
Also, even with a healthy key_buffer_size, resource intensive queries, or poorly written/indexed queries can begin to stack as well. Even if they only run every 15 minutes, if they start taking 16 minutes to complete with multiple joins, you'll have tables persistently locked all over the place.

Re: MyIsam partition database crash

Posted: Tue Sep 01, 2009 5:39 am
by jaoudestudios
Slow query logs are empty. It does not appear that any queries take more than a couple of seconds to run.

These servers are new high spec, so there is plenty of resources available (12 GB ram, 8 core etc). But I am not sure about I/O on the hard disk. How can I check the hard disk usage?

It is running as a VM on the server though, could that be a problem?

Re: MyIsam partition database crash

Posted: Tue Sep 01, 2009 5:49 am
by Benjamin
When it's crashing, view the queries using MySQL administrator. You'll probably need to enable remote connections to MySQL in my.cnf and possibly unblock the port at the firewall. You'll be able to see what queries are locking the tables, if that is indeed the problem. As far as the disk i/o, you may find some good tools with this google search:

http://www.google.com/search?q=linux+di ... =firefox-a

Keep in mind though, I'm pretty much shooting in the dark here. It could be something crazy like some out of control code in a while loop opening connections.

Re: MyIsam partition database crash

Posted: Tue Sep 01, 2009 5:58 am
by jaoudestudios
I will take a look at that google link, thanks!

Yeah I appreciate you are shooting in the dark, but bouncing ideas around and picking your brain does seem productive.

I still dont think it is a code issue as the code worked before on our previous platform that was not even a quarter of this power. So I am assuming it is a server config issue.