Marcin <migor (AT) op (DOT) pl> writes:
Well, breaking down the contents of the stats file I find:
190 backends (218880 bytes)
118 databases (8496 bytes)
81952 tables (8523008 bytes)
And the ps listing shows there actually are about 190 live backends,
so the idea about leaked backend stats entries is clearly wrong.

The per-table data is 104 bytes/table, up from 80 in 8.0, so that's
a noticeable increase but hardly a killer. What I have to conclude
is that 8.1 is tracking stats for a lot more tables than 8.0 was.
(Do you happen to have a number for the size of the stats file in
the 8.0 installation? I don't need to see its contents, I just want
to know how big it is.)

Do you actually have 81952 tables in the installation? Maybe we are
looking at a table-level leak. Do you have autovacuum on where it was
not in 8.0? Maybe that's causing it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Marcin <migor (AT) op (DOT) pl> writes:
Looking at the code, stats entries for dropped tables are cleaned out
only when a VACUUM command is done; which is something we probably ought
to change. However, that's always been the case, so I don't understand
why your stats file is so much bigger in 8.1. Have you changed your
vacuuming strategy at all since the 8.0 installation? Perhaps row or
block statistics weren't enabled in the 8.0 installation and are now?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
I was going to ask if you were confusing pgstat_vacuum_tabstat with a
VACUUM command, when I noticed that only in vacuum() is that function
called! This surprised me and I agree that it should be changed. I'm
not sure what would be a more appropiate place to call it, however.

Maybe in heap_drop_with_catalog() we could place a call, or a limited
version that'd only "vacuum" the table being dropped.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Alvaro Herrera <alvherre (AT) commandprompt (DOT) com> writes:
I don't have a problem with that. What I'm thinking is that a DROP
TABLE command should issue a tabpurge message for the specific table
(or index) being zapped. We still need vacuum_tabstat as a backstop
in case the tabpurge message gets lost, though.

Another thought is that in autovacuum, pgstat_vacuum_tabstat is really
called too often: once per autovac cycle would be sufficient, but
instead it's repeated for each table we vacuum.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Tue, Jan 17, 2006 at 05:12:09PM -0500, Tom Lane wrote:
The only change was the upgrade to 8.1. I run VACUUM FULL everyday, and
double checked that it finished succesfully in last two days.
The block and row statistics are disabled:
stats_start_collector = on
stats_command_string = off
stats_block_level = off
stats_row_level = off
stats_reset_on_server_start = off

I have no idea, why the pgstat.stat is so large. I just shutdown the
idle backend connection, so there're only 30 left (instead of 200),
and the pgstat size decreases by 200KB from 8.8 to 8.6 MB. The lowest size
of pgstat in last six hours was 8.5 MB.

--
Marcin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Marcin <migor (AT) op (DOT) pl> writes:
Stats off and it's still bloating the file??

[ studies code... ] I see the culprit: it's the pgstat_report_vacuum
and pgstat_report_analyze routines that were added in 8.1. Those send
messages unconditionally, meaning that the collector will create table
entries for every table during a database-wide vacuum, even with stats
turned off.

This seems like a bad idea. Given the nature of what's counted, I think
that treating these messages as "row level" stats would be appropriate.
Alvaro, what do you think?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:

Sorry, clearly my bug :-(

Yeah, row level seems appropiate for what we use it. I'll take care of
it, unless you want to do it.

--
Alvaro Herrera http://www.PlanetPostgreSQL.org
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera <alvherre (AT) alvh (DOT) no-ip.org> writes:
I'll fix it --- I want to put in an immediate tabpurge on drop, too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera <alvherre (AT) alvh (DOT) no-ip.org> writes:
Actually, there's another problem here: if we do have row-level stats
turned on, a manual "VACUUM" command will still cause the set of tables
listed in the stats file to grow to include every table in the DB,
whether or not anything interesting is happening to that table. I think
this is probably undesirable. I'm tempted to change pgstat_recv_vacuum
and pgstat_recv_analyze so that they will not create new hash entries,
but only insert the row count if the hash entry already exists. I am a
bit worried that I might be missing something about possible
interactions with autovacuum though. I see that autovac skips vacuuming
tables that have no hash entry ... is there some circular reasoning
going on there?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:

The idea was that autovac should skip tables for which it doesn't have
info, because it can't decide and we chose to err on the side of
caution. However, after a vacuum or analyze we do have info about the
table, which is what we store in the pgstat_recv functions
inconditionally. Thus the next autovacuum is able to make an informed
decision about this table.

The principles are: 1) store as much information as possible,
2) autovacuum should act iff it has information.

I don't think this is a bug, but I'm not dead set on it if you want to
change this behavior.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"The first of April is the day we remember what we are
the other 364 days of the year" (Mark Twain)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings