I have disabled indexes updates with alter table my_table disable keys to speed up inserts

Are there variables I can set [for the session] to speed up indexes rebuilding with alter table my_table enable keys? Some specific buffer variables for example

CREATE TABLE `my_table` (
  `tec` mediumint(1) unsigned NOT NULL,
  `nr` mediumint(1) unsigned NOT NULL,
  `fk_brand_trans` smallint(1) unsigned NOT NULL,
  `ref_trans` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `fk_coverage_tree` varchar(12) COLLATE latin1_general_ci NOT NULL,
  `ref` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `pos` tinyint(1) NOT NULL,
  KEY `coverage` (`tec`,`nr`,`fk_coverage_tree`,`fk_brand_trans`),
  KEY `pair` (`fk_brand_trans`,`ref_trans`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

| Name     | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
| my_table | MyISAM |      10 | Dynamic    | 323790932 |             34 | 11315537700 | 281474976710655 |   3792999424 |         0 |           NULL | 2018-07-16 15:57:46 | 2018-07-16 22:19:02 | 2018-07-16 22:19:01 | latin1_general_ci |NULL      |                |         |

truncate table my_table;
alter table my_table disable keys;
insert into my_table (...) select ...;
insert into my_table (...) select ...;
insert into my_table (...) select ...;
insert into my_table (...) select ...;
alter table my_table enable keys;
  • Do you really hope there exists session variables which can afffect on server core processes? – Akina Jul 16 '18 at 7:04
  • Yes, allowing specific buffers to use more memory for example – guigoz Jul 16 '18 at 8:46
  • It can affect on DML queries only. – Akina Jul 16 '18 at 8:48
  • How many INSERT..SELECT statements? About how many rows in each? – Rick James Jul 16 '18 at 23:16
  • 4 INSERT..SELECT inserting about 150M 150M 6M 2M – guigoz Jul 17 '18 at 8:48

The answer is "maybe".

It is almost never beneficial to DISABLE KEYS and reenable them. On the assumption you have a valid use case, let's discuss a specific or two. Please provide SHOW CREATE TABLE, SHOW TABLE STATUS and the statements leading up to the rebuild.

Some more

When re-enabling the keys, 11.3GB will be read and about 3.7GB will be written. (There may be intermediate results, too.) So, if you are adding a small amount of rows (only a few millions), this is probably slower than updating the indexes as you go.

How much RAM? What is the value of key_buffer_size? During normal use, that setting should be about 20% of RAM. But during the INSERT..SELECTs and without the pair of ALTERs, that should be temporarily raised to, say, 80%. This is because virtually all the random I/O will involve the key_buffer.

Also you might get another speed boost by sorting the incoming rows. That is, it might help to do

INSERT INTO my_table  SELECT ... ORDER BY `fk_brand_trans`,`ref_trans`

(It probably does not matter which index you mimic by the order by.) The idea here is to 'sequentially' insert rows into one of the indexes while doing random I/O on the other(s). This might cut in half the I/O to/from the key_buffer -- but at the expense of doing the ORDER BY. There is no way to predict whether this will actually help.

If you could convert to InnoDB, that might improve speed. However, there are several issues to cover -- lack of PRIMARY KEY, disk space might go from 15GB to 40GB, etc.

| improve this answer | |
  • Requested info appended – guigoz Jul 16 '18 at 22:25
  • I will try each of your advices separately and then combine them to each other – guigoz Jul 17 '18 at 8:54
  • @guigoz - Good. But keep in mind that combining multiple changes may lead to more (or less) benefit than the sum of the individual changes. I don't have enough info about your data to judge which of my suggestions will or won't help, nor what happens with combinations. – Rick James Jul 17 '18 at 18:04
  • @guigoz - For example, I can see a scenario where adding ram or sorting the input would each improve performance, yet the combination would improve by the max of the two, not the sum. And another scenario where neither one helps, yet the combination does help. (The scenarios involve the relative sizes of the indexes and key_buffer_size.) – Rick James Jul 17 '18 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.