Portal Home > Knowledgebase > Articles Database > [MySQL] Too Many Connections: Locked Queries
[MySQL] Too Many Connections: Locked Queries
| Posted by Mitsurugi, 12-17-2008, 06:07 AM |
| MySQL] Too Many Connections: Locked Queries
This has happened twice now. I have my wait_timeout variable set to 60 seconds, but occasionally my database gets overloaded with "locked" queries. Here's an example of my processlist:
Code:
| 14773848 | db_name | localhost | db_name | Query | 35483 | statistics | SELECT COUNT... |
| 14776290 | db_name | localhost | db_name | Query | 35144 | statistics | SELECT COUNT... |
| 14778877 | db_name | localhost | db_name | Query | 34841 | statistics | SELECT COUNT... |
| 14790744 | db_name | localhost | db_name | Query | 33056 | Locked | UPDATE... |
| 14791275 | db_name | localhost | db_name | Query | 32977 | Locked | SELECT... |
| 14791395 | db_name | localhost | db_name | Query | 32960 | Locked | SELECT... |
| 14791556 | db_name | localhost | db_name | Query | 32929 | Locked | SELECT... |
| 14791603 | db_name | localhost | db_name | Query | 32924 | Locked | SELECT... |
| 14791757 | db_name | localhost | db_name | Query | 32900 | Locked | SELECT... |
| 14792209 | db_name | localhost | db_name | Query | 32815 | Locked | SELECT... |
| 14792355 | db_name | localhost | db_name | Query | 32786 | Locked | SELECT... |
This continues on with locked queries all the way up to my limit of 450 connections, then the whole server goes down. Note the time spent processing these queries, 35483 seconds and counting! Surely the wait_timeout is supposed to prevent this from happening?!
Also, this appears to start with queries in the State "statistics". Then the State for the following queries becomes "locked". Does anyone know what these mean? Can I get them to timeout normally? Queries which never timeout are guaranteed to bring your server down eventually, surely this is not the desired behaviour.
Please help!
|
| Posted by vinod, 12-17-2008, 10:52 AM |
| you will need to optimise your queries. You can view the complete query from the command " mysqladmin -v processlist " . From the o/p take the query and go to the mysql promt, then use the ' explan $query to see what the query is it doing.
I believe the state Locked means the tables / data is presently used by some other process and this certain query is waiting for them to be freed.
Vinod.
|
| Posted by Mitsurugi, 12-17-2008, 10:59 AM |
| The point is, they stay locked forever. How can they be made to timeout?
|
| Posted by vapetrov, 12-17-2008, 08:10 PM |
| Just set variable max_user_connections=64 in my.cnf
The will allow 64 connections per user and protect whole server.
__________________
Private remote administrator of Linux servers - www.petrov.ks.ua
Quality hosting - Host-Web-Site.com
|
| Posted by Jonathan Kinney, 12-18-2008, 01:47 PM |
| I am not sure that will help his MySQL server to keep functioning, but yes, I think that would be the first step to keeping his entire server up which helps to be able to continue to diagnose the problem. Next step would be to figure out why the queries are processing so long, and if it is not completing the query, find the source and disable it.
__________________
Jonathan Kinney
Data Systems Specialist
Advantagecom Networks, Inc.http://www.simplywebhosting.com
|
Add to Favourites 
Print this Article
Also Read
for resellers (Views: 711)
Shared Hosting
Shared Hosting
