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:
max_connections
table_open_cache
table_definition_cache
open_files_limit
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.
