[MDEV-15824] innodb_defragment=ON trumps innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE Created: 2018-04-09  Updated: 2018-06-05  Resolved: 2018-06-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1.1, 10.2.0, 10.3.0
Fix Version/s: 10.1.34, 10.2.16, 10.3.8

Type: Bug Priority: Major
Reporter: David Honig Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: optimize_table
Environment:

rails, mariadb 10.2.13, debian

Issue Links:
Problem/Incident
is caused by MDEV-5834 Merge Kakao Defragmentation implement... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2018-05-31 ]

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?

Comment by David Honig [ 2018-05-31 ] 

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.

Comment by Marko Mäkelä [ 2018-05-31 ]

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;

Comment by David Honig [ 2018-06-04 ]

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

Comment by Marko Mäkelä [ 2018-06-05 ]

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.

Generated at Thu Feb 08 08:24:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.