pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex files
10+ messages / 7 participants
[nested] [flat]
* Index files
@ 2007-09-14 02:50 Harsh Azad <harsh.azad@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Harsh Azad @ 2007-09-14 02:50 UTC (permalink / raw)
To: pgsql-performance
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.
Thanks,
Azad
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 02:57 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
parent: Harsh Azad <harsh.azad@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Ow Mun Heng @ 2007-09-14 02:57 UTC (permalink / raw)
To: Harsh Azad <harsh.azad@gmail.com>; +Cc: pgsql-performance
On Fri, 2007-09-14 at 08:20 +0530, 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.
you have to search through pg_class for the "number"
Alternatively, you can try using tablespaces.
create tablespace indexspace location '/mnt/fastarray'
create index newindex on table (index_1) tablespace indexspace
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 03:03 Harsh Azad <harsh.azad@gmail.com>
parent: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Harsh Azad @ 2007-09-14 03:03 UTC (permalink / raw)
To: Ow Mun Heng <Ow.Mun.Heng@wdc.com>; +Cc: pgsql-performance
ah.. thanks. Didn't realize table spaces can be mentioned while creating a
index. BTW, are soft links ok to use for pg_clog / pg_xlog . I moved the
existing directories to /mnt/logs/pglogs and made soft links for both
directories in $PGDATA
Thanks
On 9/14/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
>
> On Fri, 2007-09-14 at 08:20 +0530, 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.
>
> you have to search through pg_class for the "number"
>
> Alternatively, you can try using tablespaces.
>
> create tablespace indexspace location '/mnt/fastarray'
> create index newindex on table (index_1) tablespace indexspace
>
>
--
Harsh Azad
=======================
Harsh.Azad@gmail.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 03:09 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
parent: Harsh Azad <harsh.azad@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ow Mun Heng @ 2007-09-14 03:09 UTC (permalink / raw)
To: Harsh Azad <harsh.azad@gmail.com>; +Cc: pgsql-performance
On Fri, 2007-09-14 at 08:33 +0530, Harsh Azad wrote:
> ah.. thanks. Didn't realize table spaces can be mentioned while
> creating a index. BTW, are soft links ok to use for pg_clog /
> pg_xlog . I moved the existing directories to /mnt/logs/pglogs and
> made soft links for both directories in $PGDATA
No idea what is the "proper" solution. Me being a newbie itself.
But from what I've read on the net and google, symlink seems to be the
order of the day.
perhaps others who are more familiar can comment as I'm lost in this.
(I'm doing symlinking btw)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 03:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Harsh Azad <harsh.azad@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2007-09-14 03:33 UTC (permalink / raw)
To: Harsh Azad <harsh.azad@gmail.com>; +Cc: pgsql-performance
"Harsh Azad" <harsh.azad@gmail.com> writes:
> Where are the database index files located in the $PGDATA directory?
Read
http://www.postgresql.org/docs/8.2/static/storage.html
> I was
> thinking on soft linking them to another physical hard disk array.
Manual symlink management, while not impossible, pretty much sucks
... especially if your tables are big enough that you actually need to
do this. Use a tablespace instead. (If you are on a PG version that
hasn't got tablespaces, you are more than overdue to upgrade.)
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 08:34 Philippe Amelant <pamelant@companeo.com>
parent: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 0 replies; 10+ messages in thread
From: Philippe Amelant @ 2007-09-14 08:34 UTC (permalink / raw)
To: pgsql-performance
Le vendredi 14 septembre 2007 à 11:09 +0800, Ow Mun Heng a écrit :
> On Fri, 2007-09-14 at 08:33 +0530, Harsh Azad wrote:
> > ah.. thanks. Didn't realize table spaces can be mentioned while
> > creating a index. BTW, are soft links ok to use for pg_clog /
> > pg_xlog . I moved the existing directories to /mnt/logs/pglogs and
> > made soft links for both directories in $PGDATA
>
>
> No idea what is the "proper" solution. Me being a newbie itself.
> But from what I've read on the net and google, symlink seems to be the
> order of the day.
>
> perhaps others who are more familiar can comment as I'm lost in this.
> (I'm doing symlinking btw)
>
You can also mount a different fs on another disk array in pglogs
I thinks update to index are not written to the wal so having index on a
different tablespace should reduce read and write on the data
tablespace, am I wrong ?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 13:22 Jean-David Beyer <jeandavid8@verizon.net>
parent: Harsh Azad <harsh.azad@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Jean-David Beyer @ 2007-09-14 13:22 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 20:21 Harsh Azad <harsh.azad@gmail.com>
parent: Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 2 replies; 10+ messages in thread
From: Harsh Azad @ 2007-09-14 20:21 UTC (permalink / raw)
To: Jean-David Beyer <jeandavid8@verizon.net>; +Cc: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 20:25 Mark Lewis <mark.lewis@mir3.com>
parent: Harsh Azad <harsh.azad@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Mark Lewis @ 2007-09-14 20:25 UTC (permalink / raw)
To: Harsh Azad <harsh.azad@gmail.com>; +Cc: Jean-David Beyer <jeandavid8@verizon.net>; pgsql-performance
On Sat, 2007-09-15 at 01:51 +0530, Harsh Azad wrote:
> 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?
You can alter an existing index:
http://www.postgresql.org/docs/8.2/static/sql-alterindex.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Index files
@ 2007-09-14 20:29 Alan Hodgson <ahodgson@simkin.ca>
parent: Harsh Azad <harsh.azad@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Alan Hodgson @ 2007-09-14 20:29 UTC (permalink / raw)
To: pgsql-performance
On Friday 14 September 2007, "Harsh Azad" <harsh.azad@gmail.com> wrote:
> 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?
>
ALTER TABLE table_name set tablespace new_tablespace;
and ALTER INDEX ...
--
Eat right. Exercise regularly. Die anyway.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-09-14 20:29 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-14 02:50 Index files Harsh Azad <harsh.azad@gmail.com>
2007-09-14 02:57 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-09-14 03:03 ` Harsh Azad <harsh.azad@gmail.com>
2007-09-14 03:09 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-09-14 08:34 ` Philippe Amelant <pamelant@companeo.com>
2007-09-14 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-14 13:22 ` Jean-David Beyer <jeandavid8@verizon.net>
2007-09-14 20:21 ` Harsh Azad <harsh.azad@gmail.com>
2007-09-14 20:25 ` Mark Lewis <mark.lewis@mir3.com>
2007-09-14 20:29 ` Alan Hodgson <ahodgson@simkin.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox