mysql using 80% cpu usage

my mysql is using about 70-80% every time of cpu and about 5% memory, and many times when high traffic, websites takes too long to load

could someone help me?

my server is 8 core with 32gb ram, im using centos 6.9 with cpanel/cloudlinux

here is my my.cnf and mysqltuner log

[mysqld]

innodb_file_per_table=1

local-infile=0

tmpdir =/dev/shm

max_connections = 1024

max_user_connections=650

key_buffer_size = 1024M

myisam_sort_buffer_size = 64M

#default-storage-engine=MyISAM

join_buffer_size = 1M

read_buffer_size = 1M

sort_buffer_size = 256K

thread_cache_size = 384

wait_timeout = 20

connect_timeout = 10

tmp_table_size = 64M

max_heap_table_size = 64M

max_allowed_packet = 64M

net_buffer_length = 16384

max_connect_errors = 10

thread_concurrency = 16

concurrent_insert = 2

read_rnd_buffer_size = 786432

bulk_insert_buffer_size = 8M

query_cache_limit = 7M

query_cache_size = 64M

query_cache_type = 1

query_prealloc_size = 262144

query_alloc_block_size = 65536

transaction_alloc_block_size = 8192

transaction_prealloc_size = 4096

max_write_lock_count = 16

innodb_buffer_pool_size=8G #75 % of RAM

innodb_flush_log_at_trx_commit=2

innodb_thread_concurrency=8 # of cpus

innodb_flush_method=O_DIRECT

[mysqld_safe]

open_files_limit = 8192

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer = 384M

sort_buffer = 384M

read_buffer = 256M

/////////////////////////////

and here is mysqltuner output



-------- Performance Metrics -----------------------------------------------------------------------


```
[--] Up for: 2d 3h 25m 54s (23M q [127.290 qps], 468K conn, TX: 138G, RX: 7G)

[--] Reads / Writes: 89% / 11%

[--] Binary logging is disabled

[--] Physical Memory : 31.3G

[--] Max MySQL memory : 12.9G

[--] Other process memory: 3.9G

[--] Total buffers: 9.1G global + 3.2M per thread (1024 max threads)

[--] P_S Max memory usage: 555M

[--] Galera GCache Max memory usage: 0B

[OK] Maximum reached memory usage: 10.0G

"You can't tune your way out of a performance problem."

The Query Cache may be part of the problem. It is running moderately efficiently, but there are a lot of prunes -- 12/second. Prunes are costly.

MyISAM may be part of the problem. You should convert to InnoDB.

While some of mysqltuner's and Wilson's suggestions will help some, the real problem with high CPU is almost always poor indexes or poorly formatted queries.

Turn on the slowlog with long_query_time 1; wait a day; run pt-query-digest or mysqldumpslow -s t to find the 'worst' queries. Then has for help in speeding them up. More.

This may be the most important tuner comment in your case:

[!!] Temporary tables created on disk: 52% (577K on disk / 1M total)

That's about 6/second, about the 90th percentile compared to other servers. Are you running on Windows? What version of MySQL?

Do you use TEXT for columns that could be suitably sized VARCHAR(...)? Do you use SELECT * when you need only smaller list of columns? Both of these can lead to tmp tables on disk.

Efficiency in many:many schema.

StackExchange Database Administrators Q#198253, answer score: 3