Optimize Mysql On Low Memory Servers

by Luigi Nori Date: 25-08-2020 mysql database


Cloud computing makes it very affordable to get your own private virtual server on the Internet. Digital Ocean offers an entry level droplet for USD$5 per month, and Amazon.com has a micro instance tier on the EC2 platform that is free for the first year. These instances are rather useful if you want to test out some new technologies (multi source replication?) or put together a proof of concept.

However, these servers comes with a very low amount of RAM, usually between half GB to one GB. It is common to see production grade database servers with literally 100x that amount. 100MB of memory usage makes no significant difference to one of these servers, but definitely noticible to a 512MB virtual machine.

I recently encountered an issue where MySQL would not start up. The interesting part was the lack of any errors from MySQL:

140521 08:26:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140521 08:26:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Yet if we check our kernel messages, we clearly see an out of memory event:

May 21 08:26:41 aes2 kernel: Out of memory: Kill process 24774 (mysqld) score 842 or sacrifice child
May 21 08:26:41 aes2 kernel: Killed process 24774, UID 0, (mysqld) total-vm:549180kB, anon-rss:437324kB, file-rss:44kB

Or in other cases, we see errors about InnoDB not being able to allocate buffer pool:

2014-05-21 08:33:23 25042 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2014-05-21 08:33:23 25042 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

But we do have RAM available:

[[email protected] ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           490         86        403          0          7         32
-/+ buffers/cache:         46        443
Swap:            0          0          0
[[email protected] ~]# 

Well, the issue here is Performance Schema, and not making it obvious.

When starting up, it allocates all the RAM it needs. By default, it will use around 400MB of RAM, which isn't noticible with a database server with 64GB of RAM, but it is quite significant for a small virtual machine. If you add in the default InnoDB buffer pool setting of 128MB, you're well over your 512MB RAM allotment and that doesn't include anything from the operating system.

We can easily disable Performance Schema by putting this under the [mysqld] section configuration:

performance_schema = off

It is possible to customize which monitors are used in Performance Schema to reduce the memory footprint. But with limited RAM, it will probably be much better to use all of that for other things, like the buffer pool.

There is a MySQL bug report that provides a pretty decent description about Performance Schema: http://bugs.mysql.com/bug.php?id=68514

And there is a feature request to print out the amount of memory used for Performance Schema, that would have made this whole problem more visable: http://bugs.mysql.com/bug.php?id=69665

 
by Luigi Nori Date: 25-08-2020 mysql database hits : 6160  
 
 
 
 

Related Posts

MySQL 8.0 is now fully supported in PHP 7.4

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…

MYSQL: How to create a new user and give it full access only to 1 database

To create a new user in MySQL and give it full access only to 1 database, say dbTest, these are the MySQL commands to do that To create the user: CREATE USER…

How JSON data types work in mysql

First introduced in MySQL 5.7.8, the JSON data type is a specialized binary column type, similar to a blob , but with added machinery to optimize the storage and retrieval…

How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04

If your app was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7, you can disable strict SQL mode. For example, apps…

MySql: How to build a performant search engine

In content-heavy websites, it becomes increasingly important to provide capable search possibilities to help your users find exactly what they’re looking for. The most obvious solution is searching your MySQL…

Install HHVM + Nginx on Ubuntu 14.04

  The LEMHH software stack is a group of software that can be used to serve dynamic web pages and web applications. This is an acronym that describes a Linux operating…

PHP: Storing arrays in database

When working with databases, sometimes it is necessary to store an array in a MySQL field. Unfortunately, there is no way to directly pass in an array as a parameter.…

PHP Recursive Backup of MySql Database

This script can be used to make backup of your MySql database, you can use the script in conjunction with cronjobs $user = 'myuser'; $passwd = 'mypass'; $host = 'myhost'; $db = 'mydb'; // Delete…

Million of visitors per day with a super cheap php mysql server using nginx and varnish

These instructions are the rather verbose, but hopefully easy enough to follow, steps to build a new Linux server using Varnish and Nginx to build a php application on a…

Memcache: Installation and Usage on Ubuntu 12.10

About Memcache Memcache is a system that works to speed up virtual private servers by caching server information. The program allows you to allocate a specific amount of the server ram…

Mysql:Guide To The MySql Query Cache

MySQL “Query Cache” is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache is -…

MySql: Setup Master Slave Replication in Ubuntu

About MySQL replication MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave…

We use our own and third-party cookies to improve our services, compile statistical information and analyze your browsing habits. This allows us to personalize the content we offer and to show you advertisements related to your preferences. By clicking "Accept all" you agree to the storage of cookies on your device to improve website navigation, analyse traffic and assist our marketing activities. You can also select "System Cookies Only" to accept only the cookies required for the website to function, or you can select the cookies you wish to activate by clicking on "settings".

Accept All Only sistem cookies Configuration