pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlong transaction
8+ messages / 3 participants
[nested] [flat]
* long transaction
@ 2008-07-18 15:34 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Sabin Coanda @ 2008-07-18 15:34 UTC (permalink / raw)
To: pgsql-performance
Hi there,
I have a script which includes 30000 called functions within a single
transaction.
At the beginning, the functions runs fast enough (about 60 ms each). In
time, it begins to run slower and slower (at final about one per 2 seconds).
I check the functions that runs slowly outside the script and they run
normally (60 ms each).
What is the problem ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-07-18 17:22 Lennin Caro <lennin.caro@yahoo.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Lennin Caro @ 2008-07-18 17:22 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Sabin Coanda <sabin.coanda@deuromedia.ro>
have you use VACUMM?
--- On Fri, 7/18/08, Sabin Coanda <sabin.coanda@deuromedia.ro> wrote:
> From: Sabin Coanda <sabin.coanda@deuromedia.ro>
> Subject: [PERFORM] long transaction
> To: pgsql-performance@postgresql.org
> Date: Friday, July 18, 2008, 3:34 PM
> Hi there,
>
> I have a script which includes 30000 called functions
> within a single
> transaction.
>
> At the beginning, the functions runs fast enough (about 60
> ms each). In
> time, it begins to run slower and slower (at final about
> one per 2 seconds).
>
> I check the functions that runs slowly outside the script
> and they run
> normally (60 ms each).
>
> What is the problem ?
>
> TIA,
> Sabin
>
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-11 06:53 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sabin Coanda @ 2008-08-11 06:53 UTC (permalink / raw)
To: pgsql-performance
No, I cannot use VACUUM inside the transaction, and it seems this is the
problem, although autovacuum is set.
However I checked the following scenario to find a solution. I call the
30000 statements without transaction. The performance it not changed. But
when I add VACUUM command after each 20 statement set, I got the linear
performance that I want. Unfortunatelly this is not possible inside a
transaction.
Do you know how could I solve my problem, keeping the 30000 statements
inside a single transaction ?
Sabin
"Lennin Caro" <lennin.caro@yahoo.com> wrote in message
news:120621.32315.qm@web59503.mail.ac4.yahoo.com...
> have you use VACUMM?
>
> --- On Fri, 7/18/08, Sabin Coanda <sabin.coanda@deuromedia.ro> wrote:
>
>> From: Sabin Coanda <sabin.coanda@deuromedia.ro>
>> Subject: [PERFORM] long transaction
>> To: pgsql-performance@postgresql.org
>> Date: Friday, July 18, 2008, 3:34 PM
>> Hi there,
>>
>> I have a script which includes 30000 called functions
>> within a single
>> transaction.
>>
>> At the beginning, the functions runs fast enough (about 60
>> ms each). In
>> time, it begins to run slower and slower (at final about
>> one per 2 seconds).
>>
>> I check the functions that runs slowly outside the script
>> and they run
>> normally (60 ms each).
>>
>> What is the problem ?
>>
>> TIA,
>> Sabin
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list
>> (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-11 19:03 Merlin Moncure <mmoncure@gmail.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2008-08-11 19:03 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
On Mon, Aug 11, 2008 at 2:53 AM, Sabin Coanda
<sabin.coanda@deuromedia.ro> wrote:
> No, I cannot use VACUUM inside the transaction, and it seems this is the
> problem, although autovacuum is set.
>
> However I checked the following scenario to find a solution. I call the
> 30000 statements without transaction. The performance it not changed. But
> when I add VACUUM command after each 20 statement set, I got the linear
> performance that I want. Unfortunatelly this is not possible inside a
> transaction.
>
> Do you know how could I solve my problem, keeping the 30000 statements
> inside a single transaction ?
long running transactions can be evil. is there a reason why this has
to run in a single transaction?
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-12 08:17 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sabin Coanda @ 2008-08-12 08:17 UTC (permalink / raw)
To: pgsql-performance
> long running transactions can be evil. is there a reason why this has
> to run in a single transaction?
This single transaction is used to import new information in a database. I
need it because the database cannot be disconected from the users, and the
whole new data has to be consistently. There are different constraints that
are checked during the import.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-12 16:56 Merlin Moncure <mmoncure@gmail.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2008-08-12 16:56 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
On Tue, Aug 12, 2008 at 4:17 AM, Sabin Coanda
<sabin.coanda@deuromedia.ro> wrote:
>> long running transactions can be evil. is there a reason why this has
>> to run in a single transaction?
>
> This single transaction is used to import new information in a database. I
> need it because the database cannot be disconected from the users, and the
> whole new data has to be consistently. There are different constraints that
> are checked during the import.
have you considered importing to a temporary 'holding' table with
copy, then doing 'big' sql statements on it to check constraints, etc?
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-13 06:07 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sabin Coanda @ 2008-08-13 06:07 UTC (permalink / raw)
To: pgsql-performance
>
> have you considered importing to a temporary 'holding' table with
> copy, then doing 'big' sql statements on it to check constraints, etc?
>
Yes I considered it, but the problem is the data is very tight related
between different tables and is important to keep the import order of each
entity into the database. With other words, the entity imprt serialization
is mandatory. In fact the import script doesn't keep just insert but also
delete and update for different entities. So copy is not enough. Also using
'big' sql statements cannot guarantee the import order.
Sabin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long transaction
@ 2008-08-18 00:04 Merlin Moncure <mmoncure@gmail.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2008-08-18 00:04 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
On Wed, Aug 13, 2008 at 2:07 AM, Sabin Coanda
<sabin.coanda@deuromedia.ro> wrote:
>>
>> have you considered importing to a temporary 'holding' table with
>> copy, then doing 'big' sql statements on it to check constraints, etc?
>>
>
> Yes I considered it, but the problem is the data is very tight related
> between different tables and is important to keep the import order of each
> entity into the database. With other words, the entity imprt serialization
> is mandatory. In fact the import script doesn't keep just insert but also
> delete and update for different entities. So copy is not enough. Also using
> 'big' sql statements cannot guarantee the import order.
More than likely, to solve your problem (outside of buying bigger box
or hacking fsync) is to rethink your import along the lines of what
I'm suggesting. You're welcome to give more specific details of
what/how your imports are running, in order to get more specific
advice.
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-08-18 00:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-18 15:34 long transaction Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-07-18 17:22 ` Lennin Caro <lennin.caro@yahoo.com>
2008-08-11 06:53 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-08-11 19:03 ` Merlin Moncure <mmoncure@gmail.com>
2008-08-12 08:17 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-08-12 16:56 ` Merlin Moncure <mmoncure@gmail.com>
2008-08-13 06:07 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2008-08-18 00:04 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox