Question

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

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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

@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

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

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