pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL is extremely slow on Windows
13+ messages / 8 participants
[nested] [flat]
* PostgreSQL is extremely slow on Windows
@ 2005-02-22 14:58 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 13+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-22 14:58 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* PostgreSQL is extremely slow on Windows
@ 2005-02-22 15:00 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 1 reply; 13+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-22 15:00 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-22 16:30 Mitch Pirtle <mitch.pirtle@gmail.com>
parent: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 13+ messages in thread
From: Mitch Pirtle @ 2005-02-22 16:30 UTC (permalink / raw)
To: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-22 18:15 Magnus Hagander <mha@sollentuna.net>
0 siblings, 2 replies; 13+ messages in thread
From: Magnus Hagander @ 2005-02-22 18:15 UTC (permalink / raw)
To: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
>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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 04:58 Neil Conway <neilc@samurai.com>
parent: Magnus Hagander <mha@sollentuna.net>
1 sibling, 0 replies; 13+ messages in thread
From: Neil Conway @ 2005-02-23 04:58 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 10:41 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 13+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-23 10:41 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: pgsql-performance
Hi,
I changed fsync to false. It took 8 minutes to restore the full database.
That is 26 times faster than before. :-/ (aprox. 200 tps)
With background writer it took 12 minutes. :-(
The funny thing is, I had a VMWARE emulation on the same Windows mashine,
running Red Hat, with fsync turned on. It took also 8 minutes to finish.
Probably the Linux code is better + VMWARE optimises (physical) disk
access.(?)
It seems to me, I need 2 types of operating modes:
- For bulk loading (database restore) : fsync=false
- Normal operation fsync=true
Am I right? How can I do it "elegantly"?
I Think, it should be a "performance tuning guide" in the docomentation.
(not just explaning the settings) Playing with the settings could be quite
anoying.
Anyway, thanks for the tips.
Bye,
Vig Sándor
-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: Tuesday, February 22, 2005 7:15 PM
To: Vig, Sandor (G/FI-2); pgsql-performance@postgresql.org
Subject: RE: [PERFORM] PostgreSQL is extremely slow on Windows
>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
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 14:37 Markus Schaber <schabios@logi-track.com>
parent: Magnus Hagander <mha@sollentuna.net>
1 sibling, 0 replies; 13+ messages in thread
From: Markus Schaber @ 2005-02-23 14:37 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 255B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 14:50 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 13+ messages in thread
From: Magnus Hagander @ 2005-02-23 14:50 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
> > 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?
Uh. What I meant was a single *IDE* disk. Sorry. Been too deep into
helping ppl with IDE disks lately to remember that SCSI can be a lot
faster :-) And we're talking about restore of a dump, so it's a single
session.
(Strictly, that shuld be a 7200rpm IDE disk. I don't know if any others
are common, though)
//mha
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 14:54 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 13+ messages in thread
From: Magnus Hagander @ 2005-02-23 14:54 UTC (permalink / raw)
To: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; +Cc: pgsql-performance
> Hi,
>
> I changed fsync to false. It took 8 minutes to restore the
> full database.
> That is 26 times faster than before. :-/ (aprox. 200 tps)
> With background writer it took 12 minutes. :-(
That seems reasonable.
> The funny thing is, I had a VMWARE emulation on the same
> Windows mashine, running Red Hat, with fsync turned on. It
> took also 8 minutes to finish.
> Probably the Linux code is better + VMWARE optimises (physical) disk
> access.(?)
Vmware makes fsync() into a no-op. It will always cache the disk.
(This is vmware workstation. Their server products behave differntly, of
course)
> It seems to me, I need 2 types of operating modes:
> - For bulk loading (database restore) : fsync=false
> - Normal operation fsync=true
Yes, fsync=false is very good for bulk loading *IFF* you can live with
data loss in case you get a crash during load.
> Am I right? How can I do it "elegantly"?
You'll need to edit postgresql.conf and restart the server for this.
> I Think, it should be a "performance tuning guide" in the
> docomentation.
> (not just explaning the settings) Playing with the settings
> could be quite anoying.
There is some information on techdocs.postgresql.org you miht be
interested in.
//Magnus
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 22:35 Neil Conway <neilc@samurai.com>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 2 replies; 13+ messages in thread
From: Neil Conway @ 2005-02-23 22:35 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
Magnus Hagander wrote:
> Yes, fsync=false is very good for bulk loading *IFF* you can live with
> data loss in case you get a crash during load.
It's not merely data loss -- you could encounter potentially
unrecoverable database corruption.
There is a TODO item about allowing the delaying of WAL writes. If we
maintain the WAL invariant (that is, a WAL record describing a change
must hit disk before the change itself does) but simply don't flush the
WAL at transaction commit, we should be able to get better performance
without the risk of database corruption (so we would need to keep pages
modified by the committed transaction pinned in memory until the WAL has
been flushed, which might be done on a periodic basis).
Naturally, there is a risk of losing data in the period between
transaction commit and syncing the WAL, but no risk of database
corruption. This seems a reasonable approach to providing better
performance for people who don't need the strict guarantees provided by
fsync=true.
-Neil
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-23 22:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-02-23 22:56 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Magnus Hagander <mha@sollentuna.net>; Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
Neil Conway <neilc@samurai.com> writes:
> There is a TODO item about allowing the delaying of WAL writes. If we
> maintain the WAL invariant (that is, a WAL record describing a change
> must hit disk before the change itself does) but simply don't flush the
> WAL at transaction commit, we should be able to get better performance
> without the risk of database corruption (so we would need to keep pages
> modified by the committed transaction pinned in memory until the WAL has
> been flushed, which might be done on a periodic basis).
That interlock already exists, in the form of the bufmgr LSN logic.
I think this "feature" might be as simple as
XLogFlush(recptr);
becomes
/* Must flush if we are deleting files... */
if (PerCommitFlush || nrels > 0)
XLogFlush(recptr);
in RecordTransactionCommit.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-24 21:25 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2005-02-24 21:25 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Magnus Hagander <mha@sollentuna.net>; Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
Neil Conway wrote:
> Magnus Hagander wrote:
> > Yes, fsync=false is very good for bulk loading *IFF* you can live with
> > data loss in case you get a crash during load.
>
> It's not merely data loss -- you could encounter potentially
> unrecoverable database corruption.
>
> There is a TODO item about allowing the delaying of WAL writes. If we
> maintain the WAL invariant (that is, a WAL record describing a change
> must hit disk before the change itself does) but simply don't flush the
> WAL at transaction commit, we should be able to get better performance
> without the risk of database corruption (so we would need to keep pages
> modified by the committed transaction pinned in memory until the WAL has
> been flushed, which might be done on a periodic basis).
>
> Naturally, there is a risk of losing data in the period between
> transaction commit and syncing the WAL, but no risk of database
> corruption. This seems a reasonable approach to providing better
> performance for people who don't need the strict guarantees provided by
> fsync=true.
Right. Just for clarity, you might lose the last 5 seconds of
transactions, but all transactsions would be completely committed or
aborted in your datbase. Right now with fsync off you can get
transactions partially commited in your database, which is a serious
problem (think moving money from one account to another).
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: PostgreSQL is extremely slow on Windows
@ 2005-02-25 05:02 Greg Stark <gsstark@mit.edu>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Greg Stark @ 2005-02-25 05:02 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Neil Conway <neilc@samurai.com>; Magnus Hagander <mha@sollentuna.net>; Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>; pgsql-performance
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Right now with fsync off you can get transactions partially commited in your
> database, which is a serious problem (think moving money from one account to
> another).
It's worse than that. You can get a totally corrupted database. Things like
duplicated records (the before and after image of an update). Or indexes that
are out of sync with the table. This can cause strange inconsistent results
depending on the plan queries use, or outright database crashes.
--
greg
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-02-25 05:02 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 14:58 PostgreSQL is extremely slow on Windows Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
2005-02-22 15:00 PostgreSQL is extremely slow on Windows Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
2005-02-22 16:30 ` Mitch Pirtle <mitch.pirtle@gmail.com>
2005-02-22 18:15 Re: PostgreSQL is extremely slow on Windows Magnus Hagander <mha@sollentuna.net>
2005-02-23 04:58 ` Neil Conway <neilc@samurai.com>
2005-02-23 14:37 ` Markus Schaber <schabios@logi-track.com>
2005-02-23 10:41 Re: PostgreSQL is extremely slow on Windows Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
2005-02-23 14:50 Re: PostgreSQL is extremely slow on Windows Magnus Hagander <mha@sollentuna.net>
2005-02-23 14:54 Re: PostgreSQL is extremely slow on Windows Magnus Hagander <mha@sollentuna.net>
2005-02-23 22:35 ` Neil Conway <neilc@samurai.com>
2005-02-23 22:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-24 21:25 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-02-25 05:02 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox