[MDEV-28499] wordpress wp_options tables cannot be optimized Created: 2022-05-07  Updated: 2024-01-23

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.32
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: turgut kalfaoglu Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: optimizer
Environment:

Alma Linux

Attachments: HTML File globals    
Issue Links:
Relates
relates to MDEV-32663 mariadb filesystem size increasing fo... Needs Feedback

 Description   

When I do an:
mysqlcheck --auto-repair --verbose --optimize --all-databases

on my hosting site, the optimization/repair hangs on many databases on the wordpress options table.
Although these databases are small, the optimization hangs on the same table: wp_options.
Here is the process list after I eventually killed the optimization after 10+ minutes..
Killing doesn't help either – either it's the rollback or I don't know why but after it's killed, it stays like that.

This happens in about a dozen of the 300 databases that are being optimized. But prevents all other databases from being optimized, as the mysqlcheck gets stuck at these tables.

MariaDB [psa]> show processlist;
 
+------+-------------+-----------+--------------+---------+------+---------------------------------+---------------------------------------------------------------------------+----------+
 
| Id   | User        | Host      | db           | Command | Time | State                           | Info                                                                      | Progress |
 
+------+-------------+-----------+--------------+---------+------+---------------------------------+---------------------------------------------------------------------------+----------+
 
| 1974 | admin       | localhost | cb_web  | Killed  | 2443 | Parallel repair                 | OPTIMIZE NO_WRITE_TO_BINLOG TABLE `wp_options`                    |    0.000 |
 
| 6457 | admin       | localhost | other_hst | Query   |  388 | Parallel repair                 | OPTIMIZE NO_WRITE_TO_BINLOG TABLE `wp_options`                          |    0.000 |
 
| 7291 | admin       | localhost | psa          | Query   |    0 | Init                            | show processlist                                                          |    0.000 |
 
+------+-------------+-----------+--------------+---------+------+---------------------------------+---------------------------------------------------------------------------+----------+

no warnings are shown, and SHOW WARNINGS shows nothing, nothing in the logs.

If the --optimize option is removed, it does NOT hang. The timing is enormous as the databases reside on SSD drive.

 Comments   
Comment by Elena Stepanova [ 2022-07-25 ]

Do you use MyISAM or Aria for wp tables?
Could you please paste or attach your config files or output of show global variables?

Comment by turgut kalfaoglu [ 2022-07-26 ]

Hello. I am using MyISAM for all AFAIK. I will attach the global variables.
globals

Comment by Michael Widenius [ 2024-01-23 ]

Looking at the process list, it shows that the problem is that REPAIR is not able to lock the table exclusively (as progress is 0.00).
A reason for that is could be that some users has done LOCK TABLE on the table.
Could this be the case?
Is that processlist shown in the description the list of all active users or just the ones doing REPAIR ?

Another possible reason is that some disk is full and MariaDB is waiting for someone to free space.
It is also strange that two different users are trying to execute REPAIR. In this case the second repair will not progress until the first one has finished.

Generated at Thu Feb 08 10:01:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.