Rate this page del.icio.us  Digg slashdot StumbleUpon

Tips and tricks: Memory storage on PostgreSQL

by Alexander Todorov

Introduction

PostgreSQL is very sophisticated and powerful database server to use with Red Hat Enterprise Linux. However, many people argue that one of the features it lacks is the memory storage engine of MySQL.

This storage engine is ideal for applications that process temporary data that is updated rarely but accessed many times. These applications use databases stored in memory to decrease the number of disc operations. An example of such an application is the web-based BitTorrent tracker.

In this tip, you will learn how to set up memory storage for PostgreSQL using standard features provided by the database server itself and the Red Hat Enterprise Linux operating system.

Creating memory filesystem

First we will need a place in memory where all database content will be stored. To create a filesystem in memory use the command:

mount -t ramfs none /mount/point

This will create a filesystem with the following features:

  • All files are kept in RAM.
  • Access is read-write.
  • Does not use fixed amount of RAM.
  • Filesystem grows and shrinks to accommodate the files it contains.
  • When the filesystem is unmounted, all its contents are lost.

Setting up a tablespace

From PostgreSQL documentation:

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.The location must be an existing, empty directory that is owned by the PostgreSQL system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary database users to make use of it. To do that, grant them the CREATE privilege on it.

First create the directory where tablespace will be stored. It must reside on your ramfs filesystem.

    	mkdir /mnt/ramfs/pgdata
    	chown postgres:postgres /mnt/ramfs/pgdata
    	chmod go-rwx /mnt/ramfs/pgdata
    	    

To create the tablespace and grant permissions on it:

    	CREATE TABLESPACE $TABLESPACE_NAME LOCATION '/mnt/ramfs/pgdata';
	GRANT CREATE ON TABLESPACE $TABLESPACE_NAME TO $ROLE_NAME;

In addition to setting up entries in internal PostgreSQL tables, a file named “PG_VERSION” will be created under the tablespace directory. Get familliar with it and all other possible files (if) created because we need to restore this structure later.

We are defining the tablespace into `template1′ database. Normally all other databases inherit from `template1′. This way we don’t need to redefine it every time and it can be used for all subsequently created objects in the database.

It is not good idea for an application to access the database as superuser. Create a new role to use with your application.

Warning: Depending on your configuration you may need to tune SELinux policy to allow user `postgres’ access to the tablespace directory.

Creating databases

To create a new database in the tablespace use the command:

       CREATE DATABASE $DATABASE_NAME WITH TABLESPACE = $TABLESPACE_NAME;

All other objects created within this database will be stored in the same tablespace. Tablespaces can be used when creating tables as well.

Dumping and restoring the database

We need to dump and restore the database structure every time the ramfs filesystem is unmounted. Applications using memory databases should be aware of data loss and responsible for its recreation.

To dump the database schema:

     pg_dump --create --schema-only --file=$FILENAME --host=$HOST -U postgres $DATABASE_NAME

To restore the database schema:

     psql --file $FILENAME -U $ROLE_NAME --dbname postgres --host $HOST

Automating the process

To make this setup work every time when the server is restarted, we need to edit `/etc/init.d/postgresql’. Make sure the items appear in the correct order.

When stopping the database server:

  1. Dump database schema:
         pg_dump --create --schema-only --file=/root/memdb.sql --host=localhost -U postgres memdb
    
  2. Stop postmaster (original init code)
  3. Unmount ramfs:
         umount /mnt/ramfs
    

When starting the database server:

  1. Mount ramfs:

    if [ ! -d /mnt/ramfs ]; then
        mkdir -p /mnt/ramfs
    fi
    mount -t ramfs none /mnt/ramfs
    	
  2. Recreate PostgreSQL tablespace directory:
    	mkdir /mnt/ramfs/pgdata
    	echo $PGMAJORVERSION > /mnt/ramfs/pgdata/PG_VERSION
    	chown -R postgres:postgres /mnt/ramfs/pgdata
    	chmod -R go-rwx /mnt/ramfs/pgdata
    	
  3. Start postmaster (original init code)
  4. Drop the database because it exists in PostgreSQL system tables only.
         psql --quiet -U postgres --host localhost --command "DROP DATABASE memdb;" 2>/root/psql.log
    
  5. Restore the database schema:

         psql --quiet --file /root/memdb.sql -U $ROLE_NAME -d postgres --host localhost 2>/root/psql.log
    

Summary

You have just learned how to set up memory storage for the PostgreSQL database server. In addition to all PostgreSQL features, when using memory databases, it does not have the limitations imposed by MySQL. For a complete setup you may tweak your init script as you wish.

5 responses to “Tips and tricks: Memory storage on PostgreSQL”

  1. Alexander Todorov says:

    NOTE: In the example above we dump only database schema. Normally applications processing data in memory don’t care about the data itself (because it has a temporary nature) or they are able to re-create the data.
    If you care about the contents in the database remove the –schema-only parameter in the code above.

  2. Pavel Stěhule says:

    Hello

    it will works only for selects. Updates and deletes have to be commited to WAL, and if WAL is on normal disk, then you cannot expect any speedup.

    So. If you want to play with this, you have to put to ramdisk complete database cluster and set fsync = off in postgresql.conf. Else don’t use ramdisk and set bigger value of shared_buffers. It has similar efect and its more correct
    solution.

    Pavel

  3. Walt says:

    Increasing shared buffers can effectively keep a db in memory. The default configuration has traditionally been way too conservative – am sure someone will eventually get round to writing a script to tune configuration based on a user response to questions and hardware probing.

    Course if the data is not critical and you just need something lightweight there are application specific options…

  4. Peter Sinclair says:

    Useful tip. I assume the first command “mount -t ramfs none /mount/point” should have mounted at /mount/ramfs as this is the mount point referred to in all later discussion.

  5. Alexander Todorov says:

    Peter,
    Thanks for the heads up.