Portal Home > Knowledgebase > Articles Database > MySQL 'freezes' every few hours
MySQL 'freezes' every few hours
| Posted by Xandrios, 07-09-2007, 08:32 PM |
| Hi,
Im running MySQL 5.0.32 on a Debian Etch server. I recently moved a couple sites from a Debian Sarge (with MySQL 4.1) server over, and now MySQL has become very unstable.
Every few hours mysql just hangs on a query. All the following queries are queued and never get executed. The only way to get things running again is forcefully killing and restarting the mysql process. After that things work again...for a few hours.
The logs (error and query log) show nothing exceptional. When it hangs and I do a 'show processlist;' I noticed that everytime a SELECT....IN (a,b,c) query is holding up everything. Could the 'IN ()' be the reason for mysql not completing the query and holding up the rest?
Another option might be to temp. go back to MySQL 4.1...but that is going to be a real problem on Debian Etch
|
| Posted by wKkaY, 07-10-2007, 02:21 AM |
| When it happens again, look not for the most popular query, rather.. look for the query with longest time spent. What is it?
|
| Posted by SparkSupport, 07-10-2007, 02:52 AM |
| Do you have too many swapped out process? Is your my.cnf settings optimized ?
|
| Posted by Xandrios, 07-10-2007, 07:38 AM |
| Thanks for the replies
The longest running query always is a sleeping one. ie:
| 1260 | user | localhost | user | Sleep | 1233 | | NULL
The one following seems to be the one causing the trouble:
| 3245 | user | localhost | user | Query | 503 | Sending data | SELECT COUNT(*) from cpgn_pictures WHERE approved = 'YES' AND aid IN (1,2,3,5,6,7,8,9,10,11,12,13 |
As you can see the query is from a Coppermine gallery script. It keeps hanging on 'Sending data". This is the case every time the thing 'hangs'.
Initially I used a tweaked my.cnf, but because of the issues I reverted back to the Debian default one. Both have the same effect on the 'crashing'.
|
| Posted by wKkaY, 07-10-2007, 08:09 AM |
| Sleep isn't a query
That query you have there looks trivial.
1. How large is that cpgn_pictures table? Is it a MyISAM or InnoDB table? You can check with a show table status.
2. Try running show full processlist when it freezes up and see just how many aid's are in the IN set.
3. Run EXPLAIN on the query and see if it uses an index.
4. How large are your sort and key buffers?
|
| Posted by expressadmin, 07-10-2007, 10:54 PM |
| Kind of sounds like a table locking issue.
A prior update/delete is locking the table and then for some reason it is never releasing the lock.
|
Add to Favourites 
Print this Article
Also Read
