MySQL Extremely High CPU Usage

jjtt

• April 01, 2016 14:20

MySQL will use as high as 1300% CPU in top under load, which is I am guessing 1000+ requests/min. This causes the server and request times to slow to a crawl. When I perform a simple test with 5-10 threads using my software which executes one SELECT and UPDATE statement on the server, CPU runs up immediately to 50-60%. Memory usage is always very low. I have tried using mysqltuner.pl to no avail, optimizing tables etc. However I am almost certain these issues are due to poor configuration. I also use nginx/apache. Server is CentOS 6.7, 4x (16 core) Xeon L5520 2.27GHz, 24GB memory mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1 /etc/my.cnf is as follows (datadir and socket because I tried transferring to SSD yesterday to improve performance):
[mysqld] default-storage-engine=MyISAM innodb_file_per_table=1 max_allowed_packet=268435456 table_open_cache=25000 open_files_limit=50000 max_connections=1000 query_cache_size=134217728 thread_cache_size=128 tmp_table_size=536870912 max_heap_table_size=536870912 key_buffer_size=268435456 datadir=/mysql/mysql socket=/mysql/mysql/mysql.sock
Original post won't let me edit in mysqltuner.pl data so here it is:
>> MySQLTuner 1.6.4 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.48-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 304M (Tables: 411) [--] Data in InnoDB tables: 93M (Tables: 91) [!!] Total fragmented tables: 48 -------- Security Recommendations ------------------------------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 605 basic passwords in the list. -------- CVE Security Recommendations --------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ------------------------------------------------- [--] Up for: 34m 29s (58K q [28.294 qps], 6K conn, TX: 560M, RX: 7M) [--] Reads / Writes: 42% / 58% [--] Binary logging is disabled [--] Total buffers: 1.0G global + 2.8M per thread (1000 max threads) [OK] Maximum reached memory usage: 1.0G (4.47% of installed RAM) [OK] Maximum possible memory usage: 3.7G (15.78% of installed RAM) [OK] Slow queries: 0% (0/58K) [OK] Highest usage of available connections: 1% (12/1000) [OK] Aborted connections: 0.00% (0/6943) [OK] Query cache efficiency: 85.1% (35K cached / 42K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 243 sorts) [!!] Joins performed without indexes: 14 [!!] Temporary tables created on disk: 36% (47 on disk / 129 total) [OK] Thread cache hit rate: 99% (12 created / 6K connections) [OK] Table cache hit rate: 94% (110 open / 117 opened) [OK] Open file limit used: 0% (154/51K) [OK] Table locks acquired immediately: 99% (14K immediate / 14K locks) -------- MyISAM Metrics ------------------------------------------------------ [!!] Key buffer used: 18.9% (50M used / 268M cache) [OK] Key buffer size / total MyISAM indexes: 256.0M/56.1M [!!] Read Key buffer hit rate: 94.4% (30K cached / 1K reads) [!!] Write Key buffer hit rate: 46.0% (3K cached / 1K writes) -------- InnoDB Metrics ------------------------------------------------------ [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/93.9M [OK] InnoDB buffer pool instances: 1 [!!] InnoDB Used buffer: 9.78% (801 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 100.00% (49599637 hits/ 49600366 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 8183 writes) -------- ThreadPool Metrics -------------------------------------------------- [--] ThreadPool stat is disabled. -------- AriaDB Metrics ------------------------------------------------------ [--] AriaDB is disabled. -------- TokuDB Metrics ------------------------------------------------------ [--] TokuDB is disabled. -------- Galera Metrics ------------------------------------------------------ [--] Galera is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 128.0K, or always use indexes with joins)

• 

  24x7server

  • April 02, 2016 11:54

  Hello :), I will suggest you please try to monitor your mysql process with " mysqladmin proc " command, With this command you can find out database which is increase high cpu load.

  0
• 

  cPanelMichael

  • April 04, 2016 15:15

  Hello :) Yes, as mentioned, it's a good idea to utilize the following command when the CPU usage is high:
  mysqladmin processlist
  It will help you determine which database/table is the culprit. Thank you.

  0

Please sign in to leave a comment.