Re: [PERFORM] Tunning postgresql on linux (fedora core 3)

Hello,
I have a little time and I decided to improve the performance of my
server(s). I have found on google many 'tips' in tuning linux kernel and
postgresql database ... but I can't decide wich 'how-to' is better ... :(
So the question is: where to find a 'easy' and complete documentation
about this tweaks ... ?

thank you,
Adrian Din

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Din Adrian wrote:
> Hello,
> I have a little time and I decided to improve the performance of my
> server(s). I have found on google many 'tips' in tuning linux kernel
> and postgresql database ... but I can't decide wich 'how-to' is better
> ... :(
> So the question is: where to find a 'easy' and complete documentation
> about this tweaks ... ?

Try the "performance tuning" article linked from this page:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

--
Richard Huxton
Archonet Ltd

Please CC the mailing list as well as replying to me, so that others can
help too.

Din Adrian wrote:
> yes I have read this as well ...
>
> One question about this option:
> fsync = true / false
> a) I have Raid and UPS - it is safe to turn this off ... (' But be
> very aware that any unexpected database shutdown will force you to
> restore the database from your last backup.' - from my last backup if
> the server goes down ??? why ? just at 'any unexpected database
> shutdown' ? ....!!!!!!!!!!!)

Because fsync=true flushes transaction details to disk (the Write Ahead
Log). That way if (say) the power-supply in your server fails you can
check the WAL and compare it to the main database files to make sure
everything is in a known state.

> b) in docs say that after 7.2 seting this to false does'n turn off the
> wall ...!? wich option does?

The docs don't say that, as far as I can see. It doesn't make sense to
turn off the WAL.
--
Richard Huxton
Archonet Ltd

I'll repeat myself:

Please CC the mailing list as well as replying to me, so that others
can help too.

Din Adrian wrote:
>
> On Thu, 03 Feb 2005 13:56:50 +0000, Richard Huxton <dev(at)archonet(dot)com>
> wrote:
>
>> Please CC the mailing list as well as replying to me, so that others
>> can help too.
>>
>>
>>> b) in docs say that after 7.2 seting this to false does'n turn off
>>> the wall ...!? wich option does?
>>
>>
>> The docs don't say that, as far as I can see. It doesn't make sense
>> to turn off the WAL.
>
>
> hmm this is the doc about ...
>
> ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop
> checkpointing, however. This is a change in the notes that follow Turn
> WAL off (fsync=false) only for a read-only database or one where the
> database can be regenerated from external software. While RAID plus
> UPSes can do a lot to protect your data, turning off fsync means that
> you will be restoring from backup in the event of hardware or power
> failure.'

I don't know what this is, and you don't give a URL, but it DOES NOT
appear to be in the manuals.

You should probably read the sections of the manuals regarding "run-time
configuration" and "write ahead logs". The manuals are quite extensive,
are available online at http://www.postgresql.org/ and also in most
distributions.

This is probably a good place to start.
http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL

> If you turn it off you should have more speed ... !!!???

Basically, as I said in my last email - fsync=true makes sure
transaction details are safely stored on disk. If you turn this off, the
database doesn't have to wait for the data to physically be written to
the disk. But, if power fails then data might be in OS or disk cache and
so lost when you restart the machine.

Please CC the mailing list if you reply to this message.
--
Richard Huxton
Archonet Ltd

Din Adrian wrote:
> sorry about cc ...
> this is the site:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> but I gues is not right ... hmm

It's not that it's incorrect, just that you should always use the
manuals as a starting point.

> On Thu, 03 Feb 2005 14:52:04 +0000, Richard Huxton <dev(at)archonet(dot)com>
> wrote:
>
>> I'll repeat myself:
>>
>>
>>
>>
>> Please CC the mailing list as well as replying to me, so that others
>> can help too.
>>
>>
>>
>>
>> Din Adrian wrote:
>>
>>> On Thu, 03 Feb 2005 13:56:50 +0000, Richard Huxton
>>> <dev(at)archonet(dot)com> wrote:
>>>
>>>> Please CC the mailing list as well as replying to me, so that
>>>> others can help too.
>>>>
>>>>
>>>>> b) in docs say that after 7.2 seting this to false does'n turn
>>>>> off the wall ...!? wich option does?
>>>>
>>>>
>>>>
>>>> The docs don't say that, as far as I can see. It doesn't make sense
>>>> to turn off the WAL.
>>>
>>> hmm this is the doc about ...
>>> ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does
>>> stop checkpointing, however. This is a change in the notes that
>>> follow Turn WAL off (fsync=false) only for a read-only database or
>>> one where the database can be regenerated from external software.
>>> While RAID plus UPSes can do a lot to protect your data, turning
>>> off fsync means that you will be restoring from backup in the event
>>> of hardware or power failure.'
>>
>>
>> I don't know what this is, and you don't give a URL, but it DOES NOT
>> appear to be in the manuals.
>>
>> You should probably read the sections of the manuals regarding
>> "run-time configuration" and "write ahead logs". The manuals are
>> quite extensive, are available online at http://www.postgresql.org/
>> and also in most distributions.
>>
>> This is probably a good place to start.
>> http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL
>>
>>
>>> If you turn it off you should have more speed ... !!!???
>>
>>
>> Basically, as I said in my last email - fsync=true makes sure
>> transaction details are safely stored on disk. If you turn this off,
>> the database doesn't have to wait for the data to physically be
>> written to the disk. But, if power fails then data might be in OS or
>> disk cache and so lost when you restart the machine.
>>
>> Please CC the mailing list if you reply to this message.
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>
>
>

--
Richard Huxton
Archonet Ltd

sorry about cc ...
this is the site:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
but I gues is not right ... hmm

Adrian Din

On Thu, 03 Feb 2005 14:52:04 +0000, Richard Huxton <dev(at)archonet(dot)com>
wrote:

> I'll repeat myself:
>
>
>
>
> Please CC the mailing list as well as replying to me, so that others
> can help too.
>
>
>
>
> Din Adrian wrote:
>> On Thu, 03 Feb 2005 13:56:50 +0000, Richard Huxton <dev(at)archonet(dot)com>
>> wrote:
>>
>>> Please CC the mailing list as well as replying to me, so that others
>>> can help too.
>>>
>>>
>>>> b) in docs say that after 7.2 seting this to false does'n turn off
>>>> the wall ...!? wich option does?
>>>
>>>
>>> The docs don't say that, as far as I can see. It doesn't make sense
>>> to turn off the WAL.
>> hmm this is the doc about ...
>> ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop
>> checkpointing, however. This is a change in the notes that follow Turn
>> WAL off (fsync=false) only for a read-only database or one where the
>> database can be regenerated from external software. While RAID plus
>> UPSes can do a lot to protect your data, turning off fsync means that
>> you will be restoring from backup in the event of hardware or power
>> failure.'
>
> I don't know what this is, and you don't give a URL, but it DOES NOT
> appear to be in the manuals.
>
> You should probably read the sections of the manuals regarding "run-time
> configuration" and "write ahead logs". The manuals are quite extensive,
> are available online at http://www.postgresql.org/ and also in most
> distributions.
>
> This is probably a good place to start.
> http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL
>
>> If you turn it off you should have more speed ... !!!???
>
> Basically, as I said in my last email - fsync=true makes sure
> transaction details are safely stored on disk. If you turn this off, the
> database doesn't have to wait for the data to physically be written to
> the disk. But, if power fails then data might be in OS or disk cache and
> so lost when you restart the machine.
>
> Please CC the mailing list if you reply to this message.
> --
> Richard Huxton
> Archonet Ltd
>

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/