Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thanks!

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.