High CPU - 300% Plus, Rails, MySQL

If you are getting a correspondingly high amount of traffic, it might be time to consider scaling out with multiple application and/or database servers. However, most commonly, this is due to either slow SQL queries or a lack of adequate caching.

For database performance tweaking, give mysqltuner.pl a try. It evaluates your current settings and performance, and makes recommendations of settings to adjust. I would recommend reading the MySQL manual for each setting before adjusting it, both to make sure there won't be unintended consequences and to learn what the setting means and how it impacts the larger system. Its report will look something like this:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 219K (Tables: 4)
[--] Data in InnoDB tables: 310M (Tables: 41)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 40d 22h 2m 26s (43M q [12.270 qps], 89K conn, TX: 40B, RX: 7B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 604.0M global + 5.5M per thread (130 max threads)
[OK] Maximum possible memory usage: 1.3G (85% of installed RAM)
[OK] Slow queries: 0% (1K/43M)
[OK] Highest usage of available connections: 61% (80/130)
[OK] Key buffer size / total MyISAM indexes: 64.0M/79.0K
[OK] Key buffer hit rate: 99.4% (153K cached / 919 reads)
[OK] Query cache efficiency: 47.3% (15M cached / 32M selects)
[!!] Query cache prunes per day: 42085
[OK] Sorts requiring temporary tables: 0% (93 temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 0% (6K on disk / 1M total)
[OK] Thread cache hit rate: 96% (3K created / 89K connections)
[!!] Table cache hit rate: 0% (53 open / 7K opened)
[OK] Open file limit used: 1% (11/1K)
[OK] Table locks acquired immediately: 99% (23M immediate / 23M locks)
[OK] InnoDB data size / buffer pool: 310.9M/330.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Increasing the query_cache size over 128M may reduce performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 192M) [see warning above]
    table_cache (> 256)

I would recommend running mysqltuner.pl, think about/research its recommendations, change no more than one (or two, if you absolutely must) setting, restart mysqld, then wait for at least 24 or 48 hours before running mysqltuner.pl again. It is a slow, iterative process, because tuning MySQL involves a lot of settings that interact with each other in strange ways.

There will be a limit to how much you can tweak it before you reach a stalemate with mysqltuner.pl. I've hit that point in the above example and can't safely implement its recommendations without throwing more RAM at the server. Life's a bitch.

Outside of your database system, caching is the most important thing. While your web site is, of course, dynamic, it isn't completely dynamic. There will be common page elements and query results that can be stored in memory for a short while. I'm not familiar enough with Rails to know what works well, but in the Django world, memcached is quick and easy to use:

CACHES = {
    'default': {
        'BACKEND': 'django.core.cache.backends.memcached.MemcachedCache',
        'LOCATION': [
            'y.y.y.103:11211',
            'y.y.y.104:11211',  ...
        ]
    }
}

You can either run it locally, or (as in my example above) on one or more separate machines devoted to the task. The former approach is probably how you're going to use it initially, but the latter is nice when you start scaling out.

If you're getting a mammoth amount of traffic and you've approached the limits of tuning, it's probably time to think about more instances. Separating application from database lets you scale both of those independently; you can then add more application servers sharing the same database, and/or build database slave servers to spread read query load around. This gets into a whole heap of fun, but done right, it's the way to fly.

well, I have been using the mysqltuner based on your recommendations a couple weeks ago…it has really improved the swap and IO problems we were having…I think what is going to happen is the we are going to make the move to multiple smaller servers…

I have absolutely no idea how we are gonna do that though… :cry: wanna help?

http://library.linode.com/linux-ha/

Perhaps just start simpler with this:

http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server

Ha! I thought this problem sounded familiar…

Yeah, standalone MySQL server is the first, glorious step. Once you decouple the data storage from the application, "the rest" just kinda happens.

I would be delighted to help, but alas, I am actually in the middle of building a system much like this(*) for a client, and finals week is rapidly approaching. But you can always feel free to e-mail me at rtucker@gmail.com with any questions or whatnot. -rt

(*) We're using chef for bootstrapping and configuration management, which is… well, it's about 2x or 3x as much work as just setting up a server by hand, except you only have to do it once. Actually, if you're a Rubyist, take a look at it… it probably makes a lot more sense to you than it did me :-)

When we do a standalone MySQL server should the Linode be the same size as the Linode where the application is hosted?

Not necessarily. Linodes can scale easily, so you might want to start small and scale up if it's not enough.