Background Writer and performances

Hello List,

I’m using BenchmarkSQL to evaluate, characterize and optimize PostgreSQL
in transaction processing. I work with PostgreSQL 8.1.3 on RHEL4-AS,
Itanium-2 processor, 8GB RAM.

The database, generated via BenchmarkSQL and used, is a 200-warehouses
database and its size is about 20GB. The best performances are obtained
with 9 terminals.

My configuration is (I can’t do better…) :
- one file system created from a LUN of 34GB, in which all the
postmaster logs, WAL files and BenchmarkSQL reports are stored => this
corresponds to the disk “sdr” that appears in the charts
- one file system created from 4 LUNS of 33GB and a stripped logical
volume, on which the data (tables and index) are stored => this
corresponds to the disk “sdz”, “sdy”, “sdx”, and “sdw” that appears in
the charts

My question concerns the Background Writer.
The Background process sleeps during “bgwriter_delay” and when it wakes,
it scans the shared buffers looking for modified pages. Then it writes
these modified pages to disk and evicts those pages from the shared
buffers. By doing this, the Background Writer decreases the effects of a
CHECKPOINT.

I got performances problems with the Background Writer : if I let the
default value for the parameters “bgwriter_delay”,
“bgwriter_lru_percent”, “bgwriter_lru_maxpages”, “bgwriter_all_percent”,
“bgwriter_all_maxpages” or if I set them to the maximum value possible,
I don’t see significant differences on the disks activities, disks IO/s,
disks write throughput.

The tests have been done with one processor, during 10 minutes and with
a checkpoint each 5 minutes.

Here are my settings from the “postgresql.conf” file:
- max_connections = 100
- shared_buffers = 50000
- work_mem = 1024
- max_fsm_pages= 20000
- max_fsm_relations = 1000
- fsync = on
- wal_sync_method = fsync
- wal_buffers = 16
- checkpoint_segments = 125
- checkpoint_timeout = 300
- effective_cache_size = 218750
- defaults_statistics_target = 1000

**********************************
I would like to send charts to show you exactly what happens on the
server but, with the pictures, this e-mail is not posted on the mailing
list.
I can send charts to a personal e-mail adress if needed.
**********************************

The tests were made by setting :
- bgwriter_delay = 200
- bgwriter_lru_percent = 1.0
- bgwriter_lru_maxpages = 5
- bgwriter_all_percent = 0.333
- bgwriter_all_maxpages = 5

and then, the opposite :
- bgwriter_delay = 50
- bgwriter_lru_percent = 100
- bgwriter_lru_maxpages = 1000
- bgwriter_all_percent = 100
- bgwriter_all_maxpages = 1000

By comparing the charts, I can see that the checkpoints are less
expensive in term of Disk activity, IO/s and disk write throughput when
the parameters are set to the maximum values but I don’t not reach to
have constant disk IO/s, disk activity, disk write throughput before and
after a checkpoint. I was expecting to see more activity on the disks
during the bench (and not only a peak during the checkpoint) when the
parameters are set to the maximum values. Is it possible ?

Have you already experimented the Background Writer ? What results could
I obtain by setting properly the parameters “bgwriter_xxx” ? Could
somenone explain me how I can used it ?

Thank you very much for your help.

Regards,
Alexandra DANTE