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
| Field | Original Value | New Value |
|---|---|---|
| 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) {{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)*}} |
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) {{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)*}} |
| 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) {{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)*}} |
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) {{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)*}} |
| 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) {{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)*}} |
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) {{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)*}} |
| 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) {{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)*}} |
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) {{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)*}} |
| 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) {{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)*}} |
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) {{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. |
| 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) {{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. |
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) {{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. |
| 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) {{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. |
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) {{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. |
| 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) {{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. |
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. |
| Labels | optimize_table |
| 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. |
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. |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Assignee | Marko Mäkelä [ marko ] |
| Affects Version/s | 10.3.0 [ 22127 ] | |
| Affects Version/s | 10.2.0 [ 20700 ] | |
| Affects Version/s | 10.1.1 [ 16801 ] | |
| Affects Version/s | 10.2.13 [ 22910 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Summary | OPTIMIZE TABLE does not optimize the search after InnoDB Defragmentation patch | innodb_defragment=ON trumps innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE |
| issue.field.resolutiondate | 2018-06-05 12:46:00.0 | 2018-06-05 12:46:00.007 |
| Fix Version/s | 10.1.34 [ 23100 ] | |
| Fix Version/s | 10.2.16 [ 23110 ] | |
| Fix Version/s | 10.3.8 [ 23113 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Confirmed [ 10101 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 86448 ] | MariaDB v4 [ 154127 ] |