The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL queries slowing down

Discussion in 'Database Discussions' started by DVDGuy, Apr 14, 2011.

  1. DVDGuy

    DVDGuy Member

    Joined:
    Jul 5, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    I've run into a strange situation. Recently moved servers, both were using the latest version of WHM/cPanel (and similar set up in terms of other software), and the same versions of php/MySQL. There was a processing script on the old server that required thousands of SELECT queries to be processed (just SELECT queries, no INSERTS), and it managed to do that pretty quickly, in about 19 seconds. The queries are being run in a 'for' loop.

    When the same script was run on the new server, which is actually faster and has more RAM, it takes much much longer, about 3 or 4 minutes. Having analysed the queries, it seems they start out really quickly, and then instead of hundreds of queries being processed per second like at the start, it falls all the way down to about a dozen every second. On the old server, they're being processed at a steady, fast, rate.

    Other than the obvious suggestion of optimizing the script (I already have, and have bypassed the problem), can anyone suggest what could be happening? Is there some kind of intentional throttling in play here?

    I bypassed the problem by having a script that processes a few hundred queries at a time, and then a main script that calls that script via curl, and even though the number of SELECT's remain the same, the processing is done in about the same time as the old server (actually a bit faster, as you would expect).

    One major difference between the old server and the new server is that the new server is running 64-bit RHEL 5, vs 32-bit RHEL 4 on the old server. Not sure if this could be the cause.

    Thanks.
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    I a pretty sure that the arch 64 bit is not at all causing the issue. Can you check whether mysql is optimized in your new server?
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Based on prior tests I've performed for MySQL, 64-bit MySQL actually should perform faster than 32-bit MySQL. Do you have the same settings in /etc/my.cnf on both machines? Can you provide the contents of /etc/my.cnf from both of those machines for us to see what they have?
     
  4. garrettp

    garrettp Well-Known Member
    PartnerNOC

    Joined:
    Jun 18, 2004
    Messages:
    312
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    Two major things that are going to make a huge difference for you, and it doesn't sound like they got moved over:

    1) Table index(es)
    2) MySQL Query Cache

    Implementing one or both will most certainly bring you back to par with your previous execution times.
     
  5. DVDGuy

    DVDGuy Member

    Joined:
    Jul 5, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Thanks for the replies.

    The new server has been optimized for MySQL, and I've checked the relevant data and they're all fairly healthy (like the query cache, # of opened tables ...).

    So I did further testing, to narrow down which function or query was actually causing the slow down, and actually found that it wasn't a database issue at all, but rather a php one, and one that's not hard to solve (with some better coding).

    So it's all good now :)
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Would you be willing to share what the exact PHP issue happens to be for future reference?
     
  7. DVDGuy

    DVDGuy Member

    Joined:
    Jul 5, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    It was the kind of silly php newb mistake, of using include() in a function, and then trying to call that function about 50,000 times. Still, it's strange that the old server managed to deal with this, while the new server would start to slow down after after the first 1,000 calls or so, and sometimes even with an out of memory error (the included file was nothing more than just a list of pre-set variables).
     
  8. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    Are you able to find anything in the error_log or mysql logs?
     
  9. garrettp

    garrettp Well-Known Member
    PartnerNOC

    Joined:
    Jun 18, 2004
    Messages:
    312
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    Perhaps your old server had a better disk subsystem allowing better I/O throughput which would be paramount for quick reads on that many includes.
     
Loading...

Share This Page