Обсуждение: Analyze and vacuum, they are sort of mandatory....
I was think about how forgetting to run analyze while developing a table loader program caused PostgreSQL to run away and use up all the memory. Is there some way that postges or psql can know that it substantially altered the database and run analyze? 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?
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/
> 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? > > http://www.postgresql.org/docs/faq ------- End of Original Message -------