Configuring MySQL (5.6) for Low Memory Usage

I found myself frustrated that most guides on lowering memory usage in MySQL say to disable InnoDB.  With an InnoDB exclusive product like Xenforo this was not an option and as it turned out not necessary for low memory usage period.

I am working with Percona 5.6, this should apply to Oracle MySQL 5.6 as well as the same version of MariaDB.  Out of the box with no config MySQL was using 450mb of ram and with a config generated from Percona’s website it went up to 700mb.  But what if I told you it could be using as little as 75mb of ram and still using your favorite settings for MyISAM and InnoDB?

Apparently MySQL 5.6 preemptively allocates a lot of memory if the following 4 settings are over a certain amount:

For example max_connections at 300 had MySQL starting with 150mb of memory but at 310 it allocated 450mb of memory.

However that isn’t the whole story.  This all boils down to Performance Schema. With performance_schema = off in my.cnf memory usage was down to 75mb to start and setting max_connections to 500 for example did not immediately increase memory usage.  Even with my InnoDB buffer pool at 592MB I was not seeing memory usage past 100MB in a load test.  Obviously we want MySQL to use as much memory as we can throw at it when it improves performance.  We also want to minimize unnecessary usage on a low end VPS.

So if you don’t need the data Performance Schema provides disabling it will reduce usage both at idle and load.  But keeping those 4 settings in check can also reduce usage even with it enabled.


Solving phpMyAdmin login issue with PHP-FPM/NGINX

If you are using multiple PHP-FPM configs you might have been unable to login to phpMyAdmin or similar software using cookie based auth.  The problem is with the session.save_path normally set in the PHP.ini.  This is set in the PHP-FPM config. Servers with per site configs containing multiple users or usergroups may be trying to use the same directory.  The problem comes from this directories owner or group not matching that of the site trying to use it.

Check the config for each site. The default is www.conf which depending on your install may be in /etc/php-fpm.d/:

; Set session path to a directory owned by process user
php_value[session.save_handler] = files
php_value[session.save_path] = /var/lib/php/session
php_value[soap.wsdl_cache_dir] = /var/lib/php/wsdlcache

The default group for my install was apache and the save path specified was owned by root:apache.  On a single config site you can chown this directory to the proper owner.  On a multiple config site the best option is to create a new directory per site and chown to that sites php handler user/group.

You may wish to use the test script in this wiki article to find what path is set –

Starting this blog…

I must be feeling all kinds -cidal. I can’t believe I just installed WordPress. I have wanted to write a front end for this site for years now. However who has time for that.

Setting up my Xenforo sales site I came to the decision I needed to blog my dev experiences out. So here we are… Nice.