by Mark Nielsen About the author: The author works at The Computer Underground, Inc. as a Linux geek and enjoys doing silly things and making up silly projects, because hey, computers are SUPPOSED to be fun. Mark also works at ZING, www.genericbooks.com as a volunteer to help and promote free and open software and literature. Content: Introduction Setting up a ramdisk for /Test Setting up a ramdisk for PostgreSQL Comments on the speed Comments References

Speeding up small databases on Linux

Abstract:

This article shows how to speed up a PostgreSQL database server using a RAMDISK.

Introduction

Ramdisk is basically memory that you pretend to use as a hard disk drive. Anytime you use a ramdisk, you basically are using memory and not your hard drive. There are advantages and disadvantages to this. Basically, the big advantage is that since you are using memory, whatever you are doing will be much faster since your hard drive is slower than your memory. The big disadvantage is that if you make changes to the database server and you reboot your computer, you will loose all your changes.

For the database server PostgreSQL, if you load all the databases in memory, you will increase its speed.  

Setting up a ramdisk for /Test

mkdir -p /Test
mkfs -t ext2 /dev/ram0
mount /dev/ram0 /Test

The above gives you a ramdisk with available space of just under 4Mb. Have a look at the Ramdisk Article to see how you can change this to e.g 50Mb.  

Setting up a ramdisk for PostgreSQL

NOTE: We assume you have set the ramdisk option in your lilo.conf file to be larger than the size of your database server. If you need to find the approximate size, issue this command "cd /var/lib/pgsql; du ".

However, to take your current postgresql server installed at "/var/lib/pgsql" and to put it into memory, do this,

        ### Stop the current postgresql server
/etc/rc.d/init.d/postgres stop
        ### rename the current directory
mv /var/lib/pgsql /var/lib/pgsql_main
        #### Create a directory to have our ramdisk on
mkdir -p /var/lib/pgsql_memory
 	#### change the ownership of the new directory to postgres or whatever
	#### the actual owner is. 
chown postgres /var/lib/pgsql_memory
	#### Make an alias or link to the original name, /var/lib/pgsql
ln -s /var/lib/pgsql_memory /var/lib/pgsql

	#### Format the ramdisk
mkfs -t ext2 /dev/ram0
	#### Mount the ramdisk to the postgresql directory
mount /dev/ram0 /var/lib/pgsql_memory

	#### Copy everything from the main directory into the ramdisk
tar -C /var/lib/pgsql_main -cp . | tar -C /var/lib/pgsql_memory -xp
        ### Start the current postgresql server
/etc/rc.d/init.d/postgres start

Comments on the speed

What I noticed is that when the data is cached by the system, the performance gain is not very much, about 10% or 20%. When there is lots of unique data being pumped into the database, the performance goes up dramatically.

The biggest advantage with the ramdisk trick is that you force the database to remain in memory and you avoid using your hard drive. If you don't use ramdisk, your database will probably get swapped out of cache eventually, and then when it gets used again, it will have to reload from the hard drive.  

Comments

1. For the Linux operating system, apparently once a file is read, it is kept in memory until that memory space needs to get used by something else. Thus, it caches it. Well, this might be a solution, except from practical experience, when my database server gets hit, I always hear the hard drive moving. Using a ramdisk forces the database server into memory making it so it doesn't get "swapped out". If you use your database server a lot, I would force it into memory.
2. The advantages of using a ramdisk for your database is speed, clear and simple. For read-only databases, this is really cool.
3. The disadvantage is that you have to know how to maintain it and if you make changes, you better make sure you backup the changes you made.

References

1. How to use a Ramdisk for Linux by Mark Nielsen
2. PostgreSQL homepage

1999-11-01, generated by lfparser version 0.8