Обсуждение: Postgres Hanging on Inserts
My application involves inserting new records into a table and altering a record in another table in a postgres databaseversion 7.3.3 several times a second . Each instance of my application inserts into the same table and changes adifferent row of the updating table. My problem is that on occasion (from several minuets to several hours) all of my inserts will stop and will ps -ef tellsme that they are waiting for something. I have waited several hours and they have never stopped waiting. I looked into pg_locks and they are all waiting to get an exclusive lock on the same relation. Is there anyway for me totell what this relation is? Does anyone know what it could be and how I can fix this problem? --- Adam Kavan --- American Amuesments --- akavan@cox.net --- 402-499-5145
Adam Kavan <akavan@cox.net> writes:
> I looked into pg_locks and they are all waiting to get an exclusive
> lock on the same relation. Is there anyway for me to tell what this
> relation is?
To decipher the OIDs in pg_locks, join against pg_class.oid, or just do
select relname from pg_class where oid = nnnn;
> Does anyone know what it could be and how I can fix this
> problem?
Look for the process that already has a lock on the same relation, and
find out what it's waiting for.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: <akavan@cox.net> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, July 30, 2003 9:25 AM Subject: Re: [GENERAL] Postgres Hanging on Inserts > Adam Kavan <akavan@cox.net> writes: > > I looked into pg_locks and they are all waiting to get an exclusive > > lock on the same relation. Is there anyway for me to tell what this > > relation is? > > To decipher the OIDs in pg_locks, join against pg_class.oid, or just do > select relname from pg_class where oid = nnnn; > > > Does anyone know what it could be and how I can fix this > > problem? > > Look for the process that already has a lock on the same relation, and > find out what it's waiting for. > > regards, tom lane I have found the problem (I think) below is the list of all the locks pending on the relation. The relation is a hash index on the table that is being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have an ExclusiveLock on the index, and they both are waiting to get an ExclusiveLock on the relation. Those are the only locks either pid doesn't have so I suspect that is what is causing the deadlock. Is there something I've done wrong? Both pids are just doing simple inserts. data=# select * from pg_locks where relation = 3731653 order by granted; relation | database | transaction | pid | mode | granted ----------+----------+-------------+-------+-----------------+--------- 3731653 | 16976 | | 10091 | ShareLock | f 3731653 | 16976 | | 10077 | ShareLock | f 3731653 | 16976 | | 10178 | ShareLock | f 3731653 | 16976 | | 10116 | ShareLock | f 3731653 | 16976 | | 10108 | ShareLock | f 3731653 | 16976 | | 10076 | ShareLock | f 3731653 | 16976 | | 10079 | ShareLock | f 3731653 | 16976 | | 10110 | ShareLock | f 3731653 | 16976 | | 10023 | ExclusiveLock | f 3731653 | 16976 | | 10177 | ShareLock | f 3731653 | 16976 | | 10208 | ShareLock | f 3731653 | 16976 | | 10166 | ShareLock | f 3731653 | 16976 | | 10142 | ShareLock | f 3731653 | 16976 | | 10160 | ShareLock | f 3731653 | 16976 | | 10214 | ShareLock | f 3731653 | 16976 | | 10226 | ShareLock | f 3731653 | 16976 | | 10031 | ShareLock | f 3731653 | 16976 | | 10237 | ShareLock | f 3731653 | 16976 | | 10075 | ShareLock | f 3731653 | 16976 | | 10109 | ShareLock | f 3731653 | 16976 | | 10207 | ShareLock | f 3731653 | 16976 | | 10190 | ShareLock | f 3731653 | 16976 | | 10041 | ShareLock | f 3731653 | 16976 | | 10130 | ShareLock | f 3731653 | 16976 | | 10043 | ShareLock | f 3731653 | 16976 | | 10026 | ShareLock | f 3731653 | 16976 | | 10074 | ShareLock | f 3731653 | 16976 | | 10092 | ShareLock | f 3731653 | 16976 | | 10158 | ShareLock | f 3731653 | 16976 | | 10024 | ExclusiveLock | f 3731653 | 16976 | | 10141 | ShareLock | f 3731653 | 16976 | | 10189 | ShareLock | f 3731653 | 16976 | | 10238 | ShareLock | f 3731653 | 16976 | | 10027 | ShareLock | f 3731653 | 16976 | | 10078 | ShareLock | f 3731653 | 16976 | | 10025 | ExclusiveLock | f 3731653 | 16976 | | 10159 | ShareLock | f 3731653 | 16976 | | 10225 | ShareLock | f 3731653 | 16976 | | 9951 | ShareLock | f 3731653 | 16976 | | 10029 | ShareLock | f 3731653 | 16976 | | 10196 | ShareLock | f 3731653 | 16976 | | 10028 | ShareLock | f 3731653 | 16976 | | 10128 | ShareLock | f 3731653 | 16976 | | 9951 | AccessShareLock | t 3731653 | 16976 | | 10024 | ExclusiveLock | t 3731653 | 16976 | | 10025 | ExclusiveLock | t 3731653 | 16976 | | 9951 | ShareLock | t 3731653 | 16976 | | 10023 | ShareLock | t (48 rows) > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote: > I have found the problem (I think) below is the list of all the locks > pending on the relation. The relation is a hash index on the table that is > being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have > an ExclusiveLock on the index, and they both are waiting to get an > ExclusiveLock on the relation. Oh, so this is the problem. Truth is hash indexes in Postgres are known to have poor concurrency, though I didn't expect them to be subject to deadlocks... you should change the hash index to a btree index and the problem will "go away"; you will also probably see a performance improvement if there's concurrent insertion and access. BTrees are way more developed than hashes. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "The ability to monopolize a planet is insignificant next to the power of the source"
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote:
>> I have found the problem (I think) below is the list of all the locks
>> pending on the relation. The relation is a hash index on the table that is
>> being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have
>> an ExclusiveLock on the index, and they both are waiting to get an
>> ExclusiveLock on the relation.
> Oh, so this is the problem. Truth is hash indexes in Postgres are known to
> have poor concurrency, though I didn't expect them to be subject to
> deadlocks...
They are known to have internal deadlock problems too. I believe what
Adam has shown us is an internal deadlock in the index. The locks that
are being taken are actually page-level locks, but the pg_locks view
doesn't show the page numbers.
I had thought that such things would trigger a "deadlock detected" error
though --- curious that it seems not to.
> you should change the hash index to a btree index
Agreed. Hash indexes would probably have gotten fixed by now if anyone
could see a reason to expend effort on them, but they seem to be mostly
an academic exercise.
regards, tom lane
> Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes: > > On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote: > >> I have found the problem (I think) below is the list of all the locks > >> pending on the relation. The relation is a hash index on the table that is > >> being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have > >> an ExclusiveLock on the index, and they both are waiting to get an > >> ExclusiveLock on the relation. > > > Oh, so this is the problem. Truth is hash indexes in Postgres are known to > > have poor concurrency, though I didn't expect them to be subject to > > deadlocks... > > They are known to have internal deadlock problems too. I believe what > Adam has shown us is an internal deadlock in the index. The locks that > are being taken are actually page-level locks, but the pg_locks view > doesn't show the page numbers. > > I had thought that such things would trigger a "deadlock detected" error > though --- curious that it seems not to. > > > you should change the hash index to a btree index > > Agreed. Hash indexes would probably have gotten fixed by now if anyone > could see a reason to expend effort on them, but they seem to be mostly > an academic exercise. > > regards, tom lane I can happily report that my system has gone through the night without any problems. Thanks a lot for helping me. --- Adam Kavan --- akavan@cox.net