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> |
Subject: | Re: Load distributed checkpoint |
Date: | 2006-12-22 06:20:45 |
Message-ID: | 020801c72591$50b8c340$19527c0a@OPERAO |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Takayuki Tsunakawa | 2006-12-22 07:05:39 | Re: Load distributed checkpoint |
Previous Message | Greg Smith | 2006-12-22 06:15:47 | Re: Load distributed checkpoint |
From | Date | Subject | |
---|---|---|---|
Next Message | Takayuki Tsunakawa | 2006-12-22 07:05:39 | Re: Load distributed checkpoint |
Previous Message | Greg Smith | 2006-12-22 06:15:47 | Re: Load distributed checkpoint |