Re: 10K vs 15k rpm for analytics - Mailing list pgsql-performance
From | Yeb Havinga |
---|---|
Subject | Re: 10K vs 15k rpm for analytics |
Date | |
Msg-id | 4B8E2664.80303@gmail.com Whole thread Raw |
In response to | Re: 10K vs 15k rpm for analytics (Greg Smith <greg@2ndquadrant.com>) |
Responses |
Re: 10K vs 15k rpm for analytics
|
List | pgsql-performance |
Greg Smith wrote: > Yeb Havinga wrote: >> With 24 drives it'll probably be the controller that is the limiting >> factor of bandwidth. Our HP SAN controller with 28 15K drives >> delivers 170MB/s at maximum with raid 0 and about 155MB/s with raid 1+0. > > You should be able to clear 1GB/s on sequential reads with 28 15K > drives in a RAID10, given proper read-ahead adjustment. I get over > 200MB/s out of the 3-disk RAID0 on my home server without even trying > hard. Can you share what HP SAN controller you're using? Yeah I should have mentioned a bit more, to allow for a better picture of the apples and pears. Controller a is the built in controller of the HP MSA1000 SAN - with 14 disks but with extra 14 disks from a MSA30. It is connected through a 2Gbit/s fibrechannel adapter - should give up to roughly 250MB/s bandwidth, maybe a bit less due to frame overhead and gib/gb difference. Controller has 256MB cache It is three years old, however HP still sells it. I performed a few dozen tests with oracle's free and standalone orion tool (http://www.oracle.com/technology/software/tech/orion/index.html) with different raid and controller settings, where I varied - controller read/write cache ratio - logical unit layout (like one big raidx, 3 luns with raid10 (giving stripe width of 4 disks and 4 hot spares), 7 luns with raid10 - stripe size set to maximum - load type (random or sequential large io) - linux io scheduler (deadline / cfq etc) - fibre channel adapter queue depth - ratio between reads and writes by the orion - our production application has about 25% writes. - I also did the short stroking that is talked about further in this thread by only using one partition of about 30% size on each disk. - etc My primary goal was large IOPS for our typical load: mostly OLTP. The orion tool tests in a matrix with on one axis the # concurrent small io's and the other axis the # concurrent large io's. It output numbers are also in a matrix, with MBps, iops and latency. I put several of these numbers in matlab to produce 3d pictures and that showed some interesting stuff - its probably bad netiquette here to post a one of those pictures. One of the striking things was that I could see something that looked like a mountain where the top was neatly cut of - my guess: controller maximum reached. Below is the output data of a recent test, where a 4Gbit/s fc adapter was connected. From this numbers I conclude that in our setup, the controller is maxed out at 155MB/s for raid 1+0 *with this setup*. In a test run I constructed to try and see what the maximum mbps of the controller would be: 100% reads, sequential large io - that went to 170MBps. I'm particularly proud of the iops of this test. Please note: large load is random, not sequential! So to come back at my original claim: controller is important when you have 24 disks. I believe I have backed up this claim by this mail. Also please take notice that for our setup, a database that has a lot of concurrent users on a medium size database (~=160GB), random IO is what we needed, and for this purpose the HP MSA has proved rock solid. But the setup that Francisco mentioned is different: a few users doing mostly sequential IO. For that load, our setup is far from optimal, mainly because of the (single) controller. regards, Yeb Havinga ORION VERSION 10.2.0.1.0 Commandline: -run advanced -testname r10-7 -num_disks 24 -size_small 4 -size_large 1024 -type rand -simulate concat -verbose -write 25 -duration 15 -matrix detailed -cache_size 256 This maps to this test: Test: r10-7 Small IO size: 4 KB Large IO size: 1024 KB IO Types: Small Random IOs, Large Random IOs Simulated Array Type: CONCAT Write: 25% Cache Size: 256 MB Duration for each Data Point: 15 seconds Small Columns:, 0, 1, 2, 3, 4, 5, 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120 Large Columns:, 0, 1, 2, 3, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48 Total Data Points: 416 Name: /dev/sda1 Size: 72834822144 Name: /dev/sdb1 Size: 72834822144 Name: /dev/sdc1 Size: 72834822144 Name: /dev/sdd1 Size: 72834822144 Name: /dev/sde1 Size: 72834822144 Name: /dev/sdf1 Size: 72834822144 Name: /dev/sdg1 Size: 72834822144 7 FILEs found. Maximum Large MBPS=155.05 @ Small=2 and Large=48 Maximum Small IOPS=6261 @ Small=120 and Large=0 Minimum Small Latency=3.93 @ Small=1 and Large=0 Below the MBps matrix - hope this reads well in email clients?? Large/Small, 0, 1, 2, 3, 4, 5, 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120 1, 76.60, 74.87, 73.24, 70.66, 70.45, 68.36, 67.58, 59.63, 54.94, 50.74, 44.65, 41.24, 37.31, 35.85, 35.05, 32.53, 29.01, 30.64, 30.39, 27.41, 26.19, 25.43, 24.17, 24.10, 22.96, 22.39 2, 114.19, 115.65, 113.65, 112.11, 111.31, 109.77, 108.57, 101.81, 95.25, 86.74, 83.48, 76.12, 70.82, 68.98, 62.85, 63.75, 57.36, 56.28, 52.78, 50.37, 47.96, 48.53, 46.82, 44.47, 45.09, 42.53 3, 135.41, 135.21, 134.20, 134.27, 133.78, 132.62, 131.03, 127.08, 121.25, 114.15, 109.51, 104.28, 98.66, 94.91, 91.95, 86.27, 82.99, 79.28, 76.09, 74.26, 71.60, 67.83, 67.94, 64.55, 65.39, 63.23 4, 144.30, 143.93, 145.00, 144.47, 143.49, 142.56, 142.23, 139.14, 135.64, 131.82, 128.82, 124.51, 121.88, 116.16, 112.13, 107.91, 105.63, 101.54, 99.06, 93.50, 90.35, 87.25, 86.98, 83.57, 83.45, 79.73 8, 152.93, 152.87, 152.60, 152.29, 152.36, 152.16, 151.85, 151.11, 150.00, 149.09, 148.18, 147.40, 146.09, 145.21, 144.94, 143.82, 142.90, 141.43, 140.93, 140.08, 137.83, 136.95, 136.17, 133.69, 134.05, 131.85 12, 154.10, 153.83, 154.07, 153.79, 154.03, 153.35, 153.09, 152.41, 152.14, 151.32, 151.49, 150.68, 150.10, 149.69, 149.19, 148.07, 148.00, 147.90, 146.78, 146.57, 145.79, 144.96, 145.21, 144.23, 143.58, 142.59 16, 154.30, 154.40, 153.71, 153.96, 154.13, 154.13, 153.58, 153.24, 152.97, 152.86, 152.29, 151.95, 151.57, 150.68, 150.85, 150.44, 150.03, 149.59, 149.15, 149.01, 148.29, 147.89, 147.44, 147.41, 146.79, 146.55 20, 154.70, 154.53, 154.33, 154.12, 154.05, 154.29, 154.05, 153.84, 152.87, 153.26, 153.02, 152.64, 152.37, 151.99, 151.65, 151.44, 150.89, 150.89, 150.69, 150.34, 149.90, 149.59, 149.38, 149.31, 148.76, 148.35 24, 154.31, 154.34, 154.28, 154.31, 154.21, 154.39, 154.07, 153.80, 153.80, 153.17, 153.28, 152.83, 152.59, 152.66, 151.97, 152.00, 151.66, 151.17, 150.79, 151.10, 150.62, 150.52, 150.17, 149.93, 149.79, 149.27 28, 154.62, 154.48, 154.34, 154.70, 154.48, 154.31, 154.44, 153.92, 153.82, 153.72, 153.54, 153.23, 152.88, 152.29, 152.23, 152.43, 151.84, 151.70, 151.32, 151.56, 150.87, 150.87, 150.90, 150.31, 150.63, 150.03 32, 154.58, 154.33, 154.90, 154.40, 154.51, 154.44, 154.41, 154.08, 154.30, 154.02, 153.53, 153.50, 153.35, 153.01, 152.83, 152.83, 152.41, 152.16, 152.06, 151.99, 151.75, 151.29, 151.12, 151.47, 151.22, 150.77 36, 154.67, 154.46, 154.43, 154.25, 154.60, 154.96, 154.25, 154.25, 154.15, 154.00, 153.83, 153.45, 153.16, 153.23, 152.74, 152.66, 152.49, 152.57, 152.28, 152.53, 151.79, 151.40, 151.23, 151.30, 151.19, 151.20 40, 154.27, 154.67, 154.63, 154.74, 154.17, 154.31, 154.82, 154.24, 154.67, 154.35, 153.81, 153.82, 153.89, 153.29, 153.18, 152.97, 153.18, 152.72, 152.69, 151.94, 151.80, 151.69, 152.12, 151.59, 151.31, 151.52 44, 154.37, 154.59, 154.51, 154.66, 154.88, 154.58, 154.26, 154.29, 153.83, 154.38, 153.84, 153.66, 153.55, 153.23, 153.02, 153.20, 152.70, 152.67, 152.88, 152.53, 152.67, 152.13, 152.10, 152.06, 151.53, 151.45 48, 154.61, 154.83, 155.05, 154.65, 154.47, 154.97, 154.29, 154.40, 154.33, 154.29, 154.00, 154.01, 153.71, 153.47, 153.58, 153.50, 153.15, 152.50, 153.08, 152.83, 152.40, 152.04, 151.46, 152.29, 152.11, 151.43 below the iops matrix Large/Small, 1, 2, 3, 4, 5, 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66, 72, 78, 84, 90, 96, 102, 108, 114, 120 0, 254, 502, 751, 960, 1177, 1388, 2343, 3047, 3557, 3945, 4247, 4529, 4752, 4953, 5111, 5280, 5412, 5550, 5670, 5785, 5904, 5987, 6093, 6167, 6261 1, 178, 353, 526, 684, 832, 999, 1801, 2445, 2937, 3382, 3742, 4054, 4262, 4489, 4685, 4910, 5030, 5139, 5312, 5439, 5549, 5685, 5760, 5873, 5953 2, 122, 240, 364, 484, 605, 715, 1342, 1907, 2416, 2808, 3208, 3526, 3789, 4072, 4217, 4477, 4629, 4840, 4964, 5187, 5242, 5381, 5490, 5543, 5704 3, 84, 167, 253, 337, 420, 510, 990, 1486, 1924, 2332, 2692, 3035, 3272, 3578, 3838, 4048, 4260, 4426, 4607, 4760, 4948, 4989, 5164, 5216, 5335 4, 61, 120, 180, 236, 303, 368, 732, 1086, 1445, 1780, 2144, 2434, 2771, 3092, 3342, 3576, 3793, 4000, 4165, 4376, 4554, 4703, 4805, 4847, 5062 8, 24, 49, 73, 100, 122, 152, 303, 448, 614, 759, 889, 1043, 1201, 1325, 1489, 1647, 1800, 1948, 2116, 2291, 2434, 2594, 2824, 2946, 3124 12, 15, 30, 45, 62, 76, 90, 188, 275, 366, 462, 543, 638, 726, 814, 906, 978, 1055, 1151, 1245, 1341, 1425, 1488, 1566, 1688, 1759 16, 10, 23, 32, 44, 55, 66, 130, 198, 259, 328, 387, 450, 519, 580, 643, 706, 767, 834, 891, 964, 1029, 1083, 1141, 1206, 1263 20, 8, 17, 25, 34, 41, 50, 102, 152, 201, 255, 302, 350, 402, 447, 496, 554, 591, 645, 688, 746, 791, 844, 882, 934, 984 24, 6, 13, 21, 28, 35, 41, 85, 123, 166, 206, 250, 288, 326, 377, 410, 451, 497, 531, 568, 610, 660, 694, 732, 772, 814 28, 6, 12, 17, 23, 29, 35, 70, 106, 142, 174, 210, 247, 279, 325, 348, 378, 419, 453, 487, 523, 553, 586, 627, 651, 691 32, 5, 10, 15, 20, 26, 31, 61, 92, 120, 154, 182, 212, 245, 274, 309, 336, 368, 395, 429, 452, 488, 514, 542, 581, 605 36, 4, 9, 13, 18, 22, 27, 56, 83, 110, 138, 166, 193, 222, 248, 279, 302, 333, 358, 385, 414, 438, 468, 496, 523, 551 40, 4, 8, 12, 17, 21, 25, 50, 77, 103, 127, 155, 184, 205, 236, 256, 285, 315, 341, 362, 387, 418, 442, 468, 492, 518 44, 4, 8, 11, 15, 20, 24, 49, 73, 98, 123, 151, 173, 197, 225, 248, 269, 294, 329, 349, 373, 390, 428, 438, 469, 498 48, 3, 7, 11, 15, 20, 23, 47, 70, 95, 120, 141, 166, 192, 212, 237, 260, 282, 308, 329, 353, 378, 400, 424, 450, 468
pgsql-performance by date: