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.
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.
> > 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
> 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
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
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
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
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