High CPU usage by MySQL Server

Question

I have a game server, MySQL is at 100-200% CPU constantly, i've tried to optimize query-cache-size but didn't help. I have some "big" tables i guess with over a million record. I have another table with 150k record, and im suspecting that because its being written and read many times during a minute. I've ran MySQLTuner, here is the results:

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [!!] Your MySQL version 5.5.62 is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file ./srv.err doesn't exist

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1.5G (Tables: 130)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: ON
[!!] Stat are updated during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'root@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 90d 23h 30m 41s (639M q [81.385 qps], 955K conn, TX: 102G, RX: 90G)
[--] Reads / Writes: 3% / 97%
[--] Binary logging is disabled
[--] Physical Memory     : 12.0G
[--] Max MySQL memory    : 583.2M
[--] Other process memory: 0B
[--] Total buffers: 168.0M 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: 349.5M (2.86% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (4.77% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (107/639M)
[OK] Highest usage of available connections: 43% (66/151)
[OK] Aborted connections: 0.55%  (5273/955610)
[!!] 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.
[!!] Query cache efficiency: 0.0% (0 cached / 25M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (456 temp sorts / 3M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 35% (2K on disk / 6K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 96% (236 open / 245 opened)
[OK] table_definition_cache(400) is upper than number of tables(211)
[OK] Open file limit used: 0% (359/352K)
[OK] Table locks acquired immediately: 99% (632M immediate / 633M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/379.3M
[OK] Read Key buffer hit rate: 99.6% (3B cached / 15M reads)
[!!] Write Key buffer hit rate: 86.2% (77M cached / 66M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

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

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Restrict Host for 'root'@% to root@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES;
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    SET innodb_stats_on_metadata = OFF
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

What do you think? What should i do? Dedicated server with Intel Xeon 5570 + 12G RAM + 2x750GB HDD in RAID1

Thanks in advance.

I would try the recommendations listed at the bottom of the analysis report. — Rob Moll, Dec 06 '19 at 17:43
Run show full processlist on the server. See what queries appear there often and optimise that. — exussum, Dec 06 '19 at 17:44
"InnoDB is enabled but isn't being used" - MyISAM doesn't perform well in a high concurrency environment. Switch to InnoDB. — Paul Spiegel, Dec 06 '19 at 17:48
A) Switch to InnoDB as soon as you can. B) Tune your InnoDB installation and make sure to assign memory to the InnoDB buffer pool. C) Upgrade MySQL as newer versions perform significantly better, especially vs. 5.5. D) Get more memory. Another 64GB wouldn't break the bank, it'd be a couple of hundred bucks, and can make a world of difference. E) Switch to SSD if you have IO issues. — tadman, Dec 06 '19 at 17:52
Additional information request. # cores, any SSD devices on MySQL Host server. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. — Wilson Hauck, Dec 08 '19 at 13:12
High CPU means missing index or poorly formulated query. Find that query and let's discuss it. — Rick James, Dec 24 '19 at 04:48

score 1 · Answer 1 · answered Dec 10 '19 at 17:10

There are many things that can be done, but it "depends", it could be that the CPU usage is due a running backup, some inserts depending on subqueries with missing indexes, etc, in any case after checking more in detail your environment and if the issue is mainly related to MySQL you could aks and get better answers in https://dba.stackexchange.com/

These are some extra things to check to complement what has been already suggested in the comments.

Before adding more hardware check for missing indexes, in many cases by just adding an index fixes the problem: https://stackoverflow.com/a/30627050/1135424
Regarding hardware, if you don't want to check the database, your best try would be to use fast disks "SSD".
If using FreeBSD and the DB running/stored on a ZFS pool you may try innodb_flush_log_at_trx_commit=2 this is a huge topic, read more about it https://dba.stackexchange.com/a/12612/109971 but again it heavily depends on your needs. https://wiki.freebsd.org/ZFSTuningGuide (if you are not using InnoDB this may not make any difference)

score 0 · Answer 2 · edited Jan 19 '20 at 00:05

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section version 5.5.62 (past End of Life)

innodb_stats_on_metadata=OFF  # from ON to conserve CPU cycles
query_cache_size=0  # to ensure it is not used
query_cache_type=0  # to ensure QC Type is OFF
query_cache_limit=0  # from 1M to conserve RAM
tmp_table_size=32M  # from 16M to expand RAM availability 
max_heap_table_size=32M  # from 16M to keep same size as tmp_table_size
thread_cache_size=64  # from 0 to support your highest use of 66 connections
slow_query_log=ON  # from OFF to allow log review and corrective action for 107 in 90 days
skip_name_resolve=ON  # from OFF to conserve CPU cycle for each connection
key_buffer_size=200M  # from 8M - you have 379M of MyISAM indexes, keep 1/2 in RAM
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50  # from 100 for hot/warm separation
key_cache_block_size=16384  # from 1024 to reduce frequency of management

You will find these configuration changes will REDUCE CPU busy significantly.

High CPU usage by MySQL Server

2 Answers2