Craig Ringer wrote:
Howard Cole wrote:
Unfortunately I am on a windows platform. Plus I am running windows
software raid so there is little tweaking allowed.
Don't write the possibility off too quickly. The driver may well accept
parameters for readahead settings etc, either through a custom
configuration applet (might be a separate download), the driver
properties interface in Device Manager, or through manual registry settings.
Hmm. It would probably be more economical to buy the bigger server!
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?
It generally is. You still need to think about how your concurrent
queries interact, though, as there are some operations that really must
be ordered. For example, if a query tries to UPDATE a record that a
concurrent query has already UPDATEd PostgreSQL will make the second
query block until the first one commits or rolls back. Doing otherwise
would cause exciting issues if the second query's UPDATE was in any way
dependent on the results of the first.
If you're only INSERTing and SELECTing then there's not much that'll
make queries interfere with each other with locks, unless you have
trigger-maintained materialized views, summary tables or the like
issuing UPDATEs behind the scenes.
You'll be pleased to know that changing the transaction to read only
(SET TRANSACTION READ ONLY)
as you suggested seemed to prevent the server from slowing to a halt.
The query still takes an age but not to the
detriment of all else. Many thanks for that tip.
Is it not possible that the query optimisation process should determine
that the query should be read only without
explicitly stating 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?
I don't think it'll change anything much, but it might tell you (by
causing a query to fail) if it's trying to write anything. This might
help you detect a point at which the queries are interacting that you
might not have expected, such as an UPDATE being issued within a trigger
or by a rule.
I do have a trigger-on-update on this table, and it is possible that an
insert is being done in parallel.
However my immediate problems appears to be solved. Special thanks to
Craig and Scott.
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
