On Tue, Feb 28, 2012 at 2:41 PM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote: > On Tue, Feb 28, 2012 at 5:48 PM, Stefan Keller <sfkeller@xxxxxxxxx> wrote: >> P.S. And yes, the database is aka 'read-only' and truncated and >> re-populated from scratch every night. fsync is off so I don't care >> about ACID. After the indexes on name, hstore and geometry are >> generated I do a VACUUM FULL FREEZE. The current installation is a >> virtual machine with 4GB memory and the filesystem is "read/write". >> The future machine will be a pizza box with 72GB memory. > > I don't get this. Something's wrong. > > 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. > > So your data is less than 4GB, but then you'll be throwing a 72GB > server? It's either tremendous overkill, or your data simply isn't > less than 4GB. > > It's quite possible the vacuum full is thrashing your disk cache due > to maintainance_work_mem. You can overcome this issue with the tar > trick, which is more easily performed as: > > tar cf /dev/null $PG_DATA/base But on many implementations, that will not work. tar detects the output is going to the bit bucket, and so doesn't bother to actually read the data. ... > > Another option is to issue a simple vacuum after the vacuum full. > Simple vacuum will just scan the tables and indices, I'm hoping doing > nothing since the vacuum full will have cleaned everything already, > but loading everything both in the OS cache and into shared_buffers. Doesn't it use a ring buffer strategy, so it would load to OS, but probably not to shared_buffers? Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
