From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Robert (DEQ) Burgholzer <Robert.Burgholzer@deq.virginia.gov>
To: pgsql-admin@postgresql.org
Subject: Re: proper tuning for restoring from pg_dump in 8.3.7
Date: Wed, 14 Jul 2010 13:06:39 -0500
Message-ID: <4C3DB65F02000025000335BC@gw.wicourts.gov> (raw)
In-Reply-To: <B6C40D17104BEF47B1CB85623CDFAC63895EFE@COVMSGCES-EMB13.cov.virginia.gov>
References: <B6C40D17104BEF47B1CB85623CDFAC63895DE6@COVMSGCES-EMB13.cov.virginia.gov>
<1279126650.22775.9.camel@jd-desktop.unknown.charter.com>
<B6C40D17104BEF47B1CB85623CDFAC63895E51@COVMSGCES-EMB13.cov.virginia.gov>
<8594.1279127977@sss.pgh.pa.us>
<B6C40D17104BEF47B1CB85623CDFAC63895EFE@COVMSGCES-EMB13.cov.virginia.gov>
"Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
wrote:
> does this "swap" number seem excessive (looks like ~100 G to me):
> Swap: 102399992k total
That's probably how much disk space you have set aside for swapping.
What matters is how much of that is *used*. For example:
kgrittn@PLATO:/home/ccsa> free -m
total used free shared buffers
cached
Mem: 64446 64144 301 0 46
59349
-/+ buffers/cache: 4748 59697
Swap: 1027 53 973
We've got 1GB available for swapping, and have 53MB swapped. That
consists of some OS libraries we just don't use which eventually got
swapped out and never found their way back to RAM for want of usage.
(Which is fine with me, as it leaves 53MB more for caching.)
>> Cached data is not a problem. Don't worry about that.
> As for my concerns about the cache'ing of files, we have found
> that we can reclaim our servers performance by doing the
> following:
> sync
> echo 1 > /proc/sys/vm/drop_caches
>
> But I am really squeamish about this - it just seems like
> something is wrong with this approach.
Dropping caches is just going to cause unnecessary disk reads when
you eventually try to re-read what were cached sectors, hurting
performance. What, exactly, caused you to think it made something
better? Might it have coincided with the completion of sync?
By the way, what have you got for drives and controllers, and how
are they arranged?
One last tip: when your restore is complete, you might want to run
VACUUM FREEZE ANALYZE in the database. Otherwise you will be
rewriting rows to set hint bits as you read them, and at some point
in the future PostgreSQL will start a VACUUM of all still-existent
tuples from the load in order to FREEZE them before transaction ID
wraparound.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, Robert.Burgholzer@deq.virginia.gov
Subject: Re: proper tuning for restoring from pg_dump in 8.3.7
In-Reply-To: <4C3DB65F02000025000335BC@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox