Multiple Connections Bringing MySQL to a Halt

Mar 18, 2008 / By Sheeri Cabral

Tags: , ,

Hi all,

I was asked this question in an e-mail — feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here’s the question:

Basically when we open more than one connection- we’re opening 5, and we do processing in mysql (innodb tables) the server cpu and memory max out and the processing grinds to a halt. One connection at a time is able to run well- even with lots of data. But once we try to process data under 5 concurrent connections, mysql gets bogged down to the point where it’s barely usable.

I’ve researched this and found a number of opinions, including whether this is just poor performance due to where the we are in the 5.1 life cycle (i.e. energies have gone into fixing big bugs not performance issues). Can you recommend any quick fix- or a reference to someone who might have more info on this. I know mysql handles thousands of concurrent connections in the web world. The difference with us is that we’re doing a lot of number crunching with the data.

Thanks for your help

2 Responses to “Multiple Connections Bringing MySQL to a Halt”

  • Arjen Lentz says:

    Memory maxes out? Then you might get swapping, which will cause the CPU to max out and indeed the whole system to grind to a halt… keep vmstat running to see.
    If the difference is between the 1 and the 5 connections, I presume that some MySQL local buffer settings are set extraordinarily high. Check that.

  • Jay Pipes says:

    Sounds like an issue of either poor threading code or trying to do too much in a single SQL statement. Without seeing the code, it’s tough to say…

    Are they using stored procedures?
    Are they pulling large (100,000+ rows) data sets in a buffered result and processing it while maintaining the connection to MySQL?
    If they are pulling large data sets, are they pulling with FOR UPDATE?
    Are they modifying large ranges of primary keys once processing of the data is done? This can lead to lock escalation in InnoDB from row to multiple pages (not sure if it will escalate all the way to a table-lock…) depending on how many rows will be updated.

    More info would help…

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>