The results and ramblings of research


High Throughput PostGRES DB performance using multiple disks

leave a comment »

One of the consistent problems I am having with my current installation of PostGRES is really slow performance of the database. I have now decided to add another enterprise disk, and to utilize this disk to store indexes and some tables. I essentially now have two disks:

  1. /dev/sda – which is mounted as “/”. The postgres default data directory /var/lib/pgsql is stored on this disk.
  2. /dev/sdb – which is mounted as “/state/partition2” will be used to store the indexes

The first set is to setup a directory owned by postgres which will be used to store data.

$ mkdir /state/partition2/pgsql/data
$ chown -R postgres /state/partition2/pgsql
$ chgrp -R postgres /state/partition2/pgsql

The next step is to setup a tablespace for store the indexes and some tables. To do that use the following commands to setup a tablespace.

$ psql -Upostgres -d db1
db1=# create tablespace indexspace LOCATION '/state/partition2/pgsql/data';
db1=# create tablespace userspace LOCATION '/state/partition2/pgsql/tables';

The next step is to move some tables and indexes to the new tablespaces. Furthermore, any new indexes can be created directly on the new tablespace using the following commands.

$ psql -Upostgres -d db1
db1=# alter table table1 set tablespace=userspace;
db1=# alter index index1 set tablespace=indexspace;
db1=# create index index1 (key1) tablespace indexspace;

And you are done. To view tablespaces use the following command

db1=# SELECT spcname FROM pg_tablespace;
db1=# SELECT * FROM pg_tablespace;

Written by anujjaiswal

July 9, 2011 at 4:45 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: