MySQL Database Memory Optimization

Don’t let your database be a memory hog - optimize! In this short real-life story we’ll show you how we approached a MySQL Database optimization issue as well as provide some general tips so you can more easily tackle these problems yourself!

So you receive a message like this:

“Hello, Our Site Is Down!
Database takes up too much memory. Our hosting says that the database must be optimized!”

Or similar… perhaps a bit less neutral… Or maybe it’s your store so you just message yourself.

And you wonder — what do I do now?

Big MySQL Databases – Big MySQL Problems

After a brief inspection it seems that MySQL process consumes about 1.5 GB of memory and it is a big database.

Big databases (usually) mean big queries, that (usually) mean big memory consumption, and it is Magento, and not all system memory is used.. so my question was “So what?” — give the server more memory if you want to host a large catalog!

However, server did run out of memory. And when I was looking, it did have some memory to work with.. so what suddenly ate all memory? According to logs, it was MySQL.. but how?

So how much memory can MySQL use? There is an online service http://www.mysqlcalculator.com/ and there are scripts that you can run on the server, and the results were outstanding: in theory MySQL could use 52 GB of memory!

The MySQL calculator uses following formula:

Total Memory = (A+…+An) + N x (B+…+Bn), where

• A…An are some buffer and cache variables expressed in memory units
• N is max alllowed connections
• B..Bn are again variables expressed in memory units, but this time they are multiplied with N!

For complete info on each variable, check the calculator yourself and read MySQL documentation — no need to cover it here.

After playing a bit with the calculator I was able to find MySQL settings that:

• did not threaten to kill the server again
• kept database running

Tips for MySQL Optimisation

1) In case of MySQL eating all your memory, see if there are exotic settings (edited my.cnf? SHOW VARIABLES return anything interesting?)

2) Such exotic settings can simply be there with no particular reason. Try testing if database works with default settings (not on live!) — most likely it will just work.

3) Try not to copy random advice from Internet, and if you do, make sure to test and document them.

Have any additional questions or comments? Need our support superheroes for your Magento eCommerce store? You can light up the Bat-signal through our contact form here or by leaving a direct message to us via [email protected].

Related articles:

• Make Your Magento Technical Support Team Great and Your Customers Pleased!
• A blueprint for Scandiweb Support

Share on: