0

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?

9
  • You consider 30 inserts in 30 ms to be slow? How much faster do you think it should be? By the way, did you disable autocommit in your application?
    – mustaccio
    Jul 11, 2018 at 12:44
  • Sorry @mustaccio, I was probably distracted when I was writing the time. The real one is 13s, which is slow isn't it?
    – thitami
    Jul 11, 2018 at 12:48
  • I would expect under 30ms for one row, even in the worst of situations; 13 seconds is 'not possible'. Something else is going on. Look elsewhere; INSERTs do not take long. Where did that "array:1..." come from?
    – Rick James
    Jul 11, 2018 at 14:01
  • @RickJames So, given the time from Laravel's Query log the above insert took 12.45ms which is fine I believe. So, e.g. 30 * ~13ms = 490ms = < 0.5s I guess that suggests that the bottleneck is elsewhere and not in my mysql structure then ?
    – thitami
    Jul 11, 2018 at 14:04
  • @thitami - Grrr... Then fix the question to say 13ms, not 13s.
    – Rick James
    Jul 11, 2018 at 14:38

1 Answer 1

3

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.

1
  • Many thanks for the detailed reply, @Rick James. Apparently, my MySQL queries are not the major issue for the slow response times of my endpoint. So I'll probably have to rethink my approach.
    – thitami
    Jul 11, 2018 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.