How to move MySQL storage to RamFS or TmpFS partition

Whether moving all MySQL storage to a tmpfs helps with speeding it up or not is questionable but I needed to do for some testing purposes, so this is a short overview of how I did that hopefully will be useful:

First mount tmpfs to a folder:

sudo mkdir /var/ramfs
sudo mount -t ramfs -o size=1G ramfs /var/ramfs/

Here I mounted ramfs to /var/ramfs. I am using ramfs in oppose to tmpfs mainly because:

  • ramfs grows dynamically(tmpfs doens’t)
  • ramfs doesn’t use swap(while tmpfs does)

RAM-backed file system is mounted, so now I need to populate it with MySQL files for processing.
To do that I will need to stop mysql, copy it’s database files over to ramfs, adjust AppArmor and MySQL settings and start mysql server again. Here is the chain of commands to do that:

Copying files:

sudo /etc/init.d/mysql stop
sudo cp -R /var/lib/mysql /var/ramfs/
sudo chown -R mysql:mysql /var/ramfs/mysql

Tweaking MySQL config:

sudo cp /etc/mysql/my.cnf /etc/mysql/original-my.cnf
sudo vim /etc/mysql/my.cnf

Find line with ‘datadir‘ definition(it will look something like datadir = /var/lib/mysql) and change it to

datadir = /var/ramfs/mysql

Next step is to tune apparmor settings:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the following few lines just before the closing curly braces:


/var/ramfs/mysql/ r,
/var/ramfs/mysql/*.pid rw,
/var/ramfs/mysql/** rwk,

Looks like we’re done with settings, let’s see if it will work:


sudo /etc/init.d/apparmor restart
sudo /etc/init.d/mysql start

If mysql daemon starts(double check /var/log/mysql.err for any errors) and you can connect to it, mostlikely now we’re running fully off of a RAM device. To double check it, run this from mysql client:

mysql> show variables where Variable_name = 'datadir' \G
*************************** 1. row ***************************
Variable_name: datadir
Value: /var/ramfs/mysql/
1 row in set (0.00 sec)

That’s pretty much it :)

MySQL5 TINYINT, SMALLINT, MEDUIMINT, INT, BIGINT boundaries

From MySQL Documentation, boundaries of the numerical types are as follows:

Type Min Value Max Value Bytes
TINYINT -128 127 1
TINYINT UNSIGNED 0 255 1
SMALLINT -32,768 32,767 2
SMALLINT UNSIGNED 0 65,535 2
MEDIUMINT -8,388,608 8,388,607 3
MEDIUMINT UNSIGNED 0 16,777,215 3
INT -2,147,483,648 2147483647 4
INT UNSIGNED 0 4,294,967,295 4
BIGINT -9223372036854775808 9223372036854775807 8
BIGINT UNSIGNED 0 18446744073709551615 8