Re: Load distributed checkpoint

From: "Takayuki Tsunakawa" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
> (5) (4) + /proc/sys/vm/dirty* tuning
> dirty_background_ratio is changed from 10 to 1, and dirty_ratio is
> changed from 40 to 4.
>
> 308 349 84 349 84

Sorry, I forgot to include the result when using Itagaki-san's patch.
The patch showd the following tps for case (5).

323 350 340 59 225

The best response time was 4 msec, and the worst one was 16 seconds.

----- Original Message -----
From: "Takayuki Tsunakawa" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: "ITAGAKI Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Friday, December 22, 2006 3:20 PM
Subject: Re: [HACKERS] Load distributed checkpoint

> Hello, Itagaki-san,
>
> Thank you for an interesting piece of information.
>
> From: "ITAGAKI Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
>> If you use linux, try the following settings:
>> 1. Decrease /proc/sys/vm/dirty_ratio and dirty_background_ratio.
>> 2. Increase wal_buffers to redule WAL flushing.
>> 3. Set wal_sync_method to open_sync; O_SYNC is faster then
fsync().
>> 4. Separate data and WAL files into different partitions or disks.
>>
>> I suppose 1 is important for you, because kernel will not write
> dirty
>> buffers until 10% of buffers become dirty in default settings.
>> You have large memory (8GB), but small data set (800MB). So kernel
>> almost never writes buffers not in checkpoints. Accumulate dirty
> buffers
>> are written at a burst in fsync().
>
> I'll show the results of this tuning to share information with
people
> who don't have experience of this kind.
> The numbers shown below are the tps when running "pgbench -c16 -t100
> postgres" five times in succession.
>
> (1) Default case(this is show again for comparison and reminder)
> The bgwriter_* and checkpoint_* are set to those defaults.
> wal_buffers and wal_sync_method are also set to those defaults (64kB
> and fdatasync respectively.)
>
> 235 80 226 77 240
>
>
> (2) Default + WAL 1MB case
> The configuration is the same as case (1) except that wal_buffers is
> set to 1024kB.
>
> 302 328 82 330 85
>
> This is better improvement than I expected.
>
>
> (3) Default + wal_sync_method=open_sync case
> The configuration is the same as case (1) except that
wal_sync_method
> is set to open_sync.
>
> 162 67 176 67 164
>
> Too bad compared to case (2). Do you know the reason?
>
>
> (4) (2)+(3) case
>
> 322 350 85 321 84
>
> This is good, too.
>
>
> (5) (4) + /proc/sys/vm/dirty* tuning
> dirty_background_ratio is changed from 10 to 1, and dirty_ratio is
> changed from 40 to 4.
>
> 308 349 84 349 84
>
> The tuning of kernel cache doesn't appear to bring performance
> improvement in my env. The kernel still waits too long before it
> starts flushing dirty buffers because the cache is large? If so,
> increasingly available RAM may cause trouble more frequently in the
> near future. Do the dirty_*_ratio accept values less than 1?
>
> BTW, in case (1), the best response time of a transaction was 6
> milliseconds. On the other hand, the worst response time was 13
> seconds.
>
>
>> We would be happy if we would be free from a difficult combination
>> of tuning. If you have *idea for improvements*, please suggest it.
>> I think we've already understood *problem itself*.
>
> I agree with you. Let's make the ideas more concrete, doing some
> experimentations.
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>