Though it’s typically applied to software, I tend to apply the YAGNI mentality to other things, as well. That is to say that I’d rather wait and upgrade when I need something rather than pay for something that I may never need.
Case in point: A couple of weeks ago, I outgrew my current host and needed to upgrade so I ended up going with Media Temple DV hosting.
In most shared hosting environments, you use whatever configuration they give you; however, because of the nature of the DV environment, the server is completely yours. That means that you’re responsible for tweaking Apache, PHP, and MySQL to make sure that it’s working best for your needs.
So what would a migration to a VPS environment be without a little bit of tweaking?
WordPress on Media Temple DV
Before actually sharing my MySQL configuration (because that was largely the thing I tweaked the most), I thought I’d also share the specifics of my setup just so you’re familiar with how I have things setup:
So there’s nothing too extraordinary about this particular setup. For what it’s worth, I did install W3TC and MaxCDN on the shared environment prior to moving on a VPS.
The Initial Setup
WIth that in place, one of the first things that I noticed as soon as I’d log in to my dashboard is that I’d have a warning that the install was using nearly 100% of my available RAM (which is 1GB).
After doubling-checking with support to make sure that there was nothing out of the ordinary with my migration, I then looked at the following two articles to help provide some initial tuning to my environment:
Though this did yield some improvement, I still wasn’t content with the performance.
Tweaking MySQL Even More
After that, I installed `mysqltuner.pl` – note that you will need SSH access for this – and then followed the recommendations that were shared at the end of the script. Unfortunately, I didn’t grab a screenshot so I can’t show the initial recommendations that the script made.
Anyway, this lead to the following `/etc/my.cnf` configuration file:
[mysqld] # Basic settings user = mysql datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock # Security settings local-infile = 0 symbolic-links = 0 # Memory and cache settings query_cache_type = 1 query_cache_limit = 24M query_cache_size = 2M thread_cache_size = 4 table_cache = 48 tmp_table_size = 128M max_heap_table_size = 128M join_buffer_size = 8M key_buffer_size = 128M max_connections = 4 wait_timeout = 300 # Innodb settings innodb_buffer_pool_size = 128M innodb_additional_mem_pool_size = 3M innodb_log_buffer_size = 3M innodb_thread_concurrency = 8 [mysqld_safe] # Basic safe settings log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid
If you’re comparing the default `my.cnf` configuration file with what you see above, you’ll notice that what’s above is significantly smaller. That’s okay – it simply leaves out a lot of the variables that are either unneeded or that are commented out in the default file.
You can see the dip in the RAM usage below (it’s toward the very end of the graph):
Secondly, if you opt to make changes to your environment, make sure that you do so after backing up the initial copy of `my.cnf`.
Is It Perfect?
I’d love to say yes, but the truth is that it’s not. There are still some spikes in the RAM usage, though it’s not nearly what it was, and I believe that there are further improvements to be made.
Unfortunately, this is one of those areas where I’m not as versed as I’d like to be so I’m constantly making little tweaks to the configuration to try to make it as performant as possible.
There’s a great question and answer for this on Server Fault which I also think is a great resource regardless of what tier of DV you’re using.
Hopefully, the configuration above will help you, but I’m always open to suggestions to make mine – and others – better. Finally, I’ll be updating this post as time goes on with the tweaks that I’ve made to `my.cnf` as I continue to get comfortable in the new environment.