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

innodb_defragment=ON trumps innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE

Details

    Description

      When using inplace defragmentation from Facebook/Kakao defragmentation patch the tables are not causing errors due to the locks. But we do have a so called 'suggestions' table previously used as full text index purposes. This table has a lot of records exchanged every night. When we use inplace defragmentation with OPTIMIZE TABLE, or OPTIMIZE LOCAL TABLE it works fast and fine, but the search gets slower and slower.

      DESCRIBE suggestions;

      Field Type Null Key Default Extra
      id int(11) NO PRI NULL auto_increment
      record_type varchar(30) YES MUL NULL  
      record_id int(11) YES MUL NULL  
      tokens text YES MUL NULL  
      ts datetime NO MUL 0000-00-00 00:00:00  

      show indexes in suggestions;

      Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
      suggestions 0 PRIMARY 1 id A 233197 NULL NULL   BTREE    
      suggestions 0 index_suggestions_on_record_type_and_record_id 1 record_type A 2 NULL NULL YES BTREE    
      suggestions 0 index_suggestions_on_record_type_and_record_id 2 record_id A 233197 NULL NULL YES BTREE    
      suggestions 1 index_suggestions_on_ts 1 ts A 2429 NULL NULL   BTREE    
      suggestions 1 index_suggestions_on_record_id 1 record_id A 233197 NULL NULL YES BTREE    
      suggestions 1 tokens 1 tokens NULL NULL NULL NULL YES FULLTEXT    
      • Rails: Suggestion.rebuild (deletes all records, add new records just for simplyfing things cca 250k records deleted and recreated)
        SELECT * FROM `suggestions` WHERE (MATCH(suggestions.tokens) AGAINST ('foobar' IN BOOLEAN MODE)) LIMIT 15 OFFSET 0;
        Empty set (0.11 sec) <---------------------
      • Rails: Suggestion.rebuild (deletes all records, add new records)
        SELECT * FROM `suggestions` WHERE (MATCH(suggestions.tokens) AGAINST ('foobar' IN BOOLEAN MODE)) LIMIT 15 OFFSET 0;
        Empty set (0.21 sec) <---------------------
      • Rails: Suggestion.rebuild (deletes all records, add new records)
        SELECT * FROM `suggestions` WHERE (MATCH(suggestions.tokens) AGAINST ('foobar' IN BOOLEAN MODE)) LIMIT 15 OFFSET 0;
        Empty set (0.31 sec) <---------------------
        OPTIMIZE TABLE suggestions;
        Table Op Msg_type Msg_text
        cms_frodo.suggestions optimize status OK

        1 row in set (4.09 sec)
        SELECT * FROM `suggestions` WHERE (MATCH(suggestions.tokens) AGAINST ('foobar' IN BOOLEAN MODE)) LIMIT 15 OFFSET 0;
        Empty set (0.31 sec) <---------------------

      But!!!

      ALTER TABLE suggestions engine=InnoDB;
      uses tmp table to resort
      Query OK, 248078 rows affected (10.53 sec)
      Records: 248078 Duplicates: 0 Warnings: 0
      SELECT * FROM `suggestions` WHERE (MATCH(suggestions.tokens) AGAINST ('foobar' IN BOOLEAN MODE)) LIMIT 15 OFFSET 0;
      Empty set (0.00 sec) <---------------------

      Why OPTIMIZE TABLE does not end with the same performace as ALTER TABLE syntax? What should be performed to make the performace the same? It is brilliant to do defrag inplace, but the performace is the same as not doing it at all.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-5834 Merge Kakao Defragmentation implementation to MariaDB 10.1

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            Are you using non-default values of either of these flags?

            SET GLOBAL innodb_defragment=1;
            		 
            SET GLOBAL innodb_optimize_fulltext_only=1;

            If yes, then OPTIMIZE should report OK. Normally, it reports that InnoDB does not support OPTIMIZE, but it will rebuild the table instead.

            Can you confirm whether this is the case?

            marko Marko Mäkelä added a comment - Are you using non-default values of either of these flags? SET GLOBAL innodb_defragment=1; SET GLOBAL innodb_optimize_fulltext_only=1; If yes, then OPTIMIZE should report OK . Normally, it reports that InnoDB does not support OPTIMIZE, but it will rebuild the table instead. Can you confirm whether this is the case?
            davidh David Honig added a comment - 

            SET GLOBAL innodb_defragment=1;
            		 
            SET GLOBAL innodb_optimize_fulltext_only=0;

            I've tested even innodb_optimize_fulltext_only=0; but no change
            The OPTIMIZE reports OK. But the search degrades over time. After full ALTER table it is back to normal. Or we used change engine InnoDB, but it is slow because it creates huge tmp table when you have 600 000 rows. And I've got some problem with these transactions happening over synchronisation to slaves.

            davidh David Honig added a comment - SET GLOBAL innodb_defragment= 1 ; SET GLOBAL innodb_optimize_fulltext_only= 0 ; I've tested even innodb_optimize_fulltext_only=0; but no change The OPTIMIZE reports OK. But the search degrades over time. After full ALTER table it is back to normal. Or we used change engine InnoDB, but it is slow because it creates huge tmp table when you have 600 000 rows. And I've got some problem with these transactions happening over synchronisation to slaves.
            marko Marko Mäkelä added a comment -

            I had a look at the code.

            • If innodb_defragment=1, the B-tree indexes of the table will be added to a defragmentation queue, and OK will be returned. Note: SPATIAL INDEX (starting with MariaDB Server 10.2) or FULLTEXT INDEX will not be defragmented.
            • If innodb_defragment=0 and innodb_optimize_fulltext_only=1, then fts_sync_table() and fts_optimize_table() will be executed if the table contains FULLTEXT INDEX, and OK will be returned.
            • If both variables are 0 (the default), ALTER TABLE will be executed to rebuild the table. (Note: if there are multiple FULLTEXT INDEX on the table, this will use ALGORITHM=COPY.)

            It looks like it could make sense to allow innodb_optimize_fulltext_only=1 to take effect even if innodb_defragment=1 is set. I believe that defragmentation itself would not help on fulltext indexes. I would expect fts_optimize_table() to rebuild the inverted index so that deleted words no longer occur there.

            davidh, can you please try what happens if you do the following:

            SET GLOBAL innodb_defragment=0;
            		 
            SET GLOBAL innodb_optimize_fulltext_only=1;

            marko Marko Mäkelä added a comment - I had a look at the code. If innodb_defragment=1 , the B-tree indexes of the table will be added to a defragmentation queue, and OK will be returned. Note: SPATIAL INDEX  (starting with MariaDB Server 10.2) or FULLTEXT INDEX will not be defragmented. If innodb_defragment=0 and innodb_optimize_fulltext_only=1 , then fts_sync_table() and fts_optimize_table() will be executed if the table contains FULLTEXT INDEX , and OK will be returned. If both variables are 0 (the default), ALTER TABLE  will be executed to rebuild the table. (Note: if there are multiple FULLTEXT INDEX on the table, this will use ALGORITHM=COPY .) It looks like it could make sense to allow innodb_optimize_fulltext_only=1 to take effect even if innodb_defragment=1 is set. I believe that defragmentation itself would not help on fulltext indexes. I would expect fts_optimize_table()  to rebuild the inverted index so that deleted words no longer occur there. davidh , can you please try what happens if you do the following: SET GLOBAL innodb_defragment=0; SET GLOBAL innodb_optimize_fulltext_only=1;
            davidh David Honig added a comment -

            Hi Marko,

            I've tried that. It works, but the thing is that I cannot say if it behaves the same way as before. We did rewrite our code completely since the issue came and now the table is just different I do not have any testing bench with the issue now.

            Sorry
            David

            davidh David Honig added a comment - Hi Marko, I've tried that. It works, but the thing is that I cannot say if it behaves the same way as before. We did rewrite our code completely since the issue came and now the table is just different I do not have any testing bench with the issue now. Sorry David
            marko Marko Mäkelä added a comment -

            Ever since MDEV-5834 introduced the parameter innodb_defragment in MariaDB Server 10.1.1, SET GLOBAL innodb_defragment=ON has prevented SET GLOBAL innodb_optimize_fulltext_only=ON from taking effect.

            I believe that the appropriate fix is to allow both parameters to take effect in OPTIMIZE TABLE, and to return HA_ADMIN_TRY_ALTER from ha_innobase::optimize() only when neither parameter is set, which is the default setting.

            marko Marko Mäkelä added a comment - Ever since MDEV-5834 introduced the parameter innodb_defragment in MariaDB Server 10.1.1, SET GLOBAL innodb_defragment=ON has prevented SET GLOBAL innodb_optimize_fulltext_only=ON  from taking effect. I believe that the appropriate fix is to allow both parameters to take effect in OPTIMIZE TABLE , and to return HA_ADMIN_TRY_ALTER from ha_innobase::optimize()  only when neither parameter is set, which is the default setting.

            People

              marko Marko Mäkelä
              davidh David Honig
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.