Обсуждение: Write ahead logging

Hi

I am doing some performance testing using postgresql. When measuring the
latency of commit I got some results, which make me doubt, if the logging
data is written to disk in time:

Using a slow disk (5400rpm) I measured about 3-5 ms for a commit. But
average rotational delay is already higher for that disk (5.5ms).
So the question is: has anybody verified, that the log is written to disk
before returning from commit?

System used was:

Linux 2.4.20
filesystem: ext3
postgresql postgresql-7.2

Regards,

    Matthias Meixner

--
Matthias Meixner                       meixner@informatik.tu-darmstadt.de
Technische Universität Darmstadt
Datenbanken und Verteilte Systeme              Telefon (+49) 6151 16 6232
Wilhelminenstraße 7, D-64283 Darmstadt, Germany    Fax (+49) 6151 16 6229

On Thu, 20 Feb 2003 09:42:02 +0100, Matthias Meixner
<meixner@dvs1.informatik.tu-darmstadt.de> wrote:
>Using a slow disk (5400rpm) I measured about 3-5 ms for a commit. But
>average rotational delay is already higher for that disk (5.5ms).
>So the question is: has anybody verified, that the log is written to disk
>before returning from commit?

Some (or all?) IDE disks are known to lie:  they report success as
soon as the data have reached the drive's RAM.

Servus
 Manfred

> >So the question is: has anybody verified, that the log is written to disk
> >before returning from commit?
>
> Some (or all?) IDE disks are known to lie:  they report success as
> soon as the data have reached the drive's RAM.

under linux, hdparm -W can turn off the write cache of IDE disk, maybe you
should try with write-caching turned off.

best regards,
    mario weilguni

Mario Weilguni wrote:
>>>So the question is: has anybody verified, that the log is written to disk
>>>before returning from commit?
>>
>>Some (or all?) IDE disks are known to lie:  they report success as
>>soon as the data have reached the drive's RAM.
>
>
> under linux, hdparm -W can turn off the write cache of IDE disk, maybe you
> should try with write-caching turned off.

Yes, that made a big difference. Latency went up to 25-95ms.

Regards,

    Matthias Meixner

--
Matthias Meixner                       meixner@informatik.tu-darmstadt.de
Technische Universität Darmstadt
Datenbanken und Verteilte Systeme              Telefon (+49) 6151 16 6232
Wilhelminenstraße 7, D-64283 Darmstadt, Germany    Fax (+49) 6151 16 6229

What were you using to measure the latency.  Don't suppose you could
send it over.  The admins locally don't like what I used to do the test
with -- they don't want to turn off write-caching for other reasons.

On Fri, 2003-02-21 at 03:52, Matthias Meixner wrote:
> Mario Weilguni wrote:
> >>>So the question is: has anybody verified, that the log is written to disk
> >>>before returning from commit?
> >>
> >>Some (or all?) IDE disks are known to lie:  they report success as
> >>soon as the data have reached the drive's RAM.
> >
> >
> > under linux, hdparm -W can turn off the write cache of IDE disk, maybe you
> > should try with write-caching turned off.
>
> Yes, that made a big difference. Latency went up to 25-95ms.
>
> Regards,
>
>     Matthias Meixner
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor wrote:
> What were you using to measure the latency.  Don't suppose you could
> send it over.  The admins locally don't like what I used to do the test
> with -- they don't want to turn off write-caching for other reasons.

I am doing an insert of few bytes, so that the amount of data does not
significantly affect the measured time. And for measuring time without
cache, I temporarily switched it off.

That was the code used (nothing unusual):

#include "timeval.h"

main()
{
     Timeval start,end;
EXEC SQL WHENEVER sqlerror sqlprint;
EXEC SQL WHENEVER not found sqlprint;

EXEC SQL CONNECT TO user@localhost;
     start=Timeval::Time();
EXEC SQL BEGIN;
EXEC SQL INSERT INTO test values ('qwertz');
EXEC SQL COMMIT;
     end=Timeval::Time();
     end-=start;
     printf("time: %d.%06d\n",end.tv_sec,end.tv_usec);
}


- Matthias Meixner

--
Matthias Meixner                       meixner@informatik.tu-darmstadt.de
Technische Universität Darmstadt
Datenbanken und Verteilte Systeme              Telefon (+49) 6151 16 6232
Wilhelminenstraße 7, D-64283 Darmstadt, Germany    Fax (+49) 6151 16 6229