Skip to main content

After new cpanel system high mysql usage

musti19
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: >> MySQLTuner 1.3.0 - Major Hayden >> 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: [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

Comments

12 comments

Date Votes
  • cPanelMichael
    Hello :) Please let MySQL run for at least 24 hours to get the most accurate results from the tuner script. Thank you.
    0
  • musti19
    here are the results: [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..
    0
  • cPanelPeter cPanel Staff
    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.
    0
  • musti19
    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
    0
  • cPanelMichael
    Are you experiencing website slowness and poor server performance/response time, or is it just the numbers alone that concern you? Thank you.
    0
  • musti19
    the system is slower then before and top shows me, that alle Cores are at %100
    0
  • cPanelMichael
    Have you ran "sar -u" or "iostat" to check the disk I/O usage? Thank you.
    0
  • musti19
    the output shows me..: ------ 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
    ------
    0
  • cPanelMichael
    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.
    0
  • musti19
    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
    0
  • cPanelMichael
    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.
    0
  • musti19
    You 're right. I've overlooked it :)
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post