2

I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25 running most of the time. Still the cpu usage goes beyond 600%

I have 24 GB RAM and 64 bit 8 core processor.

I got this results from mysqltuner but I cannot figure out what configuration change I need to do in my.cnf file.

my.cnf:

[mysqld]
max_connections=1000
innodb_file_per_table=1
innodb_io_capacity = 5000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_size=10G
sort_buffer_size=2G
read_buffer_size=2G
join_buffer_size=2G
tmp_table_size=2G
key_buffer_size=3G
max_allowed_packet=160M
table_cache=1G
slow_query_log = 1
innodb_flush_method = O_DIRECT
query_cache_type = 1
thread_cache_size = 150
thread_concurrency=16
query_cache_limit=10M
query_cache_size=2G

Output of mysqltuner:

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 288M (Tables: 20)
[--] Data in InnoDB tables: 3G (Tables: 179)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 18

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 4m 46s (7M q [300.507 qps], 266K conn, TX: 12B, RX: 2B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 15.0G global + 6.0G per thread (214 max threads)
[!!] Maximum possible memory usage: 1299.1G (5769% of installed RAM)
[OK] Slow queries: 0% (1K/7M)
[OK] Highest usage of available connections: 85% (182/214)
[OK] Key buffer size / total MyISAM indexes: 3.0G/61.2M
[OK] Key buffer hit rate: 99.8% (4M cached / 6K reads)
[OK] Query cache efficiency: 79.2% (5M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 366K sorts)
[OK] Temporary tables created on disk: 21% (12K on disk / 57K total)
[OK] Thread cache hit rate: 99% (182 created / 266K connections)
[OK] Table cache hit rate: 22% (400 open / 1K opened)
[OK] Open file limit used: 9% (96/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 10.0G/4.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability

Please help me figure out how to improve cpu usage for mysql.

1

3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing.

This is not going to be a short answer, but try this:

  • Enable the slow query log (set global slow_query_log=on and set global slow_query_log_file='/someplace/you/can/find/it'), and set it to log anything above 1 second (set global long_query_time=1).
  • Let it log a little bit. Then look at the output. You can just look at the slow log or use a tool like pt-query-digest (http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html) to find your long-running queries. Look especially for queries that scan a lot of rows to only get a few rows of results.
  • Pick a query that gets run a lot. Look at the involved tables (show create table my-table-in-this-query) and see what the indexes are. Also see the explain plan to show what indexes mysql plans to use (explain query select blah blah put your query here).
  • Add an index. See CPU decrease.

You might not be able to index everything; the queries may have to be rewritten as well. But I bet you can get a lot of CPU back by doing this for your top queries.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy