gryp, did you find anywhere in documentation that it should not be happening? Could you please paste the link for the reference?

The way the defragmentation is implemented is to reduce the impact on the database server.

See: https://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/defragmentation.pdf
Some copy paste's from slides:

A little bit more technical

•  No down time

Well, depends on how look at downtime. But the table is not accessible during the optimize table, so this means downtime for application usage.

...

•  Work on N pages at a time, N is configurable, release index lock in between

index lock is released, why would it need to do that if the table has metadata locks? Because it's supposed to be online.

Good things about this approach

...

•  Simple

•  Low overhead

As a metadata locks is held, it has a very high overhead. So clearly this is not as it should be.

Next to what is mentioned in the slides mentioned in my previous comment, we can consider looking at some variables introduced by this feature:

System variables

innodb_defragment: Enable InnoDB defragmentation.

innodb_defragment_n_pages: Number of pages considered at once when merging multiple pages to defragment.

innodb_defragment_frequency: Maximum times per second for defragmenting a single index.

Why declare a frequency? Because it's supposed to be running in the background and not interfere with the regular workload.

To give you another example, even a single select is blocked by the defragmentation:

MariaDB [(none)]> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+----------------------------+----------+

| Id | User | Host      | db   | Command | Time | State                           | Info                       | Progress |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------+----------+

|  3 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | select * from test limit 1 |    0.000 |

|  4 | root | localhost | NULL | Query   |    0 | init                            | show processlist           |    0.000 |

|  7 | root | localhost | test | Query   |    5 | executing                       | optimize table test        |    0.000 |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------+----------+

If MariaDB intended to implement this and set metadatalocks, I wonder what the real use case is here and maybe that should be updated in the documentation

• It is doing an 'offline' OPTIMIZE TABLE in a very very very slow manner, which does not interfere with workload to other tables, but the practical use is none IMO.
• The only real benefit here is that you can reduce/remove fragmentation on a table without having to create a new tablespace and need a lot of free disk space, but as it's so slow, it's not practical at all.

Thanks.

I don't know whether the Facebook presentation was overpromising, or whether it's MariaDB-specific changes that made the difference; assigning to jplindst to say which it is, and to fix if it requires fixing.

Facebook implementation introduced new SQL-syntax and it used different metadata locks. MariaDB implementation re-used OPTIMIZE TABLE SQL-syntax and uses the metadata locks based on that. Not sure why it would take exclusive lock (replication ?) when shared should be enough to avoid concurrent DDL-operations. Assigning to serg for comment, can metadata lock be changed to shared-lock or no lock.

This is a complex issue. OPTIMIZE always used to take rather strong locks because of MyISAM —MyISAM cannot allow any other connection accessing the table that is being optimized. InnoDB does not have this limitation. But we need to open the table to know whether it's MyISAM or InnoDB table. And we need to take metadata locks before the table is opened (because the table's engine is also part of metadata, we cannot read it without the metadata lock).

We'll fix it nevertheless. But may be this fix won't make it into 10.1.14

I see that this issue appears to be closed, but I just tried innodb_fragment = 1 and optimise table still blocks access to that table.

First, on connection #1:

MariaDB [geozone_app]> optimize table user_update;  

Then, on connection #2:

MariaDB [geozone_app]> select * from user_update limit 1;

It's blocked.

*************************** 1. row ***************************

      Id: 829

    User: samuel

    Host: localhost

      db: geozone_app

 Command: Query

    Time: 361

   State: executing

    Info: optimize table user_update

Progress: 0.000

*************************** 4. row ***************************

      Id: 11798

    User: samuel

    Host: localhost

      db: geozone_app

 Command: Query

    Time: 323

   State: Waiting for table level lock

    Info: select * from user_update limit 1

Progress: 0.000

Is this to be expected?

business% mysql --version

mysql  Ver 15.1 Distrib 10.1.18-MariaDB, for Linux (x86_64) using readline 5.1

ioquatix, it's a different issue. This MDEV-9115 is about metadata locks, see in the original bug description Waiting for table metadata lock, but what you see is a table level data lock, note Waiting for table level lock. This might've been fixed in 10.2.

Okay, do you know if there is an issue being tracked somewhere?

Additionally, I tried using this, but no disk space was freed up despite there being I think 5Gb of unused space by the table - I dropped all indexes, etc.