New to the community? Start here

Sleeping MySQL connections on a high traffic site

Hello, I've noticed in our Sql process that there are always a hundred or so sleeping connections. My understanding is that wordpress uses non-persistent connections when running queries. Does anyone have any idea why there are so many? We have a High traffic site (1000 concurrent users or so) with varnish + w3tc running. I'd love to clear those sleeping connections!

Tue, 21 May 2019 10:25:43
  • Adam
    • Support Gorilla

    Hi Bill

    I hope you’re well today!

    You’re right about WordPress not using persistent connections. There’s an ongoing discussion about this. Proposed solutions were dismissed in the past but the ticket was re-opened recently so there’s a hope that it will “catch up” eventually (though, as it is with other WP-core things, it might take quite some time). Take a look here:

    https://core.trac.wordpress.org/ticket/31018

    As for your case. Without persistent connection what happens is that (simply speaking): a PHP script connects to the DB, does some request(s)/queries, does “its things” and ends. The connection is sometimes “left to die”. In case of “heavy traffic sites” there’s a big chance that this will happen, especially if the site is complex and might be using (e.g. in some plugins or theme(s)) not really optimized SQL queries that take long to complete or is processing these queries results in a “non-optimzed” (code-wise) way.

    So, if such connection is “left open”, it takes pre-defined time for it to “die” and meanwhile it’s hanging there, stuck in a “sleep” state. In the WP trac ticket that I shared with you above, they suggest creating a drop-in to replace `wp::db_connect()” but that’s something that would need some customization and I never tried that – I’m not sure of a long-time effects on site.

    There is, however, quite a standard way to deal with this by optimizing DB configuration. Assuming that your site’s powered by MySQL and you do have access to MySQL configuration, you should be able to edit these configuration options in my.cnf config file:

    interactive_timeout
    
wait_timeout

    By default, they might be set to pretty long times so you could try (or ask you host to do this for you) setting them to values around 300 (that’s 5 minutes) or even less, like 180 (though I wouldn’t go lower) and see, after restarting MySQL, if that helps. It should help those sleeping connections “die” faster, thus freeing-up those otherwise blocked resources.

    Best regards,

    Adam