terrible performance in 6.1beta4

[Michal Mertl]

Miguel wrote:

.. "postgresql is slow for me"

and others wrote:

... "you may have to dedicate more memory to it"

Sorry for the above, but I haven't seen the beginning of the thread.

I don't know PostgreSQL much but I also has been recently running quite
simple program on one quite large table (2 milions of rows) and was
trying to tune it.

I haven't benchmarked anything (only that it was several orders of
magnitude faster to run it on much bigger machine (2x2.8G Xeon, 2GB RAM,
10/15kRPM SCSI disks vs. 1xP4 Celeron 2.4G, 256MB RAM and an IDE disk)).

I did the tuning after reading a little on the Net but the
_POSSIBLY_IMPORTANT_ message I found was that PostgreSQL is a little
different to other DB engines that it normally doesn't eat comparatively
much memory even on loaded system because the developers believe that
when a data set is much bigger than available memory (usual for big
databases) it doesn't make sense to cache much in the DB engine because
the OS can do it too and you can save memory...

Above is the core of what I wanted to say - PostgreSQL process size was
quite small (~100MB) on the bigger machine yet I think I tuned it
according to the recommendations and that it is probably expected and
correct with PostgreSQL.

After comparing my config file to yours I see some differences:

I have set some limits lower than you (shared_buffers 65536 vs. 10000,
work_mem 83886 vs. 10000) and one higher (max_fsm_pages 20000 vs.
100000).

I suspect that the main difference in our configs is fsync setting
though. I have "fsync = off" and you the default (on). This may be very
important difference, maybe similar to MySQL's
innodb_flush_log_at_trx_commit (which, when set to 2, raises the
performance of MySQL with InnoDB on FreeBSD significantly).

I seem to remember that I read somewhere that fsync is pretty expensive
on FreeBSD in comparison to Linux (because they cheat, as usual :-)) so
maybe you can give it a try on your PostgreSQL too. It probably is a
little dangerous though.


HTH

Michal