[Please be careful to keep the list copied.] "Burgholzer, Robert (DEQ)" <Robert.Burgholzer@xxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
