Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5181F9FB39E for ; Fri, 14 Sep 2007 17:21:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73171-08 for ; Fri, 14 Sep 2007 17:21:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.184]) by postgresql.org (Postfix) with ESMTP id EB2C69FB3BC for ; Fri, 14 Sep 2007 17:21:08 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so1507583mue for ; Fri, 14 Sep 2007 13:21:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=TknGpwISzN1bbLq7UDWEEFNdg/iWA82vQMthMEMgBUU=; b=CM2T7x9h+K8nt4Z4WZBQlyZ+OT2FVwAzGAly2lofZ2uYbXTFHCx0lqzeuYA9SqV4u/oXefOMDvhSJHSJF235v8c7LMSZWIEMF5UgdZMxbAwP36Al6ez4BAbLjKy7Ca00up8HbkHQHV3WJFGxsOSgCGYYw26Ce5kCiquGF35JZ/4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WfpTVxFACTEHEsyuC2D4zQOZ/yCXrip5rsSRCRhbyAXR/BYzEFNZTC9gJYDC6eKfe9MZPWPNAARz9OTRGqHH28aqSkHERGrYKxYixN4w+nixu8OJvJBdRVpPM81pyUZKt6GYwQ8Q3K/ZpoDfXL9U1pgTNKeuwccjG3URjApvtHw= Received: by 10.82.186.5 with SMTP id j5mr3055641buf.1189801266277; Fri, 14 Sep 2007 13:21:06 -0700 (PDT) Received: by 10.82.105.9 with HTTP; Fri, 14 Sep 2007 13:21:06 -0700 (PDT) Message-ID: Date: Sat, 15 Sep 2007 01:51:06 +0530 From: "Harsh Azad" To: "Jean-David Beyer" Subject: Re: Index files Cc: pgsql-performance@postgresql.org In-Reply-To: <46EA8B00.9030600@verizon.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5335_2253168.1189801266272" References: <46EA8B00.9030600@verizon.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/278 X-Sequence-Number: 26823 ------=_Part_5335_2253168.1189801266272 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ------=_Part_5335_2253168.1189801266272 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_5335_2253168.1189801266272--