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.

question about mysql load

Discussion in 'General Discussion' started by noimad1, Aug 14, 2007.

  1. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16
    I have a server with only one website on it. He gets a fair amount of traffic, but not to the extent where he should be using all the resources of this server.

    His load keeps jumping upwards of 10.00-30.0. And when I run a top on this server I see the mysql process at the top with a consistant 60-90% cpu usage.

    But I can run a mytop and not see any queries being run at all at the same time?

    So how can this be? Why would mysql be eating up all the cpu usage with no queries?
     
  2. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16
    anyone have any ideas on this one?

    Thanks,
    Damion
     
  3. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16
    i'm gonna bump this one more time just to see if anyone has any ideas.

    Thanks,
    Damion
     
  4. nyjimbo

    nyjimbo Well-Known Member

    Joined:
    Jan 25, 2003
    Messages:
    1,125
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    New York
    Longshot here, but if he is using inefficient coding on his database accesses then you will see very heavy loads on just open/read/close of databases. So the database session might not be visible rather the load is from mysql's housekeeping on the way he accesses the data.
     
  5. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16

    Ah, i wonder if that is the case. Hmm...maybe I'll take a look at his code.

    Do you think it could be him establishing a connection to the database and not closing it? Is that possible, and if so would it cause load on the server?
     
  6. nyjimbo

    nyjimbo Well-Known Member

    Joined:
    Jan 25, 2003
    Messages:
    1,125
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    New York
    We had a customer doing a genealogy database and it was killing one of our systems, we told them they were doing sloppy coding on the way they handle database open/close/etc.. and they should re-write snippets of the code to streamline it. Needless to say they didnt and I told them we would not accept their renewal for hosting and they had to leave.

    Not all database usage is the same, if you get alot of traffic under certain conditions you will increase load if you do not code properly. You might need to give them some info or let them use mysql tools to do this, but if they are not important and dont really pay your bills it might be easier to give them walking papers rather than to worry about it too much or upgrading the computer or trying to figure out some kind of bandaid fix.
     
    #6 nyjimbo, Sep 6, 2007
    Last edited: Sep 6, 2007
  7. linux.newbie

    linux.newbie Well-Known Member

    Joined:
    Sep 8, 2006
    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    6
    Also try disabling persistent mysql connections, ie mysql_pconnect() --> mysql_connect() if you suspect his database sessions are sloppy. No point in letting him opening up unnecessary connections ;)
     
  8. SageBrian

    SageBrian Well-Known Member

    Joined:
    Jun 1, 2002
    Messages:
    415
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    NY/CT (US)
    cPanel Access Level:
    Root Administrator
    agreed. If someone refuses to behave properly in a shared setting, they have options. Fix the issue, pay for a dedicated server so their sloppiness doesn't affect others, or move on to another host that doesn't care as much about server stability.

    As server admin, it's in your power, and everyone's best interest, to dismiss the trouble.
     
  9. gribozavr

    gribozavr Member

    Joined:
    Aug 15, 2007
    Messages:
    23
    Likes Received:
    1
    Trophy Points:
    3
    You might want to fine-tune some parameters in /etc/my.cnf, for example key_buffer or query_cache_size. But of course, you shouldn't blindly change anything, read documentation first and look through recommended example configurations in /usr/share/mysql/
     
  10. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16

    On a change like this, would it not let him open the conections at all, or just close them for him?

    He is already on his own server, so he isn't affecting others. But he's complaining about load. I'm pretty sure part of the load issue is his table design. He has one table that has over 9 million records in it.

    However, I just can't figure out why the mysql load is staying at a constant high load, even with no queries being run. Now i'm beginning to think it is the connections themselves to the database.

    Now I can't just stop him from opening his own connections, but if the above option would just close open connections that aren't being used that might be worth looking into.
     
  11. linux.newbie

    linux.newbie Well-Known Member

    Joined:
    Sep 8, 2006
    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    6
    Disabling persistent connections means the user will have to create a new database session each time the code is executed, ie the server will not get clogged with db sessions which are no longer required.

    Check this out for more : http://www.mysql.com/news-and-events/newsletter/2002-11/a0000000086.html

    But then again, tweaking your mysql settings is the need of the day. You can get a lot of my.cnf tweaks just doing a search in this forum.
     
    #11 linux.newbie, Sep 8, 2007
    Last edited: Sep 8, 2007
Loading...

Share This Page