High CPU usage mysql 5.6

[jkassem]

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

 

[thinkbot]

run mysqltuner.pl and post the result

 

[cPanelMichael]

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.

 

[jkassem]

Hello Please find the below:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  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]

[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=1MB

 

[cPanelMichael]

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.

 

[Archmactrix]

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=256M

 

[jkassem]

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.

 

[jkassem]

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=ALWAYS