Reduce MySQL memory usage

As I am running more and more on my 256mb slice, I’m trying to squeeze more performance out of the system. After a bit of digging, I’ve made a few changes.

First thing I did was switch out the default mysql config with one tweaked for smaller boxes. This configuration actually came with the mysql installation under /usr/share/doc/mysql-server-5.0/examples/. Just backup your existing my.cnf (mine is located under /etc/mysql) and use the following:

# The following options will be passed to all MySQL clients
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking
server-id	= 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

You can see estimated memory usage with a config if you just copy paste the configuration file here.

Restart MySQL (/etc/init.d/mysql restart).

The second thing I did was check out MySQLTuner. Just download the perl script and run it. It will run some analysis on your mysql setup and provide some performance and configuration tweak recommendations.

Sample output looks like this:

 >>  MySQLTuner 1.0.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.4
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 867K (Tables: 18)
[--] Data in InnoDB tables: 704K (Tables: 40)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 57m 0s (17K q [0.316 qps], 699 conn, TX: 55M, RX: 3M)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 26.0M global + 824.0K per thread (100 max threads)
[OK] Maximum possible memory usage: 106.5M (41% of installed RAM)
[OK] Slow queries: 0% (0/17K)
[OK] Highest usage of available connections: 6% (6/100)
[!!] Key buffer size / total MyISAM indexes: 16.0K/381.0K
[!!] Key buffer hit rate: 88.5% (154K cached / 17K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Temporary tables created on disk: 30% (1K on disk / 4K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (4 open / 7K opened)
[OK] Open file limit used: 0% (8/1K)
[OK] Table locks acquired immediately: 99% (17K immediate / 17K locks)
[OK] InnoDB data size / buffer pool: 704.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 381.0K)
    query_cache_size (>= 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 4)

I haven’t made any of the recommended changes yet though. I am going to try to see how this default small config performs.

I’ll try to follow up with any of my findings.

Reduce MySQL memory usage

Rails initial request slow with mod_rails

Following up on my previous post , I’ve been experience slow load times (10-15 secs) on the initial request after application restarts. This has to do with the way mod_rails manages application instances (Although, I experienced this when using mongrel_cluster and proxy balancers). It will spin up instances on page request and each instance has an idle timeout. This just means after the timeout expires, mod_rails will shutdown that instance to conserve memory allocation. While you can change timeout value (see PassengerPoolIdleTime), this will only cause all instances that get spin up to live longer. After high load times, these instances will stick around longer than neccessary.

For low traffic sites (like mine), this idle timeout may be reached causing the next visitor to our website to experience a really long delay before page load. What we really want is an option to set a minimum number of instances. This would allow us to automatically spin up an instance during start up and keep it around. Unfortunately at this time, it doesn’t look like there is a way to set this.

As a workaround, I’ve setup a crontab that makes a request to my application every 5 minutes to prevent mod_rails from killing off all application instances.

To do this just run:

crontab -e

And then specific the following cron

*/5 * * * * wget -O /dev/null http://www.myapp.com 2>/dev/null

You can verify this is working correctly but just tailing your application logs and verify every 5 minutes you get a request.

You can also run

 passenger-status

You should see at least the count variable to be at least 1 instance.

Note, this workaround will not immediate start up an instance upon restart. You can add an initial request as part of your post deploy capistrano task though. You probably should be making sure your application is up after deploying or restarting the application anyways.

Rails initial request slow with mod_rails

Setting up Phusion Passenger (mod_rails) with Capistrano support

I had heard of mod_rails awhile back but never had the time to take a closer look at it. While setting up a new rails app, I was getting frustrated with all of the configuration I needed to do to get the mongrel clusters and proxy balancers setup. So I decided to give passenger a chance. I’m a fan now 🙂

The process was dead simple.

  1. Install the passenger gem
    sudo gem install passenger
  2. Install passenger as an Apache module
    passenger-install-apache2-module
  3. Load the passenger apache module by editing the Apache config
    LoadModule passenger_module /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5/ext/apache2/mod_passenger.so
    PassengerRoot /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5
    PassengerRuby /usr/bin/ruby1.8
    
  4. Restart Apache

If all things went well, you have everything installed you need. If there were some missing dependencies, you should be presented with how to install those dependencies.

In the installation output, it tells you how to mod_railsify your apps by creating a vhost as such:

<VirtualHost *:80>
    ServerName www.mywebsite.com
    DocumentRoot /home/deploy/mywebsite/public
</VirtualHost>

That’s it! No more of this proxy balancer and mongrel_cluster.yml configuration.

There’s some magic going on in the background. As requests come in, passenger will spin up more application instances. For more tweaking your configuration options check out the user guide.

Go to your website and you should see your rails app up and running.

So now we have your app up and running, how do we update or restart our app? Passenger provides two ways for us to do this.

The first is whenever apache is restarted, your application is restarted.

The second way allows us to restart a specified application without affecting Apache. Whenever passenger detects tmp/restart.txt, it will restart the application instances for us. We can integrate this into our Capistrano deploy flow by adding the following our config/deploy.rb

namespace :passenger do
  desc "Restart Application"
  task :restart do
    run "touch #{current_path}/tmp/restart.txt"
  end
end

after :deploy, "passenger:restart"

This will create that restart.txt after the cap:deploy task gets executed, causing the application to restart.

Finally, passenger comes with some pretty useful utilities.

Check out passenger-status which produces output showing current passenger server statuses.

Sample output:

----------- General information -----------
max      = 6
count    = 1
active   = 0
inactive = 1
Using global queue: no
Waiting on global queue: 0

----------- Applications -----------
/home/deploy/www.myapp.com/releases/20081206183156: 
  PID: 30784     Sessions: 0

Another utility passenger-memory-status gives you insight into how much memory is being used by apache and passenger.

Sample output:

-------------- Apache processes ---------------
PID    PPID   Threads  VMSize    Private  Name
-----------------------------------------------
12841  1      1        225.9 MB  0.0 MB   /usr/sbin/apache2 -k start
28294  12841  1        248.4 MB  21.4 MB  /usr/sbin/apache2 -k start
28300  12841  1        243.7 MB  0.5 MB   /usr/sbin/apache2 -k start
28306  12841  1        248.4 MB  4.4 MB   /usr/sbin/apache2 -k start
28357  12841  1        249.1 MB  19.8 MB  /usr/sbin/apache2 -k start
29400  12841  1        249.4 MB  3.7 MB   /usr/sbin/apache2 -k start
29788  12841  1        249.3 MB  21.7 MB  /usr/sbin/apache2 -k start
29834  12841  1        245.8 MB  18.9 MB  /usr/sbin/apache2 -k start
29836  12841  1        245.8 MB  9.3 MB   /usr/sbin/apache2 -k start
29868  12841  1        245.8 MB  2.4 MB   /usr/sbin/apache2 -k start
29870  12841  1        246.5 MB  5.2 MB   /usr/sbin/apache2 -k start
### Processes: 11
### Total private dirty RSS: 107.44 MB

--------- Passenger processes ----------
PID    Threads  VMSize    Private  Name
----------------------------------------
28031  10       15.3 MB   0.1 MB   /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5/ext/apache2/ApplicationPoolServerExecutable 0 /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5/bin/passenger-spawn-server  /usr/bin/ruby1.8  /tmp/passenger_status.12841.fifo
28032  2        48.7 MB   0.6 MB   Passenger spawn server
29161  1        114.8 MB  0.7 MB   Passenger FrameworkSpawner: 2.1.2
30461  1        122.8 MB  32.3 MB  Passenger ApplicationSpawner: /home/deploy/www.myapp.com/releases/20081206183156
30784  1        129.3 MB  33.4 MB  Rails: /home/deploy/www.myapp.com/releases/20081206183156
### Processes: 5
### Total private dirty RSS: 67.08 MB

Pretty sweet.

Setting up Phusion Passenger (mod_rails) with Capistrano support