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 INSERT
s 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?
2 Answers 2
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.
-
Comments are not for extended discussion; this conversation has been moved to chat.2017年03月31日 10:10:42 +00:00Commented Mar 31, 2017 at 10:10
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.
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.
-
Thanks for your ideas. I'm referring to my new server test environment (
mytable2
), see Update 2 above. Increasingbulk_insert_buffer_size
to 512M didn't have any effect. Removing all non-unique indices seems to give same good timings as having sorted, increasingid1
keys. But AFAIU,ALTER TABLE mytable ENABLE KEYS;
is too expensive for small batches of 1000 rows?mstrap– mstrap2017年03月24日 11:16:59 +00:00Commented Mar 24, 2017 at 11:16 -
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.RolandoMySQLDBA– RolandoMySQLDBA2017年03月24日 15:10:08 +00:00Commented Mar 24, 2017 at 15:10
mdadm
) with RAID level 1. I agree that SSDs are expected to be faster, still I think the server database is not tuned well.