mysql using about 80% cpu, recomendation?

hi,

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 cloudlinux

here is my my.cnf and mysqltuner log

Code:

[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

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

-------- 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 (31.77% of installed RAM)

[OK] Maximum possible memory usage: 12.9G (41.28% of installed RAM)

[OK] Overall possible memory usage with other process is compatible with memory available

[OK] Slow queries: 0% (5K/23M)

[OK] Highest usage of available connections: 8% (85/1024)

[OK] Aborted connections: 0.55%  (2598/468725)

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

[!!] Query cache may be disabled by default due to mutex contention.

[OK] Query cache efficiency: 61.7% (12M cached / 19M selects)

[!!] Query cache prunes per day: 1008253

[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 34M sorts)

[!!] Joins performed without indexes: 150075

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

[OK] Thread cache hit rate: 99% (85 created / 468K connections)

[!!] Table cache hit rate: 1% (2K open / 102K opened)

[OK] Open file limit used: 24% (1K/8K)

[OK] Table locks acquired immediately: 99% (38M immediate / 38M locks)


-------- Performance schema ------------------------------------------------------------------------

[--] Memory used by P_S: 555.4M

[--] Sys schema isn't installed.


-------- ThreadPool Metrics ------------------------------------------------------------------------

[--] ThreadPool stat is disabled.


-------- MyISAM Metrics ----------------------------------------------------------------------------

[!!] Key buffer used: 26.8% (287M used / 1B cache)

[OK] Key buffer size / total MyISAM indexes: 1.0G/1.5G

[OK] Read Key buffer hit rate: 100.0% (2B cached / 572K reads)

[!!] Write Key buffer hit rate: 30.1% (2M cached / 899K writes)


-------- InnoDB Metrics ----------------------------------------------------------------------------

[--] InnoDB is enabled.

[--] InnoDB Thread Concurrency: 8

[OK] InnoDB File per table is activated

[OK] InnoDB buffer pool / data size: 8.0G/1.2G

[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.171875 %): 48.0M * 2/8.0G should be equal 25%

[OK] InnoDB buffer pool instances: 8

[--] InnoDB Buffer Pool Chunk Size not used or defined in your version

[OK] InnoDB Read buffer efficiency: 100.00% (786635092 hits/ 786673138 total)

[!!] InnoDB Write Log efficiency: 51.42% (236640 hits/ 460226 total)

[OK] InnoDB log waits: 0.00% (0 waits / 223586 writes)


-------- AriaDB Metrics ----------------------------------------------------------------------------

[--] AriaDB is disabled.


-------- TokuDB Metrics ----------------------------------------------------------------------------

[--] TokuDB is disabled.


-------- XtraDB Metrics ----------------------------------------------------------------------------

[--] XtraDB is disabled.


-------- RocksDB Metrics ---------------------------------------------------------------------------

[--] RocksDB is disabled.


-------- Spider Metrics ----------------------------------------------------------------------------

[--] Spider is disabled.


-------- Connect Metrics ---------------------------------------------------------------------------

[--] Connect is disabled.


-------- Galera Metrics ----------------------------------------------------------------------------

[--] Galera is disabled.


-------- Replication Metrics -----------------------------------------------------------------------

[--] Galera Synchronous replication: NO

[--] No replication slave(s) for this server.

[--] This is a standalone server.


-------- Recommendations ---------------------------------------------------------------------------

General recommendations:

    /var/lib/mysql/bh1.behost.es.err is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!

    Control warning line(s) into /var/lib/mysql/bh1.behost.es.err file

    Control error line(s) into /var/lib/mysql/bh1.behost.es.err file

    Run OPTIMIZE TABLE to defragment tables for better performance

      OPTIMIZE TABLE `modsec`.`hits`; -- can free 382.777297973633 MB

    Total freed space after theses OPTIMIZE TABLE : 382.777297973633 Mb

    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )

    Restrict Host for user@% to user@SpecificDNSorIp

    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

    Adjust your join queries to always utilize indexes

    When making adjustments, make tmp_table_size/max_heap_table_size equal

    Reduce your SELECT DISTINCT queries which have no LIMIT clause

    Increase table_open_cache gradually to avoid file descriptor limits

    Read this before increasing table_open_cache over 64: table_cache negative scalability

    Beware that open_files_limit (8192) variable

    should be greater than table_open_cache (2000)

    Consider installing Sys schema from GitHub - mysql/mysql-sys: The MySQL sys schema

    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: MySQL :: MySQL 5.7 Reference Manual :: 14.7.2 Changing the Number or Size of InnoDB Redo Log Files

Variables to adjust:

    query_cache_size (=0)

    query_cache_type (=0)

    query_cache_size (> 64M)

    join_buffer_size (> 1.0M, or always use indexes with joins)

    tmp_table_size (> 64M)

    max_heap_table_size (> 64M)

    table_open_cache (> 2000)

    innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

 

Hi,

Yes the problem is after a sysadmin check all parameters and all is ok, all seems similar, and the problem begin after migration from EA3 to EA4, same mysql configuration and now mysql pass from 20-25% to 80-90%

i have open a ticket, because must be something wrong in the migration