Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : MySQL 'freezes' every few hours

[Xandrios]

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

[wKkaY]

When it happens again, look not for the most popular query, rather.. look for the query with longest time spent. What is it?

[SparkSupport]

Do you have too many swapped out process? Is your my.cnf settings optimized ?

[Xandrios]

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'.

[wKkaY]

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?

[ExpressColo-TomW]

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.