Scour the ‘net for how to reduce your MySQL server footprint, and you won’t find much. Everyone seems to want to beef up its settings, and I’ve been one of them. When you have a server cluster taking thousands of requests per second, the battle cry is often for more RAM to aid in caching data. But for smaller sites, like a neighborhood hiking club, you will have a very different server setup.
Smaller sites will be run on smaller servers with smaller resources. With a server that’s only got 4GB of RAM, every megabyte matters.
Like any good household budget trimming exercise, the first thing you want to look for is caching or buffers that may be too large. If you’re running the MySQL Innodb engine, the default cache is 128MB of RAM. While that doesn’t seem like much, if you’re data set size is 32MB, that’s a lot of wasted RAM that can’t be used for anything else. 32MB of data will never fill a 128MB cache.
While you’ll need to figure out how which you’ll need to allocate, find your config file (usually in /etc/mysql/my.cf) and make sure you have a line like this:
innodb_buffer_pool_size=32M # Overall size of pool. The default is 128MB
innodb_buffer_pool_chunk_size = 8M # Default is 128MB
Then restart MySQL and see how your system performs. Still fast enough? If you’ve removed unneeded cache RAM you shouldn’t see any difference in the speed, and on small sites, caching may not be needed at all.
Another thing to look for is excessive disk usage. If you’re not running MySQL in a cluster, and in a smaller site I doubt you are, you can also remove the files used for the Binary Log, which is used to replicate MySQL to different servers, such as a redundant MySQL server. To do this, and something like this into your /etc/mysql/my.cf file. This should save you a lot of disk space as these log files can get huge over time.
binlog_expire_logs_seconds = 68400 # 1 day in seconds. Choose a value that fits your usage.
Can you think of any other tips for a smaller site? Just let me know in the comments.