Hey guys,
I have a very large rails app running on 4 instances of mongrel on a P4
server with 1GB RAM. Not the absolute best setup, but, the server has
been optimized and the application has been running extremely fast for
the past few months.
I''m having one serious problem however...Theres a specific action that
uses 99% of MySQL on the server and in most cases, doesn''t even load, I
end up with a "520 Proxy Error, Reason: Error reading from remote
server" error from mongrel.
The action in question really is like any other, It grabs images tagged
with a specific tag and paginates them:
@image_pages, @images = paginate :images,
:include => "tags",
:conditions => (["tags.name =
?",
params[:tag]]),
:order_by => "images.created_at
DESC",
:per_page => 12
I can''t figure out what''s going on, it seems to run fine on my
localhost, but simply kills the production server. The rails log looks
normal, the query is very large after the associations, but I have
similar queries that run just fine.
How could I go about figuring out what the problem is?
Any help would be extremely appreciated!
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Philip Hallstrom
2007-Jun-08 05:11 UTC
Re: Rails action using an insane amount of MySQL/CPU
> I have a very large rails app running on 4 instances of mongrel on a P4 > server with 1GB RAM. Not the absolute best setup, but, the server has > been optimized and the application has been running extremely fast for > the past few months. > > I''m having one serious problem however...Theres a specific action that > uses 99% of MySQL on the server and in most cases, doesn''t even load, I > end up with a "520 Proxy Error, Reason: Error reading from remote > server" error from mongrel. > > The action in question really is like any other, It grabs images tagged > with a specific tag and paginates them: > > @image_pages, @images = paginate :images, > :include => "tags", > :conditions => (["tags.name = ?", > params[:tag]]), > :order_by => "images.created_at > DESC", > :per_page => 12 > > I can''t figure out what''s going on, it seems to run fine on my > localhost, but simply kills the production server. The rails log looks > normal, the query is very large after the associations, but I have > similar queries that run just fine. > > How could I go about figuring out what the problem is? > > Any help would be extremely appreciated!How many images/tags are in the database? paginate is not very efficient when it comes to large datasets as it grabs them all (at least that''s my memory). I''d take a look at the dev log and the SQL being generated and see what it is that''s going on. Perhaps pass those queries into mysql prefixed with "EXPLAIN " to see if it''s using your indexes or not... -philip --~--~---------~--~----~------------~-------~--~----~ 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 6/7/07, Danny C. <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I''m having one serious problem however...Theres a specific action that > uses 99% of MySQL on the server and in most cases, doesn''t even load, I > end up with a "520 Proxy Error, Reason: Error reading from remote > server" error from mongrel. > > The action in question really is like any other, It grabs images tagged > with a specific tag and paginates them: > > @image_pages, @images = paginate :images, > :include => "tags", > :conditions => (["tags.name = ?", > params[:tag]]), > :order_by => "images.created_at > DESC", > :per_page => 12 > > I can''t figure out what''s going on, it seems to run fine on my > localhost, but simply kills the production server. The rails log looks > normal, the query is very large after the associations, but I have > similar queries that run just fine. > > How could I go about figuring out what the problem is?The problem is that paginate uses :limit and :offset which can''t work well with eager joins (your :include => ''tags'') since all the matching rows (perhaps thousands or millions) have to be pulled into Ruby, parsed, then limited and offset. To put it mildly, this does not scale. Remove the :include => tags to regain nearly all your performance at the minor cost of 12 additional queries to pull tags per page. Generally speaking, to troubleshoot database issues, look at the slow queries in your production.log and use EXPLAIN <the query> in MySQL to see why they''re performing poorly. Luckily, in this case, it''s just a matter of returning way too much data. jeremy --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Danny,
1/
Try out the query analyzer plugin :
http://agilewebdevelopment.com/plugins/query_analyzer
In no time you''ll know if your are missing some obvious indexes, the
common cause for DB slowness.
.. and read this :
Indexing for DB performance
http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance
Alain Ravet
--------
http://blog.ravet.com
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Alain Ravet wrote:> Danny, > > 1/ > Try out the query analyzer plugin : > http://agilewebdevelopment.com/plugins/query_analyzer > > In no time you''ll know if your are missing some obvious indexes, the > common cause for DB slowness. > > .. and read this : > Indexing for DB performance > http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance > > > > Alain Ravet > -------- > http://blog.ravet.comAmazing! Each post got me a bit closer to understating the problem. It was definitely missing indexes, there are about 4,000 tags in the table, hence it was doing a full scan to find a matching result(s). I added 2 indexes in the images_tags table, on image_id and tag_id which seems to have drastically sped things up. However, theres still room for improvement. The query analyzer plugin is awesome, since it breaks down all your queries in the production log. Thanks for all the help! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I would also recommend using the paginator gem instead of the built-in pagination of Rails. It is much more efficient. SInce I swapped them out, I''ve had no problems. gem install paginator documentation: http://paginator.rubyforge.org/ On 6/8/07, Danny C. <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Alain Ravet wrote: > > Danny, > > > > 1/ > > Try out the query analyzer plugin : > > http://agilewebdevelopment.com/plugins/query_analyzer > > > > In no time you''ll know if your are missing some obvious indexes, the > > common cause for DB slowness. > > > > .. and read this : > > Indexing for DB performance > > http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance > > > > > > > > Alain Ravet > > -------- > > http://blog.ravet.com > > Amazing! Each post got me a bit closer to understating the problem. > > It was definitely missing indexes, there are about 4,000 tags in the > table, hence it was doing a full scan to find a matching result(s). > > I added 2 indexes in the images_tags table, on image_id and tag_id which > seems to have drastically sped things up. However, theres still room for > improvement. > > The query analyzer plugin is awesome, since it breaks down all your > queries in the production log. > > Thanks for all the help! > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Chris Hall wrote:> I would also recommend using the paginator gem instead of the built-in > pagination of Rails. It is much more efficient. SInce I swapped them > out, I''ve had no problems. > > gem install paginator > > documentation: http://paginator.rubyforge.org/Have you had any experience using this in conjunction with ajax? I assume you could just change the "link_to next/prev page" to a link_to_remote, and reload a div with the actual items. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
that would work, just be sure to update the links for next/prev with the new offset and limit On 6/9/07, Danny C. <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Chris Hall wrote: > > I would also recommend using the paginator gem instead of the built-in > > pagination of Rails. It is much more efficient. SInce I swapped them > > out, I''ve had no problems. > > > > gem install paginator > > > > documentation: http://paginator.rubyforge.org/ > > Have you had any experience using this in conjunction with ajax? > > I assume you could just change the "link_to next/prev page" to a > link_to_remote, and reload a div with the actual items. > > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Also if you have all your images in the database, this could be the main cause for the slowness, send back so much data can take a while, even if the actual query is quick. I don''t know it would be possible, but consider maybe using Amzon S3 service if your number of image will increase and the number of requests increase. Then just keep a reference of the names in database, this should inprove the effecientcy more. On Jun 11, 8:17 am, "Chris Hall" <christopher.k.h...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> that would work, just be sure to update the links for next/prev with > the new offset and limit > > On 6/9/07, Danny C. <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > > > > > Chris Hall wrote: > > > I would also recommend using the paginator gem instead of the built-in > > > pagination of Rails. It is much more efficient. SInce I swapped them > > > out, I''ve had no problems. > > > > gem install paginator > > > > documentation:http://paginator.rubyforge.org/ > > > Have you had any experience using this in conjunction with ajax? > > > I assume you could just change the "link_to next/prev page" to a > > link_to_remote, and reload a div with the actual items. > > > -- > > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---