Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.1, 10.2.0, 10.3.0
-
rails, mariadb 10.2.13, debian
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
-
MDEV-5834 Merge Kakao Defragmentation implementation to MariaDB 10.1
-
- Closed
-
Activity
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.
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; |
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
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.
Are you using non-default values of either of these flags?
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?