Thread: PostgreSQL is extremely slow on Windows
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.
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
>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
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
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