PostgresSQL을 메모리로 올리기기(메모리DB)

[이누기]

Memory storage on PostgreSQL

by Alexander Todorov

Introduction

PostgreSQL는 Red Hat Enterprise Linux에서 사용되기에 매우 세련되고 파워풀한 데이타베이스 이지만, 많은 사람들은 MySQL의 메모리 스토리지 엔진의 기능과 같은 기능이 없는 것에 대해서 얘기하고 있다.

이 스토리지 엔진은 긴 시간동안 access되지만 드물게 업데이트되는 임시 데이타를 처리하기 위한 application 에게 매우 이상적인 것이다.

이러한 applications은 디스크의 오퍼레이션의 수를 줄이기 위해서 메모리에 저장되는 데이타베이스를 사용한다.

이와 같은 APPLICATION의 예는 웹기반의 BitTorrent tracker 이다.

이 팁에서 , 당신은 어떻게  데이타베이스 서버와 Red Hat Enterprise Linux에서 제공되는 기본적인 기능으로 PostgreSQL 를memory storage에 설정할 수 있는지 배울 수 있을 것이다.

Creating memory filesystem

먼저 데이타 베이스가 저장될 수 있는 메모리가 필요할 것이다. 메모리에 파일시스템을 만드는 방법은 아래와 같다:

mount -t ramfs none /mount/point

이것은 아래와 같은 특징을 가질 것이다:

• 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:

PostgreSQL 내 Tablespaces 은 database administrators 가 database objects 들이 저장될 수 있는 파일시스템에 위치를 지정하는 것을 허락한다.

먼저 만들었고, 하나의 tablespace 는 데이타베이스 object를 만들때 이름으로 참고될 수 있다.

위치는 반드시 존재해야 하고, 빈 디렉토리는 PostgreSQL system user가 owner가 되어야 한다.

 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.

먼저 tablespace 가 저장될 Directory를 만들어라. ramfs 파일시스템에 반드시 올려야 한다..

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

tablespace를 만들고  permissions을 주어라:

    	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

새 데이타베이스를 tablespace에 만들기 위해서 아래와 같은 명령을 사용해라:

       CREATE DATABASE $DATABASE_NAME WITH TABLESPACE = $TABLESPACE_NAME;

 

Dumping and restoring the database

ramfs 파일시스템이 unmount될때 마다 database 구조를 dump 하고 restore 하는 것이 필요하다.

메모리 데이타베이스를 사용하는 Application은 data loss와 다시 만들어야 하는 것에대해서 감지하고 확인해야 한다.

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,

`/etc/init.d/postgresql’ 를  수정을 하고 정확하게 동작하는지 확인해라.

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
   

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.

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

1. Alexander Todorov says:
   December 13th, 2007 at 4:12 am

   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:
   December 14th, 2007 at 5:06 am

   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:
   December 21st, 2007 at 6:29 pm

   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:
   December 22nd, 2007 at 4:47 pm

   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.