About

mFabrik Blog is about mobile and web software development, open source and Linux. We tell exciting tales where business, technology, web and mobile convergence.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.

Reducing MySQL memory usage on Ubuntu / Debian Linux

Posted on March 31, 2011

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.

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%

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).

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            = 128K

More info

Send in more tips please! Is 32-bit better than 64-bit for low end VPS, how much this affects MySQL?

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Setting up Apache virtual hosts behind Varnish

Posted on March 4, 2011

Varnish is a very fast front end cache server. You might want to use it at the front of Apache to speed up loading of your static pages and static media, for example for your WordPress blog. You can also use Varnish backends to multiplex the requests between Plone and Apache based PHP software running on the same server using different backend directives.

However if you wish to use Apache virtual hosts with Varnish there is a trick in it.

We use the following setup

  • Varnish listens to port 80, HTTP
  • Apache listens to port 81
  • Varnish uses Apache as a backend

The related varnish.vcl is

backend backend_apache {
.host = "127.0.0.1";
.port = "81";
}

sub vcl_recv {
 ...
 elsif (req.http.host ~ "^blog.mfabrik.com(:[0-9]+)?$") {
    set req.backend = backend_apache;
 }
 ...
}

Note that the backend IP is 127.0.0.1 (localhost). By default, with Debian or Ubuntu Linux, Apache configuration does not do virtual hosting for this.

So if /etc/apache2/sites-enabled/blog.mfabrik.com looks like:

<VirtualHost *:81>

 ServerName blog.mfabrik.com
 ...
 LogFormat       combined
 TransferLog     /var/log/apache2/blog.mfabrik.com.log

 ...

 ExpiresActive On
 ExpiresByType image/gif A3600
 ExpiresByType image/png A3600
 ExpiresByType image/image/vnd.microsoft.icon A3600
 ExpiresByType image/jpeg A3600
 ExpiresByType text/css A3600
 ExpiresByType text/javascript A3600
 ExpiresByType application/x-javascript A3600
</VirtualHost>

And now the trick – you need to add the following to /etc/apache2/httpd.conf

NameVirtualHost *:81

Unless you do all this, Apache will just pick the first virtualhost file in /etc/apache2/sites-enabled and use it for all requests.

Also you need to edit ports.conf and change Apache to listen to port 81:

Listen 81

Get developers  Subscribe mFabrik blog in a reader Follow me on Twitter

Perfect dual boot crypted hard disk setup with Truecrypt and LUKS

Posted on July 15, 2008

I have a work laptop used in Symbian and web development. I need to be able to boot both Vista and Linux. Due to client privacy, both operating systems must be crypted for the case of lost laptop. Even if I do not use Windows actively, its web browser data may contain stored password for client systems and it would be catastrophic to leak them accidentally.

Here are instructions how to encrypt your hard disk in safely but performance effective manner with Ubuntu 8.04 Hardy Heron and Windows Vista. These instructions can be applied for any version of Vista, since we use third party open source Truecrypt suite to encrypt the Windows partition. The instructions also give priority for Grub boot loader, so that the computer will boot to Linux if there is no user interaction during the boot.

  1. Install Windows Vista from the factory first boot installer
  2. Download Ubuntu 8.04 alternative install CD. The alternative install CD contains installer menus to encrypt your HD using LVM and LUKS.
  3. For the sake of performance, we only crypt /home directory on Linux partition which contains all user editable files. All other files in Linux, maybe excluding configuration files in /etc, are open source and encrypting them only slows your application start-up times. It is possible to encrypt /home after install, but it is much easier during the install time. Here are instructions how to set up encrypted home partition with alternative install CD.
  4. After this comes the exciting part. You must encrypt the Windows system partition using Truecrypt. Since Truecrypt is going to overwrite Ubuntu’s Grub bootloader on Master Boot Record (MBR), some magic is needed (detailed instructions).
    1. Install Truecrypt and overwrite MBR.
    2. Boot Ubuntu from live CD. Alternative install CD doesn’t work as it does not have grub binary. You could also try to boot from your Linux partition by giving out manual kernel root file system parameters for the CD boot loader.
    3. Back-up Truecrypt’s MBR to a file on /boot partition using dd
    4. Add Truecrypt’s MBR as a chain boot loader in Grub
    5. Rewrite MBR using Grub

For foreigners: You might want to keep the US keymap in hand, since the installer environment has not necessarily keymap set up correctly.

Note: Since my HP Pavilion dv9000 laptop has two 250 GB hds, the actual setup is following: windows system partition, windows data partition, rest is set up for Linux using LVM in stripe RAID containing the root partition and the crypted home. This effectively gives near 100 MB/s read speed from two 5400 RPM hds.