Fork me on GitHub

Running MySQL Server on a small 128MB VPS

I recently purchased a small OpenVZ VPS (Virtual Private Server) for $15/year. It has 128MB RAM, 256MB vSwap, 20GB of disk space, and 500GB of bandwidth/month. The server is running Ubuntu 12.04 LTS and has very good transfer rates. Initially, I wanted to use it to host all of my Git repos, but then I realized that it was much easier to just use BitBucket. So, I starting thinking about how I could use this tiny server.

I have a number of cron jobs that run every couple minutes, checking settings on remote servers, or grabbing a piece of data from a web page, or checking the status of a database, or something like that. Most of these run for a fraction of a second and store their results in a MySQL database table. These all currently run on my home fileserver and the idea of getting them out of my house where I don't have to worry about residential internet connection was very appealing. However, I didn't really want to install MySQL on the tiny server, having seen the way it eats resources on a much larger server. I could have easily modified everything to use SQLite since most of them use PDO but, honestly, I just didn't want to.

I decided to go for it. I installed the standard mysql-server package and looked at the system stats. As I figured, my memory usage was at 80%, my vSwap was at 50%, and MySQL had very high CPU usage (85-95%). I knew that MySQL has a number of example configuration files, so I dug through the documentation until I found the my-medium.cnf file. At the top it said: This is for a system with little memory (32M - 64M) where MySQL plays an important part, or systems up to 128M where MySQL is used together with other programs (such as a web server). That sounds like just what I'm looking for. After putting the new config in place, I started the MySQL daemon ... and it failed to start.

After looking through the MySQL config file, I found deprecated commands, weird settings, and a handful of issues. It appears that the example hadn't been updated in quite a while. I started tweaking and came up with a very nice solution. The config file I ended up with is:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

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

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

# The MySQL server
[mysqld]
user            = mysql
port	    	= 3306
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

skip-external-locking
key_buffer_size         = 16M
max_allowed_packet      = 1M
table_open_cache        = 64
sort_buffer_size        = 512K
net_buffer_length       = 8K
read_buffer_size        = 256K
read_rnd_buffer_size    = 512K
myisam_sort_buffer_size = 8M

#if no other servers are going to be connecting to this server, uncomment the following line
#bind-address            = 127.0.0.1 

skip-innodb
default-storage-engine = MyISAM

max-connections = 25

query-cache-size  = 2M
query-cache-limit = 1M

thread-stack      = 256K
thread-cache-size = 2M

slow-query-log
long_query_time   = 5

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size  = 20M
sort_buffer_size = 20M
read_buffer      = 2M
write_buffer     = 2M

[mysqlhotcopy]
interactive-timeout

Some Notes:

  • If you're only going to access the MySQL server from the local server, uncomment the bind-address config line.
  • If you're going to allow access to MySQL from the internet, I suggest changing the port to something other than the default 3306
  • The max-connections are set at 25. It could probably go to 50 without too much issue. With that in mind, I definitely wouldn't try to run much of a webpage off this database.

After running this configuration for a couple days, MySQL Tuner comes back with No additional performance recommendations are available.

If you'd like, you can download the configuration below.

my-medium.cnf