Re: proper tuning for restoring from pg_dump in 8.3.7

[Please be careful to keep the list copied.]

"Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
wrote:

>> Exactly what are you seeing which causes you to say that?
>
> An httpd request that is filled in fractions of a second now takes
> 10-30 seconds to complete - this being a page that calls a php
> script, and needs a database connection (even if it uses only a
> minimal query).

I think I'm starting to see the issue -- you have other databases
in use for production on this system while you are loading this
database, and *those* are the ones where you see the performance
problems?

> Using the following (on recommendation of Kevin/David):
>    psql -1 -f dumpfile db_name
>
> I now get the swap use, looks like there really is NOT much use:
>              total       used       free     shared    buffers
> cached
> Mem:         11874      11809         65          0          5
> 3746
> -/+ buffers/cache:       8057       3817
> Swap:        99999         70      99929
>
> Just now, the psql approach of running the script has seemed to
> ameliorate much of the performance degradataion -- except for
> scripts that employ multiple database reads on another database
> (that is not being reloaded) -- those are taking 2-3 times their
> normal, but nothing like before.  Maybe it WAS trying to load 40G
> of file into memory then dumping into pg that was causing the
> hassle.
>
> Thanks a bunch for everyone leading me through this process, I am
> sure that I am far from knowing what is going on here, but at
> least I learned a few tricks,

If I'm understanding your real problem now, the psql -1 switch will
help because it will allow the COPY statements to run without
WAL-logging, which will cut not only total disk output, but the need
to sync the cached data to disk.  The options I suggested be turned
off just for the load process will also help with that, although
they are not safe if the other databases are in the same PostgreSQL
cluster (i.e., different databases running in the same database
service).

If the database is on its own PostgreSQL cluster I would be very
tempted to restore the database on a separate (but compatible)
machine and then rsync it back with --bwlimit to limit the impact on
the other database(s).

If you still have problem, please post again, but you might get more
useful advice if you provide more details about your environment and
the actual problem you're trying to solve.  The data in cache wasn't
a problem to solve, and I suspect that the time to restore the
database wasn't really the problem, either.  It seems as the the
actual problem was the impact of the restore on other databases
running on the same server.  I don't remember seeing some
information which would be useful, like how many drives are configured
in what RAID(s) using what controller(s), and what other databases
are running in what postgresql clusters.

-Kevin