At 01:16 PM 5/5/01 -0400, mlw wrote:
>Lincoln Yeoh wrote:
>>
>> All that for maybe 10% performance increase?
>>
>> I think it's more advantageous for Postgresql to keep the filesystem layer
>> of abstraction, than to do away with it, and later reinvent certain parts
>> of it along with new bugs.
>
>I just did a test of putting pg_xlog on a FAT file system, and my first rough
>tests (pgbench) show an approximate 20% performance increase over ext2 with
>fsync enabled.
OK. I slouch corrected :). It's more than 10%.
However in the same message I did also say:
>What would be useful is if one can specify where the tables, indexes, WAL
>and other files go. That feature would probably help improve performance
>far more.
>
>For example: you could then stick the WAL on a battery backed up RAM disk.
>How much total space does a WAL log need?
>
>A battery backed RAM disk might even be cheaper than Brand X RDBMS
>Proprietary Feature #5.
And your experiments do help show that it is useful to be able to specify
where things go, that putting just the WAL somewhere else makes things 20%
faster. So you don't have to put everything on a pgfs. Just the WAL on some
other FS (even FAT32, ick ;) ).
---
OK we can do that with symlinks, but is there a PGSQL Recommended or
Standard way to do it, so as to reduce administrative errors, and at least
help improve consistency with multiadmin pgsql installations?
The WAL and DBs are in separate directories, so this makes things easy. But
the object names are now all numbers so that makes things a bit harder -
and what to do with temp tables?
Would it be good to have tables in one directory and indexes in another? Or
most people optimize on a specific table/index basis? Where does PGSQL do
the on-disk sorts?
How about naming the DB objects <object ID>.<object name>?
e.g
121575.testtable
125575.testtableindex
(or the other way round - name.OID - harder for DB, easier for admin?)
They'll still be unique, but now they're admin readable. Slower? e.g. at
that code point, pgsql no longer knows the object's name, and wants to
refer to everything by just numbers?
I apologize if there was already a long discussion on this. I seem to
recall Bruce saying that the developers agonized over this.
Cheerio,
Link.