Re: PostgreSQL strugling during high load

Actually, that solution didn't work so well. Even very small delays
in the loop caused the entire loop to perform too slowly to be useful
in the production environment. I ended up producing a small patch out
of it :P, but we ended up using pgpool to reduce connections from
another part of the app, which made the pg_autovacuum spikes less
troublesome overall.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On May 15, 2005, at 9:26 PM, Matthew T. O'Connor wrote:

> Mindaugas Riauba wrote:
>
>
>>> The "vacuum cost" parameters can be adjusted to make vacuums fired
>>> by pg_autovacuum less of a burden.  I haven't got any specific
>>> numbers
>>> to suggest, but perhaps someone else does.
>>>
>>>
>>
>>  It looks like that not only vacuum causes our problems. vacuum_cost
>> seems to lower vacuum impact but we are still noticing slow
>> queries "storm".
>> We are logging queries that takes >2000ms to process.
>>  And there is quiet periods and then suddenly 30+ slow queries
>> appears in
>> log within the same second. What else could cause such behaviour?
>> WAL log
>> switch? One WAL file seems to last <1 minute.
>>
>>
>
> How long are these quite periods?  Do the "strom" periods
> correspond to pg_autovacuum loops?  I have heard from one person
> who had LOTS of databases and tables that caused the pg_autovacuum
> to create a noticable load just updateing all its stats.  The
> solution in that case was to add a small delay insidet the inner
> pg_autovacuum loop.