Re: Index files - Mailing list pgsql-performance

From Harsh Azad
Subject Re: Index files
Date
Msg-id a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com
Whole thread Raw
In response to Re: Index files  (Jean-David Beyer <jeandavid8@verizon.net>)
Responses Re: Index files
Re: Index files
List pgsql-performance
Great, creating new tablespace for indexes worked! Now the question is whether existing tables/index can be moved to the new tablespace using an alter command or the only way possible is to drop and recreate them?

Azad

On 9/14/07, Jean-David Beyer <jeandavid8@verizon.net> wrote:
Harsh Azad wrote:
> Hi,
>
> Where are the database index files located in the $PGDATA directory? I
> was thinking on soft linking them to another physical hard disk array.
>
I am not an expert, but what I have done is put the Write-Ahead-Log on one
hard drive, some little-used relations and their indices on a second hard
drive, and the main database files on four other drives. These are SCSI hard
drives and I have two SCSI controllers. /dev/sda and /dev/sdb are on one
controller, and the other four hard drives are on the other controller.
These controllers are on a PCI-X bus all their own.

I put $PGDATA (I do not actually set or use that global variable) on /dev/sda.

[/srv/dbms/dataA/pgsql/data]$ ls -l
total 88
-rw------- 1 postgres postgres     4 Aug 11 13:32 PG_VERSION
drwx------ 5 postgres postgres  4096 Aug 11 13:32 base
drwx------ 2 postgres postgres  4096 Sep 14 09:16 global
drwx------ 2 postgres postgres  4096 Sep 13 23:35 pg_clog
-rw------- 1 postgres postgres  3396 Aug 11 13:32 pg_hba.conf
-rw------- 1 root     root      3396 Aug 16 14:32 pg_hba.conf.dist
-rw------- 1 postgres postgres  1460 Aug 11 13:32 pg_ident.conf
drwx------ 4 postgres postgres  4096 Aug 11 13:32 pg_multixact
drwx------ 2 postgres postgres  4096 Sep 14 09:16 pg_subtrans
drwx------ 2 postgres postgres  4096 Aug 12 16:14 pg_tblspc
drwx------ 2 postgres postgres  4096 Aug 11 13:32 pg_twophase
drwx------ 3 postgres postgres  4096 Sep 14 09:13 pg_xlog
-rw------- 1 postgres postgres 15526 Sep 11 22:31 postgresql.conf
-rw------- 1 postgres postgres 13659 Aug 11 13:32 postgresql.conf.dist
-rw------- 1 postgres postgres    56 Sep 14 07:33 postmaster.opts
-rw------- 1 postgres postgres    52 Sep 14 07:33 postmaster.pid

In /dev/sdb are

]$ ls -l
total 12
drwxr-x--- 2 postgres postgres 4096 Aug 18 00:00 pg_log
-rw------- 1 postgres postgres 2132 Sep 14 07:25 pgstartup.log
drwx------ 3 postgres postgres 4096 Aug 12 21:06 stock

The stuff in "stock" are little-used tables and their indices.

Everything else is on the other four drives. I put the index for a table on
a separate drive from the tata for the table.

--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
/( )\ Shrewsbury, New Jersey     http://counter.li.org
^^-^^ 09:10:01 up 1:37, 4 users, load average: 5.77, 5.12, 4.58

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate



--
Harsh Azad
=======================
Harsh.Azad@gmail.com

pgsql-performance by date:

Previous
From: Greg Smith
Date:
Subject: Re: Long Running Commits - Not Checkpoints
Next
From: Mark Lewis
Date:
Subject: Re: Index files