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 CPU high, Need help to Optimize

Discussion in 'Workarounds and Optimization' started by Tinh Duong, Aug 24, 2012.

  1. Tinh Duong

    Tinh Duong Registered

    Joined:
    Aug 24, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ha Noi, Vietnam, Vietnam
    cPanel Access Level:
    Root Administrator
    I have a server with these specs :

    PHP:
    Last loginFri Aug 24 13:26:37 2012 from 118.71.57.107
    [root@localhost ~]# free
                 
    total       used       free     shared    buffers     cached
    Mem
    :       4149456    2817900    1331556          0     342292    1348472
    -/+ buffers/cache:    1127136    3022320
    Swap
    :      8193140      55532    8137608
    [root@localhost ~]# cat /proc/cpuinfo
    processor       0
    vendor_id       
    GenuineIntel
    cpu family      
    6
    model           
    15
    model name      
    Intel(RXeon(RCPU           X3210  2.13GHz
    stepping        
    11
    cpu MHz         
    2128.000
    cache size      
    4096 KB
    physical id     
    0
    siblings        
    4
    core id         
    0
    cpu cores       
    4
    apicid          
    0
    fdiv_bug        
    no
    hlt_bug         
    no
    f00f_bug        
    no
    coma_bug        
    no
    fpu             
    yes
    fpu_exception   
    yes
    cpuid level     
    10
    wp              
    yes
    flags           
    fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
    pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni
     monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips        
    4256.15

    processor       
    1
    vendor_id       
    GenuineIntel
    cpu family      
    6
    model           
    15
    model name      
    Intel(RXeon(RCPU           X3210  2.13GHz
    stepping        
    11
    cpu MHz         
    2128.000
    cache size      
    4096 KB
    physical id     
    0
    siblings        
    4
    core id         
    1
    cpu cores       
    4
    apicid          
    1
    fdiv_bug        
    no
    hlt_bug         
    no
    f00f_bug        
    no
    coma_bug        
    no
    fpu             
    yes
    fpu_exception   
    yes
    cpuid level     
    10
    wp              
    yes
    flags           
    fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
    pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni
     monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips        
    4255.40

    processor       
    2
    vendor_id       
    GenuineIntel
    cpu family      
    6
    model           
    15
    model name      
    Intel(RXeon(RCPU           X3210  2.13GHz
    stepping        
    11
    cpu MHz         
    2128.000
    cache size      
    4096 KB
    physical id     
    0
    siblings        
    4
    core id         
    2
    cpu cores       
    4
    apicid          
    2
    fdiv_bug        
    no
    hlt_bug         
    no
    f00f_bug        
    no
    coma_bug        
    no
    fpu             
    yes
    fpu_exception   
    yes
    cpuid level     
    10
    wp              
    yes
    flags           
    fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
    pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni
     monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips        
    4255.43

    processor       
    3
    vendor_id       
    GenuineIntel
    cpu family      
    6
    model           
    15
    model name      
    Intel(RXeon(RCPU           X3210  2.13GHz
    stepping        
    11
    cpu MHz         
    2128.000
    cache size      
    4096 KB
    physical id     
    0
    siblings        
    4
    core id         
    3
    cpu cores       
    4
    apicid          
    3
    fdiv_bug        
    no
    hlt_bug         
    no
    f00f_bug        
    no
    coma_bug        
    no
    fpu             
    yes
    fpu_exception   
    yes
    cpuid level     
    10
    wp              
    yes
    flags           
    fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
    pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni
     monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr lahf_lm
    bogomips        
    4255.43

    [root@localhost ~]# cat /proc/meminfo
    MemTotal:      4149456 kB
    MemFree
    :       1149436 kB
    Buffers
    :        346228 kB
    Cached
    :        1366152 kB
    SwapCached
    :      12812 kB
    Active
    :        2122160 kB
    Inactive
    :       596420 kB
    HighTotal
    :     3274156 kB
    HighFree
    :       886504 kB
    LowTotal
    :       875300 kB
    LowFree
    :        262932 kB
    SwapTotal
    :     8193140 kB
    SwapFree
    :      8137608 kB
    Dirty
    :           12064 kB
    Writeback
    :           0 kB
    AnonPages
    :     1003532 kB
    Mapped
    :          21916 kB
    Slab
    :           253876 kB
    PageTables
    :      13496 kB
    NFS_Unstable
    :        0 kB
    Bounce
    :              0 kB
    CommitLimit
    :  10267868 kB
    Committed_AS
    :  3965640 kB
    VmallocTotal
    :   116728 kB
    VmallocUsed
    :      7508 kB
    VmallocChunk
    :   108592 kB
    HugePages_Total
    :     0
    HugePages_Free
    :      0
    HugePages_Rsvd
    :      0
    Hugepagesize
    :     2048 kB
    [root@localhost ~]#
    Sometimes, the CPU used by mysql increase to 100~150, maybe 200, this time, website load very slowly.
    When it return to <100, everything seems to be ok.

    So, Please help me to tunning my config.
    This is my.cnf :
    PHP:
    [mysqld]
    datadir=/var/lib/mysql
    socket
    =/var/lib/mysql/mysql.sock
    user
    =mysql
    # skip-networking
    skip-name-resolve
    skip
    -external-locking
    # Disabling symbolic-links is recommended to prevent assorted security risks
    # symbolic-links=0

    max_connections=300
    max_user_connections 
    =0

    log_slow_queries 
    = /var/log/mysql/mysql-slow.log
    long_query_time 
    2

    query_cache_size 
    16M
    join_buffer_size 
    4M
    tmp_table_size 
    64M


    query
    -cache-type 1
    key_buffer 
    128M
    key_buffer_size 
    128M
    max_allowed_packet 
    32M
    sort_buffer_size 
    2M
    read_buffer_size 
    2M
    read_rnd_buffer_size 
    8M
    myisam_sort_buffer_size 
    64M
    thread_cache 
    16
    query_cache_type 
    1
    query_cache_size 
    128M
    query_cache_limit 
    148M
    thread_cache_size
    =16K
    max_heap_table_size 
    64M
    table_cache 
    1K
    table_definition_cache 
    4K
    open_files_limit 
    3K
    table_open_cache 
    96
    thread_concurrency 
    6

    wait_timeout
    =20



    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid
    -file=/var/run/mysqld/mysqld.pid
    nice 
    = -5
     
Loading...

Share This Page