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

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

          Ascending order - Click to sort in descending order
          View 6 older comments
          jplindst Jan Lindström (Inactive) added a comment - - edited

          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.

          jplindst Jan Lindström (Inactive) added a comment - - edited 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.
          serg Sergei Golubchik added a comment -

          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

          serg Sergei Golubchik added a comment - 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
          ioquatix Samuel Williams added a comment -

          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 Samuel Williams added a comment - 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
          serg Sergei Golubchik added a comment -

          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.

          serg Sergei Golubchik added a comment - 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.
          ioquatix Samuel Williams added a comment -

          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.

          ioquatix Samuel Williams added a comment - 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.

          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.