Thread: Re: [PATCHES] COPY FROM performance improvements

Re: [PATCHES] COPY FROM performance improvements

From
"Joshua D. Drake"
Date:
Here is the SCSI output:

Web Server

SCSI subsystem driver Revision: 1.00
megaraid: v1.18j (Release Date: Mon Jul  7 14:39:55 EDT 2003)
megaraid: found 0x1028:0x000f:idx 0:bus 4:slot 3:func 0
scsi0 : Found a MegaRAID controller at 0xf883f000, IRQ: 18
scsi0 : Enabling 64 bit support
megaraid: [412W:H406] detected 1 logical drives
megaraid: supports extended CDBs.
megaraid: channel[1] is raid.
megaraid: channel[2] is raid.
scsi0 : LSI Logic MegaRAID 412W 254 commands 15 targs 5 chans 7 luns


Database Server

SCSI subsystem driver Revision: 1.00
megaraid: v1.18j (Release Date: Mon Jul  7 14:39:55 EDT 2003)
megaraid: found 0x101e:0x1960:idx 0:bus 5:slot 0:func 0
scsi0 : Found a MegaRAID controller at 0xf883f000, IRQ: 21
scsi0 : Enabling 64 bit support
megaraid: [196T:3.33] detected 1 logical drives
megaraid: supports extended CDBs.
megaraid: channel[1] is raid.
megaraid: channel[2] is raid.
scsi0 : LSI Logic MegaRAID 196T 254 commands 15 targs 5 chans 7 luns
Starting timer : 0 0
blk: queue c5f2d218, I/O limit 4095Mb (mask 0xffffffff)
scsi0: scanning virtual channel 0 for logical drives.
   Vendor: MegaRAID  Model: LD 0 RAID5   86G  Rev: 196T
   Type:   Direct-Access                      ANSI SCSI revision: 02
Starting timer : 0 0


The webserver is a 1U and it actually performs better on the IO than the
database server even though the database server is running 6 disks versus 3.

The database server is a PE (Power Edge) 6600

Database Server IO:

[root@master root]# /sbin/hdparm -tT /dev/sda

/dev/sda:
  Timing buffer-cache reads:   1888 MB in  2.00 seconds = 944.00 MB/sec
  Timing buffered disk reads:   32 MB in  3.06 seconds =  10.46 MB/sec

Second Database Server IO:

[root@pq-slave root]# /sbin/hdparm -tT /dev/sda

/dev/sda:
  Timing buffer-cache reads:   1816 MB in  2.00 seconds = 908.00 MB/sec
  Timing buffered disk reads:   26 MB in  3.11 seconds =   8.36 MB/sec
[root@pq-slave root]#

Which is just horrible.


Sincerely,

Joshua D. Drake






Patrick Welche wrote:
> On Thu, Jul 21, 2005 at 09:19:04PM -0700, Luke Lonergan wrote:
>
>>Joshua,
>>
>>On 7/21/05 7:53 PM, "Joshua D. Drake" <jd@commandprompt.com> wrote:
>>
>>>Well I know that isn't true at least not with ANY of the Dells my
>>>customers have purchased in the last 18 months. They are still really,
>>>really slow.
>>
>>That's too bad, can you cite some model numbers?  SCSI?
>
>
> I would be interested too, given
>
>    http://www.netbsd.org/cgi-bin/query-pr-single.pl?number=30531
>
>
> Cheers,
>
> Patrick
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: [PATCHES] COPY FROM performance improvements

From
"Luke Lonergan"
Date:
Joshua,

On 7/22/05 10:11 AM, "Joshua D. Drake" <jd@commandprompt.com> wrote:
> The database server is a PE (Power Edge) 6600
>
> Database Server IO:
>
> [root@master root]# /sbin/hdparm -tT /dev/sda
>
> /dev/sda:
>   Timing buffer-cache reads:   1888 MB in  2.00 seconds = 944.00 MB/sec
>   Timing buffered disk reads:   32 MB in  3.06 seconds =  10.46 MB/sec
>
> Second Database Server IO:
>
> [root@pq-slave root]# /sbin/hdparm -tT /dev/sda
>
> /dev/sda:
>   Timing buffer-cache reads:   1816 MB in  2.00 seconds = 908.00 MB/sec
>   Timing buffered disk reads:   26 MB in  3.11 seconds =   8.36 MB/sec
> [root@pq-slave root]#

Can you post the "time dd if=/dev/zero of=bigfile bs=8k count=500000"
results?  Also do the reverse (read the file) with "time dd if=bigfile
of=/dev/null bs=8k".

I think you are observing what we've known for a while, hardware RAID is
horribly slow.  We've not found a hardware RAID adapter of this class yet
that shows reasonable read or write performance.  The Adaptec 2400R or the
LSI or others have terrible internal I/O compared to raw SCSI with software
RAID, and even the CPU usage is higher on these cards while doing slower I/O
than linux SW RAID.

Notably - we've found that the 3Ware RAID controller does a better job than
the low end SCSI RAID at HW RAID support, and also exports JBOD at high
speeds.  If you export JBOD on the low end SCSI RAID adapters, the
performance is also very poor, though generally faster than using HW RAID.

- Luke



Re: [PATCHES] COPY FROM performance improvements

From
Mark Wong
Date:
On Fri, 22 Jul 2005 12:28:43 -0700
"Luke Lonergan" <llonergan@greenplum.com> wrote:

> Joshua,
>
> On 7/22/05 10:11 AM, "Joshua D. Drake" <jd@commandprompt.com> wrote:
> > The database server is a PE (Power Edge) 6600
> >
> > Database Server IO:
> >
> > [root@master root]# /sbin/hdparm -tT /dev/sda
> >
> > /dev/sda:
> >   Timing buffer-cache reads:   1888 MB in  2.00 seconds = 944.00 MB/sec
> >   Timing buffered disk reads:   32 MB in  3.06 seconds =  10.46 MB/sec
> >
> > Second Database Server IO:
> >
> > [root@pq-slave root]# /sbin/hdparm -tT /dev/sda
> >
> > /dev/sda:
> >   Timing buffer-cache reads:   1816 MB in  2.00 seconds = 908.00 MB/sec
> >   Timing buffered disk reads:   26 MB in  3.11 seconds =   8.36 MB/sec
> > [root@pq-slave root]#
>
> Can you post the "time dd if=/dev/zero of=bigfile bs=8k count=500000"
> results?  Also do the reverse (read the file) with "time dd if=bigfile
> of=/dev/null bs=8k".
>
> I think you are observing what we've known for a while, hardware RAID is
> horribly slow.  We've not found a hardware RAID adapter of this class yet
> that shows reasonable read or write performance.  The Adaptec 2400R or the
> LSI or others have terrible internal I/O compared to raw SCSI with software
> RAID, and even the CPU usage is higher on these cards while doing slower I/O
> than linux SW RAID.
>
> Notably - we've found that the 3Ware RAID controller does a better job than
> the low end SCSI RAID at HW RAID support, and also exports JBOD at high
> speeds.  If you export JBOD on the low end SCSI RAID adapters, the
> performance is also very poor, though generally faster than using HW RAID.

Are there any recommendations for Qlogic controllers on Linux, scsi or
fiber channel?  I might be able to my hands on some.  I have pci-x slots
for AMD, Itanium, or POWER5 if the architecture makes a difference.

Mark

Re: [PATCHES] COPY FROM performance improvements

From
"Joshua D. Drake"
Date:
>>
>>>[root@pq-slave root]# /sbin/hdparm -tT /dev/sda
>>>
>>>/dev/sda:
>>>  Timing buffer-cache reads:   1816 MB in  2.00 seconds = 908.00 MB/sec
>>>  Timing buffered disk reads:   26 MB in  3.11 seconds =   8.36 MB/sec
>>>[root@pq-slave root]#
>>
>>Can you post the "time dd if=/dev/zero of=bigfile bs=8k count=500000"
>>results?  Also do the reverse (read the file) with "time dd if=bigfile
>>of=/dev/null bs=8k".

I didn't see this come across before... here ya go:

time dd if=/dev/zero of=bigfile bs=8k count=500000

500000+0 records in
500000+0 records out

real    1m52.738s
user    0m0.310s
sys     0m36.590s

time dd if=bigfile of=/dev/null bs=8k

time dd if=bigfile of=/dev/null bs=8k
500000+0 records in
500000+0 records out

real    4m38.742s
user    0m0.320s
sys     0m27.870s


FYI on your hardware raid comment... I easily get 50 megs a second on my
3ware controllers and faster on my LSI SATA controllers.

Sincerely,

Joshua D. Drake




>
--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org

Re: [PATCHES] COPY FROM performance improvements

From
"Luke Lonergan"
Date:
>>> Can you post the "time dd if=/dev/zero of=bigfile bs=8k count=500000"
>>> results?  Also do the reverse (read the file) with "time dd if=bigfile
>>> of=/dev/null bs=8k".
>
> I didn't see this come across before... here ya go:
>
> time dd if=/dev/zero of=bigfile bs=8k count=500000
>
> 500000+0 records in
> 500000+0 records out
>
> real    1m52.738s
> user    0m0.310s
> sys     0m36.590s

So, that's 35MB/s, or 1/2 of a single disk drive.

> time dd if=bigfile of=/dev/null bs=8k
>
> time dd if=bigfile of=/dev/null bs=8k
> 500000+0 records in
> 500000+0 records out
>
> real    4m38.742s
> user    0m0.320s
> sys     0m27.870s

And that's 14MB/s, or < 1/4 of a single disk drive.

> FYI on your hardware raid comment... I easily get 50 megs a second on my
> 3ware controllers and faster on my LSI SATA controllers.

Then you are almost getting one disk worth of bandwidth.

By comparison, we get this using Linux software RAID on Xeon or Opteron:

$ time dd if=/dev/zero of=bigfile bs=8k count=500000
500000+0 records in
500000+0 records out

real    0m26.927s
user    0m0.074s
sys     0m8.769s

$ time dd if=bigfile of=/dev/null bs=8k
500000+0 records in
500000+0 records out

real    0m28.190s
user    0m0.039s
sys     0m8.349s

with less CPU usage than HW SCSI RAID controllers.

- Luke



Re: [PATCHES] COPY FROM performance improvements

From
"Luke Lonergan"
Date:
Mark,

On 7/28/05 4:43 PM, "Mark Wong" <markw@osdl.org> wrote:

> Are there any recommendations for Qlogic controllers on Linux, scsi or
> fiber channel?  I might be able to my hands on some.  I have pci-x slots
> for AMD, Itanium, or POWER5 if the architecture makes a difference.

I don't have a recommendation for a particular one, it's been too long
(1998) since I've used one with Linux.  However, I'd like to see a
comparison between Emulex and Qlogic and a winner chosen.  We've had some
apparent driver issues with a client running Emulex on Linux, even using
many different versions of the kernel.

- Luke



Re: [PATCHES] COPY FROM performance improvements

From
Bruce Momjian
Date:
Luke Lonergan wrote:
> Mark,
>
> On 7/28/05 4:43 PM, "Mark Wong" <markw@osdl.org> wrote:
>
> > Are there any recommendations for Qlogic controllers on Linux, scsi or
> > fiber channel?  I might be able to my hands on some.  I have pci-x slots
> > for AMD, Itanium, or POWER5 if the architecture makes a difference.
>
> I don't have a recommendation for a particular one, it's been too long
> (1998) since I've used one with Linux.  However, I'd like to see a
> comparison between Emulex and Qlogic and a winner chosen.  We've had some
> apparent driver issues with a client running Emulex on Linux, even using
> many different versions of the kernel.

Where is the most recent version of the COPY patch?

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

Re: [PATCHES] COPY FROM performance improvements

From
"Luke Lonergan"
Date:
Bruce,

On 7/29/05 5:37 AM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

> Where is the most recent version of the COPY patch?

My direct e-mails aren't getting to you, they are trapped in a spam filter
on your end, so you didn't get my e-mail with the patch!

I've attached it here, sorry to the list owner for the patch inclusion /
off-topic.

- Luke


Attachment

Re: [PATCHES] COPY FROM performance improvements

From
Tom Lane
Date:
"Luke Lonergan" <llonergan@greenplum.com> writes:
> On 7/29/05 5:37 AM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
>> Where is the most recent version of the COPY patch?

> I've attached it here, sorry to the list owner for the patch inclusion /
> off-topic.

This patch appears to reverse out the most recent committed changes in
copy.c.

            regards, tom lane