MYSQL causing EXTREMELY HIGH CPu uage

6 posts / 0 new
Topic locked
Last post
#1 Sat, 12/17/2016 - 08:25
juliereader

MYSQL causing EXTREMELY HIGH CPu uage

hi there,.

Since last couple of days, mysql is causing huge CPU spikes for no reason on my server..

I am running virtualmin on a cent OS 64 BIT with 12 GB RAM

These are load avg

CPU load averages 35.53 (1 min) 47.24 (5 mins) 32.57 (15 mins)

when i search TOP

Its showing mysql causing above 100% CPU uage with MYSQLD in command

can any one help to find the root cause of issue or suggest some solutions..

Sat, 12/17/2016 - 08:42
Diabolico
Diabolico's picture

If you didnt fine tune your MySQL i would start from here.

- I often come to the conclusion that my brain has too many tabs open. -
Failing at desktop publishing & graphic design since 1994.

Mon, 12/19/2016 - 02:10
juliereader

i have already installed tuning-primer and updated my.cnf

Following is my current my.cnf based on tuning-primer recommendation

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 4M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 1800
table_definition_cache = 1800
table_open_cache = 768
max_allowed_packet = 4M
key_buffer_size = 1G

wait_timeout = 5
interactive_timeout = 2

long_query_time = 1
open_files_limit = 3200
innodb_buffer_pool_size = 26M
join_buffer_size = 4M
tmp_table_size = 128M
max_heap_table_size = 72M
max_connections = 80


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Mon, 12/19/2016 - 12:50
Diabolico
Diabolico's picture

With this settings your MySQL will max at 2.7GB. How much memory you have on that server and how much is free?

- I often come to the conclusion that my brain has too many tabs open. -
Failing at desktop publishing & graphic design since 1994.

Wed, 12/21/2016 - 09:30
juliereader

I have 12GB memory

Also it seems that some wordpress queries are taking more than 1 second. I have enabled the slow query log and its already 300mb in 2 days /

Fri, 12/30/2016 - 02:51 (Reply to #5)
Masplus

Hello !

How many hosts are working with the same MySQL service ?

I recommend to execute MySQLTunner to check what is going wrong with the queries, configuration and something more.

Also, you need to consider to use SSD, NVME or faster Hard Disk to run MySQL databases with heavy load, with tweaking database does not guarantee you the hoped speed atleast without upgrading HardWare.

Topic locked