I'm on a VPS Virtuozzo server with:
SSD Hard Drive
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.
CPU Usage 99.9%
CPU Load Average 7.13, 7.42, 6.97
As a result at some point apache halts and restarts.
[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...