jkassem

Registered
Mar 7, 2014
4
0
1
cPanel Access Level
Root Administrator
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

Code:
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
 

jkassem

Registered
Mar 7, 2014
4
0
1
cPanel Access Level
Root Administrator
Hello Please find the below:

Code:
 >>  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

Administrator
Staff member
Apr 11, 2011
47,911
2,234
363
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

Well-Known Member
Jan 20, 2012
138
2
68
cPanel Access Level
Root Administrator
You have many duplicated variables and some of them conflicting, like these (conflicts):

Code:
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?

Code:
key_buffer=64M
key_buffer_size=256M
 

jkassem

Registered
Mar 7, 2014
4
0
1
cPanel Access Level
Root Administrator
Just fixed and used the below:

Code:
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