X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A916D475CE7 for ; Mon, 24 Feb 2003 14:22:31 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 44902-02 for ; Mon, 24 Feb 2003 14:22:18 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id F0E5D475E75 for ; Mon, 24 Feb 2003 14:01:32 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2855061; Mon, 24 Feb 2003 11:01:31 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Schaefer, Mario" , "'pgsql-performance@postgresql.org'" Subject: Re: partitioning os swap data log tempdb Date: Mon, 24 Feb 2003 11:00:37 -0800 User-Agent: KMail/1.4.3 References: <5187C8401CB1D211AB6E00A0C9929D06018326C5@DDV_EXCH> In-Reply-To: <5187C8401CB1D211AB6E00A0C9929D06018326C5@DDV_EXCH> MIME-Version: 1.0 Message-Id: <200302241100.37249.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/184 X-Sequence-Number: 1240 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= =20 poorly for UPDATES for PostgreSQL. That is, while reads are often better= =20 than for a single SCSI disk, UPDATES happen at half or less of the speed th= an=20 they would do on a SCSI disk alone. There is no TempDB in PostgreSQL. This gives you a whole free RAID array t= o=20 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= =20 backed up to truncate it. Deal with it the way you would deal with an MSSQ= L=20 transaction log; i.e. on its own disk, if possible. However, you gain litt= le=20 by putting it on RAID other than failover safety; in fact, you might find= =20 that the xlog peforms better on a lone SCSI disk since even the best RAID 1= =20 will slow down data writes by up to 15%. Swap is not such a concern for PostgreSQL on Linux or BSD. With proper=20 database tuning and a GB of memory, you will never use the swap. Or to put= =20 it another way, if you're seeing regular hits on swap, you need to re-tune= =20 your database. Finally, you want to make absolutely sure that either the write-through cac= he=20 on each RAID controller is disabled in the BIOS, or that you have a battery= =20 back-up which you trust 100%. Otherwise, the caching done by the RAID=20 controllers will cancel completely the benefit of the Xlog for database=20 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=20 does XFS. Ext2 is probably faster than all of the above ... provided that= =20 your machine never has an unexpected shutdown. Then Ext2 is very bad ar= =20 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 databa= ses=20 on PostgreSQL. So if those databases are all related, you probably want t= o=20 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 wa= nt=20 to read up heavily on Postgres' security model, which has been significantl= y=20 improved for 7.3. --=20 Josh Berkus Aglio Database Solutions San Francisco