vacuum deadlock

--0014852d2193248042047ed74ac9
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I am using PostgreSQL 8.3.7.
autovacuum is enabled in postgresql.conf

I got a deadlock while vacuuming all databases with vacuumdb command.

vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock detected
DETAIL: Process 1294 waits for AccessExclusiveLock on relation 2662 of
database ; blocked by process 1807.
Process 1807 waits for AccessShareLock on relation 1259 of database 16389;
blocked by process 1294.

I think, because of this lock, deleting a large table (15 million records)
was taking too much time
How can I know the name of the relations 2662 and 1259?

Do you have any ide about the problem and solution?

Thanks in advance.

--0014852d2193248042047ed74ac9
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, I am using PostgreSQL 8.3.7. autovacuum is enabled in postgre=
sql.conf I got=A0 a deadlock while vacuuming all databases with vacu=
umdb command. vacuumdb: vacuuming of database "mydb" faile=
d: ERROR:=A0 deadlock detected 
DETAIL:=A0 Process 1294 waits for AccessExclusiveLock on relation 2662 of d=
atabase =A0=A0=A0 ; blocked by process 1807. Process 1807 waits for Acce=
ssShareLock on relation 1259 of database 16389; blocked by process 1294.<br=
> 
I think, because of this lock, deleting a large table (15 million records) =
was taking too much time How can I know the name of the relations 2662 a=
nd 1259? Do you have any ide about the problem and solution? 
 Thanks in advance. 

--0014852d2193248042047ed74ac9--

--001636c5a68373379d047ede6b02
Content-Type: text/plain; charset=ISO-8859-1

Hi Yeb,

Thanks for your reply.
As far as I know, pg_class_oid_index and pg_class are postgresql internal
relations.

Normally, there is not application that directly using those relations.
How should I map this lock issue on this relations to my application side?
On which cases those are locked and used by applications?

Thanks

On Fri, Feb 5, 2010 at 2:36 PM, Yeb Havinga <yebhavinga [at] gmail.com> wrote:

> Ibrahim Harrani wrote:
>
>> Hi,
>>
>> I am using PostgreSQL 8.3.7.
>> autovacuum is enabled in postgresql.conf
>>
>> I got a deadlock while vacuuming all databases with vacuumdb command.
>>
>> vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock detected
>> DETAIL: Process 1294 waits for AccessExclusiveLock on relation 2662 of
>> database ; blocked by process 1807.
>> Process 1807 waits for AccessShareLock on relation 1259 of database 16389;
>> blocked by process 1294.
>>
>> I think, because of this lock, deleting a large table (15 million records)
>> was taking too much time
>> How can I know the name of the relations 2662 and 1259?
>>
> postgres=# select oid,relname from pg_class where oid in (2662,1259);
> oid | relname ------+--------------------
> 2662 | pg_class_oid_index
> 1259 | pg_class
> (2 rows)
>
> Do you have any ide about the problem and solution?
>>
> It would be interesting to know what are the processes 1807 and 1294. Once
> that is known, try to avoid executing both processes concurrently.
>
> regards
> Yeb Havinga
>
>

--001636c5a68373379d047ede6b02
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Yeb, Thanks for your reply. As far as I know, pg_class_oid_in=
dex and pg_class are postgresql internal relations. Normally, there=
is not application that directly using those relations. How should I ma=
p this lock issue on this relations to my application side? 
On which cases those are locked and used by applications? Thanks=
 <div class=3D"gmail_quote">On Fri, Feb 5, 2010 at 2:36 PM, Yeb Havi=
nga <<a href=3D"mailto:yebhavinga [at] gmail.com">yebhaving=
a [at] gmail.com</a>> wrote: 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class=3D"im"=
>Ibrahim Harrani wrote: 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi, 
 
I am using PostgreSQL 8.3.7. 
autovacuum is enabled in postgresql.conf 
 
I got =A0a deadlock while vacuuming all databases with vacuumdb command.<br=
>
 
vacuumdb: vacuuming of database "mydb" failed: ERROR: =A0deadlock=
detected 
DETAIL: =A0Process 1294 waits for AccessExclusiveLock on relation 2662 of d=
atabase =A0 =A0 ; blocked by process 1807. 
Process 1807 waits for AccessShareLock on relation 1259 of database 16389; =
blocked by process 1294. 
 
I think, because of this lock, deleting a large table (15 million records) =
was taking too much time 
How can I know the name of the relations 2662 and 1259? 
</blockquote></div>
postgres=3D# select oid,relname from pg_class where oid in (2662,1259); 
oid =A0| =A0 =A0 =A0relname =A0 =A0 =A0------+-------------------- 
2662 | pg_class_oid_index 
1259 | pg_class 
(2 rows)<div class=3D"im"> 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Do you have any ide about the problem and solution? 
</blockquote></div>
It would be interesting to know what are the processes 1807 and 1294. Once =
that is known, try to avoid executing both processes concurrently. 
 
regards 
Yeb Havinga 
 
</blockquote></div> 

--001636c5a68373379d047ede6b02--

Ibrahim Harrani <ibrahim.harrani [at] gmail.com> wrote:
> Yeb Havinga <yebhavinga [at] gmail.com> wrote:
>> Ibrahim Harrani wrote:

>>> I am using PostgreSQL 8.3.7.
>>> autovacuum is enabled in postgresql.conf
>>>
>>> I got a deadlock while vacuuming all databases with vacuumdb
>>> command.
>>>
>>> vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock
>>> detected
>>> DETAIL: Process 1294 waits for AccessExclusiveLock on relation
>>> 2662 of database ; blocked by process 1807.
>>> Process 1807 waits for AccessShareLock on relation 1259 of
>>> database 16389; blocked by process 1294.
>>>
>>> I think, because of this lock, deleting a large table (15
>>> million records) was taking too much time
>>> How can I know the name of the relations 2662 and 1259?
>>>
>> postgres=# select oid,relname from pg_class where oid in
>> (2662,1259);
>> oid | relname ------+--------------------
>> 2662 | pg_class_oid_index
>> 1259 | pg_class
>> (2 rows)

> As far as I know, pg_class_oid_index and pg_class are postgresql
> internal relations.
>
> Normally, there is not application that directly using those
> relations.
> How should I map this lock issue on this relations to my
> application side?
> On which cases those are locked and used by applications?

Are you creating temporary tables somehow during this process, like
within functions (including trigger functions)?

Can you test the delete with autovacuum disabled?

When you say "deleting a large table" do you mean, dropping the
table, deleting all rows from the table, or deleting some of the
rows from the table?

If you could show the actual delete statement and all information
about the table(s) involved, including indexes, triggers, and
foreign keys, that might help.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ibrahim Harrani <ibrahim.harrani [at] gmail.com> writes:
> I got a deadlock while vacuuming all databases with vacuumdb command.

> vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock detected
> DETAIL: Process 1294 waits for AccessExclusiveLock on relation 2662 of
> database ; blocked by process 1807.
> Process 1807 waits for AccessShareLock on relation 1259 of database 16389;
> blocked by process 1294.

vacuumdb -f you mean? An ordinary vacuum wouldn't be trying to take
AccessExclusiveLock.

It might be that you have an instance of a failure that was identified
just a couple weeks ago:
http://archives.postgresql.org/pgsql-committers/2010-01/msg0 0199.php

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--000e0ce0af6e1b89180480baa6d8
Content-Type: text/plain; charset=ISO-8859-1

Hi,

Yes, I am trying to full vacuum.

I think, I found the problem. There were more then 10 million records which
my scripts trying to delete at night from a table while other processes were
adding new entries to same table.
Delete can't finish in the day, and script tries to delete the table again
in next night via crontab
then this cause to dead lock?

SQL query: "delete from mytable where createtime < now - '10 days';

We added 1K limit option to delete query in a for loop. Now I can delete all
entries but slowly

DELETE FROM mytable WHERE id = any (array(SELECT id FROM mytable WHERE
createtime < NOW() - INTERVAL '10 day' LIMIT 1000))

I am planning to partion(day based) this large table, then I will simply
drop the tables after certain days.

Do you have any other suggestion?

Thanks.

On Sat, Feb 6, 2010 at 8:11 AM, Tom Lane <tgl [at] sss.pgh.pa.us> wrote:

> Ibrahim Harrani <ibrahim.harrani [at] gmail.com> writes:
> > I got a deadlock while vacuuming all databases with vacuumdb command.
>
> > vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock detected
> > DETAIL: Process 1294 waits for AccessExclusiveLock on relation 2662 of
> > database ; blocked by process 1807.
> > Process 1807 waits for AccessShareLock on relation 1259 of database
> 16389;
> > blocked by process 1294.
>
> vacuumdb -f you mean? An ordinary vacuum wouldn't be trying to take
> AccessExclusiveLock.
>
> It might be that you have an instance of a failure that was identified
> just a couple weeks ago:
> http://archives.postgresql.org/pgsql-committers/2010-01/msg0 0199.php
>
> regards, tom lane
>

--000e0ce0af6e1b89180480baa6d8
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, Yes,=A0 I am trying to full vacuum. I think, I=A0 found t=
he problem. There were more then 10 million records which my scripts trying=
to delete at night from a table while other processes were adding new entr=
ies to same table. 
Delete can't finish in the day, and script tries to delete the table ag=
ain in next night via crontab then this cause to dead lock? SQL q=
uery: "delete from mytable where createtime < now - '10 days=
9;; 
 We added 1K limit option to delete query in a for loop. Now I can d=
elete all entries but slowly=A0 DELETE FROM mytable WHERE id =3D an=
y (array(SELECT id FROM mytable WHERE createtime < NOW() - INTERVAL '=
;10 day'=A0 LIMIT 1000)) 
 I am planning to partion(day based) this large table, then I will simpl=
y drop the tables after certain days. Do you have any other suggesti=
on? Thanks. <div class=3D"gmail_quote">On Sat, Feb 6, 2010 at=
8:11 AM, Tom Lane <<a href=3D"mailto:tgl [at] sss.pgh.pa.u=
s">tgl [at] sss.pgh.pa.us</a>> wrote: 
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class=3D"im"=
>Ibrahim Harrani <<a href=3D"mailto:ibrahim.harrani [at] gmail.com">ibrahim.h=
arrani [at] gmail.com</a>> writes: 

> I got =A0a deadlock while vacuuming all databases with vacuumdb comman=
d. 
 
> vacuumdb: vacuuming of database "mydb" failed: ERROR: =A0dea=
dlock detected 
> DETAIL: =A0Process 1294 waits for AccessExclusiveLock on relation 2662=
of 
> database =A0 =A0 ; blocked by process 1807. 
> Process 1807 waits for AccessShareLock on relation 1259 of database 16=
389; 
> blocked by process 1294. 
 
</div>vacuumdb -f you mean? =A0An ordinary vacuum wouldn't be trying to=
take 
AccessExclusiveLock. 
 
It might be that you have an instance of a failure that was identified 
just a couple weeks ago: 
<a href=3D"http://archives.postgresql.org/pgsql-committers/2010-01/msg00199=
..php" target=3D"_blank">http://archives.postgresql.org/pgsql-commi tters/201=
0-01/msg00199.php</a> 
 
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane 
</blockquote></div> 

--000e0ce0af6e1b89180480baa6d8--

vacuum deadlock

Re: vacuum deadlock

Re: vacuum deadlock

Re: vacuum deadlock

Re: vacuum deadlock