1

I'm running into scaling issues with a Rails app running on top of MySQL. I'm using memcached and have lots of indexes.

I'm also starting to increase values for certain my.cnf settings like tmp_table_size and innodb_buffer_pool_size.

But then that brings me to my question. Should I move the MySQL database to a second, dedicated database server (thus increasing the amount of memory MySQL can use for buffer pools and such) OR should I increase the RAM on the current server, which hosts both Rails and MySQL?

I'm assuming I an increase the RAM available to MySQL much more if I move MySQL to its own server. But I'm worried about the latency of MySQL queries sent over the network between the two servers instead of through mysql.sock.

Any advice?

1
  • What sort of scaling issues? Are you just short on RAM or are you CPU-bound as well?
    – Bittrance
    Jun 25, 2011 at 19:13

1 Answer 1

2

First, understand where your bottleneck is. Is your app mostly reading from SQL? Writing? If you mostly read and your data set no longer fits in memory, adding more RAM will probably give you performance boost. Maybe the data already fits in memory and MySQL hogs the CPU [eg it does full table scans because it cannot make use of the index in some circumstances like subqueries]

Some advice:

  • start with monitoring what's going on with your server using software like Munin.
  • enable slow query logging
  • analyze what queries you send to SQL - eg with mysqsla - maybe after all you do unnecessary full-table scans?
  • use explain for each of most frequent querries

Only after that, decide whether to:

  • get more CPU power [and move MySQL to another machine]
  • buy more RAM
  • optimize your code

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .