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 running wild and 'top' has reached 73

Discussion in 'General Discussion' started by SupermanInNY, Jun 25, 2004.

  1. SupermanInNY

    SupermanInNY Well-Known Member

    Joined:
    Jul 19, 2003
    Messages:
    255
    Likes Received:
    0
    Trophy Points:
    16
    Hi All,

    Last night I uploaded a database size of 560MB.
    it took it over two hours (I left the computer and came back later) and finished completly with no problems.

    Today,. about 16 hours later., the user called me to let me know he is experiencing disconnects when using phpMyAdmin and the forum we loaded last night is running slow and also disconnects every couple of minutes.

    I checked top and saw number I've never seen before:

    18:50:35 up 104 days, 16:57, 4 users, load average: 73.76, 53.11, 28.25


    I was getting excited. Yeah! finally my CPU is working hard.
    Well.. I checked:

    ps fax

    and saw that the mysql is running the following:

    1301 pts/1 S 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/bess.shev.com.pid
    1321 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/bess.she
    1322 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/bess
    6454 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7096 pts/1 D 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7149 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7166 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7170 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7171 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7172 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7174 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7175 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7176 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7178 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7179 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7180 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7181 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7182 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7183 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7184 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7185 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7186 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7187 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7188 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7189 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7190 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7191 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7194 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7195 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7197 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7199 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7200 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7201 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7206 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7208 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7209 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7211 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7212 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7213 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7214 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7215 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7216 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7217 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7218 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7219 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7221 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7225 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7226 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7228 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7229 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7230 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7238 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7239 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/
    7240 pts/1 S 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/

    Well.. that list ran about 5 times of what you see here.
    I decided to stop the service:

    service mysql stop (notice I did not run mysqld as when I tried it,. it didn't recognize it).

    The service stopped (it typed Killing process and exiting or something like that.. and then terminated the process as it should).

    I then typed:

    service mysql start

    and the service started just fine,

    ps fax

    showed a single line as it should and then 5 minutes later... I'm back where it all started.
    What is going on?????

    Help!

    -Alon.
     
  2. SupermanInNY

    SupermanInNY Well-Known Member

    Joined:
    Jul 19, 2003
    Messages:
    255
    Likes Received:
    0
    Trophy Points:
    16
    top

    19:25:51 up 104 days, 17:32, 4 users, load average: 67.60, 35.83, 17.45
    542 processes: 541 sleeping, 1 running, 0 zombie, 0 stopped
    CPU0 states: 1.3% user 5.2% system 0.0% nice 0.0% iowait 92.4% idle
    CPU1 states: 2.5% user 6.4% system 0.0% nice 0.0% iowait 90.1% idle
    Mem: 904108k av, 897424k used, 6684k free, 0k shrd, 3760k buff
    466676k active, 363208k inactive
    Swap: 2097144k av, 780924k used, 1316220k free 266820k cached

    PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
    11764 root 11 0 1400 1400 764 S 7.3 0.1 0:00 1 top
    11729 root 15 0 1500 1500 868 R 3.4 0.1 0:00 1 top
    28691 root 9 0 3932 864 828 S 3.2 0.0 33:22 0 httpd
    1321 mysql 9 0 39784 21M 5940 S 0.5 2.4 0:18 1 mysqld
    5 root 11 0 0 0 0 SW 0.3 0.0 32:06 0 kswapd
    1 root 8 0 400 364 344 S 0.0 0.0 0:51 1 init
    2 root 9 0 0 0 0 SW 0.0 0.0 0:04 1 keventd
    3 root 19 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd_CPU0
    4 root 18 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU1
    6 root 9 0 0 0 0 SW 0.0 0.0 0:09 0 bdflush
    7 root 9 0 0 0 0 SW 0.0 0.0 21:21 1 kupdated
    8 root 18446744073709551615 -20 0 0 0 SW< 0.0 0.0 0:00 1 mdrecoveryd
    15 root 9 0 0 0 0 SW 0.0 0.0 20:07 0 kjournald
    73 root 9 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
    253 root 9 0 0 0 0 SW 0.0 0.0 10:06 1 kjournald
    254 root 9 0 0 0 0 SW 0.0 0.0 0:00 1 kjournald
    255 root 9 0 0 0 0 SW 0.0 0.0 28:21 1 kjournald
    256 root 9 0 0 0 0 DW 0.0 0.0 52:16 1 kjournald
    261 root 9 0 0 0 0 SW 0.0 0.0 21:01 1 kjournald
    310 root 9 0 0 0 0 SW 0.0 0.0 0:00 0 knodemgrd_0
    1524 root 9 0 328 276 276 S 0.0 0.0 1:42 1 gpm
    1533 root 8 0 468 436 392 S 0.0 0.0 0:13 0 crond
    1864 xfs 9 0 2964 440 440 S 0.0 0.0 0:00 1 xfs
    1882 daemon 8 0 428 372 372 S 0.0 0.0 0:05 0 atd
    2061 root 9 0 368 308 308 S 0.0 0.0 0:00 1 portsentry
    2100 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2101 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2102 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2103 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2104 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2105 root 9 0 312 260 260 S 0.0 0.0 0:00 1 mingetty
    2340 mailnull 9 0 8908 744 744 S 0.0 0.0 0:11 0 MailScanner
    9788 root 9 0 508 360 360 S 0.0 0.0 0:00 0 open_sesame
    31647 root 8 0 2424 1452 1440 S 0.0 0.1 2:02 0 chkservd
    10573 nobody 9 0 1164 696 596 S 0.0 0.0 1:09 1 proftpd
    16479 root 19 19 11624 1456 1448 S N 0.0 0.1 10:03 1 cpanellogd
    16485 root 0 0 2368 868 684 S 0.0 0.0 1:59 0 cppop
    5330 mailnull 8 0 1224 980 920 S 0.0 0.1 0:19 1 exim
    5338 mailnull 8 0 1228 916 916 S 0.0 0.1 0:00 0 exim




    bess.xxx.com:/root => service mysql stop
    Killing mysqld with pid 1338
    Wait for mysqld to exit................................. gave up waiting!


    bess.xxx.com:/root => service mysql start

    bess.shev.com:/root =>
     
  3. SupermanInNY

    SupermanInNY Well-Known Member

    Joined:
    Jul 19, 2003
    Messages:
    255
    Likes Received:
    0
    Trophy Points:
    16
    Could it be a too small of a buffer for the mysql?

    my.cnf ,.. should it be enlarged?

    -Alon.
     
  4. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    definately need to optimze with your my.cnf.. Look in the folder usr/share/mysql you'll see these files:
    Code:
    my-huge.cnf*
    my-large.cnf*
    my-medium.cnf*
    my-small.cnf*
    
    look these over, and see which meets your server best. I have found, overall, these will work quiet nicely with most Linux systems.. with some minor tweaks as needed.

    Once you've found one that works well with your system, put a copy in the /etc folder, rename it to my.cnf,then restart MySQL.

    Hope it helps :)
    Mickalo
     
  5. SupermanInNY

    SupermanInNY Well-Known Member

    Joined:
    Jul 19, 2003
    Messages:
    255
    Likes Received:
    0
    Trophy Points:
    16
    I remember tweaking the my.cnf a while back.. but then I had no load problems with only 10 sites on the server.. so I couldn't get any performance checking as it was no load to begin with.

    I ran

    mysqladmin variables

    and saw the list of variables.
    My problem is that I have no idea of what is my baseline for the current config.
    In http://dev.mysql.com/doc/mysql/en/Server_parameters.html

    It is mentioned that the two most important variables worth noting are:

    "When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_cache. You should first feel confident that you have these set appropriately before trying to change any other variables. "

    As mentioned I ran the mysqladmin variables

    and here are my values:

    key_buffer_size | 268435456
    table_cache | 256

    Can someone who tweaked his server, please let me know what values you have on your server?

    My server has 1GB of RAM right now,. if needed, I can up it, but till this day there was no need.

    Thanks for your help.

    -Alon.
     
  6. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    those settings should be good in most applications... by chance are you running some PHP bullentin boards ?? Possibly alot of open connections that aren't getting closed properly within the PHP coding ??

    We run about 20+ database which are used pretty heavly, but don't see too much overhead as far CPU usage goes. You may also what to enable slow query log and logs in your MySQL my.cnf to see where these problems might be comming from... In my experience, more times then not, this excessive loads come from poorly coded scripts that are connecting to the databases, poorly optimized queries not utilizing the indices or not properly indexed table designs....

    Just something to look at.

    Mickalo
     
  7. SupermanInNY

    SupermanInNY Well-Known Member

    Joined:
    Jul 19, 2003
    Messages:
    255
    Likes Received:
    0
    Trophy Points:
    16
    Yes.. the issue started out with user that I've uploaded last.
    They have an Invision Board that is running and is probably the culprit in this.

    the mon_forumes' database has one very large table: 469MB data, 380MB indexes. I get the feeling that they have highly-unoptimized code.
    The thing is that even the indexes file hardly fits into the system's RAM.



    The problem is,. how do I 'catch' them or how do I offer assistance with that to alleviate this problem?
    Is theree any settings that can be done to Limit the number of open connections per website/database?

    I want two phase process:

    1. stop the culprits on their resource eating.
    2. treat the specific site with optimization.

    -Alon.
     
  8. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    I am by no means a PHP programmer, stickly Perl :) But you may want try this settings [mysqld] group:
    Code:
    max_connections         = 500
    max_connect_errors      = 1000
    
    I've found that alot of PHP MySQL coding use a persistant db connections that maybe causing problems too. :)

    You may also want to able a couple of logs, if you haven't already, under the [mysqld] group:
    Code:
    log                     = /path/to/mysql.log
    log-slow-queries  = /path/to/mysql_slowqueries.log
    
    these can tell alot too. check these logs around the time your CPU usage goes high to help pin point the problem areas and address them.

    I found the SPRI (System Priority), by the same folks that wrote the APF firewall, to help monitor processes, and if they get too high, it will kill the processes, and MySQL can be configured into this... just another tool worth looking into.

    Hope this helps
    Mickalo:D
     
  9. Doctor

    Doctor Well-Known Member

    Joined:
    Apr 26, 2003
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    I have the same sudden MySQL load problem on several servers after upgrading them to CURRENT-53. I don't think this is caused by user accounts. Anyone?
     
  10. r00t316

    r00t316 Active Member

    Joined:
    Nov 29, 2003
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    You may want to tweak your mysql
    /etc/my.cnf

    This is what I have and it runs pretty well..

    [mysqld]
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=500
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=4
    myisam_sort_buffer_size=64M

    [safe_mysqld]
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash
    #safe-updates

    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout
     
  11. Norcomp

    Norcomp Member

    Joined:
    Jul 20, 2003
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    Have problem that my serverload go's high and server lock because of mysql dump (backup)
    can this cnf file be tune better?
    Dual xeon 2,8Ghz, 1Gb mem

    [mysqld]
    safe-show-database
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    #set-variable = max_user_connections=1500
    #set-variable = max_connections=1500
    set-variable = max_connections=100
    set-variable = max_user_connections=100

    # Novos
    #skip-locking
    #skip-innodb
    #set-variable = query_cache_limit=1M
    #set-variable = query_cache_size=32M
    #set-variable = query_cache_type=1
    #set-variable = max_connections=1500
    #set-variable = interactive_timeout=100
    #set-variable = wait_timeout=100
    #set-variable = connect_timeout=10
    #set-variable = thread_cache_size=128
    ## 512MB set-variable = thread_cache_size=50

    ## set-variable = key_buffer=40M
    #set-variable = key_buffer=150M

    #set-variable = join_buffer=1M
    #set-variable = max_allowed_packet=16M
    #set-variable = table_cache=1024
    #set-variable = record_buffer=1M
    #set-variable = sort_buffer_size=2M
    #set-variable = read_buffer_size=2M
    ##sort_buffer_size=768K
    ##read_buffer_size=512K

    #set-variable = max_connect_errors=10
    #set-variable = thread_concurrency=2
    #set-variable = myisam_sort_buffer_size=64M
    #set-variable = read_rnd_buffer_size=768K
    ##read_rnd_buffer_size=512K

    #log-bin
    #server-id=1

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    # New
    #
    #open_files_limit=8192

    #[mysqldump]
    #quick
    #max_allowed_packet=16M

    #[mysql]
    #no-auto-rehash
    #safe-updates

    #[myisamchk]
    #key_buffer=64M
    #sort_buffer=64M
    #read_buffer=16M
    #write_buffer=16M

    #[mysqlhotcopy]
    #interactive-timeout
     
  12. linux-image

    linux-image Well-Known Member

    Joined:
    Jun 8, 2004
    Messages:
    1,192
    Likes Received:
    1
    Trophy Points:
    38
    Location:
    India
    cPanel Access Level:
    Root Administrator
    pstree -ap | grep mysql

    now kill all these running processes. and then restart mysql
     
Loading...

Share This Page