MYSQL High CPU Usage

singh9211 · Jan 27, 2015

Hello,

I am in great trouble, my cpu usage is very high because of mysql, average load remains at 6.71 5.91 5.23
There is not much traffic on my websites, i have recently migrated the websites to this new server from ovh, it has 6 cores and 32 gigs of ram, so ample of resources. Due this mysql high CPU usages my websites are opening very slow.

I don't know what to do.

Here is my /etc/my.cnf file content, i have tweaked it a little:

Code:

[mysqld]
innodb_file_per_table=1
open_files_limit=1000000
local-infile=0
#bind-address=127.0.0.1
max_connections=160
query_cache_size=512M
join_buffer_size=1028K
tmp_table_size=32M
max_heap_table_size=32M
table_open_cache=2000
innodb_buffer_pool_size=500217728

max_allowed_packet=268435456

I also ran the mysqltunner and below is the output:

Code:

root@server3 [~]# perl mysqltuner.pl 

 >>  MySQLTuner 1.4.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.40-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 5G (Tables: 4859)
[--] Data in InnoDB tables: 251M (Tables: 832)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 54

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 21m 39s (11M q [131.578 qps], 115K conn, TX: 9B, RX: 2B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 1.0G global + 3.6M per thread (160 max threads)
[OK] Maximum possible memory usage: 1.6G (5% of installed RAM)
[OK] Slow queries: 0% (3/11M)
[OK] Highest usage of available connections: 62% (100/160)
[OK] Key buffer size / total MyISAM indexes: 8.0M/684.0M
[OK] Key buffer hit rate: 100.0% (45B cached / 136K reads)
[OK] Query cache efficiency: 77.0% (7M cached / 10M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 248K sorts)
[!!] Joins performed without indexes: 15135
[OK] Temporary tables created on disk: 0% (14K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 5% (2K open / 38K opened)
[OK] Open file limit used: 0% (3K/1M)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB buffer pool / data size: 477.0M/251.3M
[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
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_open_cache (> 2000)

But still no success. So its affecting my bussiness a lot, i am loosing lot of customers, so any help is greatly apprectiated.

Regards.

wrender · Jan 27, 2015

I think setting your query cache too high like you have might cause problems. Maybe try these settings (below)? Or also try disabling query_cache by setting it to "query_cache_size=0". This seem to work for us with MySQL 5.6 with cPanel 11.46.x From a lot of blogs I read apparently query_cache can cause more performance problems than it is worth. Not sure how true this is.

I am not sure about how high you can set the table_open_cache. I have read that there can be negative scalability, but so far increasing it for us seems to have improved performance greatly. I have opened another thread for this, and hoping someone provides some advice on this soon.

Also, this script seems to also be helpful : https://launchpad.net/mysql-tuning-primer
Code:
[mysqld]
open_files_limit=1000000
local-infile=0
#bind-address=127.0.0.1
max_connections=160
query_cache_size=16M
join_buffer_size=1M

tmp_table_size=64M
max_heap_table_size=64M
table_open_cache=5000
table_definition_cache=3000

innodb_file_per_table=1
innodb_buffer_pool_size=3G
innodb_flush_method=O_DIRECT

max_allowed_packet=268435456

cPanelMichael · Jan 28, 2015

Hello

Feel free to let us know if the advice from the previous thread was helpful. You may want to let MySQL run for another 24 hours and run the tuner again.

Thank you.

Forums

The Community Forums

Interact with an entire community of cPanel & WHM users!

MYSQL High CPU Usage

singh9211 Member

wrender Well-Known Member

cPanelMichael Forums Analyst
Staff Member

MySQL Very High CPU Usage issue

MySQL constant high CPU usage

Help - optimize mysqltunner to reduce high cpu usage

MySQL database high CPU usage