MYSQL High CPU Usage

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.