Re: Slow insert rate in mysql innodb
Posted by:
Rick James
Date: January 31, 2014 08:31PM
> table creation requires to add (id, timestap) for primary key. Is that fine, adding 2 col in primary key?
Yes, that is fine. The only rules for AUTO_INCREMENT indexes are:
* It must be the first field in some index. (A PK is an index).
* If it is not alone and UNIQUE (PK is UNIQUE), you could (but probably won't) explicitly insert a duplicate id. (A dup will not be generated automatically.)
In InnoDB, a table is a BTree; it contains the PRIMARY KEY as the ordering of the BTree. Any other indexes are also BTrees. INSERTing a row must update every BTree (sooner or later).
If the PRIMARY KEY is (or starts with) an AUTO_INCREMENT, that BTree will be 'appended' to as you do INSERTs. This is very efficient. Think of it as filling a block of rows, writing it, then filling a new block, etc. Rule of Thumb: 1 I/O per 100 rows INSERTed.
If you don't have an explicit PRIMARY KEY (and no suitable UNIQUE INDEX), InnoDB will create a hidden PK for you. But this means another BTree to update. So, do
PRIMARY KEY (id, timestamp)
For secondary keys:
1)
where notification_type=1
and timestamp > SYSDATE() - INTERVAL 1 MINUTE; 2)
2)
where timestamp between str_to_date('01/31/2014 10:48:02', '%m/%d/%Y %k:%i:%s')
AND str_to_date('2/1/2014 11:18:02', '%m/%d/%Y %k:%i:%s')
and host = '1.1.1.1'
and priority = 'alert'
and suppression_flag = 1
and notification_type = 0
It is unclear what secondary keys would be optimal. It depends on how common type=1 and host=1.1.1.1, etc.
Plan A: only
INDEX(timestamp)
Plan B: two indexes; the exact fields depend on cardinality of the fields.
INDEX(notification_type, timestamp),
INDEX(host, priority, timestamp)
Further analysis:
Assuming that `timestamp` is monotonically increasing (or nearly so) as you do the INSERTs, then...
If there are, say, 5 different values of notification_type, then
INDEX(notification_type, timestamp),
will have 5 "hot spots". By that I mean that 5 spots in that index there will be a lot of inserts. This is not bad. The blocks at these 5 spots are easily cached and written only infrequently -- not much I/O.
If, say, `host` is rarely the same value, then
INDEX(host, ..., timestamp)
will be jumping around all over its BTree. This will cause a lot of I/O. OTOH, it is important for your SELECT #2. That is, you are stuck with I/O on INSERT to save I/O on SELECT. I don't know your app well enough to judge which is better. Probably having INDEX(host) (with or without other fields) is a good idea.
#2 has a LIMIT, but no ORDER BY. Do you care which 2000 rows you get?
If the LIMIT is usually much less than the total number of rows satisfying the WHERE, then this becomes beneficial:
INDEX(host, priority, suppression_type, notification_type, timestamp)
With that, it will stop after 2000. Without that lengthy INDEX, it will have to bounce between the index and the data to check the rest of the WHERE.
(The order of the fields is thus: the first ones are '=' in the WHERE, in any order; the last one is 'BETWEEN'.)
Be aware that BETWEEN is inclusive, hence contains one second more than you may realize.