Re: Can anyone explain this pgbench results? - Mailing list pgsql-performance

Hi Michael,

Michael Fuhr wrote:
>>> Have you tweaked postgresql.conf at all?  If so, what non-default
>>> settings are you using?
>>
>> Yes, I have tweaked the following settings:
>>
>> shared_buffers = 40000
>> work_mem = 512000
>> maintenance_work_mem = 512000
>> max_fsm_pages = 40000
>> effective_cache_size = 131072
>
> Are you sure you need work_mem that high?  How did you decide on
> that value?
I have used http://www.powerpostgresql.com/Downloads/annotated_conf_80.html , expecting that the differences between
8.0and 8.1 do not invalidate the recommendations. I have checked with (some) of my (large) queries and adjusted upward
untillI had no temp files in the PGDATA/base/DB_OID/pgsql_tmp. (The warning about 

> Are all other settings at their defaults?
Yep.

> No changes to the write ahead log (WAL) or background writer (bgwriter) settings?
No, because the forementioned document explicitely states that it has recomendations on these subjects.

> What version of PostgreSQL are you running?  The paths in your
> original message suggest 8.1.x.
Debian's Ecth 8.1.0-3

> A checkpoint updates the database files with the data from the
> write-ahead log; you're seeing those writes to the database partition.
> The postmaster does checkpoints every checkpoint_timeout seconds
> (default 300) or every checkpoint_segment log segments (default 3);
> it also uses a background writer to trickle pages to the database
> files between checkpoints so the checkpoints don't have as much
> work to do.  I've been wondering if your pgbench runs are being
> affected by that background activity; the fact that you get
> consistently good performance after forcing a checkpoint suggests
> that that might be the case.
OK, thanks.

To be sure if I understand it correctly:

1. Every update/insert is first written to a WAL log file which is in the PGDATA/pg_xlog directory.
2. Routinely the background writer than writes the changes to the PGDATA/base/DB_OID/ directory.
2. Postmaster forces after 300 secs or if the log segments are full (which ever comes first?) a checkpoint so that the
WALlog file are empty ( I assume that that are the changes the background writer has not written yet since the last
forcedcheckpont?). 

> If you run pgbench several times without intervening checkpoints,
> do your postmaster logs have any messages like "checkpoints are
> occurring too frequently"?  It might be useful to increase
> checkpoint_warning up to the value of checkpoint_timeout and then
> see if you get any such messages during pgbench runs.  If checkpoints
> are happening a lot more often than every checkpoint_timeout seconds
> then try increasing checkpoint_segments (assuming you have the disk
> space).  After doing so, restart the database and run pgbench several
> times without intervening checkpoints and see if performance is
> more consistent.
I will try that this day.

> Note that tuning PostgreSQL for pgbench performance might be
> irrelevant for your actual needs unless your usage patterns happen
> to resemble what pgbench does.

The advantage of using pgbench is a repeatable short command that leads to something that is showing in actual real
worldusage. 

My problem is with the raw performance of my disk array (3Ware 9500S-8 SATA RAID5 controller with 5 disks). I am having
*very*serious performance problems if I do large updates on my databases. E.g. an update of 1 (boolean) column in a
table(update prototype.customers set deleted = false) that has 368915 records last forever (> 3500 secs ). The only
noticabledisk activity during such an update is on the disk/partition that has the PGDATA/base/DB_OID/ directory
(/dev/sdc,the 3Ware 9800S-8 RAID 5 array). There is *no* noticable disk activity on the disk/partition that hase the
PGDATA/pg_xlogdirectory (/dev/sdb, on a Sil 3114 on-board SAT controller). The throughtput during the update is ~ 2
MB/sec.The thoughtput during a large file copy or running bonnie (a benchmark) is > 40 MB/sec. My primary goal is to
understandthe differences ( and than sue the guilty ones ;-)), and than maybe either learn to live with it or find a
solution.The number of write operations/sec during the update is ~ 2000 /sec. I suspect that the RAID card cannot
handlea lot of small write operations (with fsync?) in a short time without performance penalty (and yes, the write
cacheon the controller is enabled). 


Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl