Skip site navigation (1) Skip section navigation (2)

Re: partitioning os swap data log tempdb

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Schaefer, Mario" <Schaefer(dot)Mario(at)dd-v(dot)de>,"'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: partitioning os swap data log tempdb
Date: 2003-02-24 19:00:37
Message-ID: 200302241100.37249.josh@agliodbs.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Joe ConwayDate: 2003-02-24 19:11:34
Subject: Re: Memory taken by FSM_relations
Previous:From: Andrew SullivanDate: 2003-02-24 18:51:25
Subject: Re: slow query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group