Re: Index location patch for review
От | Jim Buttafuoco |
---|---|
Тема | Re: Index location patch for review |
Дата | |
Msg-id | 200109121932.f8CJWgY10920@dual.buttafuoco.net обсуждение исходный текст |
Ответ на | Index location patch for review ("Jim Buttafuoco" <jim@buttafuoco.net>) |
Список | 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 > >
В списке pgsql-hackers по дате отправления: