Re: proper tuning for restoring from pg_dump in 8.3.7

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Robert (DEQ) Burgholzer" <Robert(dot)Burgholzer(at)deq(dot)virginia(dot)gov>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: proper tuning for restoring from pg_dump in 8.3.7
Date: 2010-07-14 18:06:39
Message-ID: 4C3DB65F02000025000335BC@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"Burgholzer, Robert (DEQ)" <Robert(dot)Burgholzer(at)deq(dot)virginia(dot)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(at)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2010-07-14 18:16:48 Re: proper tuning for restoring from pg_dump in 8.3.7
Previous Message Kris Deugau 2010-07-14 18:00:28 Re: proper tuning for restoring from pg_dump in 8.3.7