Too many sleeping mysql queries?

محروم.كوم · #1 01-11-2010, 04:20 PM

Hello,

Of late, we're seeing our (well-equipped) server choke up during peak traffic hours. On quering my host, these are the replies that have been received.

Quote:
From reviewing this matter it appears that there are plenty of queries that are sleeping. Below is a small example of what I am viewing in the processlist:

| 4409384 | forum_vb | localhost | forum_dbvbulletin | Sleep | 0 | | |
| 4409390 | forum_vb | localhost | forum_dbvbulletin | Sleep | 2 | | |
| 4409392 | forum_vb | localhost | forum_dbvbulletin | Sleep | 1 | | |
| 4409393 | forum_vb | localhost | forum_dbvbulletin | Sleep | 219 | | |
| 4409394 | forum_vb | localhost | forum_dbvbulletin | Sleep | 52 | | |
| 4409397 | forum_vb | localhost | forum_dbvbulletin | Sleep | 22 | | |
| 4409398 | forum_vb | localhost | forum_dbvbulletin | Sleep | 23 | | |
| 4409401 | forum_vb | localhost | forum_dbvbulletin | Sleep | 219 | |

Quote:
The biggest issue however are long MySQL queries. The long query log located in /var/lib/mysql/host-slow.log indicates that multiple queries on the forum_dbvbulletin database are taking well over the current long query threshold. There are some basic MySQL optimization tweaks I can perform for you, like analyzing and optimizing this database, or adjusting MySQL settings based on your current usage, but I would recommend you to ask vBulletin for recommendations on this. We can send you a truncated long query log file, as the full file is over 70 MB large and if they can give us any recommendations that would definitely help. Please let me know if you are interested in this option.

Best regards,

Quote:
I think that your loading times can be attributed to sleeping MySQL queries, here is what I see in mytop:

3556694 forum_v localhost forum_db 0 Sleep
3556727 forum_v localhost forum_db 0 Sleep
3556754 forum_v localhost forum_db 0 Sleep
3556770 forum_v localhost forum_db 0 Sleep
3556854 forum_v localhost forum_db 0 Sleep
3556884 forum_v localhost forum_db 0 Sleep
3557066 forum_v localhost forum_db 0 Sleep
3557476 forum_v localhost forum_db 0 Sleep
3557983 forum_v localhost forum_db 0 Sleep
3558376 forum_v localhost forum_db 0 Sleep
3558883 forum_v localhost forum_db 0 Sleep
3559187 root localhost mysql 0 Query show full processlist
3556669 forum_v localhost forum_db 1 Sleep
3556695 forum_v localhost forum_db 1 Sleep
3556818 forum_v localhost forum_db 1 Sleep
3556888 forum_v localhost forum_db 1 Sleep
3556894 forum_v localhost forum_db 1 Sleep
3556980 forum_v localhost forum_db 1 Sleep

3557000 forum_v localhost forum_db 1 Sleep
3557020 forum_v localhost forum_db 1 Sleep
3557234 forum_v localhost forum_db 1 Sleep
3557243 forum_v localhost forum_db 1 Sleep
3557255 forum_v localhost forum_db 1 Sleep
3558061 forum_v localhost forum_db 1 Sleep
3558068 forum_v localhost forum_db 1 Sleep
3558085 forum_v localhost forum_db 1 Sleep
3558111 forum_v localhost forum_db 1 Sleep
3558351 forum_v localhost forum_db 1 Sleep
3558502 forum_v localhost forum_db 1 Sleep
3558531 forum_v localhost forum_db 1 Sleep
3558538 forum_v localhost forum_db 1 Sleep
3558544 forum_v localhost forum_db 1 Sleep

This is a very large amount, sometime a few sleeping queries are OK, but this is a lot.

I would much appreciate advice on a fix / tweak for this problem.

Thanks __DEFINE_LIKE_SHARE__