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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
