Re: [Lsf-pc] Linux kernel impact on PostgreSQL performance

Claudio Freire <klaussfreire@gmail.com> wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> James Bottomley <James.Bottomley@hansenpartnership.com> wrote:

>>> I don't understand why this has to be absolute: if you advise
>>> us to hold the pages dirty and we do up until it becomes a
>>> choice to hold on to the pages or to thrash the system into a
>>> livelock, why would you ever choose the latter?

Because the former creates database corruption and the latter does
not.

>>> And if, as I'm assuming, you never would,

That assumption is totally wrong.

>>> why don't you want the kernel to make that choice for you?
>>
>> If you don't understand how write-ahead logging works, this
>> conversation is going nowhere.  Suffice it to say that the word
>> "ahead" is not optional.
>
> In essence, if you do flush when you shouldn't, and there is a
> hardware failure, or kernel panic, or anything that stops the
> rest of the writes from succeeding, your database is kaputt, and
> you've got to restore a backup.
>
> Ie: very very bad.

Yup.  And when that's a few terrabytes, you will certainly find
yourself wishing that you had been able to do a recovery up to the
end of the last successfully committed transaction rather than a
restore from backup.

Now, as Tom said, if there was an API to create write boundaries
between particular dirty pages we could leave it to the OS.  Each
WAL record's write would be conditional on the previous one and
each data page write would be conditional on the WAL record for the
last update to the page.  But nobody seems to think that would
yield acceptable performance.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company