9 ms·

Rails + MySQL scaling on a budget

80 points by prateekdayal 16y ago

rbranson 16y ago
Even if you aren't "on a budget," it's always a good idea to spend some time benchmarking and tuning your setup. You never know if you'll receive a spike of traffic or what the limits of your setup are. Most of the time, between the OS, Web Server, Application Stack, and Database, there are a few knobs that can be adjusted with minimal effort that will yield big gains in headroom. The author's suggestion of Munin is a great first step is a nice way to keep from getting caught with your pants down, and can usually be installed and configured in a few minutes. This is sort of the systems side of always making sure HTTP compression is working, minifying your JavaScript, crushing your PNGs, and keeping an eye on the aggregate size of web pages. Even if it loads fast for YOU, someone might be on a slow DSL line (or, gasp, DIALUP) or a mobile connection.
veeti 16y ago
> Rails does not (cannot) add indexes to your database. Am I missing something in this sentence? You can add indexes in migrations.
- rbranson 16y ago
  You are right. That's a pretty glaring hole.
- ascendant 16y ago
  Yeah, that kind of blew me away too. I posted a comment on the article about it. Even if Rails didn't support it natively you can always execute raw SQL statements in your migrations and just use your DB-specific syntax there to do it.
- prateekdayal 16y ago
  Sorry. I meant rails can't do it automatically for you. You will have to figure out based on your queries and add the indexes yourself. Once you have figured out what indexes to add, you can use add_index in your migration file.
  - mickeyben 16y ago
    Choosing indexes is a world of compromise ; Indexes slow down operations that involve writing. When you have an index that is rarely or never used you'll slow down writing operations on the table for nothing. So you'll have to carefully chose indexes and there's no way any framework could do this for you ... It completely depends of your application and your indexes can change over time.
  - deleted 16y ago
    [deleted]
- sant0sk1 16y ago
  I think he intended to say that Rails does not (cannot) automatically add indexes to your database, which is true and makes more sense in the context.
  - rudiger 16y ago
    Are there any frameworks that do this?
    - rahoulb 16y ago
      If there are they shouldn't. As mickeyben says above, choosing indexes is a compromise - you sacrifice performance in one area for performance in another. Only you can make the right decision there, because only you can begin to know how the app will be used in production.
    - gcampbell 16y ago
      DataMapper's dm-constraints plugin (https://github.com/datamapper/dm-constraints https://github.com/datamapper/dm-constraints) can automatically add foreign key constraints to your associations, which has the side effect (in MySQL at least) of creating indices on the foreign key columns if they don't already exist.
- patio11 16y ago
  I think the unspoken implication is "for you.". Many newbie Rails devs assume that a line like "has_many :friends" is magic and makes user.friends execute quickly. They won't understand this explanation, but that can plausibly cause a full table scan, or worse, N+2 full table scans if you have something like: photos_i_am_in = user.friends.map {|f| f.photos.select {|p| p.include? user}}.flatten (Code somewhat minimalist - don't think too hard about it.)
  - carbon8 16y ago
    The #map and #select approach in your example is even worse since it loads all of the associated friend and photo records into memory and tries to use Ruby for queries that should be performed by the database. If there are more than a handful of those records, that process will balloon up and could bring the server to a halt.
patio11 16y ago
The single best advice I can give for scaling Rails, and it is widely applicable to other frameworks: never block an HTTP request/response cycle on I/O. In Rails, for example, if you have an external API call which routinely takes 3 seconds to complete, then that call costs about three hundred megabyte-seconds of RAM time to service, because the 100 MB or so used by your mongrel is effectively worthless while you wait. When you run into resource constraints in Rails, odds are you are running out of RAM first. It's a memory munching beast in most common deployment scenarios. It is much more efficient to offload those blocking requests to a worker process and poll for results, since a single mongrel can blow through lots of poll requests and useful work in five seconds. Bonus points: your web app will feel snappier, because progressive rendering tricks users into thinking 5 seconds is not actually 5 seconds.
- prateekdayal 16y ago
  Yes .. thats a great advice. We use beanstalkd for asynchronous tasks (like sending emails or updating twitter) and backgroundrb for jobs that need polling (re-encoding a mp3 file and showing a progress bar to the user)
- rudiger 16y ago
  The single best advice I can give for scaling Rails, and it is applicable to nearly every other framework: give it more memory. Generally, when you run into a bottleneck in Rails, you're running out of RAM first. You can get a couple of 8GB sticks for around $500; just over-provision your server and worry about the problem later.
  - stuff4ben 16y ago
    Sorry, but that's terrible advice, especially for bootstrapped businesses. Throwing resources at a problem will only scale as large as your wallet is. It's both wasteful and lazy.
    - patio11 16y ago
      Throwing money at problems is sometimes the right answer, and I say that as someone who is so bootstrapped I can occasionally taste the shoe leather. Heck, I spend ~$500 a month on hosting when I could probably do it in half that on renting a dedicated server (or just buy a big beefy box and pay a pittance for a spot on a rack somewhere). I don't because it isn't worth my time to even think about migrating. My favorite anecdote: asking tptacek to help me me squeeze a few megs out of Redis to avoid having to bump my VPS up a tier. That would have been, heck if I know, $BOATLOADS / hr in engineer costs to avoid $30 per month on my Slicehost bill. He restored me to sanity fairly quickly.
mickeyben 16y ago
Here's few other ways to optimize your rails application (with the description taken from the doc) : - https://github.com/flyerhzm/bullet/ https://github.com/flyerhzm/bullet/ : The Bullet plugin/gem is designed to help you increase your application’s performance by reducing the number of queries it makes. It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you’re using eager loading that isn’t necessary and when you should use counter cache. - https://github.com/sdsykes/slim_scrooge/ https://github.com/sdsykes/slim_scrooge/ : SlimScrooge implements inline query optimisation, automatically restricting the columns fetched based on what was used during previous passes through the same part of your code. - http://guides.rubyonrails.org/caching_with_rails.html http://guides.rubyonrails.org/caching_with_rails.html : Caching With Rails from the awesome rails guides - http://guides.rubyonrails.org/performance_testing.html http://guides.rubyonrails.org/performance_testing.html : Performance Testing Rails Applications from the rails guides
- robflynn 16y ago
  Bullet is nice, but I do occasionally have an experience where it will yell at me about unused eager loading associations when they're not actually unused. I suppose I could dig into the bullet code a bit, but I've been too busy. Has this come up for you before?
  - mickeyben 16y ago
    I had the same error from time to time, but it seems resolved : https://github.com/flyerhzm/bullet/issues/closed#issue/20 https://github.com/flyerhzm/bullet/issues/closed#issue/20 You should open a ticket, eventually write a spec to reproduce.
    - robflynn 16y ago
      I'll do another code review and make certain that I'm not crazy and just missing something, then I'll send a spec his way.
flexd 16y ago
We did similar things on our server (though with much much less traffic but all dynamic content + API). We moved from passenger to nginx+unicorn and the performance increase was awesome + it uses less ram (which again means more ram for more app servers!). Our box was a old quad core with 4gb ram so it got a bit tight with everything on one machine. The DB blocking problem could easily be solved with a cheap VPS as a slave DB, then you can do backups off that. :-) Depending on your kind of website, caching with varnish is great, we also made great use of memcached for fragments and other little things. And since our main cpu consuming background task just did one thing in the end it was rewritten in C which also helped a lot. Offloading all files to Amazon S3 is also a very good way of reducing load if you have a lot of them.
- FooBarWidget 16y ago
  Were you on passenger for apache or passenger for nginx, and were you on passenger 3? Those make quite a difference. Memory usage and performance have been greatly improved in 3, with important parts optimized in C. The dynamic multiprocessing can save you more memory during idle time.
  - flexd 16y ago
    We were using passenger with apache if i remember correctly. It's a few years ago and i was more on the developer side rather than sysadmin side of things back then. Its probably worth taking another look? For my ruby applications at the moment i use nginx+unicorn, which works really great.
deleted 16y ago
[deleted]
cparedes 16y ago
I had an article published about MySQL tuning a while ago: http://blog.scoutapp.com/articles/2011/02/10/understanding-disk-i-o-when-should-you-be-worried http://blog.scoutapp.com/articles/2011/02/10/understanding-d... Always tune for your workload. Be sure to index your damn tables. And yes, measure.
trustfundbaby 16y ago
Not to be obnoxious but I wasn't wasn't very impressed by this post ... use explain plans to add indexes to your tables? use passenger instead of mongrel instances? don't use rand() because it does a table scan? these are things a developer should know and be doing already. There are also mistakes in there like saying you cannot add indexes to your tables using rails (can do this using migrations).
- cannikin 16y ago
  Agreed, I had the feeling reading this article that these devs just discovered the idea of indexes on a database. This should be common knowledge to a developer.
jacques_chester 16y ago
I've faffed around with optimising LAMP for years in order to get Wordpress to perk up. Aside from WP-supercache, the biggest difference I ever made was moving MySQL onto a second server. It halved page generation times and the whole site is much, much snappier.
jacques_chester 16y ago
As an aside, beware using idiomatic ruby while writing ActiveRecord queries. Model.all.inject { sum or average code } will destroy you once you get into production. Working at an otherwise excellent Rails shop I had to launch a mini-jihad to drive this stuff out. (NB my code is wrong because I haven't written Ruby for a while).

80 points by prateekdayal 16y ago

Even if you aren't "on a budget," it's always a good idea to spend some time benchmarking and tuning your setup. You never know if you'll receive a spike of traffic or what the limits of your setup are. Most of the time, between the OS, Web Server, Application Stack, and Database, there are a few knobs that can be adjusted with minimal effort that will yield big gains in headroom. The author's suggestion of Munin is a great first step is a nice way to keep from getting caught with your pants down, and can usually be installed and configured in a few minutes. This is sort of the systems side of always making sure HTTP compression is working, minifying your JavaScript, crushing your PNGs, and keeping an eye on the aggregate size of web pages. Even if it loads fast for YOU, someone might be on a slow DSL line (or, gasp, DIALUP) or a mobile connection.

veeti 16y ago

> Rails does not (cannot) add indexes to your database. Am I missing something in this sentence? You can add indexes in migrations.

rbranson 16y ago
You are right. That's a pretty glaring hole.
ascendant 16y ago
Yeah, that kind of blew me away too. I posted a comment on the article about it. Even if Rails didn't support it natively you can always execute raw SQL statements in your migrations and just use your DB-specific syntax there to do it.
prateekdayal 16y ago
Sorry. I meant rails can't do it automatically for you. You will have to figure out based on your queries and add the indexes yourself. Once you have figured out what indexes to add, you can use add_index in your migration file.
- mickeyben 16y ago
  Choosing indexes is a world of compromise ; Indexes slow down operations that involve writing. When you have an index that is rarely or never used you'll slow down writing operations on the table for nothing. So you'll have to carefully chose indexes and there's no way any framework could do this for you ... It completely depends of your application and your indexes can change over time.
- deleted 16y ago
  [deleted]
sant0sk1 16y ago
I think he intended to say that Rails does not (cannot) automatically add indexes to your database, which is true and makes more sense in the context.
- rudiger 16y ago
  Are there any frameworks that do this?
  - rahoulb 16y ago
    If there are they shouldn't. As mickeyben says above, choosing indexes is a compromise - you sacrifice performance in one area for performance in another. Only you can make the right decision there, because only you can begin to know how the app will be used in production.
  - gcampbell 16y ago
    DataMapper's dm-constraints plugin (https://github.com/datamapper/dm-constraints https://github.com/datamapper/dm-constraints) can automatically add foreign key constraints to your associations, which has the side effect (in MySQL at least) of creating indices on the foreign key columns if they don't already exist.
patio11 16y ago
I think the unspoken implication is "for you.". Many newbie Rails devs assume that a line like "has_many :friends" is magic and makes user.friends execute quickly. They won't understand this explanation, but that can plausibly cause a full table scan, or worse, N+2 full table scans if you have something like: photos_i_am_in = user.friends.map {|f| f.photos.select {|p| p.include? user}}.flatten (Code somewhat minimalist - don't think too hard about it.)
- carbon8 16y ago
  The #map and #select approach in your example is even worse since it loads all of the associated friend and photo records into memory and tries to use Ruby for queries that should be performed by the database. If there are more than a handful of those records, that process will balloon up and could bring the server to a halt.

patio11 16y ago

The single best advice I can give for scaling Rails, and it is widely applicable to other frameworks: never block an HTTP request/response cycle on I/O. In Rails, for example, if you have an external API call which routinely takes 3 seconds to complete, then that call costs about three hundred megabyte-seconds of RAM time to service, because the 100 MB or so used by your mongrel is effectively worthless while you wait. When you run into resource constraints in Rails, odds are you are running out of RAM first. It's a memory munching beast in most common deployment scenarios. It is much more efficient to offload those blocking requests to a worker process and poll for results, since a single mongrel can blow through lots of poll requests and useful work in five seconds. Bonus points: your web app will feel snappier, because progressive rendering tricks users into thinking 5 seconds is not actually 5 seconds.

prateekdayal 16y ago
Yes .. thats a great advice. We use beanstalkd for asynchronous tasks (like sending emails or updating twitter) and backgroundrb for jobs that need polling (re-encoding a mp3 file and showing a progress bar to the user)
rudiger 16y ago
The single best advice I can give for scaling Rails, and it is applicable to nearly every other framework: give it more memory. Generally, when you run into a bottleneck in Rails, you're running out of RAM first. You can get a couple of 8GB sticks for around $500; just over-provision your server and worry about the problem later.
- stuff4ben 16y ago
  Sorry, but that's terrible advice, especially for bootstrapped businesses. Throwing resources at a problem will only scale as large as your wallet is. It's both wasteful and lazy.
  - patio11 16y ago
    Throwing money at problems is sometimes the right answer, and I say that as someone who is so bootstrapped I can occasionally taste the shoe leather. Heck, I spend ~$500 a month on hosting when I could probably do it in half that on renting a dedicated server (or just buy a big beefy box and pay a pittance for a spot on a rack somewhere). I don't because it isn't worth my time to even think about migrating. My favorite anecdote: asking tptacek to help me me squeeze a few megs out of Redis to avoid having to bump my VPS up a tier. That would have been, heck if I know, $BOATLOADS / hr in engineer costs to avoid $30 per month on my Slicehost bill. He restored me to sanity fairly quickly.

mickeyben 16y ago

Here's few other ways to optimize your rails application (with the description taken from the doc) : - https://github.com/flyerhzm/bullet/ https://github.com/flyerhzm/bullet/ : The Bullet plugin/gem is designed to help you increase your application’s performance by reducing the number of queries it makes. It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you’re using eager loading that isn’t necessary and when you should use counter cache. - https://github.com/sdsykes/slim_scrooge/ https://github.com/sdsykes/slim_scrooge/ : SlimScrooge implements inline query optimisation, automatically restricting the columns fetched based on what was used during previous passes through the same part of your code. - http://guides.rubyonrails.org/caching_with_rails.html http://guides.rubyonrails.org/caching_with_rails.html : Caching With Rails from the awesome rails guides - http://guides.rubyonrails.org/performance_testing.html http://guides.rubyonrails.org/performance_testing.html : Performance Testing Rails Applications from the rails guides

robflynn 16y ago
Bullet is nice, but I do occasionally have an experience where it will yell at me about unused eager loading associations when they're not actually unused. I suppose I could dig into the bullet code a bit, but I've been too busy. Has this come up for you before?
- mickeyben 16y ago
  I had the same error from time to time, but it seems resolved : https://github.com/flyerhzm/bullet/issues/closed#issue/20 https://github.com/flyerhzm/bullet/issues/closed#issue/20 You should open a ticket, eventually write a spec to reproduce.
  - robflynn 16y ago
    I'll do another code review and make certain that I'm not crazy and just missing something, then I'll send a spec his way.

flexd 16y ago

We did similar things on our server (though with much much less traffic but all dynamic content + API). We moved from passenger to nginx+unicorn and the performance increase was awesome + it uses less ram (which again means more ram for more app servers!). Our box was a old quad core with 4gb ram so it got a bit tight with everything on one machine. The DB blocking problem could easily be solved with a cheap VPS as a slave DB, then you can do backups off that. :-) Depending on your kind of website, caching with varnish is great, we also made great use of memcached for fragments and other little things. And since our main cpu consuming background task just did one thing in the end it was rewritten in C which also helped a lot. Offloading all files to Amazon S3 is also a very good way of reducing load if you have a lot of them.

FooBarWidget 16y ago
Were you on passenger for apache or passenger for nginx, and were you on passenger 3? Those make quite a difference. Memory usage and performance have been greatly improved in 3, with important parts optimized in C. The dynamic multiprocessing can save you more memory during idle time.
- flexd 16y ago
  We were using passenger with apache if i remember correctly. It's a few years ago and i was more on the developer side rather than sysadmin side of things back then. Its probably worth taking another look? For my ruby applications at the moment i use nginx+unicorn, which works really great.

deleted 16y ago

[deleted]

cparedes 16y ago

I had an article published about MySQL tuning a while ago: http://blog.scoutapp.com/articles/2011/02/10/understanding-disk-i-o-when-should-you-be-worried http://blog.scoutapp.com/articles/2011/02/10/understanding-d... Always tune for your workload. Be sure to index your damn tables. And yes, measure.

trustfundbaby 16y ago

Not to be obnoxious but I wasn't wasn't very impressed by this post ... use explain plans to add indexes to your tables? use passenger instead of mongrel instances? don't use rand() because it does a table scan? these are things a developer should know and be doing already. There are also mistakes in there like saying you cannot add indexes to your tables using rails (can do this using migrations).

cannikin 16y ago
Agreed, I had the feeling reading this article that these devs just discovered the idea of indexes on a database. This should be common knowledge to a developer.

jacques_chester 16y ago

I've faffed around with optimising LAMP for years in order to get Wordpress to perk up. Aside from WP-supercache, the biggest difference I ever made was moving MySQL onto a second server. It halved page generation times and the whole site is much, much snappier.

jacques_chester 16y ago

As an aside, beware using idiomatic ruby while writing ActiveRecord queries. Model.all.inject { sum or average code } will destroy you once you get into production. Working at an otherwise excellent Rails shop I had to launch a mini-jihad to drive this stuff out. (NB my code is wrong because I haven't written Ruby for a while).