From: Marc Cousin <mcousin@sigma.fr>
Organization: Sigma Informatique
To: pgsql-performance@postgresql.org
Subject: insert performance for win32
Date: Tue, 6 Sep 2005 16:12:27 +0200
User-Agent: KMail/1.8.2
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200509061612.27746.mcousin@sigma.fr>

Hi, 

I usually use PostgreSQL coupled with Linux, but I have to use Windows for a 
perticular project.

So I wanted to do some tests to know if the performance will be acceptable (I 
don't need PostgreSQL to be as fast with windows as with linux, but it has to 
be usable...).

I started with trying to do lots of inserts, and I'm quite astonished by the 
catastrophics results ...

The test :
The computer was the same (my workstation, a PIV Dell with SATA disk), dual 
boot

The windows OS is XP.

Both Oses are PostgreSQL 8.0.3

Both PostgreSQL clusters (windows and linux) have the same tuning 
(shared_buffers=20000, wal_buffers=128, checkpoint_segments=10)

Before each test, the clusters are vacuum analyzed, and the test database is 
recreated.

The script is quite dumb :
BEGIN;
CREATE TABLE test (col1 serial, col2 text);
INSERT INTO test (col2) values ('test');
INSERT INTO test (col2) values ('test');
INSERT INTO test (col2) values ('test');
INSERT INTO test (col2) values ('test');
INSERT INTO test (col2) values ('test');
...... 500,000 times
Then COMMIT.

I know it isn't realistic, but I needed to start with something :)

The results are as follows :
Linux : 1'9''
Windows : 9'38''

What I've tried to solve, and didn't work :

- Deactivate antivirus on windows
- fsync=no
- raise the checkpoint_segments value (32)
- remove hyperthreading (who knows...)

I don't know what could cause this (I'm not a windows admin...at all). All I 
see is a very high kernel load during the execution of this script, but I 
can't determine where it comes from.


I'd like to know if this is a know problem, if there is something I can do, 
etc...

Thanks a lot.