Mysqld is using 600% of CPU !

[george912]

Hello ..

I had a VPS 4GB ram 8 CPU Cores with KVM virtualization , i migrated my site to it and i found mysql is causing high load at specific times 3:00 AM but they are not my peak times anyway here is my.cnf file

root@server [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1
max_connections = 150
wait_timeout = 60

thread_cache_size = 64
table_cache = 8192
query_cache_size = 96M
tmp_table_size = 96M
max_heap_table_size = 96M

key_buffer_size = 64M

innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M

sort_buffer_size = 256K
join_buffer_size = 256K

and here is mysqltuner.pl results

root@server [~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 824)
[--] Data in InnoDB tables: 208K (Tables: 13)
[--] Data in MEMORY tables: 124K (Tables: 2)
[!!] Total fragmented tables: 17

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 18m 43s (181K q [9.515 qps], 46K conn, TX: 5B, RX: 19M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 773.0M global + 1.1M per thread (150 max threads)
[OK] Maximum possible memory usage: 941.8M (23% of installed RAM)
[OK] Slow queries: 0% (34/181K)
[OK] Highest usage of available connections: 18% (28/150)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.1G
[OK] Key buffer hit rate: 100.0% (119M cached / 21K reads)
[OK] Query cache efficiency: 78.0% (63K cached / 81K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 57% (2K temp sorts / 3K sorts)
[!!] Temporary tables created on disk: 36% (75 on disk / 207 total)
[OK] Thread cache hit rate: 99% (28 created / 46K connections)
[OK] Table cache hit rate: 97% (400 open / 411 opened)
[OK] Open file limit used: 3% (549/16K)
[OK] Table locks acquired immediately: 98% (40K immediate / 40K locks)
[OK] InnoDB data size / buffer pool: 208.0K/512.0M

-------- 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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
tmp_table_size (> 96M)
max_heap_table_size (> 96M)

I repaired/optimized all server database and nothing changed


Waiting your appreciable replies