User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Sat, 25 Feb 2006 07:49:51 -0800
Subject: Re: Reliability recommendations
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Ron" <rjpeace@earthlink.net>,
	pgsql-performance@postgresql.org
Message-ID: <C025BCA0.1DCCD%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Reliability recommendations
Thread-Index: AcY5/iDeWoYsFgWERASdh6+tS202CQAJPwJU
In-Reply-To: <7.0.1.0.2.20060225055917.03da49f8@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary=B_3223698593_18201262

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3223698593_18201262
Content-Type: text/plain;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Ron,

On 2/25/06 3:24 AM, "Ron" <rjpeace@earthlink.net> wrote:

>> These are each RAID5 arrays of 8 internal SATA disks on 3Ware HW RAID
>> controllers.
>=20
> Impressive IO rates.  A more detailed HW list would help put them in cont=
ext.
>=20
> Which 3Ware?  The 9550SX?  How much cache on it (AFAIK, the only
> options are 128MB and 256MB?)?
>=20
> Which HDs?
>=20
> What CPUs (looks like Opterons, but which flavor?) and mainboard?
>=20
> What's CPU utilization when hammering the physical IO subsystem this hard=
?

OK.  There are four machines. Each machine has:
Qty 2 of 3Ware 9550SX/PCIX/128MB cache SATAII RAID controllers
Qty 2 of AMD Opteron 250 CPUs (2.4 GHz)
Qty 16 of 1GB PC3200 RAM (16GB total)
Qty 1 of Tyan 2882S Motherboard
Qty 16 Western Digital 400GB Raid Edition 2 SATA disks
Cost: About $10,000 each

They are connected together using a Netgear 48 port Gigabit Ethernet switch
and copper CAT6 cables.
Cost: About $1,500

Total of all machines:
8 CPUs
64GB RAM
64 Disks
20TB storage in RAID5
Total HW cost: About $45,000.

CPU utilization is apparently (adding up usr+system and dividing by real):
Writing at 2,132MB/s: 51%
Reading at 3,168MB/s: 56%

>>     revenue
>> ----------------
>>  356492404.3164
>> (1 row)
>>=20
>> Time: 114908.149 ms
> Hmmm.  ~115secs @ ~500MBps =3D> ~57.5GB of data manipulated.

Actually, this query sequential scans all of both lineitem and part, so it=B9=
s
accessing 127.5GB of data, and performing the work of the hash join, in
about double the scan time.  That=B9s possible because we=B9re using all 8 CPUs=
,
4 network interfaces and the 64 disks while we are performing the query:

 Aggregate  (cost=3D3751996.97..3751996.99 rows=3D1 width=3D22)
   ->  Gather Motion  (cost=3D3751996.97..3751996.99 rows=3D1 width=3D22)
         ->  Aggregate  (cost=3D3751996.97..3751996.99 rows=3D1 width=3D22)
               ->  Hash Join  (cost=3D123440.49..3751993.62 rows=3D1339
width=3D22)
                     Hash Cond: ("outer".l_partkey =3D "inner".p_partkey)
                     Join Filter: ((("inner".p_brand =3D 'Brand#42'::bpchar)
AND (("inner".p_container =3D 'SM CASE'::bpchar) OR ("inner".p_container =3D 'S=
M
BOX'::bpchar) OR ("inner".p_container =3D 'SM PACK'::bpchar) OR
("inner".p_container =3D 'SM PKG'::bpchar)) AND ("outer".l_quantity >=3D
7::numeric) AND ("outer".l_quantity <=3D 17::numeric) AND ("inner".p_size <=3D
5)) OR (("inner".p_brand =3D 'Brand#15'::bpchar) AND (("inner".p_container =3D
'MED BAG'::bpchar) OR ("inner".p_container =3D 'MED BOX'::bpchar) OR
("inner".p_container =3D 'MED PKG'::bpchar) OR ("inner".p_container =3D 'MED
PACK'::bpchar)) AND ("outer".l_quantity >=3D 14::numeric) AND
("outer".l_quantity <=3D 24::numeric) AND ("inner".p_size <=3D 10)) OR
(("inner".p_brand =3D 'Brand#53'::bpchar) AND (("inner".p_container =3D 'LG
CASE'::bpchar) OR ("inner".p_container =3D 'LG BOX'::bpchar) OR
("inner".p_container =3D 'LG PACK'::bpchar) OR ("inner".p_container =3D 'LG
PKG'::bpchar)) AND ("outer".l_quantity >=3D 22::numeric) AND
("outer".l_quantity <=3D 32::numeric) AND ("inner".p_size <=3D 15)))
                     ->  Redistribute Motion  (cost=3D0.00..3340198.25
rows=3D2611796 width=3D36)
                           Hash Key: l_partkey
                           ->  Seq Scan on lineitem  (cost=3D0.00..3287962.32
rows=3D2611796 width=3D36)
                                 Filter: (((l_shipmode =3D 'AIR'::bpchar) OR
(l_shipmode =3D 'AIR REG'::bpchar)) AND (l_shipinstruct =3D 'DELIVER IN
PERSON'::bpchar))
                     ->  Hash  (cost=3D95213.75..95213.75 rows=3D2500296
width=3D36)
                           ->  Seq Scan on part  (cost=3D0.00..95213.75
rows=3D2500296 width=3D36)
                                 Filter: (p_size >=3D 1)
(13 rows)
=20
>> Time: 424981.813 ms
> ...and this implies ~425secs @ ~500MBps =3D> 212.5GB

I've attached this explain plan because it's large.  Same thing applies as
in previous, all of the involved tables are scanned, and in this case we've
got all manner of CPU work being performed: sorts, hashes, aggregations,...

> What are the IO rates during these joins?

They burst then disappear during the progression of the queries.  These are
now CPU bound because the I/O available to each CPU is so fast.

> How much data is being handled to complete these joins?

See above, basically all of it because there are no indexes.
=20
> How much data is being exchanged between these machines to complete the j=
oins?

Good question, you can infer it from reading the EXPLAIN plans, but the
stats are not what they appear - generally multiply them by 8.
=20
> What is the connectivity between these 4 machines?

See above.
=20
> Putting these numbers in context may help the advocacy effort
> considerably as well as help us improve things even further. ;-)

Thanks!  (bait bait) I can safely say that there have never been query
speeds with Postgres this fast, not even close. (bait bait)

What's next is a machine 4 or maybe 8 times this fast.

Explain for second query is attached, compressed with gzip (it=B9s kinda
huge).

- Luke


--B_3223698593_18201262
Content-Type: application/octet-stream;
 name=q2-explain.txt.gz
Content-Disposition: attachment;
 filename=q2-explain.txt.gz
Content-Transfer-Encoding: base64

H4sICKd7AEQAA3EyLWV4cGxhaW4udHh0AN1ZW2/bNhh9968gAgxJgJYgxasMeEDaZR26NOsS
9GFPhmKriVBH0iQFrffQ3z5SlHWzfJNlW+15cAgCIQ+/G88nAnB4/P3p+u4f8PHm6vYIm4HB
658OA3DjPXsJABeTIE5GmCCKLQyRhDAf2yAKvsYjDL560+RpZGF2OVDWeP0rAO+c5MmNwIcg
8QK/zSIGH9zo0QV/uvMhiGE8diaT5MGZvQI+9Me+8+y+SqfNKIThOHSi5Is7LxbQXFofpAy9
0H0Q7b2OQbrSHseqM/vDiZ/A+8DLTW1jTIQNZZmd2JadQbrm28CfDsHFWfCSuNFZSkp7VJEB
I3Dm+b6e9YvZtStqpnfu1IuTyHtQC9bCI+UsIGMZZyyhIFXO2EZrdygxN9YtmG3+N03v1o0T
dwpugiBcRwu3oWWQeul3b6bsqQ2bmTAexy9hmJk1M3aYT269+A4WtmyJBFV/q0chYuvNDApj
F3x3W2F1ANeooj2plgjX4lpRz3KsFNZ53rXaptnPz54/VpvpIy57ejbX8612S8uT+y+4nzg+
UA7XzPWSyikLiyKIEISEcM64Gma2RAhJ9ZMHcuvdU7MuOy8fVn3H221UbGduhnLESGkzi0D7
QJsamG3r0dN58NRPuyanhZSIScgohBLZTB2fLIqmxbkUi2ThexMxKOX7+ntpV6RedZRBPWfm
/eeCz0EEpq4znQWTLyB2PrvJvOHMwpbYJpDl8YwJtXnXhy4YvouCl/Dq8TFyHxXXnBC3sSKE
NSHJhFBD0eCEDTfuHqya5NYpSRmU5FvHwbKMfjqnznC5bFXYEcYxxOwU7Aya61v3t+OuqMtv
iinlDApV7NVQSAvSk/i0jELUL27fkuWOR6NRTnFpc6jqJKSICEtCap3aWgYrxFhJDNcF8tEp
rpRWh9dV+9IuazIuuLombQizgeHJCCEsI0mPzrFg+iYKnOnEUaq4eodZkFg9pFwQX8q0jDHW
jKVhzDkXvSBs0H1jfxzUEzFtmTwleyqJSKm6DBBdlDeG1NjYXvbkDOWstKClUlL/ZoRxjwLF
YF16Yohwz+kbNKbqgj2ufRnrG3mDprT1x0rqLqVtVMz28iQbOlqTxzj/NNWn/F2FoisuuaS/
dOul1JT5ugMWVbTfDqiXVJPV+vdHyGqDuj9MCC354wc6kUH+/TNKXzNUjTq//nT318fr8+Hw
IZw8OdFJukfCKLeVUFNXL+HIpgSKLNS1XmAUncDA1e5x06PPIdDU6FRDUKouUnXa6DC2Wnpi
Q1j3+FTJ6cWQZwmQf+AjHXmoZP2uTd9o1lr3mJ0vf+Ho/HwGeTpehONYf2MdAdXzg6vb39Kp
ZB66l8Nh4n5LwPfv4PyXN3dX9/fnZuZyp0eoRp0eb6vUMccbHxKrKlr3XPq39kK0jflWSUPW
ekWDJr0WNeo1v4VeW6fKcwGVhZNsmaIrbqXoMPdSh3dFgySgTYJm5xfEFbppo3LSG11wU70u
B4P/AWxN+JcCIwAA

--B_3223698593_18201262--