Problem Description
- MariaDB CPU usage is high, possibly after update to version 10.11
- mysqladmin processlist shows long-running "Waiting for table level lock" queries
Problem Resolution
Run mysqladmin processlist -v to see the full query. Examine the query for the tables it accesses, then check in phpMyAdmin (or via raw mysql queries if you know them) to view the table type.
If the afflicted table is of type MyISAM, then you've found your issue because MyISAM locks the entire table when any one process is writing to it, while innodb locks only the row. The solution is to convert the tables to InnoDB.
Generally that can be done like this (replacing $TABLE with the actual table name):
ALTER TABLE $TABLE ENGINE=InnoDB;
Specifically, below you'll see solutions for the most common content management systems.
Converting WordPress tables from MyISAM to InnoDB
WordPress switched the table defaults to innodb ages ago, but if you installed WordPress before that happened, this issue can occur. The most important tables to convert are wp_posts and wp_postmeta, but there may be others - it all comes down to which ones are causing that table lock in the mysql process list!
You can convert all commonly used WordPress tables to innodb with the following queries. These can be run via phpMyAdmin or after invoking mysql via cli.
ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_usermeta ENGINE=InnoDB;
ALTER TABLE wp_users ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;
ALTER TABLE wp_terms ENGINE=InnoDB;
ALTER TABLE wp_term_relationships ENGINE=InnoDB;
ALTER TABLE wp_term_taxonomy ENGINE=InnoDB;
ALTER TABLE wp_comments ENGINE=InnoDB;
ALTER TABLE wp_commentmeta ENGINE=InnoDB;
Converting ModX tables from MyISAM to InnoDB
The most common ones that need the change for ModX is modx_site_content.
ALTER TABLE modx_site_content ENGINE=InnoDB;
But there may be others - it all comes down to which ones are causing that table lock in the mysql process list!
