PostgreSQL Performance - Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec

Author

Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec

Miguel

2006-03-21, 3:34 am

Vivek Khera wrote:

>
> On Mar 17, 2006, at 5:11 PM, Kenji Morishige wrote:
>
>
>
> FreeBSD 6.x will definitely get you improvements. Many speedup
> improvements have been made to both the generic disk layer and the
> specific drivers. However, the current best of breed RAID controller
> is the LSI 320-x (I use 320-2X). I have one box into which this
> card will not fit (Thanks Sun, for making a box with only low-profile
> slots!) so I use an Adaptec 2230SLP card in it. Testing shows it is
> about 80% speed of a LSI 320-2x on sequential workload (load DB, run
> some queries, rebuild indexes, etc.)
>
> If you do put on FreeBSD 6, I'd love to see the output of "diskinfo -
> v -t" on your RAID volume(s).
>
Not directly related ...
i have a HP dl380 g3 with array 5i controlled (1+0), these are my results

shiva2# /usr/sbin/diskinfo -v -t /dev/da2s1d
/dev/da2s1d
512 # sectorsize
218513555456 # mediasize in bytes (204G)
426784288 # mediasize in sectors
52301 # Cylinders according to firmware.
255 # Heads according to firmware.
32 # Sectors according to firmware.

Seek times:
Full stroke: 250 iter in 1.138232 sec = 4.553 msec
Half stroke: 250 iter in 1.084474 sec = 4.338 msec
Quarter stroke: 500 iter in 1.690313 sec = 3.381 msec
Short forward: 400 iter in 0.752646 sec = 1.882 msec
Short backward: 400 iter in 1.306270 sec = 3.266 msec
Seq outer: 2048 iter in 0.766676 sec = 0.374 msec
Seq inner: 2048 iter in 0.803759 sec = 0.392 msec
Transfer rates:
outside: 102400 kbytes in 2.075984 sec = 49326 kbytes/sec
middle: 102400 kbytes in 2.100510 sec = 48750 kbytes/sec
inside: 102400 kbytes in 2.042313 sec = 50139 kbytes/sec

is this good enough?

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Miguel

2006-03-21, 3:34 am

Vivek Khera wrote:

>
>
> Is that on a loaded box or a mostly quiet box? Those number seem
> rather low for my tastes. For comparison, here are numbers from a
> Dell 1850 with a built-in PERC 4e/Si RAID in a two disk mirror. All
> numbers below are on mostly or totally quiet disk systems.

My numbers are on totally quiet box, i've just installed it.

>
> amrd0
> 512 # sectorsize
> 73274490880 # mediasize in bytes (68G)
> 143114240 # mediasize in sectors
> 8908 # Cylinders according to firmware.
> 255 # Heads according to firmware.
> 63 # Sectors according to firmware.
>
> Seek times:
> Full stroke: 250 iter in 0.756718 sec = 3.027 msec
> Half stroke: 250 iter in 0.717824 sec = 2.871 msec
> Quarter stroke: 500 iter in 1.972368 sec = 3.945 msec
> Short forward: 400 iter in 1.193179 sec = 2.983 msec
> Short backward: 400 iter in 1.322440 sec = 3.306 msec
> Seq outer: 2048 iter in 0.271402 sec = 0.133 msec
> Seq inner: 2048 iter in 0.271151 sec = 0.132 msec
> Transfer rates:
> outside: 102400 kbytes in 1.080339 sec = 94785
> kbytes/sec
> middle: 102400 kbytes in 1.166021 sec = 87820
> kbytes/sec
> inside: 102400 kbytes in 1.461498 sec = 70065
> kbytes/sec
>
>
Umm, in my box i see better seektimes but worst transfer rates, does it
make sense?
i think i have something wrong, the question i cant answer is what
tunning am i missing?

---
Miguel

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Miguel

2006-03-21, 3:34 am

NNTP-Posting-Host: news.hub.org
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news.hub.org 1142896297 22121 200.46.204.72 (20 Mar 2006 23:11:37 GMT)
X-Complaints-To: usenet@news.hub.org
NNTP-Posting-Date: Mon, 20 Mar 2006 23:11:37 +0000 (UTC)
X-Received: from postgresql.org (postgresql.org [200.46.204.71])
by news.hub.org (8.13.1/8.13.1) with ESMTP id k2KNBbUk022094
for <pgsql-performance@news.hub.org>; Mon, 20 Mar 2006 19:11:37 -0400 (AST)
(envelope-from pgsql-performance- owner+M17793=pgsql+2
Dperformance=news.hub.org@postgresql.org)
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
X-Received: from localhost (av.hub.org [200.46.204.144])
by postgresql.org (Postfix) with ESMTP id B2A169DC9FC
for <pgsql-performance-postgresql.org@localhost.postgresql.org>; Mon, 20 Mar 2006 19:11:22 -0400 (AST)
X-Received: from postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 11256-06
for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
Mon, 20 Mar 2006 19:11:24 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Received: from asgard1.americatelsal.com (asgard.americatelsal.com [200.13.161.7])
by postgresql.org (Postfix) with ESMTP id 27D129DC889
for <pgsql- performance@postgres
ql.org>; Mon, 20 Mar 2006 19:11:18 -0400 (AST)
X-Received: (qmail 71668 invoked from network); 20 Mar 2006 23:34:02 -0000
X-Received: from unknown (HELO ?192.168.10.124?) (200.13.161.68)
by asgard1.americatelsal.com with SMTP; 20 Mar 2006 23:34:02 -0000
X-User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
X-To: Vivek Khera <vivek@khera.org>
X-CC: Postgresql Performance <pgsql- performance@postgres
ql.org>
X-In-Reply-To: <5919B132-34DA-4B3F-ABB7-1E6B4030EA9F@khera.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.03 required=5 tests=[AWL=0.030]
X-Spam-Score: 0.03
X-Spam-Level:
X-Mailing-List: pgsql-performance
X-List-Archive: <http://archives.postgresql.org/pgsql-performance>
X-List-Help: < mailto:majordomo@pos
tgresql.org?body=help>
X-List-ID: <pgsql-performance.postgresql.org>
X-List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
X-List-Post: <mailto:pgsql- performance@postgres
ql.org>
X-List-Subscribe: < mailto:majordomo@pos
tgresql.org?body=sub%20pgsql-performance>
X-List-Unsubscribe: < mailto:majordomo@pos
tgresql.org?body=unsub%20pgsql-performance>
X-Precedence: bulk
Xref: news.hub.org pgsql.performance:8144

Vivek Khera wrote:

>
> On Mar 20, 2006, at 6:04 PM, Miguel wrote:
>
>
>
> Well, I forgot to mention I have 15k RPM disks, so the transfers
> should be faster.
>
> I did no tuning to the disk configurations. I think your controller
> is either just not supported well in FreeBSD, or is bad in general...

:-(

I guess you are right, i made a really bad choice, i better look at dell
next time,
thanks

---
Miguel

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

PFC

2006-03-21, 3:34 am

This is a 2-Disk Linux software RAID1 with 2 7200RPM IDE Drives, 1 PATA
and 1 SATA :

apollo13 ~ # hdparm -t /dev/md0

/dev/md0:
Timing buffered disk reads: 156 MB in 3.02 seconds = 51.58 MB/sec
apollo13 ~ # hdparm -t /dev/md0

/dev/md0:
Timing buffered disk reads: 168 MB in 3.06 seconds = 54.87 MB/sec

This is a 5-Disk Linux software RAID5 with 4 7200RPM IDE Drives and 1
5400RPM, 3 SATA and 2 PATA:

apollo13 ~ # hdparm -t /dev/md2
/dev/md2:
Timing buffered disk reads: 348 MB in 3.17 seconds = 109.66 MB/sec

apollo13 ~ # hdparm -t /dev/md2
/dev/md2:
Timing buffered disk reads: 424 MB in 3.00 seconds = 141.21 MB/sec

apollo13 ~ # hdparm -t /dev/md2
/dev/md2:
Timing buffered disk reads: 426 MB in 3.00 seconds = 141.88 MB/sec

apollo13 ~ # hdparm -t /dev/md2
/dev/md2:
Timing buffered disk reads: 426 MB in 3.01 seconds = 141.64 MB/sec

The machine is a desktop Athlon 64 3000+, buggy nforce3 chipset, 1G
DDR400, Gentoo Linux 2.6.15-ck4 running in 64 bit mode.
The bottleneck is the PCI bus.

Expensive SCSI hardware RAID cards with expensive 10Krpm harddisks should
not get humiliated by such a simple (and cheap) setup. (I'm referring to
the 12-drive RAID10 mentioned before, not the other one which was a simple
2-disk mirror). Toms hardware benchmarked some hardware RAIDs and got
humongous transfer rates... hm ?

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Vivek Khera

2006-03-21, 11:33 am

On Mar 20, 2006, at 6:27 PM, PFC wrote:

> Expensive SCSI hardware RAID cards with expensive 10Krpm harddisks
> should not get humiliated by such a simple (and cheap) setup. (I'm
> referring to the 12-drive RAID10 mentioned before, not the other
> one which was a simple 2-disk mirror). Toms hardware benchmarked
> some hardware RAIDs and got humongous transfer rates... hm ?
>

I'll put up my "slow" 12 disk SCSI array up against your IDE array on
a large parallel load any day.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

PFC

2006-03-21, 8:33 pm

>
> I'll put up my "slow" 12 disk SCSI array up against your IDE array on a
> large parallel load any day.

Sure, and I have no doubt that yours will be immensely faster on parallel
loads than mine, but still, it should also be the case on sequential
scan... especially since I have desktop PCI and the original poster has a
real server with PCI-X I think.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Vivek Khera

2006-03-21, 8:33 pm

On Mar 21, 2006, at 2:04 PM, PFC wrote:

> especially since I have desktop PCI and the original poster has a
> real server with PCI-X I think.

that was me :-)

but yeah, I never seem to get full line speed for some reason. i
don't know if it is because of inadequate measurement tools or what...

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links