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

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