pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: Initial database loading and IDE x SCSI
5+ messages / 4 participants
[nested] [flat]
* RES: Initial database loading and IDE x SCSI
@ 2006-06-02 19:54 carlosreimer@terra.com.br
2006-06-02 20:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Lewis <mark.lewis@mir3.com>
0 siblings, 1 reply; 5+ messages in thread
From: carlosreimer@terra.com.br @ 2006-06-02 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +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 ...
>
Many thanks for the answers! There are some more thinks I could not
understand about this issue?
I was considering it but if you have a lot of writes operations, will not
the disk cache full quickly?
If it´s full will not the system wait until something could be write to the
disk surface?
If you have almost all the time the cache full will it not useless?
Should not, in this scenary, with almost all the time the cache full, IDE
and SCSI write operations have almost the same performance?
Thanks in advance,
Reimer
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RES: Initial database loading and IDE x SCSI
2006-06-02 19:54 RES: Initial database loading and IDE x SCSI carlosreimer@terra.com.br
@ 2006-06-02 20:13 ` Mark Lewis <mark.lewis@mir3.com>
2006-06-03 00:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Lewis @ 2006-06-02 20:13 UTC (permalink / raw)
To: carlosreimer@terra.com.br; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Fri, 2006-06-02 at 16:54 -0300, carlosreimer@terra.com.br wrote:
> > <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 ...
> >
>
> Many thanks for the answers! There are some more thinks I could not
> understand about this issue?
>
> I was considering it but if you have a lot of writes operations, will not
> the disk cache full quickly?
>
> If it´s full will not the system wait until something could be write to the
> disk surface?
>
> If you have almost all the time the cache full will it not useless?
>
> Should not, in this scenary, with almost all the time the cache full, IDE
> and SCSI write operations have almost the same performance?
>
This is the ideal case. However, you only get to that case if you use
large transactions or run with fsync=off or run with a write cache (like
IDE drives, or nice RAID controllers which have a battery-backed cache).
Remember that one of the important qualities of a transaction is that
it's durable, so once you commit it the data is definitely stored on the
disk and one nanosecond later you could power the machine off and it
would still be there.
To achieve that durability guarantee, the system needs to make sure that
if you commit a transaction, the data is actually written to the
physical platters on the hard drive.
This means that if you take the naive approach to importing data (one
row at a time, each in its own transaction), then instead of blasting
data onto the hard drive at maximum speed, the application will wait for
the platter to rotate to the right position, write one row's worth of
data, then wait for the platter to rotate to the right position again
and insert another row, etc. This approach is very slow.
The naive approach works on IDE drives because they don't (usually)
honor the request to write the data immediately, so it can fill its
write cache up with several megabytes of data and write it out to the
disk at its leisure.
> Thanks in advance,
>
> Reimer
>
-- Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RES: Initial database loading and IDE x SCSI
2006-06-02 19:54 RES: Initial database loading and IDE x SCSI carlosreimer@terra.com.br
2006-06-02 20:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Lewis <mark.lewis@mir3.com>
@ 2006-06-03 00:13 ` Mark Kirkwood <markir@paradise.net.nz>
2006-06-04 02:03 ` Re: RES: Initial database loading and IDE x SCSI Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Kirkwood @ 2006-06-03 00:13 UTC (permalink / raw)
To: carlosreimer@terra.com.br; +Cc: Mark Lewis <mark.lewis@mir3.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Mark Lewis wrote:
>
> The naive approach works on IDE drives because they don't (usually)
> honor the request to write the data immediately, so it can fill its
> write cache up with several megabytes of data and write it out to the
> disk at its leisure.
>
FWIW - If you are using MacOS X or Windows, then later SATA (in
particular, not sure about older IDE) will honor the request to write
immediately, even if the disk write cache is enabled.
I believe that Linux 2.6+ and SATA II will also behave this way (I'm
thinking that write barrier support *is* in 2.6 now - however you would
be wise to follow up on the Linux kernel list if you want to be sure!)
In these cases data integrity becomes similar to SCSI - however, unless
you buy SATA specifically designed for a server type workload (e.g WD
Raptor), then ATA/SATA tend to fail more quickly if used in this way
(e.g. 24/7, hot/dusty environment etc).
Cheers
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RES: Initial database loading and IDE x SCSI
2006-06-02 19:54 RES: Initial database loading and IDE x SCSI carlosreimer@terra.com.br
2006-06-02 20:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Lewis <mark.lewis@mir3.com>
2006-06-03 00:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Kirkwood <markir@paradise.net.nz>
@ 2006-06-04 02:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-06-04 02:21 ` Re: RES: Initial database loading and IDE x SCSI Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2006-06-04 02:03 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: carlosreimer@terra.com.br, Mark Lewis <mark.lewis@mir3.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Mark Kirkwood wrote:
> Mark Lewis wrote:
>
> >
> > The naive approach works on IDE drives because they don't (usually)
> > honor the request to write the data immediately, so it can fill its
> > write cache up with several megabytes of data and write it out to the
> > disk at its leisure.
> >
>
> FWIW - If you are using MacOS X or Windows, then later SATA (in
> particular, not sure about older IDE) will honor the request to write
> immediately, even if the disk write cache is enabled.
>
> I believe that Linux 2.6+ and SATA II will also behave this way (I'm
> thinking that write barrier support *is* in 2.6 now - however you would
> be wise to follow up on the Linux kernel list if you want to be sure!)
>
> In these cases data integrity becomes similar to SCSI - however, unless
> you buy SATA specifically designed for a server type workload (e.g WD
> Raptor), then ATA/SATA tend to fail more quickly if used in this way
> (e.g. 24/7, hot/dusty environment etc).
The definitive guide to servers vs. desktop drives is:
http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RES: Initial database loading and IDE x SCSI
2006-06-02 19:54 RES: Initial database loading and IDE x SCSI carlosreimer@terra.com.br
2006-06-02 20:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Lewis <mark.lewis@mir3.com>
2006-06-03 00:13 ` Re: RES: Initial database loading and IDE x SCSI Mark Kirkwood <markir@paradise.net.nz>
2006-06-04 02:03 ` Re: RES: Initial database loading and IDE x SCSI Bruce Momjian <pgman@candle.pha.pa.us>
@ 2006-06-04 02:21 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 5+ messages in thread
From: Mark Kirkwood @ 2006-06-04 02:21 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: carlosreimer@terra.com.br, Mark Lewis <mark.lewis@mir3.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Bruce Momjian wrote:
>
> The definitive guide to servers vs. desktop drives is:
>
> http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf
>
Yeah - very nice paper, well worth a read (in spite of the fact that it
is also Seagate propaganda, supporting their marketing position and
terminology!)
Cheers
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-06-04 02:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-02 19:54 RES: Initial database loading and IDE x SCSI carlosreimer@terra.com.br
2006-06-02 20:13 ` Mark Lewis <mark.lewis@mir3.com>
2006-06-03 00:13 ` Mark Kirkwood <markir@paradise.net.nz>
2006-06-04 02:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-06-04 02:21 ` Mark Kirkwood <markir@paradise.net.nz>
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