Search Postgresql Archives

Re: How to reduce impact of a query.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Nov 17, 2008 at 9:36 AM, Howard Cole <howardnews@xxxxxxxxxxxxx> wrote:
> 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.

Simpler to probably just do:

select count(*) from sometable;

to force it into cache.  Buy some more memory and it'll likely stay in
memory without such tricks.

Best of luck on this.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux