request for tuning suggestions from PC Week Labs
request for tuning suggestions from PC Week Labs
От:
Timothy Dyck <Timothy_Dyck@zd.com>
Дата:
Hi all, I'm the database analyst for PC Week Labs and am comparing PostgreSQL with Inprise's InterBase (which will be open sourced later this year). As part of this project, I'm running a benchmark with a mix of OLTP and DSS queries in a set of various mixes and at a variety of user loads (up to 100 concurrent users). I'd like to get your tuning suggestions on the engine to make sure I am not missing anything. My test server is a departmental-type machine with two Pentium III 450MHz CPUs with 512MB of RAM running RedHat Linux 6.1. PGDATA is pointing to a RAID 5 array and the database is ~40 MB of data before indexing, and so will fit entirely into the db cache. The OS swapfile is not used at all. I will be using ODBC to query the database. 1. The biggest performance item I've seen in looking through the mailing lists is the fsync option. I want to leave this enabled as I don't think a transactional database should ever lose data. My understanding is that with it on PG checkpoints after every commit. Is there a way to let the log grow to a certain size before checkpointing? When fsync is off, how is data loss possible? 2. How do I move the log to a different spindle from the disks the database data is on? 3. Any other suggestions are much appreciated. Regards, Tim Dyck Senior Analyst, PC Week Labs timothy_dyck@zd.com
Re: [ADMIN] request for tuning suggestions from PC Week Labs
От:
Ragnar Kjørstad <postgres@ragnark.vestdata.no>
Дата:
On Sun, Jan 30, 2000 at 12:56:39AM -0500, Timothy Dyck wrote: > Hi all, I'm the database analyst for PC Week Labs and am comparing > PostgreSQL with Inprise's InterBase (which will be open sourced later this > year). As part of this project, I'm running a benchmark with a mix of OLTP > and DSS queries in a set of various mixes and at a variety of user loads > (up to 100 concurrent users). I'd like to get your tuning suggestions on > the engine to make sure I am not missing anything. > > My test server is a departmental-type machine with two Pentium III 450MHz > CPUs with 512MB of RAM running RedHat Linux 6.1. PGDATA is pointing to a > RAID 5 array and the database is ~40 MB of data before indexing, and so > will fit entirely into the db cache. The OS swapfile is not used at all. I > will be using ODBC to query the database. > > 1. The biggest performance item I've seen in looking through the mailing > lists is the fsync option. I want to leave this enabled as I don't think a > transactional database should ever lose data. My understanding is that > with it on PG checkpoints after every commit. Is there a way to let the > log grow to a certain size before checkpointing? When fsync is off, how is > data loss possible? > > 2. How do I move the log to a different spindle from the disks the > database data is on? > > 3. Any other suggestions are much appreciated. The speed of fsync is a filesystem issue. Reiserfs is generally somewhat faster than ext2, and has journaling. Unfortenately I think the log can only be used for metadata right now - but you shold check that with the developers. -- Ragnar Kjørstad