"Takayuki Tsunakawa" <[EMAIL PROTECTED]> wrote:
> > For pg, half RAM for shared_buffers is too much. The ratio is good for
> > other db software, that does not use the OS cache.
>
> What percentage of RAM is recommended for shared buffers in general?
> 40%? 30%? Or, is the general recommendation like "According to the
> amount of your data, this much RAM should be left for the kernel
> cache. But tha's the story on Linux. It may be different for other
> OSes."?
> Hmm, if it is so, it sounds hard for system designers/administrators
> to judge.
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().
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*.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
