1

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;
  • According to the docs for auto-inc lock mode, set innodb_autoinc_lock_mode = 2 (interleaved mode) will help with improving concurrency insert. Am I heading the right direction? – Dio Phung Oct 31 '17 at 22:21
  • Another similar recommendation: stackoverflow.com/questions/22840883/… – Dio Phung Oct 31 '17 at 22:21
  • Please add information to the body of the question by editing it. you can always answer your own question! – Vérace Oct 31 '17 at 22:30
  • These kind of locks happen because of the code your transactions lock each other. You may simply need to commit after each insert (if your logic allows this), but we cannot tell you that without seeing the code of the whole transaction (the code in the thread, not (only) the trigger). Also relevant: how many inserts/s. If e.g. each of your 25 threads tries to insert 1.000 rows/s at a time (thus 10 queries generating 25.000 seperate trigger queries), you might simply reach your system limitations and might need to do it without triggers. – Solarflare Nov 1 '17 at 6:48
  • @Solarflare: 25 threads generated only about 250-300 inserts per second. And each insert followed by a commit. – Dio Phung Nov 1 '17 at 20:27
1

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?

  • It's 64GB RAM , we're allocating 40GB for innodb_buffer_pool_size. Another thing : this issue only happened for Aurora server, not for MySQL. I ran SHOW VARIABLES on both servers and there are so many differences. Which one should I focus on? for e.g: - table_open_cache : Aurora: 6000, MySQL: 2000 - table_open_cache_instances: Aurora: 8, MySQL: 1 Which one could potentially cause lock wait timeout error ? – Dio Phung Nov 3 '17 at 8:41
  • Ouch. There are a big differences at a low level between Aurora and MySQL, in spite of them being essentially 'identical'. My first guess is that Aurora is taking longer to commit to disk because it does such more aggressively -- to multiple drives before COMMITting. – Rick James Nov 3 '17 at 21:14
  • Next steps... I see that the Question is tagged [Aurora], but you might need to rattle their cage harder, especially if you have a support contract. Meanwhile, can you provide ram size, SHOW VARIABLES, and SHOW GLOBAL STATUS (after being up for a day) for each machine? More: mysql.rjweb.org/doc.php/mysql_analysis#tuning – Rick James Nov 3 '17 at 21:17
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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