(This was originally posted on the Admin list where it was suggested it
should be posted to pgsql-hackers.)
Hi all, I'm the database analyst for PC Week and am comparing
PostgreSQL with Inprise's InterBase (which will be open sourced later this
year). I wrote the features list that is circulating around the list right
now by
Marc Fournier.
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. I compiled with -m486 and am
using a page size of 2KB instead of 8KB as the benchmark is mostly
OLTP-type queries.
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. Can I move the log to a different spindle from the disks the
database data is on? The manuals seem to indicate the log is actually
part of the datafile itself, which would imply it can't be moved
elsewhere.
3. Any other suggestions are much appreciated.
Regards,
Tim Dyck
Senior Analyst, PC Week Labs
timothy_dyck@zd.com
519-746-4241