Re: Speed while runnning large transactions.

> On Thu, Sep 24, 2009 at 2: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".
>
> What is your transaction doing during this time?

It is a massive DB-update affecting probably 1.000.000 records with a lot
of roundtrips to the update-application during that.

>> 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 does
> select count(*) from pg_stat_activity where waiting;
> say?

There is no "particular query". No indication of locks it just seems that
having the transaction open (with a lot of changes hold in it) has an
impact on the general performance. Even without touching the same records.

>> What can I do to make the system handle other queries better?
>
> Really kinda depends on what your transaction is doing.

insert's, updates, delete..

--
Jesper