Re: Analyze and vacuum, they are sort of mandatory....
Mark Woodward wrote:
I know this is a kind of stupid question, but postgresql does not
behave well when the system changes in a major way without at least
an analyze. There must be something that can be done to protect the
casual user (or busy sometimes absent minded developer) from these
dangerous edge conditions?
autovacuum
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Import Notes
Reply to msg id not found: 16507.24.91.171.78.1139682819.squirrel@mail.mohawksoft.comReference msg id not found: 16507.24.91.171.78.1139682819.squirrel@mail.mohawksoft.com
Mark Woodward wrote:
I know this is a kind of stupid question, but postgresql does not
behave well when the system changes in a major way without at least
an analyze. There must be something that can be done to protect the
casual user (or busy sometimes absent minded developer) from these
dangerous edge conditions?autovacuum
That's a good simple answer, sure, but it is no different than "run
analyze," they are obvious when you know the problems, but not so when you
don't are focusing on something else.
I didn't see the "problem" because I didn't suspect HassHagg would behave
so badly, who would?
One of my biggest problems with Oracle is that there are so many ways that
it can fail. One can argue that the DBA should "know what they are doing,"
and it is a good argument, but there is a diffeence between knowing the
findimentals of server design, query design, parallel processing, I/O
bandwidth, etc. and knowing the esoterica of a particular platform. One
tends to acquire the essoterica as needed.
What I discovered with PostgreSQL was a failure. It had run away memory
cconsuption, this is bad behavior. On Linux it was killed, while I don't
want to have that discussion, it is a real world fact that saying "turn
OOM off," is not acceptable.
If PostgreSQL exhibits bad behavior, it is PostgreSQL's problem.
My question was based on an observation that ANALYZE and VACUUM are
nessisary, both for different reasons. The system or tools must be able to
detect substantial changes in the database and at least run analyze if
failing to do so would cause PostgreSQL to fail badly.
Mark Woodward wrote:
My question was based on an observation that ANALYZE and VACUUM are
nessisary, both for different reasons. The system or tools must be
able to detect substantial changes in the database and at least run
analyze if failing to do so would cause PostgreSQL to fail badly.
Yes, that is what autovacuum does. It detects changes in the database
and runs analyze if failing to do so would cause PostgreSQL to behave
badly. I don't know why it's not turned on by default. You could
argue about that. But I don't know what else you are looking for.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Mark Woodward wrote:
My question was based on an observation that ANALYZE and VACUUM are
nessisary, both for different reasons. The system or tools must be
able to detect substantial changes in the database and at least run
analyze if failing to do so would cause PostgreSQL to fail badly.Yes, that is what autovacuum does. It detects changes in the database
and runs analyze if failing to do so would cause PostgreSQL to behave
badly. I don't know why it's not turned on by default. You could
argue about that. But I don't know what else you are looking for.
If that is the answer, then I agree with you, it should be on by default.
Peter Eisentraut <peter_e@gmx.net> writes:
Yes, that is what autovacuum does. It detects changes in the database
and runs analyze if failing to do so would cause PostgreSQL to behave
badly. I don't know why it's not turned on by default.
Conservatism. It may well be on by default in some future release,
but that's not happening in the first release where the code exists
at all.
autovacuum isn't a 100% solution to the sort of problems Mark is
complaining about anyway: on a freshly-loaded table you could get bad
plans because autovacuum hadn't gotten to it yet.
One thing we could consider doing is boosting up the default no-stats
assumption about the number of distinct values in a column, to the point
where the planner wouldn't try a hash aggregate unless it had actual
stats. However, I'm unsure what negative side-effects that might have.
regards, tom lane
if we had a pg_vacuum table that had the last timestamp of a vacuum/analyze for each table and the stats looked like
the default, why not just print a warning message out to the user?
---------- Original Message -----------
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: "Mark Woodward" <pgsql@mohawksoft.com>, pgsql-hackers@postgresql.org
Sent: Sun, 12 Feb 2006 11:18:03 -0500
Subject: Re: [HACKERS] Analyze and vacuum, they are sort of mandatory....
Peter Eisentraut <peter_e@gmx.net> writes:
Yes, that is what autovacuum does. It detects changes in the database
and runs analyze if failing to do so would cause PostgreSQL to behave
badly. I don't know why it's not turned on by default.Conservatism. It may well be on by default in some future release,
but that's not happening in the first release where the code exists
at all.autovacuum isn't a 100% solution to the sort of problems Mark is
complaining about anyway: on a freshly-loaded table you could get bad
plans because autovacuum hadn't gotten to it yet.One thing we could consider doing is boosting up the default no-stats
assumption about the number of distinct values in a column, to the point
where the planner wouldn't try a hash aggregate unless it had actual
stats. However, I'm unsure what negative side-effects that might have.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
------- End of Original Message -------