pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge transaction problem
9+ messages / 3 participants
[nested] [flat]
* Large transaction problem
@ 2004-11-08 22:58 Paul B. Anderson <paul@pnlassociates.com>
2004-11-08 23:19 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Paul B. Anderson @ 2004-11-08 22:58 UTC (permalink / raw)
To: pgsql-admin
I'm trying to move data from an Oracle database into postgresql 7.4.3.
I have a program that is extracting data and writing it to an sql script
as a long series of inserts (20,000 at a time). When I run these
scripts using psql, I occassionally get a problem that is reported as
ERROR: canceling query due to user request
And then it won't clear. If I quit psql and start it again, every sql
statement on that database gives this error after 30 seconds (approx) of
hanging. The only way to clear this is to restart the postgresql
backend and then try vacuuming a couple of times. It eventually clears
in this manner.
What am I doing wrong? I increased the WAL segments from 3 to 30 but
this did not get rid of the problem. Is there a limit on the size of a
transaction?
Thanks.
Paul
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-08 22:58 Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
@ 2004-11-08 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-11-08 23:19 UTC (permalink / raw)
To: Paul B. Anderson <paul@pnlassociates.com>; +Cc: pgsql-admin
"Paul B. Anderson" <paul@pnlassociates.com> writes:
> I'm trying to move data from an Oracle database into postgresql 7.4.3.
> I have a program that is extracting data and writing it to an sql script
> as a long series of inserts (20,000 at a time). When I run these
> scripts using psql, I occassionally get a problem that is reported as
> ERROR: canceling query due to user request
> And then it won't clear. If I quit psql and start it again, every sql
> statement on that database gives this error after 30 seconds (approx) of
> hanging.
It sounds to me like you are starting the postmaster under finite ulimit
settings --- could be either CPU or filesize limits.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
@ 2004-11-11 19:50 Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Paul B. Anderson @ 2004-11-11 19:50 UTC (permalink / raw)
To: pgsql-admin
I think I've solved this problem. It involved the vacuumdb command
rather than the large insert. I found this out when a nightly vacuumdb
script reported the problem even though no records had been loaded at all.
My postgresql.conf file had a 10 second timeout and the large database
required more than 10 seconds for the vacuum. It seems that this left
postmaster and/or the particular database in a state where any SQL
against that database gave the same error response about being canceled
by the user.
I'm guessing postgresql had left some internal state set for the
database and was trying to complete the vacuum first. Anyway, I set
the statement_timeout = 0 and the problem isn't recurring.
Paul
"Paul B. Anderson" <paul@pnlassociates.com> writes:
>> Ulimit says
>
>
>> # su - postgres
>> -bash-2.05b$ ulimit -a
>> core file size (blocks, -c) 0
>> data seg size (kbytes, -d) unlimited
>> file size (blocks, -f) unlimited
>> max locked memory (kbytes, -l) 4
>> max memory size (kbytes, -m) unlimited
>> open files (-n) 1024
>> pipe size (512 bytes, -p) 8
>> stack size (kbytes, -s) 10240
>> cpu time (seconds, -t) unlimited
>> max user processes (-u) 3072
>> virtual memory (kbytes, -v) unlimited
>
>
>> None of the actual limits seems to be a problem. I can't seem to change
>> max locked memory using ulimit but I'll try increasing stack size.
>> Could that be involved?
>
>
No, the 'file size' and 'cpu time' limits are the ones I'd expect to
cause this sort of behavior.
One possible gotcha is that depending on how you start the postmaster,
it might be living under different ulimit settings than what you see
interactively.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
@ 2004-11-11 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-11-11 21:07 UTC (permalink / raw)
To: Paul B. Anderson <paul@pnlassociates.com>; +Cc: pgsql-admin
"Paul B. Anderson" <paul@pnlassociates.com> writes:
> My postgresql.conf file had a 10 second timeout and the large database
> required more than 10 seconds for the vacuum. It seems that this left
> postmaster and/or the particular database in a state where any SQL
> against that database gave the same error response about being canceled
> by the user.
Hmm, I couldn't duplicate this. I thought maybe the vacuum wasn't
releasing some lock after it failed, but there's no sign of such a
problem.
Are you sure it isn't just that all your queries were running into the
timeout?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-11-11 22:14 ` Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 22:48 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2004-11-12 14:33 ` Re: Large transaction problem Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 9+ messages in thread
From: Paul B. Anderson @ 2004-11-11 22:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
After I restart postgresql, I execute vacuum several times in sql until
it succeeds. Then, everything else works OK again.
After I clear the problem,
select count(*) from archive
takes a second but, before the problem is cleared, it takes about 30
seconds and then gives the canceled by user response.
The vacuum was run using the vacuumdb command rather than from psql. It
was in a cron script running under user postgres. There was a .pgpass
file. The command was
/usr/bin/vacuumdb --all --full --analyze
This is postgresql on Red Hat Enterprise Linux 3 (ES) from RPMs
postgresql-7.4.3-2PGDG, etc.
Thanks.
Paul
Tom Lane wrote:
>"Paul B. Anderson" <paul@pnlassociates.com> writes:
>
>
>>My postgresql.conf file had a 10 second timeout and the large database
>>required more than 10 seconds for the vacuum. It seems that this left
>>postmaster and/or the particular database in a state where any SQL
>>against that database gave the same error response about being canceled
>>by the user.
>>
>>
>
>Hmm, I couldn't duplicate this. I thought maybe the vacuum wasn't
>releasing some lock after it failed, but there's no sign of such a
>problem.
>
>Are you sure it isn't just that all your queries were running into the
>timeout?
>
> regards, tom lane
>
>.
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
@ 2004-11-11 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-11-11 22:48 UTC (permalink / raw)
To: Paul B. Anderson <paul@pnlassociates.com>; +Cc: pgsql-admin
"Paul B. Anderson" <paul@pnlassociates.com> writes:
> After I restart postgresql, I execute vacuum several times in sql until
> it succeeds. Then, everything else works OK again.
> After I clear the problem,
> select count(*) from archive
> takes a second but, before the problem is cleared, it takes about 30
> seconds and then gives the canceled by user response.
This all sounds exactly like an overly enthusiastic statement_timeout.
What you are doing to "clear the problem" is merely to populate the disk
cache sufficiently that your query finishes before timing out.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
@ 2004-11-12 14:33 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-12 15:30 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2004-11-12 14:33 UTC (permalink / raw)
To: pgsql-admin
On Thu, Nov 11, 2004 at 05:14:50PM -0500, Paul B. Anderson wrote:
>
> /usr/bin/vacuumdb --all --full --analyze
^^^^^
You do realise that takes an exclusive lock, right? Why are you
doing VACUUM FULL? If your FSM is set right, you really shouldn't
need it for most cases.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-12 14:33 ` Re: Large transaction problem Andrew Sullivan <ajs@crankycanuck.ca>
@ 2004-11-12 15:30 ` Paul B. Anderson <paul@pnlassociates.com>
2004-11-12 15:57 ` Re: Large transaction problem Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Paul B. Anderson @ 2004-11-12 15:30 UTC (permalink / raw)
To: pgsql-admin
When the vacuum full timed out, could the exclusive lock have been left
set, leaving the database in an unusable state?
BTW, thanks. I removed the --full option.
Paul
Andrew Sullivan wrote:
>On Thu, Nov 11, 2004 at 05:14:50PM -0500, Paul B. Anderson wrote:
>
>
>> /usr/bin/vacuumdb --all --full --analyze
>>
>>
> ^^^^^
>
>You do realise that takes an exclusive lock, right? Why are you
>doing VACUUM FULL? If your FSM is set right, you really shouldn't
>need it for most cases.
>
>A
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Large transaction problem
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Re: Large transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-12 14:33 ` Re: Large transaction problem Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-12 15:30 ` Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
@ 2004-11-12 15:57 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2004-11-12 15:57 UTC (permalink / raw)
To: pgsql-admin
On Fri, Nov 12, 2004 at 10:30:27AM -0500, Paul B. Anderson wrote:
> When the vacuum full timed out, could the exclusive lock have been left
> set, leaving the database in an unusable state?
No, but cancelling a VACUUM FULL takes a long time, in my experience,
and so the lock hangs around for quite a while.
> BTW, thanks. I removed the --full option.
My bet is that will help matters.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-11-12 15:57 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-08 22:58 Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-08 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 19:50 Re: Large transaction problem Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 22:14 ` Paul B. Anderson <paul@pnlassociates.com>
2004-11-11 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-12 14:33 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-12 15:30 ` Paul B. Anderson <paul@pnlassociates.com>
2004-11-12 15:57 ` Andrew Sullivan <ajs@crankycanuck.ca>
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