Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to check our used memory. We were swapping about 70MB worth. I went on a memory witch-hunt with mysqltuner, and it said about 400% max memory usage possible. I now have it down to just a little > 100%., thanks to Percona's Configuration Wizard.

However, we still have this lock problem, so I'm assuming it is NOT related to the memory/swapping. Why am I still receiving the lockout? What's going on here?

I'm confident a reboot will solve the problem, but that shouldn't be the solution. What could we do to prevent this in the future? I tried flushing the query cache and the tables -- that worked.

Other kinds of flushes did not work, thanks to RDS :/

Here is a wealth of information I could figure to provide:

QUERY

INSERT INTO `myTable` (`firstName`, `lastName`, `email`) VALUES ('Travis', 'B...', '...@gmail.com')

Error message

Lock wait timeout exceeded; try restarting transaction

Table Schema

CREATE TABLE IF NOT EXISTS `myTable` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `lastName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique Emails` (`email`),
  KEY `FullName` (`firstName`,`lastName`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=504 ;

mysqltuner.pl

```
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Performing tests on emp:3306
[OK] Logged in using credentials passed on the command line
[--] Assuming 17511 MB of physical memory
[!!] Assuming 0 MB of swap space (use --forceswap to specify)

-------- Gene

Since you are running MySQL 5.5, have you considering tuning InnoDB for better performance?

There are several variables that have been added to the MySQL 5.1 InnoDB Plugin that are now native to MySQL 5.5. They usually help increase hyperthreading and take advantage of more IOPS if the environment can handled it. In your case, you should be able to.

I have spun RDS models before (See my post : Local database vs Amazon RDS) and can tell you that RDS is not very helpful in tuning InnoDB. You will have to take the bull by the horns on this one.

When you spun up the RDS instance, you probably used the default DB Parameter Group

You should be able to create a new DB Parameter Group for yourself. When you get to this, set the following:

• innodb_read_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.

• innodb_write_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.

• innodb_thread_concurrency : Default is already 0. Never set this value to any other number.

I suggest these because they are default in RDS for innodb_read_io_threads and innodb_write_io_threads are just too low. In addition, your SHOW ENGINE INNODB STATUS\G

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

only shows the default of 4 for each class of io threads.

Here is the Bad News: To implement config changes, once you create your own DB Parameter Group, you must export the data from the old RDS instance, create a new instance using your new DB Parameter Group, and reload. In more detail...

• Create a Custom DB Parameter Group (call it MySettings)

• Download RDS CLI and setup a config file with your AWS Credentials

• Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=innodb_read_io_threads,value=16,method=immediate"

• Modify using DB Parameter Option List MySettings

• Restart the MySQL RDS Instance

I hope this helps !!!

StackExchange Database Administrators Q#22796, answer score: 6