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

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                davidh David Honig
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: