High CPU usage mysql 5.6
Hello,
Recently i upgraded from mysql 5.5 to mysql 5.6 and now i am facing a very high cpu usage.
Please find below the my.cnf configuration that worked on mysql 5.5 but its seems its not working on 5.6
innodb_buffer_pool_size=2g
open_files_limit=3692
tmpdir="/mysqltmp"
concurrent_insert=ALWAYS
join_buffer_size=3M
table_open_cache=2k
max_heap_table_size=512M
query_cache_size=128M
tmp_table_size=512M
low_priority_updates=1
max_connections=500
key_buffer_size=256M
thread_cache_size=16k
table_definition_cache=8k
key_buffer=64M
query_cache_limit=4M
innodb_file_per_table=1
long_query_time=5
interactive_timeout=30
low_priority_updates=1
max_connections=500
key_buffer_size=256M
thread_cache_size=16k
table_definition_cache=8k
key_buffer=64M
query_cache_limit=4M
innodb_file_per_table=1
long_query_time=5
interactive_timeout=30
wait_timeout=30
default-storage-engine=MyISAM
query_cache_size=8M
local-infile=0
-
run mysqltuner.pl and post the result 0 -
Hello :) Yes, please let MySQL run for at least 24 hours and then run the MySQL tuner. You can post the output here. You can find more information on obtaining MySQL statistics at: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you. 0 -
Hello Please find the below: >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.16 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 407) [--] Data in InnoDB tables: 9M (Tables: 177) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] Total fragmented tables: 35 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1m 3s (37K q [599.889 qps], 812 conn, TX: 903M, RX: 5M) [--] Reads / Writes: 83% / 17% [--] Total buffers: 2.6G global + 4.9M per thread (1500 max threads) [!!] Maximum possible memory usage: 9.7G (127% of installed RAM) [OK] Slow queries: 0% (0/37K) [OK] Highest usage of available connections: 1% (22/1500) [OK] Key buffer size / total MyISAM indexes: 64.0M/260.4M [OK] Key buffer hit rate: 99.9% (10M cached / 11K reads) [OK] Query cache efficiency: 70.8% (23K cached / 33K selects) [!!] Query cache prunes per day: 4749257 [OK] Sorts requiring temporary tables: 2% (112 temp sorts / 3K sorts) [!!] Temporary tables created on disk: 33% (427 on disk / 1K total) [OK] Thread cache hit rate: 97% (22 created / 812 connections) [OK] Table cache hit rate: 94% (132 open / 139 opened) [OK] Open file limit used: 0% (130/30K) [OK] Table locks acquired immediately: 99% (12K immediate / 12K locks) [OK] InnoDB data size / buffer pool: 9.2M/2.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 8M)
[QUOTE] [mysqld] max_connect_errors=0 max_allowed_packet=1MB host_cache_size=128 performance_schema=ON table_definition_cache=400 innodb_buffer_pool_size=2g open_files_limit=30000 tmpdir="/mysqltmp" concurrent_insert=ALWAYS join_buffer_size=4M table_open_cache=2k max_heap_table_size=512M query_cache_size=128M tmp_table_size=512M low_priority_updates=1 max_connections=500 key_buffer_size=256M thread_cache_size=50 table_definition_cache=8k table_open_cache=10000 key_buffer=64M query_cache_limit=4M innodb_file_per_table=1 long_query_time=5 interactive_timeout=30 low_priority_updates=1 max_connections=1500 key_buffer_size=256M table_definition_cache=400 key_buffer=64M query_cache_limit=4M innodb_file_per_table=1 long_query_time=5 interactive_timeout=30 wait_timeout=30 default-storage-engine=MyISAM query_cache_size=8M local-infile=0 query_cache_type=ON max_allowed_packet=1MB0 -
That output suggests MySQL was only running for a little over a minute. Let it run for at least 24 hours before running the tuner for more accurate results. Thank you. 0 -
You have many duplicated variables and some of them conflicting, like these (conflicts): max_connections=1500 max_connections=500 query_cache_size=128M query_cache_size=8M table_definition_cache=400 table_definition_cache=8k table_open_cache=10000 table_open_cache=2k
Is the first one of these two, key_buffer, a variable?key_buffer=64M key_buffer_size=256M0 -
Hello, Actually i am not a professional with this, can u help me to delete and fix what its need to be fixed? Thank you. 0 -
Just fixed and used the below: innodb_file_per_table=1 innodb_buffer_pool_size=2g default-storage-engine=MyISAM max_connect_errors=0 max_allowed_packet=1MB max_connections=1500 host_cache_size=128 thread_cache_size=50 table_open_cache=2k table_definition_cache=8k open_files_limit=30000 long_query_time=5 query_cache_size=128M query_cache_limit=4M key_buffer_size=256M key_buffer=64M join_buffer_size=4M max_heap_table_size=512M tmp_table_size=512M low_priority_updates=1 interactive_timeout=30 wait_timeout=30 local-infile=0 query_cache_type=ON performance_schema=ON concurrent_insert=ALWAYS0
Please sign in to leave a comment.
Comments
7 comments