Help with my.cnf and high cpu usage

[clopezi]

Hi,

I have a very busy website in this server:

4 vCores (2.4Ghz, from E5-2620v3)
60GB RAM

MySQL 5.6
Centos 7

I have a very cpu usage in heavy conditions, and i hope that you can help me a little to improve performance. In the other hand, i have almost 30GB of ram free.

I have this my.cnf config:

[mysqld]
tmpdir=/var/mysqltmp
innodb_file_per_table=1

innodb_use_native_aio = 0
slow-query-log=1
long-query-time=1

max_allowed_packet=268435456
open_files_limit=50000

max_heap_table_size=512M
max_connections=500
query_cache_size=200M
query_cache_limit=16M
query_cache_type=1

innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=8
thread_cache_size=100


join_buffer_size=2M
innodb_log_buffer_size=20M
innodb_log_file_size=1G
table_definition_cache=8400


table_open_cache=64
innodb_file_per_table=1
table_open_cache_instances=8


default-storage-engine=MyISAM

And this says mysqltuner (only a few hours working)

>> MySQLTuner 1.6.9 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 922M (Tables: 376)
[--] Data in InnoDB tables: 1G (Tables: 194)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 25

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '[email protected]' has user name as password.
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6h 27m 16s (840K q [36.159 qps], 63K conn, TX: 9G, RX: 132M)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is disabled
[--] Total buffers: 10.2G global + 2.9M per thread (500 max threads)
[--] P_S Max memory usage: 493M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 10.8G (18.78% of installed RAM)
[OK] Maximum possible memory usage: 12.1G (21.06% of installed RAM)
[OK] Slow queries: 0% (1K/840K)
[OK] Highest usage of available connections: 6% (32/500)
[OK] Aborted connections: 0.00% (1/63568)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Temporary tables created on disk: 60% (12K on disk / 21K total)
[OK] Thread cache hit rate: 99% (32 created / 63K connections)
[!!] Table cache hit rate: 0% (64 open / 102K opened)
[OK] Open file limit used: 0% (83/65K)
[!!] Table locks acquired immediately: 93%

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 493.3M

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.6% (1M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/341.5M
[!!] Read Key buffer hit rate: 61.4% (1M cached / 457K reads)
[!!] Write Key buffer hit rate: 4.8% (27K cached / 25K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 10.0G/1.1G
[!!] InnoDB buffer pool instances: 8
[!!] InnoDB Used buffer: 17.08% (111915 used/ 655356 total)
[OK] InnoDB Read buffer efficiency: 99.99% (111232660 hits/ 111242642 total)
[OK] InnoDB Write log efficiency: 99.90% (3002088 hits/ 3005008 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2920 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: table_cache negative scalability
Beware that open_files_limit (65536) variable
should be greater than table_open_cache ( 64)
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
query_cache_type (=0)
tmp_table_size (> 16M)
max_heap_table_size (> 512M)
table_open_cache (> 64)
key_buffer_size (> 341.5M)
innodb_buffer_pool_instances(=10)

Thank you!

 

[cPanelMichael]

Hello

You can try adjusting your /etc/my.cnf file based on the "Variables to adjust" output of the MySQL tuner, and then run the MySQL tuner again after 24 hours to see if the results have changed.

Thank you.