Re: Postgres 7.3.1 poor insert/update/search performance

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Curt Sampson <cjs(at)cynic(dot)net>, Andrew Sullivan <andrew(at)libertyrms(dot)info>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
Date: 2003-02-03 00:41:00
Message-ID: 200302030041.h130f0P03763@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Neil Conway wrote:
> On Sun, 2003-02-02 at 05:39, Bruce Momjian wrote:
> > We need free-behind for large sequential scans, like Solaris has. Do we
> > have LRU-2 or LRU-K now?
>
> No.
>
> > As I remember, DIRECT doesn't return until the data hits the disk
> > (because there is no OS cache), so if you want to write a page so you
> > can reused the buffer, DIRECT would be quite slow.
>
> Why? If there is a finite amount of memory for doing buffering, the data
> needs to be written to disk at *some* point, anyway. And if we didn't
> use the OS cache, the size of the PostgreSQL shared buffer would be much
> larger (I'd think 80% or more of the physical RAM in a typical high-end
> machine, for dedicated PostgreSQL usage).
>
> One possible problem would be the fact that it might mean that writing
> out dirty pages would become part of some key code paths in PostgreSQL
> (rather than assuming that the OS can write out dirty pages in the
> background, as it chooses to). But there are lots of ways to work around
> this, notably by using a daemon to periodically write out some of the
> pages in the background.

Right. This is what we _don't_ want to do. If we need a buffer, we
need it now. We can't wait for some other process to write the buffer
directly to disk, nor do we want to group the writes somehow.

And the other person mentioning we have to group writes again causes the
same issues --- we are bypassing the kernel buffers which know more than
we do. I can see advantage of preventing double buffering _quickly_
being overtaken by the extra overhead of direct i/o.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Andreas Pflug 2003-02-04 12:15:46 SELECT DISTINCT is picky about constants
Previous Message Josh Berkus 2003-02-02 19:31:30 Re: Postgres 7.3.1 poor insert/update/search performance