From: | "Jim Buttafuoco" <jim(at)buttafuoco(dot)net> |
---|---|
To: | "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM>, "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Bruce Momjian'" <pgman(at)candle(dot)pha(dot)pa(dot)us>, jim(at)buttafuoco(dot)net, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index location patch for review |
Date: | 2001-09-12 19:32:42 |
Message-ID: | 200109121932.f8CJWgY10920@dual.buttafuoco.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Vadim,
I don't understand the WAL issue below, can you explain. The dir name
is the same name as the database with _index added to it. This is how
the current datpath stuff works. I really just copied the datpath code
to get this patch to work...
Also I have been running this patch (both 7.1.3 and 7.2devel) against
some of my companies applications. I have loaded a small database 10G
data and 15G indexes both with and without the patch. There seems to be
between 5% and 10% performance gain doing most common db commands
(selects, selects with joins and inserts). The system is a DUAL P3 733
with 3 IDE disks. One for PGDATA, second for APPDATA and third for
APPIDX. As you can see I have seperated WAL files, GLOBAL, Application
data and application indexes over 3 disks. Our production systems have
around 50k queries/day ( not including data loads), so I believe that
when this patch get put into production, with 20 disks and 10 database
the performance increase should go up.
I should also add, that I have been working on the second part of this
patch, which will allow tables and indexes to be put into LOCATIONS
also. I am going planning on having a PG_LOCATIONS table and
CREATE|DROP|ALTER location SQL command instead of the initlocation shell
script we currently have. The only thing stopping me now is 7.2 testing
I am planning on doing once the beta begins and problems adding a
location column to the pg_class table with the necessary support code in
heap.c...
Thanks for all the comments (keep them comming)
Jim
> > > The more general and "standard" way to go are TABLESPACEs.
> > > But probably proposed feature will be compatible with
> > > tablespaces, when we'll got them:
> >
> > Will it be? I'm afraid of creating a backwards-compatibility
> > problem for ourselves when it comes time to implement tablespaces.
>
> As I said, INDEX_LOCATION in CREATE DATABASE could mean location
> of default tablespace for indices in future and one will be able
> to override tablespace for particular index with TABLESPACE
> clause in CREATE INDEX command.
>
> > At the very least I'd like to see some information demonstrating
> > how much benefit there is to this proposed patch, before we
> > consider whether to adopt it. If there's a significant performance
> > benefit to splitting a PG database along the table-vs-index divide,
> > then it's interesting as a short-term improvement ... but Jim didn't
> > even make that assertion, let alone provide evidence to back it up.
>
> Agreed. He mentioned significant performance difference but it would
> be great to see results of pgbench tests with scaling factor of >= 10.
> Jim?
>
> Also, after reviewing patch I have to say that it will NOT work
> with WAL. Jim, please do not name index' dir as "<TBL_NODE>_index".
> Instead, just use different TBL_NODE for indices (different number).
> It's not good to put if(reln->rd_rel->relkind == RELKIND_INDEX)
> stuff into storage manager - only two numbers (tblnode & relnode)
> must be used to identify file, no any other logical information
> totally unrelated to storage issues.
>
> Vadim
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mikheev, Vadim | 2001-09-12 19:54:25 | Re: Index location patch for review |
Previous Message | Mikheev, Vadim | 2001-09-12 19:07:46 | Re: Index location patch for review |