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.

Optimizing MySQL

Discussion in 'Workarounds and Optimization' started by mm1250, Jul 30, 2008.

  1. mm1250

    mm1250 Well-Known Member

    Joined:
    Nov 10, 2006
    Messages:
    108
    Likes Received:
    0
    Trophy Points:
    16
    Hello,

    I amd running a shared-hosting server with around 250clients bu I am always noticing during my TOP session that MySQL is always at the top and CPU usage ranges from 5 to 50% CPU. I have used the "Show MySQL Processes" in WHM but doesn't show much besides the cphulk and eximstat. I refresh it often and dont' seem major usage like what TOP is reporting. So i'm a little worried.

    How can I figure out who is hammering my MySQL server? Also I'm interested in knowing hot to set limits such as database size, queries per hour limit etc... Any suggestions?
     
  2. thewebhosting

    thewebhosting Well-Known Member

    Joined:
    May 9, 2008
    Messages:
    1,201
    Likes Received:
    1
    Trophy Points:
    38
    continue keep a watch on running processes on server using top -c command. Meanwhile, I think there is no functionality in MySQL to limit the database size. However, you can create shell script and setup cron job for it. You can get an example of script from http://www.howtoforge.com/forums/archive/index.php/t-1944.html.
     
  3. gupi

    gupi Well-Known Member

    Joined:
    Apr 27, 2004
    Messages:
    125
    Likes Received:
    0
    Trophy Points:
    16
  4. Manoj_Krishnan

    Manoj_Krishnan Registered

    Joined:
    Aug 14, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    You may use the command " mysqladmin processlist " in shell.

    It will display the resources used by the databases that are being used at the particular moment .

    Manoj Krishnan
    Systems Engineer
    http://SupportPRO.com :: Transparent Web Hosting Support Services to Web Hosting Businesses ..
     
  5. madaboutlinux

    madaboutlinux Well-Known Member

    Joined:
    Jan 24, 2005
    Messages:
    1,052
    Likes Received:
    2
    Trophy Points:
    38
    Location:
    Earth
    Yes, the command

    watch mysqladmin proc

    which will show real time connections to your databases just like top shows server side connections. As 'thewebhosting' said earlier you cannot limit the size of the databases but you can very well limit the number of mysql connections to your server and connections on per user basis. All these limits can be set in /etc/my.cnf file.
     
  6. JamesAB

    JamesAB Active Member

    Joined:
    Apr 12, 2003
    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    New Hampshire
    Could sombody please post an example of how mysql connections can be limited on a per user basis by making changes in /etc/my.cnf ?

    Or a link to a good reference would also help.

    Also, how would you handle the task of setting this up with new accounts, if the database name(s) haven't even been chosen?

    Thanks,
    James
     
  7. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
    JamesAB, Iam also looking for optimizing my.cnf, and restrict per user max connections.

    currently one of my server which normally does loads of under 1.0 is going bonkers during specific times and those 1-2 hours are painful.

    Most of the load is from mysql, and my.cnf i have seems to be only the default one with no tweak visible.
     
Loading...

Share This Page