Improving the performance of a website for a VPS: MySQL

I’m running this website, and others, on a VPS hosted at VPSLink. For those who don’t know, a VPS (Virtual Private Server) is a “fake” server that is partitioned off of a real server. For example, say you have this big beefy box with 16GB of RAM, over 1 Terabyte of disk space, Dual Processors with Dual Cores. Sounds pretty nice. Well you could if you wanted to, segment the server into small servers by creating these smaller servers virtually. Through the use of software like OpenVZ or XEN you can do exactly this. You can take a beefy server and make multiple servers out of it. It’s different from shared hosting where you share resources with other sites on a server. With a VPS, you get a dedicated portion of the resources, or a slice of the box. It works out great if you want to have a server with it’s own resources to get around the issues of shared hosting, but not spend the full cost of full server like with a dedicated server. So that’s a VPS, and back to my problem.

I’ve setup the websites on this box to use wordpress, mysql, php, and apache. When I had just one site, I noticed that my memory usage seemed a bit high. It’s a combination of the default settings for mysql, and the memory requirements for wordpress. Fixing wordpress would take some time and that’s really for another posting, so instead, I decided to attack mysql, since that was the bigger culprit of my memory woes.

I have 256MB of Ram dedicated to my VPS, and with the one site, I was using about 170MB. That seemed excessive. I did some digging online for some information about improving the memory usage for MySQL. Here is what I found.

  • If you are not using the berkeley database engine, disable it in your configuration file for mysql. You can do this by adding the entry “skip-bdb” under the [mysqld] section. This will tell mysql to not support this database, and it will not load what it would normally need for it.
  • If you are not using the inno database engine, disable it in your configuration file for mysql. You can do this by adding the entry “skip-innodb” under the [mysqld] section. This will tell mysql to not support this database, and it will not load what it would normally need for it.
  • Another thing I did was add the entry “skip-networking”. This tells mysql to not open itself up to outside traffic. If you’re running a database soley for the server it’s sitting on, then you don’t need the networking support.
  • Below are some other entries I’ve added to optimize the performance for a small environment. This will lower your query size, there by taking up less memory:
    • query_cache_limit = 256K
    • query_cache_size = 4M
    • key_buffer = 1M

When I find the original link I will post it. I was able to drop my memory usage on the box from 170MB to 45MB. When I added two more websites, it went up to 85MB. So far so good.