Database processes stuck on Waiting for table level lock Print

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. For WordPress it's usually wp_posts and wp_postmeta.

If the table type is MyISAM, 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. This is why WordPress switched them to innodb by default ages ago. You can convert them to innodb as follows:

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;

Note: this is a common set of changes that will help, but you may have other tables that also need converting to improve performance.