[OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 22:17 +, Jeremy Harris wrote: We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize |iname| ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize ++---+--+--++-+-++-+-++---++--+--+- public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 |1.8 | 2979 | 24403968 | 23 MB public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 | 3819 |1.2 | 908 | 7438336 | 7264 kB Can you let me know what is the sql used to generate such a nice summary of the tables? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [OT] Re: [GENERAL] enabling autovacuum
On Tue, 29 Jan 2008, Ow Mun Heng wrote: Can you let me know what is the sql used to generate such a nice summary of the tables? Might as well dupe the old text; this went out to the performance list: Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can grab at http://bucardo.org/nagios_postgres/ , and while that is itself nice the thing I found most remarkable is the bloat check. The majority of that code is an impressive bit of SQL that anyone could use even if you have no interest in Nagios, which is why I point it out for broader attention. Look in check_postgres.pl for the check_bloat routine and the big statement starting at the aptly labled This was fun to write section. If you pull that out of there and replace $MINPAGES and $MINIPAGES near the end with real values, you can pop that into a standalone query and execute it directly. That's what gives the summary Jeremy included in his message. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 20:57 -0500, Greg Smith wrote: On Tue, 29 Jan 2008, Ow Mun Heng wrote: Can you let me know what is the sql used to generate such a nice summary of the tables? Might as well dupe the old text; this went out to the performance list: Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can grab at http://bucardo.org/nagios_postgres/ , and while that is itself nice the thing I found most remarkable is the bloat check. The majority of that code is an impressive bit of SQL that anyone could use even if you have no interest in Nagios, which is why I point it out for broader attention. Look in check_postgres.pl for the check_bloat routine and the big statement starting at the aptly labled This was fun to write section. If you pull that out of there and replace $MINPAGES and $MINIPAGES near the end with real values, you can pop that into a standalone query and execute it directly. I'm subscribed to perf list and I _did_ take a look at the tool previously. However, something happened and I didn't managed to look at it throughly or something. I'll take another look at it and thanks for the pointers.. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
