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
