MySQL spikes CPU (>80%) when publishing posts in Wordpress

February 6, 2017 3.6k views
Apache MariaDB WordPress CentOS
hcassin
By:
hcassin

Hey all!

Every time I publish or update a post in Wordpress on my CentOS there is a huge CPU spike and running # top shows its ~80% MySQL.

I have deactivated plugins and themes, and the problem persists, leading me to think that it is an issue with the database.

It also takes around 20-30 seconds for the Wordpress admin to load the publish/update for the post. It's extremely slow performing this one task. Every thing else on the site is very fast.

Any help would be greatly appreciated!

Droplet Info:
4GB / 60GB
CentOS 7.2
MariaDB 5.5

Log In to Comment
3 Answers
jtittle1 February 6, 2017

@hcassin

It's most likely not so much an issue with the database itself as it is with MariaDB if you're running the stock installation and have yet to tweak/optimize the default configuration for better performance.

By default, the stock installation of MariaDB (or MySQL/Percona) is not setup for real production use as it'd be impossible to create a configuration that just works for anything, so it's generalized.

Your MySQL configuration file should be located at either

/etc/my.cnf

or

/etc/mysql/my.cnf

If you would, please post the contents of this file in a code block and we can take a look at it.

Reply
  • hcassin February 6, 2017

    @jtittle Thanks for the response!

    Here is the content of etc/my.cnf

    [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#slow_launch_time=5
#slow_query_log=1
#slow_query_log_file=/var/lib/mysql/mysql-slow.log
#log_queries_not_using_indexes = 0
#long_query_time=2
log-slow-queries
slow_query_log          = 1
slow_query_log_file     = /var/lib/mysql/mysql-slow.log
long_query_time         = 2
#log-queries-not-using-indexes

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
    • jtittle1 February 6, 2017

      @hcassin - No problem!

      Please also check the directory noted at the bottom, /etc/my.cnf.d, and post the contents of those files as well.

      Also, before we go about changing configuration, make sure you create a backup. You can create a directory in a location of your choice using:

      mkdir -p /path/to/backup/dir

      and then run:

      cp /etc/my.cnf /path/to/backup/dir \
cp -R /etc/my.cnf.d /path/to/backup/dir

      That way you can easily revert back should anything go wrong.

      • hcassin February 6, 2017

        @jtittle awesome!

        There are 3 files in the /etc/my.cnf.d and they are client.cnf, mysql-clients.cnf, and server.cnf

        The contents of client.cnf are:

        #
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#


[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

        The contents of mysql-clients are:

        #
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

        The contents of server.cnf are:

        #
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

        I've gone ahead and created a backup or the file and directory.

        Thanks so much for the help with this!

jtittle1 February 6, 2017

@hcassin

Thanks!

Go ahead and run systemctl stop mariadb to shutdown MySQL, then open /etc/my.cnf.

Below this line:

#log-queries-not-using-indexes

... paste in:

back_log = 75
max_connections = 300
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 128K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 64M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

innodb_large_prefix=1
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_support_xa=1

innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2

Now close out and save the file and start MySQL using systemctl start mariadb.

I just tested the above on a fresh CentOS 7 Droplet to make sure it'd work without any errors and it did, though if it fails to restart, please let me know and post the last 20-30 lines of the error log.

Normally, we want to evaluate the configuration over 24-48 hours to see how it's working, so see how it works now and evaluate over that period.

Reply
  • hcassin February 6, 2017

    Hey @jtittle

    Ok so I shut down mariaDB and pasted the code, however I was not able to start mariaDB again after pasting the code. However, after removing the new code I was able to restart it again.

    Here are the mariadb logs from the event:

    170206 16:23:44 [Note] /usr/libexec/mysqld: Normal shutdown

170206 16:23:44 [Note] Event Scheduler: Purging the queue. 0 events
170206 16:23:44  InnoDB: Starting shutdown...
170206 16:23:44  InnoDB: Waiting for 40 pages to be flushed
170206 16:23:45  InnoDB: Shutdown completed; log sequence number 46325122921
170206 16:23:45 [Note] /usr/libexec/mysqld: Shutdown complete

170206 16:23:45 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170206 16:24:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170206 16:24:14 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
170206 16:24:14 [Note] /usr/libexec/mysqld (mysqld 5.5.47-MariaDB-log) starting as process 15418 ...
170206 16:24:14 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400
170206 16:24:14 InnoDB: The InnoDB memory heap is disabled
170206 16:24:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170206 16:24:14 InnoDB: Compressed tables use zlib 1.2.7
170206 16:24:14 InnoDB: Using Linux native AIO
170206 16:24:14 InnoDB: Initializing buffer pool, size = 128.0M
170206 16:24:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
InnoDB: Possible causes for this error:
 (a) Incorrect log file is used or log file size is changed
 (b) In case default size is used this log file is from 10.0
 (c) Log file is corrupted or there was not enough disk space
 In case (b) you need to set innodb_log_file_size = 48M
170206 16:24:14 [ERROR] Plugin 'InnoDB' init function returned error.
170206 16:24:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170206 16:24:14 [Note] Plugin 'FEEDBACK' is disabled.
170206 16:24:14 [ERROR] Unknown/unsupported storage engine: InnoDB
170206 16:24:14 [ERROR] Aborting

170206 16:24:14 [Note] /usr/libexec/mysqld: Shutdown complete

170206 16:24:14 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170206 16:24:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170206 16:24:38 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
170206 16:24:38 [Note] /usr/libexec/mysqld (mysqld 5.5.47-MariaDB-log) starting as process 16278 ...
170206 16:24:38 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400
170206 16:24:38 InnoDB: The InnoDB memory heap is disabled
170206 16:24:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170206 16:24:38 InnoDB: Compressed tables use zlib 1.2.7
170206 16:24:38 InnoDB: Using Linux native AIO
170206 16:24:38 InnoDB: Initializing buffer pool, size = 128.0M
170206 16:24:38 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
InnoDB: Possible causes for this error:
 (a) Incorrect log file is used or log file size is changed
 (b) In case default size is used this log file is from 10.0
 (c) Log file is corrupted or there was not enough disk space
 In case (b) you need to set innodb_log_file_size = 48M
170206 16:24:38 [ERROR] Plugin 'InnoDB' init function returned error.
170206 16:24:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170206 16:24:38 [Note] Plugin 'FEEDBACK' is disabled.
170206 16:24:38 [ERROR] Unknown/unsupported storage engine: InnoDB
170206 16:24:38 [ERROR] Aborting

170206 16:24:38 [Note] /usr/libexec/mysqld: Shutdown complete

170206 16:24:38 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
170206 16:24:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170206 16:24:47 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
170206 16:24:47 [Note] /usr/libexec/mysqld (mysqld 5.5.47-MariaDB-log) starting as process 16531 ...
170206 16:24:47 InnoDB: The InnoDB memory heap is disabled
170206 16:24:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170206 16:24:47 InnoDB: Compressed tables use zlib 1.2.7
170206 16:24:47 InnoDB: Using Linux native AIO
170206 16:24:47 InnoDB: Initializing buffer pool, size = 128.0M
170206 16:24:47 InnoDB: Completed initialization of buffer pool
170206 16:24:47 InnoDB: highest supported file format is Barracuda.
170206 16:24:47  InnoDB: Waiting for the background threads to start
170206 16:24:48 Percona XtraDB (http://www.percona.com) 5.5.46-MariaDB-37.6 started; log sequence number 46325122921
170206 16:24:48 [Note] Plugin 'FEEDBACK' is disabled.
170206 16:24:48 [Note] Server socket created on IP: '0.0.0.0'.
170206 16:24:48 [Note] Event Scheduler: Loaded 0 events
170206 16:24:48 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.47-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

    Thanks!

jtittle1 February 6, 2017

@hcassin

Shutdown MariaDB:

systemctl stop mariadb

In /var/lib/mysql, backup the log files that start with ib_logfile, i.e.

cp -R /var/lib/mysql/ib_logfile* /path/to/backup

Then delete them from `/var/lib/mysql:

rm -rf /var/lib/mysql/ib_logfile*

Now try pasting the same configuration below the same line as noted in my previous reply, save the file, and then start MariaDB:

systemctl start mariadb
Reply
  • hcassin February 7, 2017

    @jtittle

    Thanks for the help on that, I was able to start the updated my.cnf after delete the logs from the step above.

    I'll evaluate the configuration for the next 24-48 hours.

    Thanks for all your help, I really appreciate it!

Have another answer? Share your knowledge.

Related Questions