LMeve - reduce your memory footprint

hello fellow industrialists.


Just a quick post for those of you who are running a VM for LMeve, with a very limited amount of memory.

As I was toying with a VM running with only 128mb of RAM, I noticed that my MySQL DB, which was empty at that moment, was already using more than 128mb of RAM+swap. I decided to take a look on how to reduce it memory footprint.
That DB will be used only by a few handmade scripts, with a very low bandwidth usage and access, very similar to the LMeve usage for a one man corporation, or a couple of industrialist in a small corp.

After a few tests, I came up with these numbers, which are, in my case, enough. Bear in mind that I'm not a DBA, so, I've a limited understanding in DB bits and bytes. But here's what I found (feel free to correct me if I'm wrong)

• innodb_buffer_pool_size is one of the main parameter to deal with MySQL inner buffers pools in memory. Reducing its size will reduce its RAM usage.
• join_buffer_size and sort_buffer_size are used while standard operations are taking place (select, read, write, etc). Since I've little use of them, I see no point in having a lot of memory devoted to that.
• read_rnd_buffer_size is used in conjunction with sort_buffer_size, so I set them at the same size. 

With those values, I've seen no impact on my low memory VM. I may change them if performance are bad when the DB will have more rows. (if it impacts your LMeve, restore the .orig, and/or, tune those values)

The last parameter is the one that was using an insane amount of memory for my empty and low b/w DB. I needn't it to be fast, I will write/read stuff a few times per hour, as for LMeve. So, I disable those performance schema.
After those modifications, my empty MySQL which was using more than 128mb in memory+swap combined has disappeared from the swap usage.


# cp /etc/my.cnf /etc/my.cnf.orig
# vi /etc/my.cnf
  edit:
innodb_buffer_pool_size = 16M
join_buffer_size = 4M
sort_buffer_size = 128k
read_rnd_buffer_size =128k

# disable Performance Schema (free up memory) - in the [mysqld] section.
performance_schema = off

  Save and exit.

# systemctl restart mysqld
# for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | grep -i mysql
 mysqld_safe 0 kB
mysqld 0 kB
#

An other "optimization", is to reduce buffer to active connections, since I know that I won't have more than two active connections at a given time:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set global max_connections=10;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10    |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> exit
Bye
#