Forum: Ruby on Rails Strange rails/db performance issue.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Tomim (Guest)
on 2009-03-06 23:21
(Received via mailing list)
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.
Frederick C. (Guest)
on 2009-03-06 23:22
(Received via mailing list)
On Mar 6, 8:49 pm, Tomim <removed_email_address@domain.invalid> 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
Tomim (Guest)
on 2009-03-06 23:27
(Received via mailing list)
Philip H. (Guest)
on 2009-03-06 23:39
(Received via mailing list)
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?
Tomim (Guest)
on 2009-03-06 23:43
(Received via mailing list)
Sorry, here it is :) - http://www.pastie.org/409724
Philip H. (Guest)
on 2009-03-07 00:21
(Received via mailing list)
>>
>>> 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?
Tomim (Guest)
on 2009-03-07 00:39
(Received via mailing list)
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.
Philip H. (Guest)
on 2009-03-07 01:03
(Received via mailing list)
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.
Tomim (Guest)
on 2009-03-07 01:10
(Received via mailing list)
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!
Tomim (Guest)
on 2009-03-07 01:49
(Received via mailing list)
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 :)
Denis H. (Guest)
on 2009-03-07 04:41
(Received via mailing list)
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
This topic is locked and can not be replied to.