Help - optimize mysqltunner to reduce high cpu usage

Hello, can you please help me to optimize mysql to reduce high cpu usage and sometimes big load? Thanks my.cnf



[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

mysqltuner

[--] 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.

If you need more info just ask :)

cPanelMichael

November 14, 2016 18:52

Hello, I'll leave this thread open for others to provide user-feedback on custom MySQL tuning, but regarding the high load average, you may also want to review the following thread: Troubleshooting high server loads on Linux servers Thank you.

urkeee

November 14, 2016 19:25

Thanks, very useful! Can someone help me about MySql tuning?

Eminds

November 15, 2016 00:40

Go through the mysql tuner report .. compare it with your my.cnf file and update the parameters or you may need to add new parameters to your my.cnf as per the report. After making the changes monitor the server for 24 hours to check the exact results.

November 16, 2016 20:23

Can you please post how my.cnf should look like? I dont know what to add or what to change

November 16, 2016 21:26

Hello, You may need to consult with a qualified system administrator if you need direct and specific tuning advice. You can find a list of system administration services at: System Administration Services | cPanel Forums Thank you.

Help - optimize mysqltunner to reduce high cpu usage

Comments

Didn't find what you were looking for?