--nextPart1296397.5YDAtfoSAv
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

I got this log on 8.3.9:

Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-1] DB=3D U=3D H=3D WARNING:=

relation "pg_toast.pg_toast_1910021" contains more than "max_fsm_pages"
pages with useful free space
Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-2] DB=3D U=3D H=3D HINT:
Consider using VACUUM FULL on this relation or increasing the
configuration parameter "max_fsm_pages".
Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-1] DB=3D U=3D H=3D LOG:
automatic vacuum of table "dbmail.pg_toast.pg_toast_1910021": index
scans: 1
Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-2] pages: 0
removed, 740218 remain
Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-3] tuples: 601310
removed, 2397087 remain
Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-4] system usage:
CPU 5.73s/1.92u sec elapsed 835.67 sec

and I'd like to know
1) which db uses pg_toast.pg_toast_1910021? (Later I found it:) That
should be dbmail, as it writes "dbmail.pg_toast.pg_toast_1910021" later
on. But wouldn't it be good to log that directly? Making it easier for
admins...
2) what table is using that toast?
3) why did postgres suddenly decide to remove the old cruft suddenly?

Autovacuum is on, the nightly backups do an extra "vacuum analyze", and
once a week a CLUSTER is done for the big tables. Maybe I missed one?


=2D-
mit freundlichen Gr=C3=BCssen,
Michael Monnerie, Ing. BSc

it-management Internet Services
http://it-management.at
Tel: 0660 / 415 65 31

// Wir haben zwei H=C3=A4user zu verkaufen:
// http://zmi.at/langegg/
// http://willhaben.at/iad/realestate/object?adId=3D15923011

--nextPart1296397.5YDAtfoSAv
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.12 (GNU/Linux)

iEYEABECAAYFAktdfjAACgkQzhSR9xwSCbR5dQCeIHwOKkfPh60znRcC1Cpl 6mID
eVQAoMg2YhBpnguOafyiicdIi8tTHGxs
=bktm
-----END PGP SIGNATURE-----

--nextPart1296397.5YDAtfoSAv--

Michael Monnerie wrote:

> why did postgres suddenly decide to remove the old cruft suddenly?

Have you read through this yet?:

http://www.postgresql.org/docs/8.3/interactive/runtime-confi g-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

> Autovacuum is on, the nightly backups do an extra "vacuum analyze",
> and once a week a CLUSTER is done for the big tables.

You should probably make that a "vacuum analyze verbose" to get a
good idea of where you should set max_fsm_pages, and to look for
where you are accumumlating free space to track.

-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

--nextPart4471488.19YB7igBTa
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

On Montag, 25. Januar 2010 Kevin Grittner wrote:
> Michael Monnerie wrote:
> > why did postgres suddenly decide to remove the old cruft suddenly?
>
> Have you read through this yet?:
>
> http://www.postgresql.org/docs/8.3/interactive/runtime-confi g-resourc
> e.html#RUNTIME-CONFIG-RESOURCE-FSM

Yes I did.

> > Autovacuum is on, the nightly backups do an extra "vacuum analyze",
> > and once a week a CLUSTER is done for the big tables.
>
> You should probably make that a "vacuum analyze verbose" to get a
> good idea of where you should set max_fsm_pages, and to look for
> where you are accumumlating free space to track.

That's why I find it strange. I always log the VACUUM VERBOSE ANALYZE
command, it is run nightly:
INFO: free space map contains 21517 pages in 107 relations
DETAIL: A total of 22912 page slots are in use (including overhead).
22912 page slots are required to track all free space.
Current limits are: 150000 page slots, 1000 relations, using 984 kB.

So, as there was that one relation that was bloatet - how could it be?
Autovaccuum, nightly vacuum analyze, weekly cluster - and still a heavy
bloated toast* something. I must do something wrong.

=2D-
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services
http://it-management.at
Tel: 0660 / 415 65 31

// Wir haben zwei Häuser zu verkaufen:
// http://zmi.at/langegg/
// http://willhaben.at/iad/realestate/object?adId=3D15923011

--nextPart4471488.19YB7igBTa
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.12 (GNU/Linux)

iEYEABECAAYFAktdrVMACgkQzhSR9xwSCbSKBgCgs4tEUaCxNZNTiiSeONAB Pm9y
i6EAn1IzTt+k6tQqsYz2N1gbqnqeXgFX
=i7V1
-----END PGP SIGNATURE-----

--nextPart4471488.19YB7igBTa--

Michael Monnerie <michael.monnerie [at] is.it-management.at> wrote:
> So, as there was that one relation that was bloatet - how could it
> be? Autovaccuum, nightly vacuum analyze, weekly cluster - and
> still a heavy bloated toast* something. I must do something wrong.

Any chance you had or have long-running transactions. We once had
very low free space in a big database which suddenly ballooned. It
turned out an application programmer had left a connection in "idle
in transaction" state for a few days.

Another possibility is that you had an update or delete which
affected a lot of rows. Even if it rolls back, it can cause bloat.

-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

--nextPart1606113.36pv5sRRb0
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

On Montag, 25. Januar 2010 Kevin Grittner wrote:
> Any chance you had or have long-running transactions. We once had
> very low free space in a big database which suddenly ballooned. It
> turned out an application programmer had left a connection in "idle
> in transaction" state for a few days.
>
> Another possibility is that you had an update or delete which
> affected a lot of rows. Even if it rolls back, it can cause bloat.

I don't believe that. This machine holds the "dbmail" Mail Database,
locks and transactions are very small, just to insert a new mail. The
system is very low loaded, so such a bloat can't happen in a day.

=46inding out which table the toast belongs to would be great, maybe that
helps further investigation.

=2D-
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services
http://it-management.at
Tel: 0660 / 415 65 31

// Wir haben zwei Häuser zu verkaufen:
// http://zmi.at/langegg/
// http://willhaben.at/iad/realestate/object?adId=3D15923011

--nextPart1606113.36pv5sRRb0
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.12 (GNU/Linux)

iEYEABECAAYFAktdujMACgkQzhSR9xwSCbQyYgCgn8mVGwf0bAS3yyU0wWsI 4HNI
oWAAn2BmvkBG03Bw2p7OI6oH+3VfXPqz
=RTfA
-----END PGP SIGNATURE-----

--nextPart1606113.36pv5sRRb0--

Michael Monnerie <michael.monnerie [at] is.it-management.at> wrote:

> such a bloat can't happen in a day.

That is evidence that you may have a problem with some long-running
transaction which stays open for days, possibly "idle in
transaction". Bloat will accumulate, without any vacuum being able
to prevent it or recover from it, until the transaction terminates.
It will not show up as free space in your vacuum verbose output
while the transaction remains open; although I believe it will show
up in the "dead row versions cannot be removed yet" count.

> Finding out which table the toast belongs to would be great, maybe
> that helps further investigation.

Have a look at reltoastrelid:

http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cl ass.html

-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

--nextPart1919350.x8yIei0z3n
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

On Montag, 25. Januar 2010 Kevin Grittner wrote:
> Have a look at reltoastrelid:

Hmm.. select * from pg_class; doesnt give anything containing 1910021
that I had in the log. So what? A temporary table? But that shouldn't be
bloated nor auto-vacuumed, right?

=2D-
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services
http://it-management.at
Tel: 0660 / 415 65 31

// Wir haben zwei Häuser zu verkaufen:
// http://zmi.at/langegg/
// http://willhaben.at/iad/realestate/object?adId=3D15923011

--nextPart1919350.x8yIei0z3n
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.12 (GNU/Linux)

iEYEABECAAYFAktdyLUACgkQzhSR9xwSCbTlLACgyYRhgUQKono9AoJ3sgvt Zfkf
ZpkAoL/0gVx1e7T6xqTcqyVyLDaFKFGr
=DG6e
-----END PGP SIGNATURE-----

--nextPart1919350.x8yIei0z3n--