about some parameters

Hi,

Every time i explain what is the fsync parameter for, the next thing i
always say is: "never turn it off", and now that we have
synchronous_commit there is no good reason for turn fsync off... so
why are we still let it be in the postgresql.conf where it's so
vulnerable to a misguided dba?
even just not show it should be great.

another parameter that is interesting is seq_page_cost, AFAIUI all the
other cost parameters (in the planner section of course) are relative
to this one. so what is the logic in allow changing it?
going a little further, when we have this per tablespace what will
mean to change a tablespace seq_page_cost? it will be relative to the
general one?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> Every time i explain what is the fsync parameter for, the next thing i
> always say is: "never turn it off", and now that we have
> synchronous_commit there is no good reason for turn fsync off... so
> why are we still let it be in the postgresql.conf where it's so
> vulnerable to a misguided dba?

It's a "never turn it off *in production*" kind of setting. That
doesn't make it useless --- in development or debug situations it
might be just fine.

The larger picture here is that just making sure fsync is on doesn't
mean squat as far as the safety of your data goes :-(. You also need
to be sure that every hardware/software layer below Postgres is honoring
fsync; which is a much harder thing to be sure of. In fact, one of the
most reliable end-to-end tests I can think of for that is to see whether
turning off fsync creates a major speed change. So we would not really
accomplish much by removing or hiding the parameter, and we would take
away a simple but important test procedure.

> another parameter that is interesting is seq_page_cost, AFAIUI all the
> other cost parameters (in the planner section of course) are relative
> to this one. so what is the logic in allow changing it?

Please read the discussions that went on when we added that parameter.

regards, tom lane

Tom Lane wrote:
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
>> Every time i explain what is the fsync parameter for, the next thing i
>> always say is: "never turn it off", and now that we have
>> synchronous_commit there is no good reason for turn fsync off... so
>> why are we still let it be in the postgresql.conf where it's so
>> vulnerable to a misguided dba?
>
> It's a "never turn it off *in production*" kind of setting. That
> doesn't make it useless --- in development or debug situations it
> might be just fine.

It's also not insane to turn it off if you're log-shipping to another
machine, and you don't mind having to restore from your last PITR log
update.

Some people use MyISAM tables (in MySQL) for raw speed at the cost of
reliability. With Pg and fsync=off you can get much of the speed, but
still have solid transactional behavior and rational handling of bad
data. Sure, you risk downtime if you have to restore, but with a warm
standby following the logs you can just cut over to it while you rebuild
the master.

With the approaching support for upcoming synchronous replication, it
makes even more sense to consider using fsync=off in some environments.

I wouldn't do it personally, but then I don't have apps where losing the
last few minutes' data is in case of failure is acceptable.

--
Craig Ringer

On Fri, Jan 1, 2010 at 12:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> another parameter that is interesting is seq_page_cost, AFAIUI all the
>> other cost parameters (in the planner section of course) are relative
>> to this one. so what is the logic in allow changing it?
>
> Please read the discussions that went on when we added that parameter.
>

digging the archives is alway an interesting sport, from this one i
have learnt some things:

1) now i know was the meaning of GUC: Grand Unified Config (one mistery less)
2) that seq_page_cost was added when i was around here but apparently
not looking
3) seems like the idea of this GUC started as a multiplier for all
*_cost parameters but ended being just for random_page_cost
4) ... and that Tom has three hands
(http://archives.postgresql.org/pgsql-hackers/2006-05/msg01346.php)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157