On Wed, Jul 14, 2010 at 01:07:48PM -0400, Burgholzer, Robert (DEQ) wrote:
- That said, the time to restore is explainable, but is there something in
- my tuning that is causing all of my memory to be eaten?
-
- We seem to have some undiagnosed issue whereby opening and closing large
- files on the system leaves a lot in the cache -- I am guessing that this
- is my culprit and NOT my pg tuning?
-
- Thanks again,
- r.b.
cat dumpfile | psql db_name
means you're going to load as much of dumpfile into memory as you can, and then
psql will read from memory and write to the DB.
if your file is 40GB then it's going to use all of the available memory to your
process (based on your ulimit, which is probably set to unlimited minus the OS
default saved for root ~5%)
try using pg_restore, psql -f (not sure of that would work, i don't do my restores
that way) or use a named pipe and write/read from that.
Dave
-
- Robert W. Burgholzer
- Surface Water Modeler
- Office of Water Supply and Planning
- Virginia Department of Environmental Quality
- rwburgholzer@deq.virginia.gov
- 804-698-4405
- Open Source Modeling Tools:
- http://sourceforge.net/projects/npsource/
-
- -----Original Message-----
- From: Joshua D. Drake [mailto:jd@commandprompt.com]
- Sent: Wednesday, July 14, 2010 12:58 PM
- To: Burgholzer, Robert (DEQ)
- Cc: pgsql-admin@postgresql.org
- Subject: Re: [ADMIN] proper tuning for restoring from pg_dump in 8.3.7
-
- On Wed, 2010-07-14 at 12:33 -0400, Burgholzer, Robert (DEQ) wrote:
- > I am restoring a fairly sizable database from a pg_dump file (COPY
- FROM
- > STDIN style of data) -- the pg_dump file is ~40G.
- >
- > My system has 4 cores, and 12G of RAM. I drop, then recreate the
- > database, and I do this restore via a: cat dumpfile | psql db_name.
- The
- > trouble is that my system free memory (according to top) goes to about
- > 60M, which causes all operations on the server to grind to a halt, and
- > this 40G restore will take a couple hours to complete.
- >
- > I noted that the restore file doesn't do anything inappropriate such
- as
- > creating indices BEFORE adding the data or anything - thus I can only
- > suspect that my trouble has to do with performance tuning ineptitude
- in
- > postgresql.conf.
-
- The best you will get is ~ 22G an hour. If this is a backup you can take
- again in a different format, use -Fc and then use parallel restore. Even
- if half of the database is one table, you will still knock the restore
- time by 50% or so.
-
- Joshua D. Drake
-
-
-
- --
- PostgreSQL.org Major Contributor
- Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
- Consulting, Training, Support, Custom Development, Engineering
-
-
- --
- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
- To make changes to your subscription:
- http://www.postgresql.org/mailpref/pgsql-admin