MySQL – Randomly Slow Inserts with InnoDB

TL;DR : How can I improve insertion time in my table so that every inserts take under 100ms to perform?

This table is used by a webservice which receives transactions one by one so I can't group inserts. The timeout of the webservice must be under 150ms.

• Infrastructure : AWS
• Application (php) : EC2 t2.micro
• Mysql : RDS t2.small – 100GB magnetic storage

Here are the measurments I've done in production

Top left hand corner : We can see that a lot of inserts take more than 500ms and some even take more than 1s. Only a few are under 50ms. (red bars are 95th percentile)

Top right hand corner : RDS cpu is always low (memory usage is also constant and fine)

Bottom left hand corner : Number of inserts in the table

Bottom right hand corner : blue line is write IOPS, red line write lentency, blue bars is disk_queue_depth * 100. Disk queue is pretty much always below 1. We can see huge spikes in write lentency when everything else looks fine : no increase in IOPS nor in hardware ressource consumption.

I've created a script to measure insertion time in account_transaction in isolation :

// pseudo code
for ($i=0; $i<100; $i++) {
    $ts=microtime(true);
    $db->insert(data);
    echo microtime(true)-$ts;
}

Output for 100 inserts : (sorted)

0.012468814849854
0.012633085250854
0.012740135192871
0.012858867645264
0.012872934341431
...
...
...
0.038048028945923
0.040112018585205
0.040735960006714
0.042028903961182
0.42462396621704
Avg : 0.025037434101105

I pretty much always end up with the same results : 1% or 2% of the inserts are very slow. Same if i perform 1000 inserts.

What I've tried

• disable all indexes except PK : no improvements.
• rebuild all indexes : no improvements.
• innodb_flush_log_at_trx_commit=0 : great improvements :
  • slow inserts totally disappeared
  • average insert time 5 time faster
• switch to a bigger RDS (m4.large with 100GB SSD with 1000 IOPS preprovisioed) : some improvements :
  • slow inserts almost disappeared : maybe 0.1% if any and it rarely exceeds 100ms
  • average insert time 3 time faster

Unfortunately innodb_flush_log_at_trx_commit=0 isn't an option as I can't afford to lose any transaction.

My thoughts

I would expect inserts to be pretty fast most of the time (<1ms) even on basic hardware. Table only has 4 indexes and 200k rows. Am I wrong to assume that?

I suspect the bottleneck to be in the I/O realm because of the test with innodb_flush_log_at_trx_commit=0 but the metrics I've got from AWS doesn't seem to confirm that. Am I wrong again?

Table account_transaction

CREATE TABLE `account_transaction` (
  `id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `seqid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `token` bigint(20) unsigned DEFAULT NULL,
  ...
  ...
  `creation_date` datetime NOT NULL,
  `processing_date` datetime DEFAULT NULL,
  PRIMARY KEY (`seqid`),
  UNIQUE KEY `id` (`id`),
  KEY `consolidation_idx` (`trans_link`,`token`),
  KEY `idx_token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=239304 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id look like 1473430240HBCRSPL9FPB = unix epoch + 10 random chars : we use this format vs a standard UUID to preserve good insert performance. Always 21 char.

seqid is used to keep track of the row number. Our id has a random part so we can't use it for that purpose.

select count(1) from account_transaction = 239k

Questions

1. Is there a way to remove the high lentency spikes?
2. Should i go with bigger hardware to improve performance? My traffic is really low…
3. I must ensure that 99.9% of inserts are done under 100ms. Is that realistic on RDS mysql?