Обсуждение: Large transaction problem
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
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
"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
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
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
"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
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:
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 .
"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
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
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:
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
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
Hi all
I have a table in y database that have 8,000,000 of rows, when I execut a
query on this table, the answuer is very slow.
I have a index in this table by datem, my table is
Column | Type | Modifiers
---------+---------------------------+-----------
nombre | character varying(15) |
mensaje | character varying(250) |
nombre_env | character varying(100) |
cel_env | character varying(15) |
fecha | date |
hora | time(0) without time zone |
Indexes: correo_fecha
Myindex is:
Index "correo_fecha"
Column | Type
--------+------
fecha | date
btree
My query is
select EXTRACT(MONTH FROM M.fecha) as ho,count(*) as tot from correo M
where EXTRACT(YEAR FROM M.fecha)='2004' group by EXTRACT(MONTH FROM
M.fecha);
When I executed a EXPLAIN ANALIZE, I hace the following Answer
Aggregate (cost=122439.31..122558.36 rows=2381 width=4) (actual
time=64626.46..76021.93 rows=11 loops=1)
-> Group (cost=122439.31..122498.84 rows=23809 width=4) (actual
time=63951.10..73332.27 rows=4177209 loops=1)
-> Sort (cost=122439.31..122439.31 rows=23809 width=4) (actual
time=63951.09..67240.94 rows=4177209 loops=1)
-> Seq Scan on web_sms m (cost=0.00..120708.48 rows=23809
width=4) (actual time=0.30..55077.31 rows=4177209 loops=1)
Total runtime: 76069.22 msec
How can I do to acceletate the answer?
Thanks.
--- Vida Luz <vlal@ideay.net.ni> escribió:
> Hi all
>
> I have a table in y database that have 8,000,000 of
> rows, when I execut a
> query on this table, the answuer is very slow.
>
> I have a index in this table by datem, my table is
>
> Column | Type | Modifiers
> ---------+---------------------------+-----------
> nombre | character varying(15) |
> mensaje | character varying(250) |
> nombre_env | character varying(100) |
> cel_env | character varying(15) |
> fecha | date |
> hora | time(0) without time zone |
> Indexes: correo_fecha
>
> Myindex is:
> Index "correo_fecha"
> Column | Type
> --------+------
> fecha | date
> btree
>
> My query is
>
> select EXTRACT(MONTH FROM M.fecha) as ho,count(*) as
> tot from correo M
> where EXTRACT(YEAR FROM M.fecha)='2004' group by
> EXTRACT(MONTH FROM
> M.fecha);
>
> When I executed a EXPLAIN ANALIZE, I hace the
> following Answer
>
> Aggregate (cost=122439.31..122558.36 rows=2381
> width=4) (actual
> time=64626.46..76021.93 rows=11 loops=1)
> -> Group (cost=122439.31..122498.84 rows=23809
> width=4) (actual
> time=63951.10..73332.27 rows=4177209 loops=1)
> -> Sort (cost=122439.31..122439.31
> rows=23809 width=4) (actual
> time=63951.09..67240.94 rows=4177209 loops=1)
> -> Seq Scan on web_sms m
> (cost=0.00..120708.48 rows=23809
> width=4) (actual time=0.30..55077.31 rows=4177209
> loops=1)
> Total runtime: 76069.22 msec
>
> How can I do to acceletate the answer?
>
> Thanks.
>
Maybe this question should be done at the PERFORMANCE
list.
What about creating the index on the extract
expresion?
CREATE INDEX tuIndice ON correo (date_part('year',
fecha));
I found the other way i suggested in the spanish list
is not correct (sintax) but this way it works (i have
proved it).
regards,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
V > > My query is > > select EXTRACT(MONTH FROM M.fecha) as ho,count(*) as tot from correo M > where EXTRACT(YEAR FROM M.fecha)='2004' group by EXTRACT(MONTH FROM > M.fecha); > > When I executed a EXPLAIN ANALIZE, I hace the following Answer > > Aggregate (cost=122439.31..122558.36 rows=2381 width=4) (actual > time=64626.46..76021.93 rows=11 loops=1) > -> Group (cost=122439.31..122498.84 rows=23809 width=4) (actual > time=63951.10..73332.27 rows=4177209 loops=1) > -> Sort (cost=122439.31..122439.31 rows=23809 width=4) (actual > time=63951.09..67240.94 rows=4177209 loops=1) > -> Seq Scan on web_sms m (cost=0.00..120708.48 rows=23809 > width=4) (actual time=0.30..55077.31 rows=4177209 loops=1) > Total runtime: 76069.22 msec > > How can I do to acceletate the answer? You could use date_part and create a funtional index. Sincerely, Joshua D. Drake > > Thanks. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL