From: Edoardo Serra <osdevel@webrainstorm.it>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postmaster using only 4-5% CPU
Date: Thu, 23 Mar 2006 10:14:24 +0100
Message-ID: <7.0.0.16.2.20060322120000.0796c930@webrainstorm.it> (raw)
In-Reply-To: <1142963080.17883.193.camel@state.g2switchworks.com>
References: <7.0.0.16.2.20060321115738.060f9078@webrainstorm.it>
<1142963080.17883.193.camel@state.g2switchworks.com>
At 18.44 21/03/2006, Scott Marlowe wrote:
>Here's what's happening. On the "fast" machine, you are almost
>certainly using IDE drives.
Oh yes, the fast machine has IDE drives, you got it ;)
>Meanwhile, back in the jungle... The machine with IDE drives operates
>differently. Most, if not all, IDE drives, when told by the OS to
>fsync() tell the OS immediately that the fsync() call has completed, and
>the data is written to the drive. Shortly thereafter, the drive
>actually commences to write the data out. When it gets a chance.
I really didn't know this behaviour of IDE drives.
I was stracing the postmaster while investigating the problem and noticed
many fsync syscalls (one after each INSERT).
I was investigating on it but I didn't explain me why SCSI was slower.
You helped me a lot ;) tnx
>For PostgreSQL, the way IDE drives operate is dangerous. Write data
>out, call fsync(), get an immediate return, mark the data as committed,
>move on the next operation, operator trips over power cord / power
>conditioner explodes, power supply dies, brown out causes the machine to
>reboot, et. al., and when the machine comes up, PostgreSQL politely
>informs you that your database is corrupt, and you come to the
>pgsql-general group asking how to get your database back online. Very
>bad.
Yes, it sounds very bad... what about SATA drives ?
I heard about command queueing in SATA but I don't know if the kernel
handles it properly
>Try wrapping the inserts in the sql file in begin; / commit; statements,
>like so:
>
>begin;
>insert into table ...
>(100,000 inserts here)
>insert into table ...
>commit;
>
>and it should fly.
Oh, yes with the insert wrapped in a transaction the import time is as follows:
- SCSI: 35 secs
- IDE: 50 secs
>When a good friend of mine first started using PostgreSQL, he was a
>total MySQL bigot. He was importing a 10,000 row dataset, and made a
>smartassed remark after 10 minutes how it would have imported in minutes
>on MySQL. It was a test database, so I had him stop the import, delete
>all the imported rows, and wrap the whole import inside begin; and
>commit;
>
>The import took about 20 seconds or so.
;)
>Now, for the interesting test. Run the import on both machines, with
>the begin; commit; pairs around it. Halfway through the import, pull
>the power cord, and see which one comes back up. Don't do this to
>servers with data you like, only test machines, obviously. For an even
>more interesting test, do this with MySQL, Oracle, DB2, etc...
I will surely run a test like this ;)
Tnx a lot again for help
Regards
Edoardo Serra
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: osdevel@webrainstorm.it, smarlowe@g2switchworks.com
Subject: Re: Postmaster using only 4-5% CPU
In-Reply-To: <7.0.0.16.2.20060322120000.0796c930@webrainstorm.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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