Slow insert query

Posted on

Question :

I am currently investigating some really slow queries on my MySQL InnoDB DB.

Inserting around 30 records into different tables results in a response time of 13s and I am suspecting that something might be wrong with my indices etc.

This is an example of one of the tables:

CREATE TABLE `record_descriptions60 ` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `record_id` int(10) unsigned NOT NULL,
  `description` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `locale` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `record_descriptions60_record_id_locale_unique` (`record_id`,`locale`),
  CONSTRAINT `record_descriptions60_record_id_foreign` FOREIGN KEY (`record_id`) REFERENCES `records` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am trying to do a basic insert as per the log:

array:1 [
  0 => array:3 [
    "query" => "insert into `record_descriptions60` (`description`, `status`, `source`, `api_name`, `comment`, `locale`, `record_id`, `updated_at`, `created_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    "bindings" => array:10 [
      0 => "Some desc"
      1 => "approved"
      2 => "api"
      3 => "Some API"
      4 => "A comment."
      5 => "ENG"
      7 => 99
      8 => "2018-07-11 10:26:11"
      9 => "2018-07-11 10:26:11"
    ]
    "time" => 12.45
  ]
]

Any feedback on its structure? Any issues that you can notice?

Answer :

12.45 milliseconds to INSERT one row is reasonable. Here are some of the issues:

  • All UNIQUE keys must be checked for this insert being a duplicate.
  • InnoDB is crash-safe; this involves doing at least one disk write to assure it. (This is one per “transaction”; by default each statement is a transaction.)
  • Rule of Thumb: 10ms to do one disk I/O on a spinning (HDD) drive.
  • So, 12.45ms elapsed time is realistic.

There are multiple ways to speed that up, and not take 0.5s for 30 inserts:

  • “Batch” the inserts — a single INSERT statement with many rows.
  • LOAD DATA
  • Put several INSERTs in a single transaction (BEGIN...COMMIT) so that that mandatory disk write is done only once for the batch.
  • Turn off the write that I am talking about (trade speed vs security).
  • Use multiple connections.
  • SSDs are faster.
  • Consider chucking the AUTO_INCREMENT and promoting the UNIQUE to PRIMARY.

With some combination of those, it is ‘easy’ to average 1ms per row. Still, the elapsed time for one write (of several rows) is likely to continue to be more than 10ms. That is, the response time, as opposed to the throughput, is not likely to get below 10ms.

Also, keep in mind that MySQL can be doing lots of other things (via other connections) at the same time.

Leave a Reply

Your email address will not be published.