I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this:
318263 myapp1 domU-XX-XX-XX-0F-90-C1.compute-1.internal:55660 mydb Query 88 Updating UPDATE `profiles` SET `updated_at` = '2012-05-23 14:24:46', `latitude` = 38.896, `longitude` = -77.0452 WHERE (`profiles`.`id` = 100767) 318264 myapp1 domU-XX-XX-XX-01-60-B1.compute-1.internal:46609 mydb Query 91 updating DELETE FROM `unread_message_indices` WHERE (`unread_message_indices`.`sender_id` = 100601) AND (`unread_message_indices`.`recipient_id` = 101515) 318265 myapp1 domU-XX-XX-XX-14-41-C1.compute-1.internal:59277 mydb Query 88 Updating UPDATE `inbox_profile_indices` SET `updated_at` = '2012-05-23 14:24:22' WHERE (`inbox_profile_indices`.`id` = 127613)
As you can see from the number after query, these have been in the state of "updating" for 80-90 seconds! I am running the largest DB instance, so clearly something bad is happening on the EBS node on which the DB is running.
In these situations, I would prefer the query fail after 1 or 2 seconds, not wait for over 1 min stuck in the "Updating" state. I am using Ruby's ActiveRecord FYI. What is the best way to force a failure in this case after 2 seconds? Should I use innodb_lock_wait_timeout (Don't think so since these tables aren't locked and besides these times are clearly > 50, which is what it is set at). I believe the optimal approach is to set the per-session read timeout and write timeout. In Ruby on Rails this is done by editing the databases.yml file and entering something like:
production: ... write_timeout: 2 read_timeout: 10
Will this approach work? Are there any other approaches I can use to more quickly fail these queries instead of having them hang my application threads indefinitely?