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.

High Load Mysql

Discussion in 'General Discussion' started by techteen, Nov 8, 2007.

  1. techteen

    techteen Active Member

    Joined:
    Jul 31, 2004
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    I got a server that has a high mysql rate, i am wondering why.

    PHP:
    [mysqld]
    datadir=/var/lib/mysql
    socket
    =/var/lib/mysql/mysql.sock
    safe
    -show-database
    bind
    -address=127.0.0.1
    log
    -slow-queries=/var/log/mysql_slow_query.log
    skip
    -locking
    skip
    -bdb
    skip
    -innodb

    query_cache_limit
    =1M
    query_cache_size
    =8M
    query_cache_type
    =1

    max_connections
    =500
    max_user_connections
    =500
    interactive_timeout
    =20
    wait_timeout
    =6
    long_query_time
    =5
    connect_timeout
    =6

    thread_cache_size
    =80
    key_buffer
    =128M
    join_buffer
    =1M
    max_allowed_packet
    =16M
    table_cache
    =2048
    tmp_table_size
    =512M
    record_buffer
    =1M
    sort_buffer_size
    =1M
    key_buffer_size
    =6M
    read_buffer_size
    =1M
    max_connect_errors
    =10
    thread_concurrency
    =2
    myisam_sort_buffer_size
    =64M
    read_rnd_buffer_size
    =2MB

    server
    -id=1
    concurrent_insert
    =2             
    low_priority_updates
    =1

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

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

    [mysql]
    no-auto-rehash     

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

    [mysqldump]    
    quick         
    max_allowed_packet
    =16M
    TOP Stats
    PHP:
    [root@s1 dl]# top
    top 17:09:09 up 9 days,  1:30,  1 user,  load average4.124.073.44
    Tasks
    135 total,   2 running133 sleeping,   0 stopped,   0 zombie
    Cpu
    (s): 95.2us,  4.8sy,  0.0ni,  0.0id,  0.0wa,  0.0hi,  0.0si
    Mem
    :   1033172k total,   981184k used,    51988k free,   103748k buffers
    Swap
    :  2040212k total,   139804k used,  1900408k free,   492576k cached

      PID USER      PR  NI  VIRT  RES  SHR S 
    %CPU %MEM    TIME+  COMMAND                                                                                                              
    14952 mysql     16   0 53360  28m 2752 S  192  2.8   1589
    :07 mysqld                                                                                                               
    32362 nobody    17   0 27252  12m 2908 S    4  1.3   0
    :00.62 httpd                                                                                                                
    32370 nobody    15   0 22176 7988 2896 S    1  0.8   0
    :00.19 httpd                                                                                                                
    31633 nobody    16   0 28288  14m 3928 S    1  1.5   0
    :01.77 httpd                                                                                                                
    31634 nobody    15   0 27008  13m 3792 S    1  1.3   0
    :01.26 httpd                                                                                                                
     2067 root      15   0     0    0    0 S    0  0.0   0
    :17.04 kjournald                                                                                                            
    28306 nobody    16   0 24756  10m 3688 S    0  1.1   0
    :02.38 httpd                                                                                                                
    31639 root      16   0  7208 2308 1848 R    0  0.2   0
    :00.07 sshd                                                                                                                 
        1 root      16   0  2060  448  420 S    0  0.0   0
    :11.84 init                                                                                                                 
        2 root      RT   0     0    0    0 S    0  0.0   0
    :00.91 migration/0                                                                                                          
        3 root      34  19     0    0    0 S    0  0.0   0
    :00.06 ksoftirqd/0                                                                                                          
        4 root      RT   0     0    0    0 S    0  0.0   0
    :00.45 migration/1                                                                                                          
        5 root      34  19     0    0    0 S    0  0.0   0
    :00.06 ksoftirqd/1                                                                                                          
        6 root       5 
    -10     0    0    0 S    0  0.0   0:00.08 events/0                                                                                                             
        7 root       5 
    -10     0    0    0 S    0  0.0   0:00.05 events/1                                                                                                             
        8 root       8 
    -10     0    0    0 S    0  0.0   0:00.00 khelper                                                                                                              
        9 root      14 
    -10     0    0    0 S    0  0.0   0:00.00 kacpid                                                                                                               
       33 root       5 
    -10     0    0    0 S    0  0.0   0:00.00 kblockd/0                                                                                                            
       34 root       5 
    -10     0    0    0 S    0  0.0   0:00.00 kblockd/1                                                                                                            
       35 root      15   0     0    0    0 S    0  0.0   0
    :00.00 khubd                                                                                                                
       54 root      15   0     0    0    0 S    0  0.0   0
    :33.93 kswapd0                                                                                                              
       55 root       6 
    -10     0    0    0 S    0  0.0   0:00.00 aio/0                                                                                                                
       56 root       5 
    -10     0    0    0 S    0  0.0   0:00.00 aio/1                                                                                                                
      200 root      25   0     0    0    0 S    0  0.0   0
    :00.00 kseriod                                                                                                              
      429 root       5 
    -10     0    0    0 S    0  0.0   0:00.00 ata/0                                                                                                                
      430 root       9 
    -10     0    0    0 S    0  0.0   0:00.00 ata/1                                                                                                                
      431 root       9 
    -10     0    0    0 S    0  0.0   0:00.00 ata_aux                                                                                                              
      435 root      15   0     0    0    0 S    0  0.0   0
    :00.00 scsi_eh_0                                                                                                            
      436 root      15   0     0    0    0 S    0  0.0   0
    :00.00 scsi_eh_1                                                                                                            
      457 root      15   0     0    0    0 S    0  0.0   0
    :06.55 kjournald                                                                                                            
     1682 root       6 
    -10  3308  348  344 S    0  0.0   0:00.01 udevd                                                                                                                
     1983 root       6 
    -10     0    0    0 S    0  0.0   0:00.00 kauditd                                                                                                              
     2066 root      15   0     0    0    0 S    0  0.0   0
    :00.00 kjournald                                                                                                            
     2068 root      16   0     0    0    0 S    0  0.0   0
    :06.82 kjournald                                                                                                            
     2069 root      15   0     0    0    0 S    0  0.0   0
    :14.51 kjournald 
    I get this when i run a optimization script mentioned on this site


    SLOW QUERIES
    Current long_query_time = 5 sec.
    You have 62309 out of 1692002 that take longer than 5 sec. to complete
    The slow query log is enabled.
    Your long_query_time seems to be fine

    The aversage time is like 10 sconds. thats way to long.


    MEMORY USAGE
    Max Memory Ever Allocated : 381 M
    Configured Max Per-thread Buffers : 2 G
    Configured Max Global Buffers : 24 M
    Configured Max Memory Limit : 2 G
    Total System Memory : 2.93 G

    Max memory limit exceeds 85% of total system memory

    HMMM too much memory usuage?

    CPU INFO

    Processor #1 Vendor: GenuineIntel
    Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #1 speed: 2793.342 MHz
    Processor #1 cache size: 1024 KB

    Processor #2 Vendor: GenuineIntel
    Processor #2 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #2 speed: 2793.342 MHz
    Processor #2 cache size: 1024 KB

    Memory Info


    Memory: 1032012k/1047088k available (1884k kernel code, 14412k reserved, 760k data, 188k init, 129584k highmem)

    Current Memory Usage
    total used free shared buffers cached
    Mem: 1033172 843604 189568 0 97684 385020
    -/+ buffers/cache: 360900 672272
    Swap: 2040212 139804 1900408
    Total: 3073384 983408 2089976




    Suggestions for my.cnf to bring down the CPU load but also keep site loading good?
     
  2. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    When did this start happening? Configuration may or may not help and the most probable cause is heavy scripts, corrupt tables or non-indexed tables with high usage hammering at mysql. Check which processes are running and using up resources.

    Install mytop to monitor mysql processes and get a better idea of the cause. You could also use more ram as well.

    http://jeremy.zawodny.com/mysql/mytop/
     
Loading...

Share This Page