High CPU consumption MySQL

v3
#1

Hello!
I have a wordpress news portal with easyengine + wp + redis and come to days with a performance problem, my mysql is consuming a lot of cpu usage
I have more than 500k posts
someone to help me solve this problem?

print cpu

server details
Ubuntu 16.04 - minimal - RAID1
Harddisk 256 GB SSD 2.5"
Harddisk 256 GB SSD 2.5"
RAM 32 GB
CPU Intel Xeon E3-1225v3

mysql configuration my.cnf

my.txt (5.4 KB)

#2

Have you tried this one: https://easyengine.io/tutorials/mysql/tuning-primer/

#3 
    -- MYSQL PERFORMANCE TUNING PRIMER --
         - By: Matthew Montgomery -

MySQL Version 10.1.17-MariaDB-1~xenial x86_64

Uptime = 0 days 1 hrs 12 min 15 sec Avg. qps = 128 Total Questions = 557252 Threads Connected = 56

Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations

To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/10.1/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL’s Enterprise Monitoring and Advisory Service

SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 0 out of 557278 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine

BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/10.1/en/point-in-time-recovery.html

WORKER THREADS Current thread_cache_size = 300 Current threads_cached = 60 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine

MAX CONNECTIONS Current max_connections = 300 Current threads_connected = 41 Historic max_used_connections = 103 The number of used connections is 34% of the configured maximum. Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE Max Memory Ever Allocated : 16.15 G Configured Max Per-thread Buffers : 2.20 G Configured Max Global Buffers : 15.39 G Configured Max Memory Limit : 17.60 G Physical Memory : 31.33 G Max memory limit seem to be within acceptable norms

KEY BUFFER Current MyISAM index space = 511 K Current key_buffer_size = 128 M Key cache miss rate is 1 : 567 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine

QUERY CACHE Query cache is enabled Current query_cache_size = 256 M Current query_cache_used = 2 M Current query_cache_limit = 2 M Current Query cache Memory fill ratio = 1.06 % Current query_cache_min_res_unit = 4 K Query Cache is 32 % fragmented Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation. Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS Current sort_buffer_size = 4 M Current read_rnd_buffer_size = 1 M Sort buffer seems to be fine

JOINS /usr/local/bin/tuning-primer.sh: 401: local: 2097152: bad variable name

#4

use heart beat control tool if using wordpress.

#5