Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group