Thread: PostgreSQL is extremely slow on Windows

PostgreSQL is extremely slow on Windows

From
"Vig, Sandor (G/FI-2)"
Date:


Hi,

I've downloaded the latest release (PostgreSQL 8.0) for windows.
Installation was OK, but I have tried to restore a database.
It had more than ~100.000 records. Usually I use PostgreSQL
under Linux, and it used to be done under 10 minutes.

Under W2k und XP it took 3 hours(!) Why is it so slow????

The commands I used:

Under Linux: (duration: 1 minute)
    pg_dump -D databasename > databasename.db

Under Windows: (duration: 3 - 3.5 hours(!))
    psql databasename < databasename.db >nul

It seemed to me, that only 20-30 transactions/sec were
writen to the database.

I need to write scripts for automatic (sheduled) database
backup and recovery.

Help anyone?

Bye,

Vig Sándor


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

Re: PostgreSQL is extremely slow on Windows

From
Mitch Pirtle
Date:
On Tue, 22 Feb 2005 16:00:59 +0100, Vig, Sandor (G/FI-2)
<Sandor.Vig@audi.hu> wrote:
>
>
> Hi,
>
> I've downloaded the latest release (PostgreSQL 8.0) for windows.
> Installation was OK, but I have tried to restore a database.
> It had more than ~100.000 records. Usually I use PostgreSQL
> under Linux, and it used to be done under 10 minutes.
>
> Under W2k und XP it took 3 hours(!) Why is it so slow????

Can you tell us your postgresql.conf configuration settings? We cannot
help without some information about your environment...

-- Mitch

Re: PostgreSQL is extremely slow on Windows

From
"Magnus Hagander"
Date:
>I've downloaded the latest release (PostgreSQL 8.0) for windows.
>Installation was OK, but I have tried to restore a database.
>It had more than ~100.000 records. Usually I use PostgreSQL
>under Linux, and it used to be done under 10 minutes.
>
>Under W2k und XP it took 3 hours(!) Why is it so slow????
>
>The commands I used:
>
>Under Linux: (duration: 1 minute)
>    pg_dump -D databasename > databasename.db
>
>Under Windows: (duration: 3 - 3.5 hours(!))
>    psql databasename < databasename.db >nul
>
>It seemed to me, that only 20-30 transactions/sec were
>writen to the database.

20-30 transactionsi s about what you'll get on a single disk on Windows
today.
We have a patch in testing that will bring this up to about 80.
You can *never* get above 80 without using write cache, regardless of
your OS, if you have a single disk. You might want to look into wether
write cacheing is enabled on your linux box, and disable it. (unless you
are using RAID) A lot points towards write cache enabled on your system.

If you need the performance that equals the one with write cache on, you
can set fsync=off. But then you will lose the guarantee that your
machine will survive an unclean shutdown or crash. I would strongly
advice against it on a production system - same goes for running with
write cache!

//Magnus

Re: PostgreSQL is extremely slow on Windows

From
Neil Conway
Date:
Magnus Hagander wrote:
> You can *never* get above 80 without using write cache, regardless of
> your OS, if you have a single disk.

Why? Even with, say, a 15K RPM disk? Or the ability to fsync() multiple
concurrently-committing transactions at once?

-Neil

Re: PostgreSQL is extremely slow on Windows

From
Markus Schaber
Date:
Hi, Magnus & all,

Magnus Hagander schrieb:
> 20-30 transactionsi s about what you'll get on a single disk on Windows
> today.
> We have a patch in testing that will bring this up to about 80.
> You can *never* get above 80 without using write cache, regardless of
> your OS, if you have a single disk. You might want to look into wether
> write cacheing is enabled on your linux box, and disable it. (unless you
> are using RAID) A lot points towards write cache enabled on your system.

Note that you can get higher rates for the server as a whole when using
concurrent transactions (that is, several independend connections
committing at the same time). The commit delay settings should be tuned
accordingly.


Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com


Attachment