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 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
- 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.