pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInitial database loading and IDE x SCSI
4+ messages / 4 participants
[nested] [flat]
* Initial database loading and IDE x SCSI
@ 2006-06-02 18:25 carlosreimer@terra.com.br
2006-06-02 18:40 ` Re: Initial database loading and IDE x SCSI Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-02 18:41 ` Re: Initial database loading and IDE x SCSI Mark Lewis <mark.lewis@mir3.com>
2006-06-02 18:43 ` Re: Initial database loading and IDE x SCSI Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 4+ messages in thread
From: carlosreimer@terra.com.br @ 2006-06-02 18:25 UTC (permalink / raw)
To: pgsql-performance
Hi,
I would like to know if my supposition is right.
Considering an environment with only one hard disk attached to a server, an
initial loading of the database probably is much faster using an IDE/ATA
interface with write-back on than using an SCSI interface. That´s because of
the SCSI command interface overhead.
Then main advantage of SCSI interfaces, the multiuser environment is lost in
this scenery.
Am I right? Am I missing something here?
Even if I´m right, is something that could be done too improove SCSI loading
performance in this scenery?
Thanks in advance!
Reimer
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Initial database loading and IDE x SCSI
2006-06-02 18:25 Initial database loading and IDE x SCSI carlosreimer@terra.com.br
@ 2006-06-02 18:40 ` Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2006-06-02 18:40 UTC (permalink / raw)
To: carlosreimer@terra.com.br; +Cc: pgsql-performance
On Fri, 2006-06-02 at 13:25, carlosreimer@terra.com.br wrote:
> Hi,
>
> I would like to know if my supposition is right.
>
> Considering an environment with only one hard disk attached to a server, an
> initial loading of the database probably is much faster using an IDE/ATA
> interface with write-back on than using an SCSI interface. That´s because of
> the SCSI command interface overhead.
>
> Then main advantage of SCSI interfaces, the multiuser environment is lost in
> this scenery.
>
> Am I right? Am I missing something here?
>
> Even if I´m right, is something that could be done too improove SCSI loading
> performance in this scenery?
The answer is yes. And no.
IDE drives notoriously lie about their cache, so that if you have the
cache enabled, the IDE drive will nominally ack to an fsync before it's
actually written the data. So, the IDE drive will write faster, but
your data probably won't survive a system crash or power loss during a
write. If you turn off the cache, then the IDE drive will be much
slower.
SCSI overhead isn't really a big issue during loads because you're
usually writing data at a good clip, and the overhead of SCSI is pretty
small by comparison to how much data you'll be slinging.
However, SCSI drives don't lie about Fsync, so the maximum speed of your
output will be limited by the speed at which your machine can fsync the
pg_xlog output.
For a single disk system, just doing development or a reporting
database, an IDE drive is often just fine. But under no circumstances
should you put an accounting system on a single drive, especially IDE
with cache turned on.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Initial database loading and IDE x SCSI
2006-06-02 18:25 Initial database loading and IDE x SCSI carlosreimer@terra.com.br
@ 2006-06-02 18:41 ` Mark Lewis <mark.lewis@mir3.com>
2 siblings, 0 replies; 4+ messages in thread
From: Mark Lewis @ 2006-06-02 18:41 UTC (permalink / raw)
To: carlosreimer@terra.com.br; +Cc: pgsql-performance
On Fri, 2006-06-02 at 15:25 -0300, carlosreimer@terra.com.br wrote:
> Hi,
>
> I would like to know if my supposition is right.
>
> Considering an environment with only one hard disk attached to a server, an
> initial loading of the database probably is much faster using an IDE/ATA
> interface with write-back on than using an SCSI interface. That´s because of
> the SCSI command interface overhead.
No, it's because the SCSI drive is honoring the database's request to
make sure the data is safe.
> Then main advantage of SCSI interfaces, the multiuser environment is lost in
> this scenery.
>
> Am I right? Am I missing something here?
>
> Even if I´m right, is something that could be done too improove SCSI loading
> performance in this scenery?
You can perform the initial load in large transactions. The extra
overhead for ensuring that data is safely written to disk will only be
incurred once per transaction, so try to minimize the number of
transactions.
You could optionally set fsync=off in postgresql.conf, which means that
the SCSI drive will operate with no more safety than an IDE drive. But
you should only do that if you're willing to deal with catastrophic data
corruption. But if this is for a desktop application where you need to
support IDE drives, you'll need to deal with that anyway.
> Thanks in advance!
>
> Reimer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Initial database loading and IDE x SCSI
2006-06-02 18:25 Initial database loading and IDE x SCSI carlosreimer@terra.com.br
@ 2006-06-02 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-06-02 18:43 UTC (permalink / raw)
To: carlosreimer@terra.com.br; +Cc: pgsql-performance
<carlosreimer@terra.com.br> writes:
> I would like to know if my supposition is right.
> Considering an environment with only one hard disk attached to a server, an
> initial loading of the database probably is much faster using an IDE/ATA
> interface with write-back on than using an SCSI interface. That´s because of
> the SCSI command interface overhead.
I *seriously* doubt that.
If you see a difference in practice it's likely got more to do with the
SCSI drive not lying about write-complete ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-02 18:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-02 18:25 Initial database loading and IDE x SCSI carlosreimer@terra.com.br
2006-06-02 18:40 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-02 18:41 ` Mark Lewis <mark.lewis@mir3.com>
2006-06-02 18:43 ` Tom Lane <tgl@sss.pgh.pa.us>
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