Re: partitioning os swap data log tempdb - Mailing list pgsql-performance

Mario,

> we want to migrate from MS SQL Server (windows2000)
> to PostgreSQL (Linux) :-))
> and we want to use the old MSSQL Hardware.

I don't blame you.  I just finished an MSSQL project.  BLEAH!

> The configuration for MS-SQL was this:
> OS on the 2 IDE Harddisks with Software-RAID1
> SQL-Data on RAID-Controller with RAID-5 (3 x 18GB SCSI Harddisks)
> SQL-TempDB on RAID-Controller with RAID-1 (2 x 9GB SCSI Harddisk)
> SQL-TransactionLog on RAID-Controller with RAID-1 (2 x 9GB SCSI Harddisk)
>
> Can i make a similar configuration with PostgreSQL?

Yes.  Many of the concerns are the same.   However, 3-disk RAID 5 performs
poorly for UPDATES for PostgreSQL.  That is, while reads are often better
than for a single SCSI disk, UPDATES happen at half or less of the speed than
they would do on a SCSI disk alone.

There is no TempDB in PostgreSQL.  This gives you a whole free RAID array to
play with.

> Or what is the prefered fragmentation for
> operatingsystem, swap-partition, data, indexes, tempdb and transactionlog?
>
> What is pg_xlog and how important is it?

It is analogous to the SQL Transaction Log, although it does not need to be
backed up to truncate it.  Deal with it the way you would deal with an MSSQL
transaction log; i.e. on its own disk, if possible.  However, you gain little
by putting it on RAID other than failover safety; in fact, you might find
that the xlog peforms better on a lone SCSI disk since even the best RAID 1
will slow down data writes by up to 15%.

Swap is not such a concern for PostgreSQL on Linux or BSD.   With proper
database tuning and a GB of memory, you will never use the swap.  Or to put
it another way, if you're seeing regular hits on swap, you need to re-tune
your database.

Finally, you want to make absolutely sure that either the write-through cache
on each RAID controller is disabled in the BIOS, or that you have a battery
back-up which you trust 100%.  Otherwise, the caching done by the RAID
controllers will cancel completely the benefit of the Xlog for database
recovery.

> What ist the prefered filesystem (ext2, ext3 or raiserfs)?

That's a matter of open debate.  I like Reiser.   Ext3 has its proponents, as
does XFS.   Ext2 is probably faster than all of the above ... provided that
your machine never has an unexpected shutdown.   Then Ext2 is very bad ar
recovering from power-outs ...

> We want to use about 20 databases with varios size from 5 MB to 500MB per
> database
> and more selects than inserts (insert/select ratio about 1/10) for fast
> webaccess.

Keep in mind that unlike SQL Server, you cannot easily query between databases
on PostgreSQL.   So if those databases are all related, you probably want to
put them in the same PostgreSQL 7.3.2 database and use schema instead.

If the databases are for different users and completely seperate, you'll want
to read up heavily on Postgres' security model, which has been significantly
improved for 7.3.

--
Josh Berkus
Aglio Database Solutions
San Francisco