pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Schaefer, Mario <Schaefer.Mario@dd-v.de>
To: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: Re: partitioning os swap data log tempdb
Date: Mon, 24 Feb 2003 11:00:37 -0800
Message-ID: <200302241100.37249.josh@agliodbs.com> (raw)
In-Reply-To: <5187C8401CB1D211AB6E00A0C9929D06018326C5@DDV_EXCH>
References: <5187C8401CB1D211AB6E00A0C9929D06018326C5@DDV_EXCH>

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



view thread (5+ messages)

Message-ID: <200302241100.37249.josh@agliodbs.com>
Permalink:  ../200302241100.37249.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200302241100.37249.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, Schaefer.Mario@dd-v.de
  Subject: Re: partitioning os swap data log tempdb
  In-Reply-To: <200302241100.37249.josh@agliodbs.com>
  References: <5187C8401CB1D211AB6E00A0C9929D06018326C5@DDV_EXCH> <200302241100.37249.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox