I'm on a VPS Virtuozzo server with:
8GB RAM
6 CPU
SSD Hard Drive
Debian 8.8
PHP 5.6.30
MySQL 5.5.55
There only one website that is served.
Its a wordpress with SSL enabled website with around 22000 articles.
It gets medium traffic, around 8k users, 18k pageviews per day.
Because of the nature of the website we get a lot of bad robots requests and hack attempts. fail2ban is configured and reporting well.
top -i reporting sometimes mysql CPU% as 200.
Virtuozzo reports
CPU Usage 99.9%
CPU Load Average 7.13, 7.42, 6.97
Memory 42%
As a result at some point apache halts and restarts.
my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 100
#table_cache = 64
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 3200
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
#long_query_time = 2
#log_queries_not_using_indexes
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
innodb_buffer_pool_size = 5G
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
The slow query log is not reporting any slow queries.
(my.cnf file configured by an server admin expert that I hired, because I am not one)
Is MySQL misconfigured?
UPDATE: I have tried to disable all plugins and applied the basic theme. There isn't a particular plugin that causing this. I goes without saying that the CPU usage was lower but the RAM load was lower as well. By the way if there are no plugins and we have the basic theme applied there is not so much engagement from users. We can't hit 200 users per minute with basic setup in order to debug properly…
Best Answer
As you said you are using word-press, check for bad plugins as some plugins can get compromised, test it disabling plugins one by one
I cans see innodb_buffer_pool_size = 5G still your memory usage is 42% out of 8gb usage should be more than, have you restarted mysql service after changing your my.cnf setting cause change effect only after restart mainly for innodb_buffer
check for time wait for resources using top command check for wa if there is some value then it means there is some issue with resources like hdd or ram might be your ssd or ram has slow read-write issues
additionally you can use Percona Configuration Wizard to configure your my.cnf as per your server configuration