How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed

Question

I ran a multi-threaded client spawning 25 threads to make concurrent API calls and insert data to AWS Aurora server.

After some time, I started to see timeout error: lock wait timeout exceeded try restarting transaction.We run the same test for a server running MySQL 5.6.10, and no lock wait timeout happened.

Is there a way to avoid this timeout ?

On the AWS Aurora server, SHOW ENGINE INNODB STATUS showed:

---TRANSACTION 8530565676, ACTIVE 81 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 405, OS thread handle 0x2ae270b03700, query id 11045 10.50.101.56 app_migration
INSERT INTO contacts_contactaudit (action,
    contact_id,
    date_created,
    date_updated,
    external_contact_id,
    entity_name,
    first_name,
    last_name,
    middle_name,
    actor_created_id,
    actor_updated_id,
    email,
    phone_number_id,
    external_contact_guid,
    external_shared_contact_id,
    active_timezone, audit_date)
SELECT 'I' as action, new.id,
    new.date_created,
    new.date_updated,
    new.external_contact_id,
    new.entity_name,
    new.first_name,
    new.last_name,
    new.middle_name,
    new.actor_created_id,
    new.actor_updated_id,
    new.email,
    new.phone_number_id,
    new.external_contact_guid,
    new.external_shared_contact_id,
    new.active_timezone, now();

This is the trigger we created for INSERTs statement:

CREATE TRIGGER contacts_contact_insert_audit
AFTER INSERT ON contacts_contact
FOR EACH ROW
    INSERT INTO contacts_contactaudit (action,
    ...
    audit_date)
SELECT 'I' as action, new.id,
    ... 
now();

And this is the audit table schema:

  CREATE TABLE `contacts_contactaudit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime(6) DEFAULT NULL,
  `date_updated` datetime(6) DEFAULT NULL,
  `action` varchar(1) NOT NULL,
  `audit_date` datetime(6) NOT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `external_contact_id` bigint(20) DEFAULT NULL,
  `entity_name` varchar(128) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `middle_name` varchar(128) DEFAULT NULL,
  `actor_created_id` int(11) DEFAULT NULL,
  `actor_updated_id` int(11) DEFAULT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number_id` int(11) DEFAULT NULL,
  `external_contact_guid` varchar(128) DEFAULT NULL,
  `external_shared_contact_id` bigint(20) DEFAULT NULL,
  `active_timezone` varchar(128),
  PRIMARY KEY (`id`),
  KEY `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` (`actor_created_id`),
  KEY `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` (`actor_updated_id`),
  KEY `contacts_contactaudit_contact_id_9b809fe7_uniq` (`contact_id`),
  CONSTRAINT `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` FOREIGN KEY (`actor_created_id`) REFERENCES `actors_actor` (`id`),
  CONSTRAINT `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` FOREIGN KEY (`actor_updated_id`) REFERENCES `actors_actor` (`id`)
) 
ENGINE=InnoDB 
AUTO_INCREMENT=21577 
DEFAULT CHARSET=utf8;

score 4 · Answer 1 · answered Dec 13 '17 at 19:54

We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 .

Here is the summary from the official doc:

0: traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
1: consecutive lock mode: In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time
2: interleaved lock mode: In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.

In our case, when multiple API called INSERT statements, if one API call is prolonged, that INSERT statement would hold a TABLE lock on the destination table, which then causes the timeouts.

We switch it to innodb_autoinc_lock_mode = 2, restart the server and voila. Now we can call multiple APIs to insert into the same table without having time-outs.

score 1 · Answer 2 · answered Nov 02 '17 at 14:07

Maybe there could be a few 'master' APIs, each doing some subset of the 250-300 inserts?

For ACID, each transaction requires a disk write -- that is what is limiting your speed. Not knowing details of your SQL, I can only guess at things to help:

Batching INSERTs into a table (multiple rows in a single transaction).
Each of the 25 threads does only one (or a small number of) transaction for the hundreds of INSERTs. (This works fine if multiple tables are involved.) Caution: While it cuts down (drastically) on the chance of timeouts, it does increase the chance of deadlocks. Be ready to replay a transaction that gets rolledback.
Rethink the TRIGGERs. Since you have an API, it could do the auditing in separate queries, thereby allowing more flexibility in building transactions.
Consider Stored Procedures to do clumps of data. (I usually prefer doing the equivalent in app code, but SPs are fine.)

Some other things to check on... How many rows in the main table and the audit table? What is the value of innodb_buffer_pool_size? How much RAM?

How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed

2 Answers2