• Plesk Uservoice will be deprecated by October. Moving forward, all product feature requests and improvement suggestions will be managed through our new platform Plesk Productboard.
    To continue sharing your ideas and feedback, please visit features.plesk.com

Question MariaDB and wp_options tables - optimize gets stuck

tkalfaoglu

Silver Pleskian
Server operating system version
AlmaLinux
Plesk version and microupdate number
Obsidian
Did anyone else notice that OPTIMIZE takes forever on wp_options tables?

I was running an
mysqlcheck --all-databases --auto-repair --verbose --optimize --skip-write-binlog
command and they always get stuck on the options tables for some reason..

A drop database;create database helps things, but why should we?

I killed the previous optimize after 5 minutes and restarted it.. still gets stuck..

Code:
MariaDB [psa]> show processlist;
+------+--------------+-----------+-------------+---------+------+---------------------------------+-------------------------------------------------------------------------+----------+
| Id   | User         | Host      | db          | Command | Time | State                           | Info                                                                    | Progress |
+------+--------------+-----------+-------------+---------+------+---------------------------------+-------------------------------------------------------------------------+----------+
|    1 | system user  |           | NULL        | Daemon  | NULL | InnoDB purge coordinator        | NULL                                                                    |    0.000 |
|    2 | system user  |           | NULL        | Daemon  | NULL | InnoDB purge worker             | NULL                                                                    |    0.000 |
|    3 | system user  |           | NULL        | Daemon  | NULL | InnoDB purge worker             | NULL                                                                    |    0.000 |
|    4 | system user  |           | NULL        | Daemon  | NULL | InnoDB purge worker             | NULL                                                                    |    0.000 |
|    5 | system user  |           | NULL        | Daemon  | NULL | InnoDB shutdown handler         | NULL                                                                    |    0.000 |
|  103 | admin        | localhost | psa         | Sleep   |   10 |                                 | NULL                                                                    |    0.000 |
| 1046 | admin        | localhost | xxxx_web | Killed  |  706 | Parallel repair                 | OPTIMIZE TABLE `zzhr8WQw5_options`                                     |    0.000 |
| 1598 | admin        | localhost | psa         | Sleep   |  528 |                                 | NULL                                                                    |    0.000 |
| 2506 | admin        | localhost | yyyyy_wp | Query   |  113 | Parallel repair                 | OPTIMIZE NO_WRITE_TO_BINLOG TABLE `wp_options`                          |    0.000 |
| 2512 | admin        | localhost | psa         | Query   |    0 | Init                            | show processlist                                                        |    0.000 |
| 2657 | yyyyyy_user | localhost | yyyyyy_wp | Query   |   36 | Waiting for table metadata lock | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' |    0.000 |
+------+--------------+-----------+-------------+---------+------+---------------------------------+-------------------------------------------------------------------------+----------+
11 rows in set (0.000 sec)

The optimize statements can run for 5 minutes or more on these databases; despite them being 7 and 62MB in size respectively for xxxx and yyyyy

PS: This is 10.3.32-MariaDB

Sorry for being off-topic.
 
Back
Top