Re: [PATCHES] How can I use 2GB of shared buffers on Windows?

On Fri, Feb 09, 2007 at 01:06:13PM +0900, Takayuki Tsunakawa wrote:
> > On Thu, Feb 08, 2007 at 09:50:26PM +0900, Takayuki Tsunakawa wrote:
> >> When I try to start PostgreSQL 8.2.1 on Windows 2003 Server with
> >> shared_buffers=1024MB, I get the following error messages in the
> Event
> >> Log (with log_min_messages=debug5) and can't start PostgreSQL:
> >
> > Is this for testing, or for production? From what I've heard, you
> would
> > normally never want that much shared memory - I've seen more reports
> on
> > taht you shuld keep it as low as possible, really. For performance
> > reasons.
>
> For testing. I wanted to place all data in shared buffers to
> eliminate reads from disk while I run pgbench repeatedly (actually
> most reads should come from kernel cache, though.)

Right. Which is why you're likely to see better performance if you keep
shared buffers smaller. There is something in dealing with it that's
slow on win32, per reports from the field. It needs to be investigated
further...

> Does PostgreSQL for Windows have any problem when using a large
> database cache unlike UNIX versions?

We've had reports that it's slow with large shared_buffers, yes.

> I'm excited about your current
> great work to enable building all of PostgreSQL with MSVC. I thought
> you are aiming at making PostgreSQL 64-bit on Windows in the near
> future (though you may not have signified in ML.) I'm afraid MinGW
> will not extend to 64-bit (for x64 and Itanium) at least reliably and
> immediately, due to the difference of data model -- 'long' is still
> 32-bit in 64-bit applications on Windows. I thought Magnus-san got
> worried about it and started the activity of completely switching to
> MSVC.

Well, that's one of the reasons, yes. But being able to build 64-bit
won't automatically mean that larger shared buffers is the way to go.

> BTW, the current PostgreSQL for Windows is very slow, isn't it? I
> compared the performance of PostgreSQL 8.2.x for Linux (RHEL4 for x86,
> kernel 2.6.x) and Windows Server 2003. I ran 'pgbench -c32 -t500' on
> the same machine with the same disk layout for data files and WAL,
> i.e. they are stored on separate disks. The settings in
> postgresql.conf is the same, except for wal_sync_method -- it is set
> to open_sync on Linux and open_datasync on Windows, because they are
> the best for each platform.
> Linux version shows 1100 tps, but Windows version shows only 450 tps.

It's certainly slower than on Linux, but that's a larger difference than
i've usually seen. Are you sure you're not running this on for example
IDE disks with write-cache that lies? Windows will write through that
write-cache even if the disk lies, whereas most linux versions won't. At
least that used to be the case not too long ago, but there has also been
talking about fixign that in linux, so maybe that's done...

Also note that when you run pg_bench on the local machine, you take a
much higher hit from the fact that context switching between processes
is a lot more expensive on Windows than it is on Linux. But it shouldn't
be big enough to explain the huge difference you had in your test.

//Magnus