MyIsam partition database crash
Moderator: General Moderators
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
MyIsam partition database crash
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.
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
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?
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?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MyIsam partition database crash
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.
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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: MyIsam partition database crash
Have you tried enabling the slow log to detect any queries taking too long?
Re: MyIsam partition database crash
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.jaoudestudios wrote:CPU usage of MySQL is about 20%.
Re: MyIsam partition database crash
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.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MyIsam partition database crash
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?
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
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.
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.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MyIsam partition database crash
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.
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.