Re: Index location patch for review

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
>
>