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.

mysql server load

Discussion in 'General Discussion' started by ephlox, Sep 29, 2006.

  1. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    Hi,
    My server load is going high again n again after every few days. it was using 98%



    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.evirtualservers.com.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

    Cause of server load i have written these two lines in httpd.conf still have server load problem.
    RLimitMEM 203241813
    RLimitCPU 240



    How i can fix this ? Your help will be appreciated thanks.

    Best Regards from,
    Farrukh
     
  2. jollymon

    jollymon Member

    Joined:
    Aug 28, 2006
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    monitor it with top-i from ssh for abit and see if this is temp spike or if it is a constant loads. It looks like you are looking at the whm page that logs the top processes, which is a crap page. How many dbs are you running? If it is in like several 100's and they are fairly large and have verbose traffic then yea, your going to peak out your sql. Also cehck to see if your sql is updated and are you running suexec?

    Did this happen when you were installing, or transferring new databases? Load gets high when a few long lasting sql querries run at the same time on 1 server. Better hardware if thats the case.
     
  3. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
  4. celliott

    celliott Well-Known Member

    Joined:
    Jan 2, 2006
    Messages:
    460
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United Kingdom
    Hi,

    I reccomend you install MyTop so you can easily monitor MySQL to find out which users are causing the high load.

    wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.4.tar.gz
    tar -zxf mytop-1.4.tar.gz
    cd mytop*
    perl Makefile.PL
    make test
    make
    make install

    Then just enter mytop in SSH and it should open.

    High load is often caused by outdated scripts, therefore it may be worth checking your accounts and the script versions in there. As a bare minimum, ensure that any cPaddon and Fantastico installed scripts are updated.

    Chris
     
  5. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    Sql Problem

    Hi,
    I got this from WHM "Show Current CPU Usage " , No databases are not too many. there are only few databases and also those are not heavy.

    I think i am heaving load cause of this website "http://www.pakheaven.com" i say "Apache status" there are many threads for his websites. And those are heavey cause people are downloading songs froms his website.


    How i can limit his website resources ?

    I already added as global,
    RLimitMEM 203241813
    RLimitCPU 240


    I have 2.8GHz with 1 GB RAM.

    Your help will be appreciated thanks.

    Regards,
    Farrukh
     
  6. celliott

    celliott Well-Known Member

    Joined:
    Jan 2, 2006
    Messages:
    460
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United Kingdom
    Hi,

    Its still worth installing MyTop as its an invaluable tool to monitor load.

    As his site is mostly script driven, have you considered installing some PHP caching software? Eaccellerator is very good and can be used in conjunction with Zend as an extension. This can drop load quite consierably and speed up loading times.

    Downloading files through Apache shouldnt cause your CPU usage to raise as high as 98%, have you checked for any possible Cronjobs etc running in the background?
     
  7. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    Hi

    Hi,
    No . i didn't install any cache software for PHP. Where i can get "Eaccellerator" and how i can install it? please tell me same as installation procedure of "mytop" .

    I checked there is no cronjobs are running in background.

    Regards,
    Farrukh
     
  8. jollymon

    jollymon Member

    Joined:
    Aug 28, 2006
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    First i would find out WHAT kind of songs people are downloading off his website, if ya know what I mean ;) Next i would look for how they are downloading these songs. Are they downloading them from hrefs or is it some wierd php based player or some streming thing? those are rather important items to consider.

    Also just FYI i would talk to this guy first off about server resources and how he is using them. If you just change things then you will get the angry customer syndrome :eek: and you dont want that now do ya? Check your max sizes for php also bro and if ppl are uploading via a script or some CMS lots of huge files then talk to him about what your going to have to do.
     
  9. celliott

    celliott Well-Known Member

    Joined:
    Jan 2, 2006
    Messages:
    460
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United Kingdom
    MyTop installation procudure as shown in previous post:

    Eaccellerator:

    If you don't already have Zend Optimizer installed, run /scripts/installzendopt and follow the intructions.

    wget http://easynews.dl.sourceforge.net/sourceforge/eaccelerator/eaccelerator-0.9.4.tar.bz2
    tar jvfx eaccelerator-0.9.4.tar.bz2
    cd eaccelerator-0.9.4
    /usr/local/bin/phpize
    ./configure --enable-eaccelerator=shared --with-php-config=/usr/local/bin/php-config
    make
    make install
    mv /usr/local/lib/php/extensions/no-debug-non-zts-*/eaccelerator.so /usr/local/lib/php/extensions


    edit /usr/local/lib/php.ini and add the following to the bottom. You may want to create a backup of php.ini first.:


    ;;eAccelerator
    extension="eaccelerator.so"
    eaccelerator.shm_size="16"
    eaccelerator.cache_dir="/php-cache"
    eaccelerator.enable="1"
    eaccelerator.optimizer="1"
    eaccelerator.check_mtime="1"
    eaccelerator.debug="0"
    eaccelerator.filter=""
    eaccelerator.shm_max="0"
    eaccelerator.shm_ttl="0"
    eaccelerator.shm_prune_period="0"
    eaccelerator.shm_only="0"
    eaccelerator.compress="0"


    create /php-cache


    mkdir /php-cache; chmod 0777 /php-cache

    This should work fine and has been tested with PHP 4.4.4. If however it does not work, you can revert to the backup of php.ini which you made earlier.

    Eaccellerator will cache frequently used php files in /php-cache. Its advisable to clear this folder every few weeks or so as it will build up and can use a lot of disk space. However once installed, it can greatly improve performance.

    Chris.
     
  10. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    MySql

    Hi,
    People are downloading mp3 songs. he is not providing streaming,


    Srv PID Acc M CPU SS Req Conn Child Slot Host VHost Request
    0-3 9691 0/10757/16826 W 184.16 149 0 0.0 166.45 236.69 203.101.174.122 www.pakheaven.com GET /Songs/Pakistani_Drama/Tum-Kaisi-Muhabbat-Karte-Ho(PakHeave



    PHP maximum file upload size is 32 MB. But nobody is uploading file by CMS.

    Regards ,
    Farrukh
     
  11. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    Mysql

    Hi,
    Thanks for help, I have installed eaccelerator-0.9.4. How to check php-chache is loading /caching files? i checked by ls command
    there is no file. Also i have restarted https service.


    Regards ,
    Farrukh
     
  12. jollymon

    jollymon Member

    Joined:
    Aug 28, 2006
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Well I dont usually bother checking out the sites, and this is a prime example of why:confused: . Some people should lay off the scripts. I was just wondering if they were like music mp3's, however they are some wierd stuff so who cares. Yes the site is as well as visually distracting also a mess of code patchwork. Who knows man, who knows. Check your top, or mytop, often and early. Also think back to your TOS in some instances. Have fun man and many prosperous moons!
     
  13. celliott

    celliott Well-Known Member

    Joined:
    Jan 2, 2006
    Messages:
    460
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United Kingdom
    If you do "php -v" in SSH you should be able to see whether or not eaccelerator is running.
     
  14. nwilkens

    nwilkens Well-Known Member

    Joined:
    May 4, 2006
    Messages:
    59
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Monroe MI
    cPanel Access Level:
    DataCenter Provider
    By default, there may be no limit on the number of times that a client can connect to the server or the number of queries it can issue. GRANT can establish limits on an account's resource consumption for the following characteristics:

    - The number of times per hour the account is allowed to connect to the server
    - The number of queries per hour the account is allowed to issue
    - The number of updates per hour the account is allowed to issue

    The following example creates an account that can use the test database, but can
    connect to the server a maximum of only 200 times per hour. The account can issue 500 queries per hour, and at
    most 100 of those queries can modify data:

    GRANT ALL ON test.* TO 'username'@'hostname'
    IDENTIFIED BY 'SomePass'
    WITH MAX_CONNECTIONS_PER_HOUR 200
    MAX_QUERIES_PER_HOUR 500
    MAX_UPDATES_PER_HOUR 100;

    Or this same information can be changed in phpmyadmin.

    This gives you an idea of how you could potentially limit this client (and future) from overwhelming your site.

    Also reference http://dev.mysql.com/doc/refman/4.1/en/user-resources.html

    Most likely he has poor coding [and / or requires proper indexes], which is causing mysql to query/update without need.

    Hope this helps,
    Nick
     
  15. ephlox

    ephlox Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    60
    Likes Received:
    0
    Trophy Points:
    6
    mysql

    Hi,
    I typed php -v and i got this,

    php -v
    PHP 4.4.3 (cli) (built: Aug 23 2006 09:31:13)
    Copyright (c) 1997-2006 The PHP Group
    Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
    with Zend Extension Manager v1.0.10, Copyright (c) 2003-2006, by Zend Technologies
    with Zend Optimizer v3.0.0, Copyright (c) 1998-2006, by Zend Technologies



    i think eaccelerator is not working. i did everything as you said. i also edited php file. but no idea why it is not working. Do you have any idea ?


    Regards from,
    Farrukh
     
  16. carluk

    carluk Well-Known Member

    Joined:
    Sep 2, 2003
    Messages:
    162
    Likes Received:
    0
    Trophy Points:
    16
    Also worth doing:
    Code:
    mysqladmin processlist
    
     
  17. dafut

    dafut Well-Known Member

    Joined:
    Dec 14, 2005
    Messages:
    75
    Likes Received:
    0
    Trophy Points:
    6
    Look for the error_log in the directory where you ran the eaccelerator install script. That should give a clue.

    Also, I've found the mysqlreport script to give me very good info re finetuning mysql. See http://hackmysql.com for more tips on mysql optimization.
     
  18. Manuel_accu

    Manuel_accu Well-Known Member

    Joined:
    Jun 19, 2005
    Messages:
    191
    Likes Received:
    0
    Trophy Points:
    16
  19. AndyReed

    AndyReed Well-Known Member
    PartnerNOC

    Joined:
    May 29, 2004
    Messages:
    2,222
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Minneapolis, MN
    If Phpsuexec is activated on your server, eAccelerator might not work. As you may know, Phpsuexec forces Php to run as a CGI. Unless things changed, all known eAcceleration products work with Php running as a module in Apache.
     
    #19 AndyReed, Oct 11, 2006
    Last edited: Oct 11, 2006
  20. d-woo

    d-woo Well-Known Member

    Joined:
    Aug 9, 2003
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    I installed mytop

    When I run the command in SSH I get

    Code:
    root@srv1 [~]# mytop
    Cannot connect to MySQL server. Please check the:
    
      * database you specified "test" (default is "test")
      * username you specified "root" (default is "root")
      * password you specified "" (default is "")
      * hostname you specified "localhost" (default is "localhost")
      * port you specified "3306" (default is 3306)
      * socket you specified "" (default is "")
    
    The options my be specified on the command-line or in a ~/.mytop
    config file. See the manual (perldoc mytop) for details.
    
    Here's the exact error from DBI. It might help you debug:
    
    Unknown database 'test'
     
Loading...

Share This Page