Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9155

Enabling Defragmenting in 10.1.8 still causes OPTIMIZE TABLE to take metadatalocks

    XMLWordPrintable

Details

    • 10.1.14

    Description

      Enabling Defragmenting in 10.1.8 still causes OPTIMIZE TABLE to take metadatalocks.

      enabled it:

      	MariaDB [(none)]> set global innodb_defragment=on;
      	Query OK, 0 rows affected (0.00 sec)
       
      	MariaDB [(none)]> set global innodb_defragment_frequency=1000;
      	Query OK, 0 rows affected (0.00 sec)
       
      	MariaDB [(none)]> set global innodb_defragment_n_pages=32;
      	Query OK, 0 rows affected (0.00 sec)
       
      	MariaDB [(none)]> show global variables like '%defrag%';
      	+--------------------------------------+----------+
      	| Variable_name                        | Value    |
      	+--------------------------------------+----------+
      	| innodb_defragment                    | ON       |
      	| innodb_defragment_fill_factor        | 0.900000 |
      	| innodb_defragment_fill_factor_n_recs | 20       |
      	| innodb_defragment_frequency          | 1000     |
      	| innodb_defragment_n_pages            | 32       |
      	| innodb_defragment_stats_accuracy     | 0        |
      	+--------------------------------------+----------+
      	6 rows in set (0.00 sec)

      this is how processlist looks like:

      	MariaDB [(none)]> show processlist;
      	+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+
      	| Id  | User        | Host              | db   | Command | Time | State                            | Info                                    | Progress |
      	+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+
      	|  16 | monitoring  | localhost         | NULL | Query   |  440 | executing                        | optimize table graph.graph   |    0.000 |
      	| 283 | system user |                   | NULL | Connect |  230 | Waiting for master to send event | NULL                                    |    0.000 |
      	| 284 | system user |                   | NULL | Connect |  230 | Waiting for table metadata lock  | NULL                                    |    0.000 |
      	| 477 | monitoring  | localhost         | sys  | Query   |   12 | Waiting for table metadata lock  | select * from information_schema.TABLES |    0.000 |
      	+-----+-------------+-------------------+------+---------+------+----------------------------------+-----------------------------------------+----------+

      Attachments

        Activity

          People

            serg Sergei Golubchik
            gryp Kenny
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.