|
Tuning for bulk loading: Make sure the Linux kernel
paramters in /proc/sys/vm related to the page cache are set well. Set swappiness to 0 or 1. Make sure you understand and
configure /proc/sys/vm/dirty_background_ratio and /proc/sys/vm/dirty_ratio
well. With enough RAM the default on
some kernel versions is way, way off (40% of RAM with dirty pages! yuck). http://www.westnet.com/~gsmith/content/linux-pdflush.htm If postgres is doing a lot of
caching for you you probably want dirty_ratio at 10% or less, and you'll want
the OS to start flushing to disk sooner rather than later. A
dirty_background_ratio of 3% with 24GB of RAM is 720MB -- a pretty big
buffer. I would not personally want this buffer to be larger than 5 seconds
of max write speed of the disk I/O. You'll need to tune your
background writer to be aggressive enough to actually write data fast enough so
that checkpoints don't suck, and tune your checkpoint size and settings as
well. Turn on checkpoint logging on the database and run tests while
looking at the output of those. Ideally, most of your batch writes have
made it to the OS before the checkpoint, and the OS has actually started moving
most of it to disk. If your settings are wrong, you'll have the
data buffered twice, and most or nearly all of it will be in memory when the
checkpoint happens, and the checkpoint will take a LONG time. The default
Linux settings + default postgres settings + large shared_buffers will almost
guarantee this situation for bulk loads. Both have to be configured with
complementary settings. If you have a large postgres buffer, the OS
buffer should be small and write more aggressively. If you have a small
postgres buffer, the OS can be more lazy and cache much more. From:
pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]
On Behalf Of Ryan Hansen Hey all, This may be more of a Linux question than a PG question, but
I’m wondering if any of you have successfully allocated more than 8 GB of
memory to PG before. I have a fairly robust server running Ubuntu Hardy Heron, 24
GB of memory, and I’ve tried to commit half the memory to PG’s
shared buffer, but it seems to fail. I’m setting the kernel shared
memory accordingly using sysctl, which seems to work fine, but when I set the
shared buffer in PG and restart the service, it fails if it’s above about
8 GB. I actually have it currently set at 6 GB. I don’t have the exact failure message handy, but I
can certainly get it if that helps. Mostly I’m just looking to know
if there’s any general reason why it would fail, some inherent kernel or
db limitation that I’m unaware of. If it matters, this DB is going to be hosting and processing
hundreds of GB and eventually TB of data, it’s a heavy read-write system,
not transactional processing, just a lot of data file parsing (python/bash) and
bulk loading. Obviously the disks get hit pretty hard already, so I want
to make the most of the large amount of available memory wherever
possible. So I’m trying to tune in that direction. Any info is appreciated. Thanks! |
