On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller@xxxxxxxxx> wrote: > 2012/2/28 Claudio Freire <klaussfreire@xxxxxxxxx>: >> >> In the OP, you say "There is enough main memory to hold all table >> contents.". I'm assuming, there you refer to your current system, with >> 4GB memory. > > Sorry for the confusion: I'm doing these tests on this machine with > one table (osm_point) and one country. This table has a size of 2.6GB > and 10 million tuples. The other machine has to deal with at least 5 > tables in total and will be hold more than one country plus routing > etc.. What is your shared_buffers set to? 2.6GB is uncomfortably close to 4GB, considering the computer has other things it needs to use memory for as well. A problem is that often the shared_buffers and the OS cache end up being basically copies of one another, rather than complementing each other. So on read-only applications, the actually useful size of the total cache turns out to be max(shared_buffers, RAM - 2*shared_buffers - unknown_overhead). So one choice is setting shared_buffers low (<0.5GB) and let the OS cache be your main cache. Advantages of this are that the OS cache survives PG server restarts, gets populated even by sequential scans, and can be pre-warmed by the tar trick. Disadvantages are that pages can be driven out of the OS cache by non-PG related activity, which can be hard to monitor and control. Also, there is some small cost to constantly transferring data from OS cache to PG cache, but in your case I htink that would be negligible. The other choice is setting shared_buffers high (>3GB) and having it be your main cache. The advantage is that non-PG activity generally won't drive it out. The disadvantages are that it is hard to pre-populate as the tar trick won't work, and neither will sequential scans on tables due to the ring buffer. Actually, the tar trick might work somewhat if applied either shortly before or shortly after the database is started. If the database starts out not using its full allotment of memory, the OS will use it for cache, and you can pre-populate that cache. Then as the database runs, the PG cache gets larger by copying needed data from the OS cache into it. As the PG cache grows, pages need to get evicted from OS cache to make room for it. Ideally, the pages evicted from the OS cache would be the ones just copied into PG, but the kernel is not aware of that. So the whole thing is rather sub-optimal. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
