0

We have a MariaDB database running WordPress 4.8 and found a lot of transient named records in the wp_options table. The table was cleaned up with a Plugin and reduced from ~800K records down to ~20K records. Still getting slow query entries regarding the table:

# User@Host: wmnfdb[wmnfdb] @ localhost []
# Thread_id: 950  Schema: wmnf_www  QC_hit: No
# Query_time: 34.284704  Lock_time: 0.000068  Rows_sent: 1010 Rows_examined: 13711
SET timestamp=1510330639;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Found another post to create an index and did:

ALTER TABLE wp_options ADD INDEX (`autoload`);

That was taking too long and taking website offline. I found a lot of 'Waiting for table metadata lock' in the processlist. After canceling the ALTER TABLE, got all running again still with high loads and entries of course in the slow query log. I also tried creating the index with the web server offline and a clean processlist. Should it take so long if I try to create again tonight?

rwfitzy
  • 413
  • 5
  • 16

1 Answers1

0

If you are deleting most of a table, it is better to create a new table, copy the desired rows over, then rename. The unfortunate aspect is that any added/modified rows during the steps would not get reflected in the copied table. (A plus: You could have had the new index already in place.)

In this, I give multiple ways to do big deletes.

What is probably hanging your system:

A big DELETE stashes away all the old values in case of a rollback -- which killing the DELETE invoked! It might have been faster to let it finish.

ALTER TABLE .. ADD INDEX -- If you are using MySQL 5.5 or older, that must copy the entire table over. Even if you are using a newer version (that can do ALGORITHM=INPLACE) there is still a metadata lock. How often is wp_options touched? (Sounds like too many times.)

Bottom line: If you recover from your attempts, but the delete is still to be done, pick the best approach in my link. After that, adding an index to only 20K rows should take some time, but not a deadly long time. And consider upgrading to 5.6 or newer.

If you need further discussion, please provide SHOW CREATE TABLE wp_options.

But wait! If autoload is a simple yes/no 'flag', the index might not be used. That is, it may be a waste to add the index! (For low cardinality, it is faster to do a table scan than to bounce back and forth between the index BTree and the data BTree.) Please provide a link to that post; I want to spit at them.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The post said to check first how many 'no' autoloads there were and mine has 95%: https://wordpress.stackexchange.com/questions/71691/slow-query-for-the-wp-options-table – rwfitzy Nov 10 '17 at 20:28
  • The Optimizer does not (yet) notice the uneven distribution and use the index. – Rick James Nov 10 '17 at 22:09
  • Looking at the sizes of all in phpMyAdmin, I noticed my database is showing only 3.1GB in size when earlier I checked disk space of the db directory showing 19GB in size. I found this wp_options.ibd is 15GB. I think I'll post this issue in WordPress stackexchange since this is all WP related. That wp_options table holds a lot of what is called '_transient_' temp records and things improve when I purge expired transients, but still high loads and that large index file. – rwfitzy Nov 11 '17 at 14:53
  • There were over 800K of these transient records and only 2500 after purging expired records. Most coming from a bug in a plugin verified with the plugin maintainer and fixed with update. But this index file remains large even after the wp_options table is now only 131MB after purging. I'm looking for ways to clean that up now. – rwfitzy Nov 11 '17 at 14:59
  • @rwfitzy - This is one of the rare uses for `OPTIMIZE TABLE`. – Rick James Nov 11 '17 at 16:38
  • Yes, planning to do tonight. The wp_options all normal again now after purging unwanted transients and finding yet another plugin issue with over a million columns in one of the values. Load normalized, just need to take care of the index. – rwfitzy Nov 11 '17 at 20:11
  • Still haven't done this, my worry is shortage of disk space. How much disk space would be required to run optimize table on a 20GB ibd file? – rwfitzy Nov 12 '17 at 16:31
  • @rwfitzy - 20GB or perhaps a little more. – Rick James Nov 12 '17 at 18:28
  • @rwfitzy - Correction: Optimize needs the amount of space (data+indexes) of the _resulting_ table. Since you have deleted a lot of rows, it will be a lot less than 20GB. You had 800K rows? Now have 2.5K? Maybe 100MB will suffice. Do `SHOW TABLE STATUS` and look at `Data_length` + `Index_length`. – Rick James Nov 13 '17 at 23:43
  • Data_length 908165120 + Index_length 6307840 = 914472960/1024 = 893040MB? PhpMyAdmin says there are currently 5,836 rows with table size of 887.1MB. This plugin issue still not resolved, I hope to have less than that before doing the optimize table. Mysteriously, the idb file stopped growing at 39GB or maybe I'm just keeping it under better control now. – rwfitzy Nov 14 '17 at 15:32
  • @rwfitzy - 1024 is K; 1048576 is M. – Rick James Nov 14 '17 at 18:04
  • @rwfitzy - 887MB/5836 = 130KB per row? Are the rows really that big? (I know nothing about `wp_option` other than it is currently a problem for a lot of people.) – Rick James Nov 14 '17 at 18:08