Craig Ringer 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.
Unfortunately I am on a windows platform. Plus I am running windows
software raid so there is little tweaking allowed.
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.
I didn't even know you could do that! I can do this on a system wide
basis for all of my read only queries so I shall see if it makes a
difference. I'll check the locking issues but I was under the impression
that postgres was excellent for this? One of the reqular, smaller
queries does however use the same table so I shall check if this is
having a major impact. - If I set them both to read-only then that might
have the desired impact? Perhaps this is something arising from the
MVCC? If so is that something that can be switched off?
On another point, I tried setting up a scheduled query to force the
tables into cache and this had some strange effects... As I mentioned in
an earlier post I have multiple databases running on the same server so
I ran a select queries for all of them. This speeded up the queries as
expected with the cached data. However, two of the databases seemingly
refused to speed up - They always seemed to take 30+ seconds (again
eating up the machine IO resource). Even if I ran the query-as-a-job on
only one of these databases, it didn't seem to speed up. Perhaps there
is something wrong with these databases? The explain analyse seems to
come back with identical plans on these. Any ideas? (p.s. I am running
autovacuum)
Howard Cole
www.selestial.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
