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