Search Postgresql Archives

Background Writer and performances

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux