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 <[email protected]>
 >>  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 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.