Hello,
can you please help me to optimize mysql to reduce high cpu usage and sometimes big load?
Thanks
my.cnf
mysqltuner
If you need more info just ask 
can you please help me to optimize mysql to reduce high cpu usage and sometimes big load?
Thanks
my.cnf
Code:
[mysqld]
slow-query-log=1
long-query-time=1
key_buffer_size =3G
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_size = 128M
table_open_cache = 96
thread_cache_size = 4
innodb_file_per_table=1
max_allowed_packet=268435456
innodb_buffer_pool_size=134217728
open_files_limit=10000
default-storage-engine=MyISAM
Code:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 16G (Tables: 795)
[--] Data in InnoDB tables: 3M (Tables: 28)
[OK] Total fragmented tables: 0
-------- Security Recommendations ---------------------------------------------- --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[!!] CVE-2016-6662(<= 5.5.52) : "Oracle MySQL through 5.5.52
[--] False positive CVE(s) for MySQL and MariaDB 5.5.x can be found.
[--] Check careful each CVE for those particular versions
[!!] 1 CVE(s) found for your MySQL release.
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 15h 15m 8s (109M q [348.735 qps], 6M conn, TX: 36736G, RX: 6G)
[--] Reads / Writes: 70% / 30%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 3.9G
[--] Other process memory: 2.6G
[--] Total buffers: 3.5G global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.9G (12.53% of installed RAM)
[OK] Maximum possible memory usage: 3.9G (12.52% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (29K/109M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.01% (924/6690922)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 4% (88K temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 94% (1M on disk / 1M total)
[OK] Thread cache hit rate: 89% (693K created / 6M connections)
[!!] Table cache hit rate: 0% (96 open / 228K opened)
[OK] Open file limit used: 1% (168/10K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.9% (641M used / 3B cache)
[OK] Key buffer size / total MyISAM indexes: 3.0G/4.6G
[OK] Read Key buffer hit rate: 98.3% (764M cached / 13M reads)
[!!] Write Key buffer hit rate: 75.4% (4M cached / 1M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/4.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.90625 %): 5.0M/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (2837190 hits/ 2838089 total)
[OK] InnoDB Write log efficiency: 99.77% (99305 hits/ 99538 total)
[OK] InnoDB log waits: 0.00% (0 waits / 233 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
1 CVE(s) found for your MySQL release. Consider upgrading your version !
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (10000) variable
should be greater than table_open_cache (96)
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_type (=0)
table_open_cache (> 96)
innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.
