Howard Cole wrote:
> If I reduce maintenance_work_mem
> then the database dump/restore is slower but there is less overall
> impact on the server.
There could be more impact, rather than less, if it forces a sort that'd
be done in memory out to disk instead. If you have dedicated storage on
separate spindles for disk sorts etc that might be OK, but it doesn't
sound like you do.
> Is there some equivalent parameter on the server
> to throttle general queries?
As far as I know there is no facility for this within PostgreSQL.
On a Linux (or maybe other UNIX too) machine you can use ionice to tell
the OS I/O scheduler to give that process lower priority for disk access
or rate limit it's disk access. Note that setting the CPU access
priority (`nice' level) will NOT help unless the server is CPU-limited,
and even then probably not much.
Maybe there is a similar facility to ionice for Windows, or the generic
process priority setting also affects disk I/O? You'll probably have to
do some research to find out.
I'm not sure there's any way to stop it pushing other useful data out of
shared_buffers, though. Anyone?
> It would be unfortunate if all queries
> slowed down a bit, but a better outcome than having the entire server
> hang for 40 seconds.
Are you sure there isn't a table locking issue involved - something your
batch query is doing that's causing other queries to block until that
transaction commits/rolls back? Check pg_locks:
SELECT * FROM pg_locks;
Also: Try setting the transaction to readonly before running it, and see
if it succeeds.
SET transaction_read_only = true;
This is probably a good thing to do anyway, as it *might* help the
database make better decisions.
--
Craig Ringer