I''ve got a strange problem with rails/db performance http://www.pastie.org/409654 The problem is that some queries take more than 1sek! - and i''ve got no idea why. This doesnt happen on my localhost (sqlite/mysql). The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1. --~--~---------~--~----~------------~-------~--~----~ 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 Mar 6, 8:49 pm, Tomim <tomi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''ve got a strange problem with rails/db performancehttp://www.pastie.org/409654 >Pastie says "Sorry, there is no pastie #409654 or it has expired" Fred> The problem is that some queries take more than 1sek! - and i''ve got > no idea why. > This doesnt happen on my localhost (sqlite/mysql). > > The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Sorry :) - http://www.pastie.org/409724 On 6 Mar, 22:21, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Mar 6, 8:49 pm, Tomim <tomi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > I''ve got a strange problem with rails/db performancehttp://www.pastie.org/409654 > > Pastie says "Sorry, there is no pastie #409654 or it has expired" > > Fred > > > The problem is that some queries take more than 1sek! - and i''ve got > > no idea why. > > This doesnt happen on my localhost (sqlite/mysql). > > > The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Mar 6, 2009, at 12:49 PM, Tomim wrote:> > I''ve got a strange problem with rails/db performance > http://www.pastie.org/409654Sorry, there is no pastie #409654 or it has expired. Wanna try again?> The problem is that some queries take more than 1sek! - and i''ve got > no idea why. > This doesnt happen on my localhost (sqlite/mysql). > > The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
>> >>> The problem is that some queries take more than 1sek! - and i''ve got >>> no idea why. >>> This doesnt happen on my localhost (sqlite/mysql). >>Can we see the describe output of those tables? (users, projects, users_projects)? Also, how many rows in each? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Sorry, here it is :) - http://www.pastie.org/409724 On 6 Mar, 22:38, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> On Mar 6, 2009, at 12:49 PM, Tomim wrote: > > > > > I''ve got a strange problem with rails/db performance > >http://www.pastie.org/409654 > > Sorry, there is no pastie #409654 or it has expired. > > Wanna try again? > > > The problem is that some queries take more than 1sek! - and i''ve got > > no idea why. > > This doesnt happen on my localhost (sqlite/mysql). > > > The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Tables are almost empty - just a few(<10) records for testing. It seems that those slow queries happen sporadically, one time it takes 3.4sec, the next time 0.0002sec. But the interesting part is that it always takes very short or very long amount of time - never in between. So because the whole database holds just a few records, and there are moments that it works as it should - i think it is a server/caching issue. I just started developing this app, and it only has primary keys on "id" column, no other indexes etc. On 6 Mar, 22:40, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> >>> The problem is that some queries take more than 1sek! - and i''ve got > >>> no idea why. > >>> This doesnt happen on my localhost (sqlite/mysql). > > Can we see the describe output of those tables? (users, projects, > users_projects)? Also, how many rows in each?--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Based on this, I''d say there is something wrong with your database. Or at the OS level... swapping, bad disks, something. I''d remove rails from the equation and run a test update query directly via mysql until you can figure it out. On Mar 6, 2009, at 2:38 PM, Tomim wrote:> > Tables are almost empty - just a few(<10) records for testing. > It seems that those slow queries happen sporadically, one time it > takes 3.4sec, the next time 0.0002sec. > But the interesting part is that it always takes very short or very > long amount of time - never in between. > So because the whole database holds just a few records, and there are > moments that it works as it should - i think it is a server/caching > issue. > I just started developing this app, and it only has primary keys on > "id" column, no other indexes etc. > > On 6 Mar, 22:40, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote: >>>>> The problem is that some queries take more than 1sek! - and i''ve >>>>> got >>>>> no idea why. >>>>> This doesnt happen on my localhost (sqlite/mysql). >> >> Can we see the describe output of those tables? (users, projects, >> users_projects)? Also, how many rows in each? > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Yup!, i''ve just created a test case of this behaviour in mysql console, so it has nothing to do with rails. Anyway thanks for your help! On 7 Mar, 00:02, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> Based on this, I''d say there is something wrong with your database. > Or at the OS level... swapping, bad disks, something. > > I''d remove rails from the equation and run a test update query > directly via mysql until you can figure it out. > > On Mar 6, 2009, at 2:38 PM, Tomim wrote: > > > > > Tables are almost empty - just a few(<10) records for testing. > > It seems that those slow queries happen sporadically, one time it > > takes 3.4sec, the next time 0.0002sec. > > But the interesting part is that it always takes very short or very > > long amount of time - never in between. > > So because the whole database holds just a few records, and there are > > moments that it works as it should - i think it is a server/caching > > issue. > > I just started developing this app, and it only has primary keys on > > "id" column, no other indexes etc. > > > On 6 Mar, 22:40, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote: > >>>>> The problem is that some queries take more than 1sek! - and i''ve > >>>>> got > >>>>> no idea why. > >>>>> This doesnt happen on my localhost (sqlite/mysql). > > >> Can we see the describe output of those tables? (users, projects, > >> users_projects)? Also, how many rows in each?--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Fixed! :) - sort of. If anybody is interested - it helped changing DB type from InnoDB to MyISAM. Somehow InnoDB transactions took very long - it might be a config problem, i''ll investigate it later, but for now, i can continue developing :) On 7 Mar, 00:09, Tomim <tomi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Yup!, i''ve just created a test case of this behaviour in mysql > console, so it has nothing to do with rails. > > Anyway thanks for your help! > > On 7 Mar, 00:02, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote: > > > Based on this, I''d say there is something wrong with your database. > > Or at the OS level... swapping, bad disks, something. > > > I''d remove rails from the equation and run a test update query > > directly via mysql until you can figure it out. > > > On Mar 6, 2009, at 2:38 PM, Tomim wrote: > > > > Tables are almost empty - just a few(<10) records for testing. > > > It seems that those slow queries happen sporadically, one time it > > > takes 3.4sec, the next time 0.0002sec. > > > But the interesting part is that it always takes very short or very > > > long amount of time - never in between. > > > So because the whole database holds just a few records, and there are > > > moments that it works as it should - i think it is a server/caching > > > issue. > > > I just started developing this app, and it only has primary keys on > > > "id" column, no other indexes etc. > > > > On 6 Mar, 22:40, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote: > > >>>>> The problem is that some queries take more than 1sek! - and i''ve > > >>>>> got > > >>>>> no idea why. > > >>>>> This doesnt happen on my localhost (sqlite/mysql). > > > >> Can we see the describe output of those tables? (users, projects, > > >> users_projects)? Also, how many rows in each?--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Hard to really answer without seeing what else is going on on that server. Is it idle or is there traffic on it? Off the cuff, I''m guessing contention in the db--it''s your updates that are taking a long time. Is this MySQL or another db? It also looks like you''re updating your users table with ''last_updated_at'' on every request... I don''t think that''s gonna scale. dwh Tomim wrote:> Sorry, here it is :) - http://www.pastie.org/409724 > > >>> I''ve got a strange problem with rails/db performance >>> http://www.pastie.org/409654 >>> >> >>> The problem is that some queries take more than 1sek! - and i''ve got >>> no idea why. >>> This doesnt happen on my localhost (sqlite/mysql). >>> >>> The server is a fast Xenon + debian 4.0 + nginx + rails 2.3RC1. >>> > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---