Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    35

    Question Advice needed on process using high CPU

    could some one advise me on a problem i have ?

    I run a dedicated server,
    Running
    CentOS 4.5 final
    Apache 2.0.59
    php 4.3.11
    MySQL 4.1.20

    I have a user who has been importing large sql databases and this causes the cpu to run at 80% - 90% this in turn causes high loads and sometimes causing the server to crash.
    Is there a way to automatically limit or control how much the process can use or stop the process to allow the server to recover.
    Just trying to put some sort of safe guard in place

    Hope i have explained what i am trying to do right.

    Any help appreciated

    Thank you

  2. #2
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    if IMPORTING a db causes this much strain on the sql server for a repeated length of time, then it's time to ask the owner to get a dedicated server. Not a semi-ded, but a dedicated.

    Why?
    Simply put, if the DB takes that much time and extracts the resources that much on import, then how much MORE resource is it going to use on a day to day basis? Trust me, that's a road you don't want to go down.

    Is there a way to automatically limit or control how much the process can use or stop the process to allow the server to recover.
    No, not and keep the DB reasonably intact.
    Tom Whiting, WHMCS Guru extraordinaire
    Linux problems? WHMCS Problems? Give me a shout
    Check out my WHMCS Addons

  3. #3
    Join Date
    Oct 2005
    Posts
    292
    a couple of random tips (kindly research how these are done)
    - enable logging of long lived queries
    - selectively kill sql threads by using logging in as root and using 'SHOW PROCESSLIST', 'SHOW FULL PROCESSLIST', 'KILL' combo
    - talk to your user if it's possible to revise his import script to user 'INSERT .. DELAYED' syntax
    - tune your my.cnf

    Cheers!

  4. #4
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,771
    Quote Originally Posted by Gazza-t View Post

    I have a user who has been importing large sql databases and this causes the cpu to run at 80% - 90% this in turn causes high loads and sometimes causing the server to crash.
    If your server has enough memory, try increasing the value max_allowed_packet to a higher value under the sections [mysqld] and [mysqldump] inside my.cnf file. It will speed up the mysql dump/restore process.
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  5. #5
    Join Date
    Jun 2006
    Posts
    35
    Hi

    @linux-tech
    The users been on the server for 8 months with no problems but recently been importing backups after messing up his database, thats when the problem arose but yes i can see the point your making.
    Thank you for your reply

    @cygnusd

    logging of long lived queries is enabled and there where not that many, i also reduced long query time to 5 sec sometime back.
    my.cnf was optimised but taking another look to see if it can be improved.
    The user was importing with bigdumps and by reducing linespersession has reduced the load on the CPU
    Thank you for your reply

    @david510

    [mysqld]
    max_allowed_packet = 16m

    set already at 16m could go higher as i have 2G memory.

    [mysqldump] that setting was not in my.cnf but have added to see what effects it has on the server after a bit of research

    [mysqldump]
    quick
    max_allowed_packet=16M

    Thank you for your reply

  6. #6
    Ask user to do d/b import in batch files. Like he can make 3-4 files of large sql files and than import one by one. It makes sense if you do that for your customer. They will appreciate and stay with you longer.
    Dinsol.com Web Hosting Since Year 1999 Open Running Stable
    Social Media Optimization & Marketing Services with World Class Quality
    www.dinsol.com

  7. #7
    Join Date
    Jun 2006
    Posts
    35
    Quote Originally Posted by Shikha View Post
    Ask user to do d/b import in batch files. Like he can make 3-4 files of large sql files and than import one by one. It makes sense if you do that for your customer. They will appreciate and stay with you longer.
    thats an idea i will look into that.
    thanks

Posting Permissions

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