2

We are experiencing issues with mySQL loading 100% of the server's CPU. While checking the running processes we found many of the following:

Waiting for table level lock | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

The table is configured to MyISAM.

Any suggestion of how to optimize mysql?

2 Answers 2

1

I had this problem and added an index to wp_option.option_name

0

Converting engine to row level lock will prevent this issue. You may change to InnoDB
ALTER TABLE 'wp_options' ENGINE=INNODB;
Hope it helps for you

3
  • As this is a part of WordPress, could this change cause issues? (I don't know wnough about WordPress to know one way or the other; just a concern when your DB is part of a vendor offering.
    – RDFozz
    Commented Jul 27, 2018 at 20:05
  • Hi @RDFozz. Thank you for your question. Wordpress using default engine configured in my.cnf. You can see it here https://github.com/WordPress/WordPress/blob/master/wp-admin/includes/schema.php. Engine for schema creation is not specified. It is looks like when he initially installed Wordpress default engine was MyISAM and maybe some migration to newer version of mysql was happened where InnoDB by default. Now is default engine is InnoDB. So, vendor in not offering tables engine and it is ok to change engine.
    – Alex S
    Commented Jul 28, 2018 at 9:11
  • But better to dump table content before any schema changes of course :) CREATE TABLE wp_options_temp LIKE wp_options; INSERT INTO wp_options_temp SELECT * FROM wp_options;
    – Alex S
    Commented Jul 28, 2018 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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