Announcement

Collapse
No announcement yet.

Too many sleeping mysql queries?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • voter
    replied
    At most cases the stucked queries are because of plugins.

    Switch off Plugins/Hooks and check if sleeping queries are gone. If yes, then switch on the plugins again and step by step switch one by one off checking to identify which one of them is the reason.

    At my case the reason was a plugin from a Advertiser in global start, who were checking every time the ads that have to be loaded with a generated page, but as of problems on Advertiser company server all query were geting stucked, waiting for answere from a third party server...

    Leave a comment:


  • Rushabh
    replied
    Hello,

    The problem has resurfaced again.

    1. Would someone please help out with a solution to this "too many sleeping queries" issue.

    2. How do I reduce / eliminate this large number of sleeping queries?

    Hi Rush,

    I took a look at your processes and it seems to be MySQL and Apache (PHP) processes causing the most load:

    3159 mysql 5 -10 2278m 1.7g 3632 S 35.0 11.0 1502:02 mysqld
    8844 nobody 16 0 294m 39m 5596 S 34.7 0.2 0:23.45 httpd
    12520 nobody 15 0 295m 40m 5592 S 23.8 0.3 0:03.58 httpd
    11529 nobody 15 0 294m 39m 5996 S 19.8 0.2 0:11.61 httpd
    12283 nobody 15 0 292m 37m 5600 R 16.9 0.2 0:04.41 httpd
    12777 nobody 16 0 294m 38m 4984 S 14.2 0.2 0:01.68 httpd
    13077 nobody 15 0 287m 33m 4260 S 12.9 0.2 0:00.70 httpd
    12813 nobody 15 0 294m 39m 4992 S 12.6 0.2 0:02.98 httpd
    13034 nobody 16 0 283m 29m 4264 S 12.6 0.2 0:00.74 httpd
    11163 nobody 15 0 294m 38m 4992 S 12.2 0.2 0:09.70 httpd
    11586 nobody 15 0 294m 38m 5024 S 11.9 0.2 0:05.89 httpd
    8991 nobody 15 0 295m 40m 5684 S 11.2 0.3 0:22.43 httpd
    12537 nobody 16 0 293m 39m 5480 S 11.2 0.2 0:03.92 httpd
    12763 nobody 16 0 294m 39m 4952 S 10.9 0.2 0:01.66 httpd

    With regards to the SQL process' load there appear to be a very large number of sleeping processes, I see several hundred. It is possible these are contributing to the load.
    Thanks

    Leave a comment:


  • Rushabh
    started a topic Too many sleeping mysql queries?

    Too many sleeping mysql queries?

    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.

    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 | |
    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,
    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

Related Topics

Collapse

Working...
X