I have a small VPS server which hosts a couple of low traffic websites. I've been battling it for a couple of years, trying to reduce the memory consumption. The VPS has 0.5Gb RAM, and the same sized swap file. Sure, I could upgrade the VPS, but sometimes this geeky battling stuff is half the fun!
I've been using the two obvious Mysql tuning scripts and altering the configuration gradually over the last year or so. If you've ever looked at any of this yourselves you'll know them: mysqltuner.pl and tuning-primer.sh
So the problem was this. I'd start up Mysql and it would eat up all the memory, start swapping, and eventually invoke oom_killer. The first change I made was to disable innodb. I didn't need it on this server, and immediately memory usage of MySQL dropped to about half. Now it would start up fine, and run for a week or two, but eventually it would crash out. It was using about 300Mb of the available 512Mb RAM, but would eventually use up all the swap as well. I set up monit to watch it and restart it when that happened, but that wasn't a great solution, so I started chipping away at the my.cnf file, to reduce memory footprint.
One of the biggest helps in reducing memory usage I found was reducing the max number of connections. The default is set at 300. Bringing that down to 40 reduced the mysql memory footprint to around 100Mb only, and as this is a low traffic site, my server was still not even approaching 20% of that value, yet I'd saved around 1Mb of memory per connection. Tweaking other parameters brought other benefits, but this is a long process as you have to wait a couple of days before the scripts will report accurate values again. Anyway, with mysql memory usage reported to be a maximum of around 100Mb, I thought I was in the clear. But no. Using htop to look at memory usage, mysql was taking up 300+ still. And when I looked at what was using up the swap memory, it showed mysql was eating up 350kb of that too!
for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | head -n 20
So by now I was getting a bit tired of mysql's lies, so I decided to try something different. I went over to MariaDB's website, plonked my server details into their repo generator, https://downloads.mariadb.org/mariadb/repositories/, backed up, gritted my teeth and switched over to mariadb.
The upgrade went smoothly, all databases were unharmed and the website is humming along smoothly. And now, in htop instead of the solid green bar of used memory and the half red bar of used swap, I see a that the green bar is only half way along (used memory approx 280Mb), and the orange cached memory takes up the rest of it. The red swap bar is almost non-existent.
So that apparently is how you solve mysql's aggressive memory use. You get rid of it. I have one mystery left now. I left my my.cnf file alone during the upgrade, but mysqltuner.pl reports that maria is now using substantially more memory that mysql was meant to (317Mb vs 100Mb), despite the fact its actually using less in reality.
I'll do some more tuning and report back.