# Rails action using an insane amount of MySQL/CPU

**URL:** <https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294>\
**Category:** Rails\
**Created:** [June 8, 2007, 5:32am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294 "2007-06-08T05:32:39Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 5:32am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/1 "2007-06-08T05:32:39Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 6:58am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/2 "2007-06-08T06:58:23Z")

</div>

> The action in question really is like any other, It grabs images tagged  
> 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

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 7:23am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/3 "2007-06-08T07:23:24Z")

</div>

On 6/7/07, Danny C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ```
> :conditions => (["tags.name = ?",
> 
> ```
> 
> 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 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

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 12:49pm UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/4 "2007-06-08T12:49:59Z")

</div>

Danny,

1/  
Try out the query analyzer plugin :  
[http://agilewebdevelopment.com/plugins/query\_analyzer](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  
[Buckblog: Indexing for DB performance](http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance)

## Alain R.

[http://blog.ravet.com](http://blog.ravet.com)

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 8:15pm UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/5 "2007-06-08T20:15:58Z")

</div>

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/](http://paginator.rubyforge.org/)

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 8, 2007, 6:43pm UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/6 "2007-06-08T18:43:11Z")

</div>

Alain R. wrote:

> Danny,
> 
> 1/  
> Try out the query analyzer plugin :  
> [http://agilewebdevelopment.com/plugins/query\_analyzer](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  
> [Buckblog: Indexing for DB performance](http://weblog.jamisbuck.org/2006/10/23/indexing-for-db-performance)
> 
> ## Alain R.
> 
> [http://blog.ravet.com](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!

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 11, 2007, 12:18am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/7 "2007-06-11T00:18:24Z")

</div>

that would work, just be sure to update the links for next/prev with  
the new offset and limit

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 9, 2007, 9:57pm UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/8 "2007-06-09T21:57:53Z")

</div>

Chris H. 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/](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.

---

<div class="post-metadata">

**Author:** ![dji](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/eb9ed0/32.png) [@dji](https://www.ruby-forum.com/u/dji)\
**Post date:** [June 11, 2007, 5:21am UTC](https://www.ruby-forum.com/t/rails-action-using-an-insane-amount-of-mysql-cpu/106294/9 "2007-06-11T05:21:45Z")

</div>

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.