SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Mar 2004
    Posts
    639
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL causes heavy load of dedicated server

    Hello,

    I have a dedicated server and PHP/MySQL website. Sometimes my server become extremely slow. I mean I can't connect to it, I get different kind of errors (PHP maximum execution time exceed or even error 500).

    Task Manager shows that MySQL loads server sometimes and the load is abnormal.

    I've connected to MySQL with MySQL Administartor and I've seen that simple queries take too much time:




    I wonder how can I fix this and what can cause those problems?

    Thanks in advance.

  2. #2
    SitePoint Guru
    Join Date
    Mar 2004
    Posts
    639
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, I wonder is it possible to get some information about MySQL query but it's PID (which is shown in MySQL Administrator)? I'd like to know what script creates that query.

  3. #3
    SitePoint Guru
    Join Date
    Mar 2004
    Posts
    639
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone please? How can I fix this?

    I guess the code causes this is right:

    PHP Code:
    $sql "SELECT * FROM geo.thing WHERE country_code = '$countryCode' ORDER BY ansiname LIMIT ".($allShow 70).",70";
        
    $trResult mysql_query($sql$conn) or die('Query failed: ' mysql_error()); 
    Did I make some error in it?

  4. #4
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Does the geo.thing table have any indexes set?

    Try running this query:

    Code:
    EXPLAIN SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 41300, 70;
    It will tell you what's going on. If you post the results of that query here, we can help you a bit further

  5. #5
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey

    It's your LIMIT.
    Just test these two queries:
    Code:
    SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 300, 70;
    SELECT * FROM geo.thing WHERE country_code = 'US' ORDER BY ansiname LIMIT 141300, 70;
    The second one should be a hell of allot slower than the first.
    Basically, to be able to get those 70 records, mysql will have to select the first 370 in the first example, and the first 141370 in the second (allot slower).

    To get over this, you can use 2 queries, and need a small table modification.

    #1 Add a field to your table, numeric, unique, indexed, call it "orderKey".
    #2 populate this field, with numbers, 1 to N, based on the order of your ansiname field, so the first one will be 1, second 2, 3rd 3, etc.
    #3 When you need to select, your query will look like:
    Code:
    SELECT * FROM geo.thing WHERE country_code = 'US' AND orderKey >= 41300 ORDER BY orderKey ASC LIMIT 70;
    That should be allot faster than your current solution.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •