Thread: vacuum'ing toast crumbs, detecting dangling transactions
Hi folks
I had a couple of semi-newbie questions about this, which I couldn't find obvious answers to in the archives ... we are using Postgres 8.3, and the behaviour is the same across Windows and Linux.
I am working with an app which, among other things stores XML files (average about 50KB in size) in blobs in Postgres (column type "text") which Postgres puts in a pg_toast_nnnnn table. The pattern of access is that a group of a few hundred new rows is written to the main table once every few hours, but then the XML documents in that recent batch of rows will be updated about once every 5 minutes each, until the next batch of new rows is created - in that way, the contents of the table are the most recent version of each document, plus a historical trail of one version every few hours.
I'm not defending the decision to store blobs in a database (it was taken a while ago, before the need for frequent updates of the XML) and it isn't something that can be readily changed at short notice, so please no advice about "don't do that" :-)
Obviously, the app causes high turnover of rows in both the parent table and the toast table, so it relies heavily on vacuum to keep the size down. There is no DBA here and no Postgres tuning has been done yet (I plan to have a poke, but my DB tuning experience is Oracle with a side of MySQL, I am a Postgres newbie).
Questions:
1. When I run vacuum manually on the parent table with the application running, it has no effect on either the parent or toast table (as reported by the "pgstattuple" add-on), even when the table is showing 40-50% dead tuples. However, if I disconnect the app, all the dead tuples clean up and moved to the "free space" category.
Is this a normal amount of dead space, and if not, what does this mean? My best guess is that (a) it's not normal, and (b) somewhere the app is holding open an old transaction, so Postgres thinks it has to retain all that data.
2. If there is a hanging transaction, what's the best way to trace it from the PG end? Client is classic Java (Spring / Hibernate / Apache DBCP) if that matters.
Cheers
Dave
I had a couple of semi-newbie questions about this, which I couldn't find obvious answers to in the archives ... we are using Postgres 8.3, and the behaviour is the same across Windows and Linux.
I am working with an app which, among other things stores XML files (average about 50KB in size) in blobs in Postgres (column type "text") which Postgres puts in a pg_toast_nnnnn table. The pattern of access is that a group of a few hundred new rows is written to the main table once every few hours, but then the XML documents in that recent batch of rows will be updated about once every 5 minutes each, until the next batch of new rows is created - in that way, the contents of the table are the most recent version of each document, plus a historical trail of one version every few hours.
I'm not defending the decision to store blobs in a database (it was taken a while ago, before the need for frequent updates of the XML) and it isn't something that can be readily changed at short notice, so please no advice about "don't do that" :-)
Obviously, the app causes high turnover of rows in both the parent table and the toast table, so it relies heavily on vacuum to keep the size down. There is no DBA here and no Postgres tuning has been done yet (I plan to have a poke, but my DB tuning experience is Oracle with a side of MySQL, I am a Postgres newbie).
Questions:
1. When I run vacuum manually on the parent table with the application running, it has no effect on either the parent or toast table (as reported by the "pgstattuple" add-on), even when the table is showing 40-50% dead tuples. However, if I disconnect the app, all the dead tuples clean up and moved to the "free space" category.
Is this a normal amount of dead space, and if not, what does this mean? My best guess is that (a) it's not normal, and (b) somewhere the app is holding open an old transaction, so Postgres thinks it has to retain all that data.
2. If there is a hanging transaction, what's the best way to trace it from the PG end? Client is classic Java (Spring / Hibernate / Apache DBCP) if that matters.
Cheers
Dave
Dave Crooke <dcrooke@gmail.com> wrote: > I'm not defending the decision to store blobs in a database (it was > taken a while ago, before the need for frequent updates of the XML) > and it isn't something that can be readily changed at short notice, > so please no advice about "don't do that" :-) I wouldn't sweat 50kB chunks of XML. We store 10MB PDF files. :-) > is showing 40-50% dead tuples. However, if I disconnect the app, all > the dead tuples clean up and moved to the "free space" category. As you suspected, that sounds like lingering database transactions. Try looking at the pg_stat_activity table for transactions "IDLE in transaction". If you're having trouble pinning down the cause, look the pg_locks view to see what tables they've been in. -Kevin
On Wed, Nov 4, 2009 at 2:18 PM, Dave Crooke <dcrooke@gmail.com> wrote: > 2. If there is a hanging transaction, what's the best way to trace it from > the PG end? Client is classic Java (Spring / Hibernate / Apache DBCP) if > that matters. Last place I worked we had the same issue and it was in our jdbc settings or maybe needed an upgraded version. It was some slick trick someone thought of to do a commit;begin; at the end of each access to the db. It's that begin; that gets in the way, especially if there's an occasional select 1 to make sure the connection is alive.