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.

After new cpanel system high mysql usage

Discussion in 'Workarounds and Optimization' started by musti19, May 30, 2014.

  1. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello,
    i installed a new cpanel server and modified the configs like the old server.
    But the mysql server is ever high loaded, cant understand where is the problem.
    On old server the cpu usage was ~0.80, now >4.0

    My system: i7 processor and 32GB RAM

    I changed the values by recommendations from this script,
    without improvements..

    Here are the results of mysqltuner:
    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 5G (Tables: 32257)
    [--] Data in InnoDB tables: 624M (Tables: 12982)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 247)
    [!!] Total fragmented tables: 1072
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 48m 34s (1M q [369.249 qps], 17K conn, TX: 15B, RX: 151M)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 1.0G global + 4.1M per thread (600 max threads)
    [OK] Maximum possible memory usage: 3.4G (10% of installed RAM)
    [OK] Slow queries: 0% (764/1M)
    [OK] Highest usage of available connections: 26% (157/600)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.0G
    [OK] Key buffer hit rate: 96.0% (1M cached / 41K reads)
    [OK] Query cache efficiency: 91.0% (862K cached / 947K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (76 temp sorts / 8K sorts)
    [!!] Joins performed without indexes: 3267
    [OK] Temporary tables created on disk: 16% (1K on disk / 11K total)
    [OK] Thread cache hit rate: 99% (157 created / 17K connections)
    [!!] Table cache hit rate: 0% (5K open / 871K opened)
    [OK] Open file limit used: 6% (3K/50K)
    [OK] Table locks acquired immediately: 97% (159K immediate / 164K locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/624.4M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 5000)
        innodb_buffer_pool_size (>= 624M)
    
    Config:
    Code:
    [mysqld]
    max_connections = 600
    max_user_connections=150
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 20
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 128M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 100
    concurrent_insert = 2
    #table_lock_wait_timeout only for mysql5
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 5M
    query_cache_size = 128M # alt 512
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    slow_query_log
    open_files_limit=50000
    
    innodb_flush_log_at_trx_commit  = 2
    innodb_support_xa               = 0
    innodb_log_buffer_size          = 128M
    
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    [myisamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M # alt 80M
    thread_stack = 192K
    
    thank you
     
    #1 musti19, May 30, 2014
    Last edited: May 30, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,675
    Likes Received:
    647
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    here are the results:


    Code:
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 5G (Tables: 32257)
    [--] Data in InnoDB tables: 625M (Tables: 13004)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 247)
    [!!] Total fragmented tables: 1107
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 2h 25m 7s (21M q [221.407 qps], 583K conn, TX: 85B, RX: 2B)
    [--] Reads / Writes: 61% / 39%
    [--] Total buffers: 1.4G global + 4.1M per thread (600 max threads)
    [OK] Maximum possible memory usage: 3.8G (12% of installed RAM)
    [OK] Slow queries: 0% (9K/21M)
    [OK] Highest usage of available connections: 21% (129/600)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.0G
    [OK] Key buffer hit rate: 96.2% (28M cached / 1M reads)
    [OK] Query cache efficiency: 87.1% (15M cached / 17M selects)
    [!!] Query cache prunes per day: 110553
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 179K sorts)
    [!!] Joins performed without indexes: 21136
    [OK] Temporary tables created on disk: 21% (51K on disk / 242K total)
    [OK] Thread cache hit rate: 99% (129 created / 583K connections)
    [!!] Table cache hit rate: 0% (5K open / 29M opened)
    [OK] Open file limit used: 6% (3K/50K)
    [OK] Table locks acquired immediately: 96% (3M immediate / 4M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/625.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 512M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 5000)
        innodb_buffer_pool_size (>= 625M)
    without results..
     
  4. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    Have you made the suggested recommendations yet as outlined? If not, please make backups of your /etc/my.cnf file and then make the changes listed. Then restart MySQL and let it run again for 24 hours and run mysqltuner to see if it has improved.
     
  5. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    hello,
    i don't understand where can be the problem.
    I do all recommends, but the mysql uses 486% CPU and 25% Memory. :(
    Before new Installation of cPanel , there was no problems.
    This problem exists since directly new installation of cPanel with same mysql-config and same mysql version.

    The command of the process in whm looks like:
    /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/server....err --open-files-limit=50000 --pid-file=/var/lib/mysql/server....pid
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,675
    Likes Received:
    647
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Are you experiencing website slowness and poor server performance/response time, or is it just the numbers alone that concern you?

    Thank you.
     
  7. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    the system is slower then before and top shows me, that alle Cores are at %100
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,675
    Likes Received:
    647
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  9. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    the output shows me..:
    ------
    Code:
    Average:        CPU     %user     %nice   %system   %iowait    %steal     %idle
    Average:        all     24.24      1.73     57.61      1.42      0.00     15.01
    
    
    Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
    sdb             149.14      6679.37      4918.06 11842407864 8719634955
    sda             161.92      7150.43      4918.06 12677584786 8719634955
    md0               0.53         1.64         2.56    2907472    4540504
    md1               0.00         0.00         0.00       1454         40
    md2             634.87       606.30      4914.17 1074957876 8712742320
    ------
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,675
    Likes Received:
    647
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    The "iowait" value looks fine. You are welcome to run the tuner again after implementing the changes advised on it's first run and post the results for user-feedback on additional changes that might help.

    Thank you.
     
  11. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    ok, i found the problem.
    I found the command "mysqladmin processlist" with that i can see all mysql processes,
    and detected that one users account using extremly sql statements, because an error.


    Would be happy, if this command is integrated in whm beside to the cpu process lists.
    thank you
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,675
    Likes Received:
    647
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I am happy to see you were able to determine the issue. That output is available at:

    "WHM Home » SQL Services » Show MySQL Processes"

    Thank you.
     
  13. musti19

    musti19 Well-Known Member

    Joined:
    Jan 20, 2013
    Messages:
    103
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You 're right. I've overlooked it :)
     
Loading...

Share This Page