Which MySQL server parameters should be adjusted to improve INSERT performance for a large MyISAM table?

Question

I have a MyISAM table with roughly 20M rows for which inserting 1000 rows takes about 40 seconds on the production server (Ubuntu 12.04, MySQL 5.6.35). On the test machine (Windows 8.1, MySQL 5.6.22), the same insert takes less than 1 second which seems a more reasonable timing for me. On the production server inserts of ~1000 rows happens every 5 minutes. Which my.cnf configuration values should I try to change to improve performance on the production server?

Note: I've already increased key_buffer_size=256M (and even to key_buffer_size=1024M) which didn't change anything.

This is how the table definition looks like:

CREATE TABLE `mytable` (
 `id1` varchar(40) NOT NULL,
 `t` datetime NOT NULL,
 `id2` varchar(12) NOT NULL,
 `a` varchar(8) NOT NULL,
 `b` int(11) NOT NULL,
 `f1` varchar(4) DEFAULT NULL,
 `f2` varchar(5) DEFAULT NULL,
 `f3` varchar(16) DEFAULT NULL,
 `f4` varchar(16) DEFAULT NULL,
 `f5` tinyint(4) DEFAULT NULL,
 `f6` tinyint(4) DEFAULT NULL,
 `f7` tinyint(4) DEFAULT NULL,
 `f8` tinyint(4) DEFAULT NULL,
 `f9` tinyint(4) DEFAULT NULL,
 `f10` tinyint(4) DEFAULT NULL,
 `f11` tinyint(4) DEFAULT NULL,
 `f12` tinyint(4) DEFAULT NULL,
 `f13` varchar(8) DEFAULT NULL,
 `f14` int(11) DEFAULT NULL,
 UNIQUE KEY `id1` (`id1`,`t`,`id2`),
 KEY `t` (`t`),
 KEY `id2` (`id2`),
 KEY `a` (`a`),
 KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

id1 and id2 are random values, t is a timestamp representing web user local times, so from a coarse perspective it's constantly increasing, but within a bunch of 1000 rows times may vary +/- 1 day.

Update 1

Rows will only be inserted into the table, never updated. Hence, there is no fragmentation, as expected:

mysql> select ENGINE, TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE from information_schema.tables;
+--------+--------------+----------------+-------------+--------------+-----------+
| ENGINE | TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+--------+--------------+----------------+-------------+--------------+-----------+
| ... |
| MyISAM | products | mytable | 2301965084 | 2030800896 | 0 |
| ... |
+--------+--------------+----------------+-------------+--------------+-----------+

Update 2

I've 'cloned' mytable to mytable2 for testing purposes and running test INSERTs into mytable2. In general I'm seeing better performance here: roughly 8 seconds for inserting 1000 rows (after some initial startup time). So, (1) looks like I'm encountering OS level caching issues for my production table. (2) When changing the random id1 to an increasing value, inserting 1000 rows takes roughly 400ms.

Update 3

After changing indexes to:

 UNIQUE KEY `t` (`t`,`id1`,`id2`),
 KEY `id1` (`id1`),
 KEY `id2` (`id2`)

inserting 1000 rows with random t, id1 and id2 keys, this takes roughly 500ms after some initial startup time: inserting first batch took 40000ms, second batch took 18000ms, ... 10th batch took 500ms. Hence, the initial question comes up again: is there any my.cnf setting to improve this startup delay?

• RMathis: Do both environments have similar capabilities (cpu, ram, swap space, disk speed, etc...). Have you checked for differences in the my.cnf files? If any, have you tried the windows settings in Ubuntu? Does the windows table have the same rows as the Ubuntu table (it is much more efficient to insert into an empty index than one with 20M rows). Is the Ubuntu table fragmented? It might be time to rebuild it. One last thing, the id1 column is covered by two indexes (id1 & id1_2) - the id1_2 index is not needed and could be dropped.
• mstrap: CPU and RAM are similar, on the test machine I have an SSD, on the server it's a HDD (Software) RAID. Both tables have almost the same content (just on the server every 10 minutes roughly 1000 rows are added, so a bit more rows there). I've updated the question with fragmentation details (am I correct, that the table isn't fragmented at all?) Thanks for pointing to the redundant index.
• Rick James: Is that "software raid"? RAID-0/1/5/10/what? SSDs are likely to be a lot faster; isn't this the answer?
• mstrap: It's a standard Ubuntu Software RAID (mdadm) with RAID level 1. I agree that SSDs are expected to be faster, still I think the server database is not tuned well.

Answer 1

A long list of possible improvements:

Turn off updating "atime" on the filesystem?

INDEX(id1) can be DROPped since there is another index starting with that. This would decrease from 6 to 5 the number of indexes to update -- a small speedup.

Toss any other indexes you are not using.

Use NOT NULL where appropriate. I doubt if you meant for all columns to be NULLable?

With a 1G key_buffer, I hope that you have at least 6G of RAM. If not, decrease that setting.

Increasing the key_buffer will not instantly improve the index caching -- it takes time for blocks to get into the key_buffer and become useful.

If practical, "batch" the inserts. That is,

INSERT INTO mytable (...) VALUES (...), (...), ...;

Optimal is to batch 100-1000 at a time.

Switch to InnoDB.

What will you do with the data? (Usually the performance problems occur on the read side.)

Two Hardware suggestions: SSD; RAID-striping with write buffer.

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.

Answer 2

If you are using

INSERT INTO mytable (...) VALUES (...), (...), ...;

I would like to suggest one or more of the following

SUGGESTION #1

Increase your bulk_insert_buffer_size to 128M or 256M.

I have suggested this before.

SUGGESTION #2

Temporarily disable non-unique indexes before bulk inserts and enable them afterwards.

ALTER TABLE mytable DISABLE KEYS;
INSERT INTO mytable (...) VALUES (...), (...), ...;
ALTER TABLE mytable ENABLE KEYS;

SUGGESTION #3 (OPTIONAL)

If you do any deletes, then set concurrent_insert=2.

I have suggested this before.

• mstrap: Thanks for your ideas. I'm referring to my new server test environment (mytable2), see Update 2 above. Increasing bulk_insert_buffer_size to 512M didn't have any effect. Removing all non-unique indices seems to give same good timings as having sorted, increasing id1 keys. But AFAIU, ALTER TABLE mytable ENABLE KEYS; is too expensive for small batches of 1000 rows?
• RolandoMySQLDBA: In your particular case, you are right. For inserting 1000 rows, 10000, or even 100000 rows. using ENABLE KEYS is not worth it. One million or more rows, maybe.