Re: PostgreSQL 8.x defaults
--On måndag, april 09, 2007 08.36.19 -0500 Craig Boston [EMAIL PROTECTED] wrote: I'm not sure of a good solution however. Do you think it would be possible / reasonable for the periodic job to check if the user has set postgresql_enable and do nothing if it is not enabled? That is certainly a good idea! ;-) /Palle ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: PostgreSQL 8.x defaults
5 apr 2007 kl. 15.24 skrev Craig Boston:
I recently installed some PostgreSQL 8.2 servers (and upgraded some
from
8.1), and it reminded me of a few lingering nits in our port that bug
me. Mostly the port seems that it can't make up its mind about VACCUM
strategy.
* We have a patch that sets autovacuum = yes in the default
postgresql.conf. However, it leaves the default stats_row_level
= no,
so autovacuum doesn't actually run.
Hi,
I've checked it out. Seems I have indeed missed the
stats_row_level... I'll fix this!
* Despite trying to turn on autovacuum, the port installs
periodic/daily/502.pgsql, which runs VACUUM (not even VACUUM
ANALYZE)
by default nightly.
It does run vaccumdb -aqz per default, where -z is for analyze:
$ grep daily_pgsql_vacuum_args files/502.pgsql
daily_pgsql_vacuum_args=-z
su -l pgsql -c vacuumdb -a -q ${daily_pgsql_vacuum_args}
It seems to me that we should pick one or the other -- either
1. Fully enable autovacuum and default to
daily_pgsql_vacuum_enable=NO
to avoid a superfluous cron job
or
2. Leave the periodic job enabled and not tease people with an
ineffectual autovacuum = yes in postgresql.conf.
Of the two I'd prefer the former, but that's just my opinion.
About the two strategies you present; autovacuum will probably need
some tweaking for most applications, since it will not perform
vacuums unless a certain percentage of the tuples are changed. Hence,
usually I use a combination of autovacuum and a nightly vacuumdb -za.
For smaller installations, the vanilla setup could of course be
either of your suggestions, or my just suggested combo, it is
probably a matter of taste. I'd prefer you #1 or the combo... I'll
think about it a bit more, and will fix the port. :)
Happy easter,
Palle
___
freebsd-ports@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: PostgreSQL 8.x defaults
On Apr 9, 2007, at 5:46 AM, Palle Girgensohn wrote: About the two strategies you present; autovacuum will probably need some tweaking for most applications, since it will not perform vacuums unless a certain percentage of the tuples are changed. Hence, usually I use a combination of autovacuum and a nightly vacuumdb -za. For smaller installations, the vanilla setup could of course be either of your suggestions, or my just suggested combo, it is probably a matter of taste. I'd prefer you #1 or the combo... I'll think about it a bit more, and will fix the port. :) I think the port should not try to meddle with the postgresql.conf file *at all* and leave both vacuums off by default. The package installer needs to configure postgres, and part of that is to enable proper vacuum maintenance as per site policy. Having to undo work done by the port is not sensible. In short: leave the default status of the periodic script off, and do not try to enable autovacuum (I suppose if you ask the user at package install time, it would be ok to do so.) ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: PostgreSQL 8.x defaults
Palle,
Thanks for listening to my thoughts and for your insight on why things
are set up they way they are -- I knew there must be a good reason :)
On Mon, Apr 09, 2007 at 11:46:50AM +0200, Palle Girgensohn wrote:
It does run vaccumdb -aqz per default, where -z is for analyze:
$ grep daily_pgsql_vacuum_args files/502.pgsql
daily_pgsql_vacuum_args=-z
su -l pgsql -c vacuumdb -a -q ${daily_pgsql_vacuum_args}
Ah, I missed the daily_pgsql_vacuum_args variable being set at the start
of the file!
About the two strategies you present; autovacuum will probably need
some tweaking for most applications, since it will not perform
vacuums unless a certain percentage of the tuples are changed. Hence,
usually I use a combination of autovacuum and a nightly vacuumdb -za.
Hmm, yes, thinking about it that way using both makes sense. In theory,
will running autovacuum lessen the impact of running the scheduled
VACUUM [ANALYZE] (because there is less work to do)?
For smaller installations, the vanilla setup could of course be
either of your suggestions, or my just suggested combo, it is
probably a matter of taste. I'd prefer you #1 or the combo... I'll
think about it a bit more, and will fix the port. :)
Now that I know there's a good reason for running both it seems to me
that the combo is a sane default. The only objection I can think of is
that ports typically default to being off -- i.e. you must explicitly
enable rc.d scripts in rc.conf, explicitly copy/symlink web apps into
your server's root directory, etc.
Thus, the periodic vacuum job that runs by default simply because the
port is installed, even if you don't enable PostgreSQL itself to run,
feels somewhat wrong. The impact is minimal -- an extra message in the
daily output complaining about being unable to connect -- so it's not
something that is critical.
I'm not sure of a good solution however. Do you think it would be
possible / reasonable for the periodic job to check if the user has set
postgresql_enable and do nothing if it is not enabled?
Craig
___
freebsd-ports@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to [EMAIL PROTECTED]
PostgreSQL 8.x defaults
I recently installed some PostgreSQL 8.2 servers (and upgraded some from 8.1), and it reminded me of a few lingering nits in our port that bug me. Mostly the port seems that it can't make up its mind about VACCUM strategy. * We have a patch that sets autovacuum = yes in the default postgresql.conf. However, it leaves the default stats_row_level = no, so autovacuum doesn't actually run. * Despite trying to turn on autovacuum, the port installs periodic/daily/502.pgsql, which runs VACUUM (not even VACUUM ANALYZE) by default nightly. It seems to me that we should pick one or the other -- either 1. Fully enable autovacuum and default to daily_pgsql_vacuum_enable=NO to avoid a superfluous cron job or 2. Leave the periodic job enabled and not tease people with an ineffectual autovacuum = yes in postgresql.conf. Of the two I'd prefer the former, but that's just my opinion. Craig ___ freebsd-ports@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-ports To unsubscribe, send any mail to [EMAIL PROTECTED]
