Reducing MySQL memory usage on Ubuntu / Debian Linux

If you are running your services on a low end virtual hosting every byte of memory you can save is important. The memory is often the limiting factor of how many applications you can run on VPS: CPUs are shared, memory not, on the same physical host.

  • Low-end VPS come with 512 MB memory or less
  • Front front-end server Apache / Nginx / Varnish takes > 100 MB +  min. 20 MB for each child process
  • Memecached takes its toll
  • MySQL takes 200 – 400 MB
  • Each Python / PHP process takes at least 15 MB and you need parallel processes for paraller HTTP requests (FCGI, pre-fork, others… )
  • Operating system processes need some memory (SSH, cron, sendmail)

As you can see it gets very crowded in 512 MB.

It’s especially troublesome since the memory is allocated lazily and the memory usage builds up slowly. In some point caches are no longer caches, but swapped to a disk – virtual memory usage grows beyond available RAM. To keep the server response, everything time critical should fit to RAM once and if the processes themselves don’t know how to release memory in this situation you need to tune a memory cap for them.

1. MySQL memory consumption

MySQL can be a greedy bastard what comes to memory consumption. Here on this server MySQL seems to take 417M virtual memory which seems to be little excessive for just running two WordPress instances and one Django / Python application:

1310 mysql     20   0  417M 21100  2776 S  0.0  1.2  0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v

After some tuning I was able to bring it down a bit

3354 mysql     20   0  276m  19m 2848 S    0  1.2   3:41.19 mysqld

A reduction of 130 MB, or 1/4 of the server total memory. Not bad.

Use mtop to monitor running MySQL, its querieries, etc. so you know what’s going on. As you can see this MySQL has very good cache rate meaning that basically it is keeping everything in memory. If the content of the sites is less than 10 MBytes total, 400 MB contains plenty of space to cache the content:

load average: 0.05, 0.08, 0.16 mysqld 5.0.51a-3ubuntu5.8-log up 1 day(s), 19:47 hrs                                                             
2 threads: 1 running, 6 cached. Queries/slow: 187.1K/0 Cache Hit: 99.39%

2. What eats memory

I am not an expert on MySQL, so I hope someone with more insight could post comments regarding how to tune MySQL for low memory situations and how it is expected to behave.

Some ideas I run through my head

  • MySQL default cache settings are not too tight on Ubuntu/Debian, making it suitable for moderate loads, not low loads. If you don’t have much content, everything is just kept in memory (even if not needed)
  • MySQL uses round robin for connections and if there is 100 max connections it will allocate a thread stack for each connection (someone please confirm this – I found contracting infos).

3. Configuring MySQL

Here are listed some methods how to reduce the memory usage. This is what I done on this little box

MySQL is mostly configured in /etc/mysql/my.cnf on Ubuntu / Debian.

The final adjustments

key_buffer              = 8M
max_connections         = 30
query_cache_size        = 8M
query_cache_limit       = 512K
thread_stack            = 128

source from : http://opensourcehacker.com/2011/03/31/reducing-mysql-memory-usage-on-ubuntu-debian-linux/
Advertisements