1

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...

  • 2
    Before you put much effort into troubleshooting this, move to a proper server. What you have now is merely a container, and is prone to all sorts of misbehavior. – EEAA May 8 '17 at 18:29
  • @EEAA A dedicated server? – fat_mike May 8 '17 at 18:31
  • That would work, or just a proper VPS, based on a virtualization technology like KVM or Xen. – EEAA May 8 '17 at 18:32
  • @EEAA Whats wrong with VZ? – fat_mike May 8 '17 at 18:33
  • It's an ancient, broken technology that isn't suited for anything but the most simple, low-load, and low-criticality workloads. – EEAA May 8 '17 at 18:34
0

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

  • Hi, thanks for answering. Check my updated question. Yes of course I have restarted MySQL. I have set innodb_buffer_pool_size = 4G before and the same amount(~) of RAM was used by the system. Sorry I didn't get "check for wa" in your answer. – fat_mike May 10 '17 at 4:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.