MyIsam partition database crash

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

MyIsam partition database crash

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MyIsam partition database crash

Post 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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MyIsam partition database crash

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MyIsam partition database crash

Post by John Cartwright »

Have you tried enabling the slow log to detect any queries taking too long?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MyIsam partition database crash

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MyIsam partition database crash

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MyIsam partition database crash

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MyIsam partition database crash

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MyIsam partition database crash

Post 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.
Post Reply