Scott Marlowe wrote:
The problem is most likely you're I/O bound. If one query is hitting
a table it can pull in data (sequentially) at 40 to 80 megabytes per
second. Since most of your queries are small, they don't run into
each other a lot, so to speak. As soon as your big reporting query
hits it's likely hitting the drives much longer and getting in the way
of all the other queries.
Thanks for the input Scott. You are correct - I am IO bound, but only
for the query described. 99% of the time, my IO runs at 3% or less, even
during peak times, only this one query, which happens approximately 10
times a day grinds the system to a halt.
I am considering running this query in the background every couple of
minutes or so to force the tables/indexes into cache. Once the tables
are cached the query runs very quickly and there is no significant IO
impact.This is a bodge but hopefully should work.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
