Rails - Sep 2008 - Mysql tuning plus catching ActiveRecord::StatementInvalid errors..

Recently our Rails app has been choking every once and a while on
updates to the "users" table. The error normally appears like so:

A ActiveRecord::StatementInvalid occurred in
activity_responses#create:

  Mysql::Error: Lock wait timeout exceeded; try restarting
transaction: UPDATE `users` SET `created_at` = ''2007-03-02
23:26:33'',
`home_phone` = NULL, ....

The users table currently has about  350,000 rows and consists of
about 50 fields. The error only occurs on the users table yet there
are 3 or 4 other tables with much more rows that work just fine
(although the updating is not as often as the users table). I have
googled and googled this error and haven''t come up with much. I have
tweaked and retweaked mysql settings to no avail and also tried
tweaking a few rails variables. I think it may have to do with certain
queries that are not indexed properly, but I can''t seem to find
anything that would point me to where or what to fix. Any ideas on
where to go next to track down this issue?

Onto the 2nd part.. While I hate that some users get this error, it is
happening once or twice a day. Is there anyway to catch this error so
I can send them a specific error message on the subject and let them
know it is ok to try again in a minute or two? (Rather then the
standard error message)

Thanks..
-tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sep 30, 6:52 pm, Tim W
<tiwat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Recently our Rails app has been choking every once and a while on
> updates to the "users" table. The error normally appears like so:
>
> A ActiveRecord::StatementInvalid occurred in
> activity_responses#create:
>
>   Mysql::Error: Lock wait timeout exceeded; try restarting
> transaction: UPDATE `users` SET `created_at` = ''2007-03-02
23:26:33'',
> `home_phone` = NULL, ....
>
> The users table currently has about  350,000 rows and consists of
> about 50 fields. The error only occurs on the users table yet there
> are 3 or 4 other tables with much more rows that work just fine
> (although the updating is not as often as the users table). I have
> googled and googled this error and haven''t come up with much. I
have
> tweaked and retweaked mysql settings to no avail and also tried
> tweaking a few rails variables. I think it may have to do with certain
> queries that are not indexed properly, but I can''t seem to find
> anything that would point me to where or what to fix. Any ideas on
> where to go next to track down this issue?
>
Basically that error means that one transaction was waiting to get a
lock on some rows, waited a while and then gave up.
InnoDB does row level locking, and locks index ranges, so a missing
(or inefficient (ie not very precise)) index can cause it to lock far
more rows than it needs to, making this error much more likely. There
is also a mysql setting that controls how long it will wait before
giving up. THere is also a plugin (deadlock_retry) that will retry
failed writes like this (for us at least it has not been a panacea).
Looking at the source for this plugin should also give you some clues
about how to deal with the second part of your problem.

Fred
> Onto the 2nd part.. While I hate that some users get this error, it is
> happening once or twice a day. Is there anyway to catch this error so
> I can send them a specific error message on the subject and let them
> know it is ok to try again in a minute or two? (Rather then the
> standard error message)
>
> Thanks..
> -tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---