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:
    166
    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?
     
    #1 noimad1, Aug 14, 2007
  2. noimad1

    noimad1 Well-Known Member

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

    Thanks,
    Damion
     
    #2 noimad1, Aug 16, 2007
  3. noimad1

    noimad1 Well-Known Member

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

    Thanks,
    Damion
     
    #3 noimad1, Sep 6, 2007
  4. nyjimbo

    nyjimbo Well-Known Member

    Joined:
    Jan 25, 2003
    Messages:
    1,125
    Likes Received:
    0
    Trophy Points:
    166
    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.
     
    #4 nyjimbo, Sep 6, 2007
  5. noimad1

    noimad1 Well-Known Member

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

    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?
     
    #5 noimad1, Sep 6, 2007
  6. nyjimbo

    nyjimbo Well-Known Member

    Joined:
    Jan 25, 2003
    Messages:
    1,125
    Likes Received:
    0
    Trophy Points:
    166
    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:
    156
    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 ;)
     
    #7 linux.newbie, Sep 7, 2007
  8. SageBrian

    SageBrian Well-Known Member

    Joined:
    Jun 1, 2002
    Messages:
    415
    Likes Received:
    2
    Trophy Points:
    318
    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.
     
    #8 SageBrian, Sep 7, 2007
  9. gribozavr

    gribozavr Member

    Joined:
    Aug 15, 2007
    Messages:
    23
    Likes Received:
    1
    Trophy Points:
    53
    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/
     
    #9 gribozavr, Sep 7, 2007
  10. noimad1

    noimad1 Well-Known Member

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

    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.
     
    #10 noimad1, Sep 7, 2007
  11. linux.newbie

    linux.newbie Well-Known Member

    Joined:
    Sep 8, 2006
    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    156
    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
(You must log in or sign up to post here.)
Loading...
Similar Threads - question mysql load
  1. Droidism20011

    MySQL -> MariaDB security questions

    Droidism20011, Jul 20, 2017, in forum: Security
    Replies:
    3
    Views:
    118
    Droidism20011
    Jul 24, 2017
  2. Bananenbaas

    SOLVED A few questions about MySQL & Redundancy

    Bananenbaas, Apr 18, 2017, in forum: Database Discussions
    Replies:
    4
    Views:
    217
    Infopro
    Apr 19, 2017
  3. copernic2006

    Converting RoundCube database from MySQL to SQLite question

    copernic2006, Feb 28, 2017, in forum: E-mail Discussions
    Replies:
    2
    Views:
    365
    cPanelMichael
    Feb 28, 2017
  4. adonius

    SOLVED WHM backup MySQL question

    adonius, Dec 22, 2016, in forum: Data Protection
    Replies:
    3
    Views:
    421
    adonius
    Dec 22, 2016
  5. Musthafa

    SOLVED Restoring MySQL Databases Question

    Musthafa, Dec 15, 2016, in forum: Database Discussions
    Replies:
    3
    Views:
    418
    SysSachin
    Dec 21, 2016

Share This Page