Re: Speed while runnning large transactions.

2009/9/24 <jesper(at)krogh(dot)cc>:
>> On Thu, Sep 24, 2009 at 9:27 AM, <jesper(at)krogh(dot)cc> wrote:
>>
>>> Hi.
>>>
>>> I have a transaction running at the database for around 20 hours ..
>>> still
>>> isn't done. But during the last hours it has come to the point where it
>>> really hurts performance of "other queries".
>>>
>>> Given pg_stat_activity output there seems to be no locks interfering but
>>> the overall cpu-usage of all queries continue to rise. iowait numbers
>>> are
>>> also very low.
>>>
>>> What can I do to make the system handle other queries better?
>>>
>>> show us explain from the query(s).
>> use select * from pg_stat_activity to find out the state query is in, and
>> perhaps which one of the queries it really is.
>
> I'm actively monitoring pg_stat_activity for potential problems but the
> thread is spending most of the time in the application side. The
> transaction is holding a large set of inserts/update and delete for the
> DB.

I don't think there's much you can do about this. Your other
transactions are probably slowing down due to accumulation of dead row
versions that VACUUM can't collect because they are still visible to
your long-running transaction.

You might need to think about replicating some of your data to a
reporting server.

...Robert