Details

OpenLink Software
Burlington, United States

Subscribe

Post Categories

Recent Articles

Community Member Blogs

Display Settings

articles per page.
order.

Translate

In Hoc Signo Vinces (part 19 of n): Scalability, 1000G, and 3000G [ Virtuso Data Space Bot ]

Scalability, specifically linear scalability, means that twice the data takes twice as long to process, or that double the gear processes the same data in half the time. This is only literally true for "embarrassingly parallel" workloads.

There are parts of TPC-H which have an embarrassingly parallel nature, like Q1 and Q7. There are parts that are almost as easy, like Q14, Q17, Q19, and Q21, where there is a big scan and a selective hash join with a hash table small enough to replicate everywhere. The scan scales linearly; building the hash does not, since it is done at single-server speed (once in each process). Some queries like Q9 and Q13 end up doing a big cross-partition join which runs into communication overheads.

This is our first look at how performance behaves with bigger data and a larger platform. The results shown here are interesting but are not final. I bet I can do better; by how much is what we'll find out soon enough.

We will here compare a 1000G setup on my desktop, and a 3000G setup at the CWI's Scilens cluster. The former is 2 boxes of dual Xeon E5 2630, and the latter is 8 boxes of dual Xeon E5 2650v2. All things run from memory and both have QDR IB interconnect. Counting cores and clock, the CWI cluster is 6x larger.

As a rough approximation, for the worst queries, 6x the gear runs 3x the data in the same amount of real time. The 1000G setup has near full platform utilization and the 3000G setup has about half platform utilization. In both cases, running two instances of the same query at the same time takes twice as long.

We use Q9 for this study. The plan makes a hash table of part with 1/14 of all parts, replicating to all processes. Then there is a hash table of partsupp with a key of ps_partkey, ps_suppkey, and a dependent of ps_supplycost. This is much larger than the part hash table and is therefore partitioned on ps_partkey. The build is for 1/14th of partsupp. Then there is a scan of lineitem filtered by the part hash table; then a cross-partition join to the partsupp hash table; then a cross partition join to orders, this time by index; then a hash join on a replicated hash table of supplier; then nation; then aggregation. The aggregation is done in each slice; then the slices are added up at the end.

The plan could be made better by one fewer partition crossing. Now there is a crossing from l_orderkey to l_partkey and back to o_orderkey. This would not be so if the cost model knew that the partsupp always hits. The cost model thinks it hits 1/14 of the time, because it does not know that the selection on the build is exactly the same as on the probe.

For the present purposes, the extra crossing just serves to make the matter of interest more visible.

So, for the 1000G setup, we have 43.6 seconds (s) and

Cluster 4 nodes, 44 s. 459 m/s 119788 KB/s  3120% cpu 0%  read 19% clw threads 1r 0w 0i buffers 17622126 68 d 0 w 0 pfs
 

For the 3000G setup, we have 49.9 s and

Cluster 16 nodes, 50 s. 49389 m/s 1801815 KB/s  7283% cpu 0%  read 18% clw threads 1r 0w 0i buffers 135122893 15895255 d 0 w 17 pfs
 

The platform utilization on the small system is better, at 31/48 (running/total threads); the large one has 73/256.

The large case is clearly network bound. If this were for CPU only, it should be done in half the time it takes the small system to do 1000G.

We confirm this by looking at write wait: 3940 seconds of thread time blocked on write over 50s of real time. The figures on the small one are 3.9s of thread time blocked for 39s of real time. The data transfer on the large one is 93 GB.

How to block less? One idea would be to write less. So we try compression; there is a Google snappy-based message compression option in Virtuoso.

We now get 39.6 s and

Cluster 16 nodes, 40 s. 65161 m/s 1239922 KB/s  10201% cpu 0%  read 21% clw threads 1r 0w 0i buffers 52828440 172 d 0 w 0 pfs
 

The write block time is 397 s of thread time over 39 s of real time, 10x better. The data transfer is 50.9 GB after compression. Snappy is somewhat effective for compression and very fast; in CPU profile, it is under 3% of Q9 on the small system. Gains on the small system are less, though, since blocking is not a big issue to start with.

This is still not full platform. But if the data transfer is further cut in half by a better plan, the situation will be quite good. Now we have 102/256 threads running, meaning that there could be another 40-50% of throughput to be added. The last 128 threads are second threads of a core, so count for roughly 30% of a real core.

The main cluster-specific operation is a send from one to many. This is now done by formulating the message to each recipient in a chain of string buffers; then, after all the messages are prepared, these are optionally compressed and sent to their recipient. This is needlessly simple: Compressing can proceed if ever there is a would-block situation on writing. If all the compression is done, then a blocked write should switch to another recipient, and only after all recipients have a would-block situation, then the thread can call-select with all descriptors and block on them collectively. There is a piece of code to this effect, but is not now being used. It has been seen to add no value in small cases, but could be useful here.

The IB fabric has been seen to do 1.8 GB/s bidirectionally on multiple independent point-to-point TCP links. This is about half the nominal 4 GB/s (40 Gbit/s with 10/8 encoding). So the aggregate throughputs that we see here are nowhere near the nominal spec of the network. Lower level interfaces and the occasional busy wait on the reading end could be tried to some advantage. We have not tried 10GbE either; but if that works at nominal speed, then 10GbE should also be good enough. We will try this at Amazon in due time.

In the meantime, there is a 3000G test made at the CWI cluster without message compression. The score is about 4x that of the single server at 300G using the same hardware. The run is with approximately half platform utilization. There are three runs of power plus throughput, the first run being cold.

Run Power Throughput Composite
Run 1 305,881.5 1,072,411.9 572,739.8
Run 2 1,292,085.1 1,179,391.6 1,234,453.1
Run 3 1,178,534.1 1,092,936.2 1,134,928.4

The numerical quantities summaries follow. One problem of the run is a high peak of query memory consumption leading to slowdown. Some parts should probably be done in multiple passes to keep the peak lower and not run into swapping. The details will have to be sorted out. This is a demonstration of capability; the perfected accomplishment is to follow.

3000G Run 1

Virt-H Executive Summary

Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 305,881.5
Virt-H Throughput 1,072,411.9
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
572,739.8
Measurement Interval in
Throughput Test (Ts)
1,772.554000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 12:54:52 09/29/2014 13:31:17 0:36:25
Stream 1 09/29/2014 13:31:24 09/29/2014 13:59:24 0:28:00
Stream 2 09/29/2014 13:31:24 09/29/2014 13:58:59 0:27:35
Stream 3 09/29/2014 13:31:24 09/29/2014 13:58:29 0:27:05
Stream 4 09/29/2014 13:31:24 09/29/2014 13:58:52 0:27:28
Stream 5 09/29/2014 13:31:24 09/29/2014 14:00:06 0:28:42
Stream 6 09/29/2014 13:31:24 09/29/2014 13:58:18 0:26:54
Stream 7 09/29/2014 13:31:24 09/29/2014 13:59:25 0:28:01
Stream 8 09/29/2014 13:31:24 09/29/2014 13:58:50 0:27:26
Refresh 0 09/29/2014 12:54:52 09/29/2014 12:56:59 0:02:07
09/29/2014 13:31:17 09/29/2014 13:31:23 0:00:06
Refresh 1 09/29/2014 14:00:38 09/29/2014 14:01:11 0:00:33
Refresh 2 09/29/2014 13:31:25 09/29/2014 13:36:57 0:05:32
Refresh 3 09/29/2014 13:36:56 09/29/2014 13:47:02 0:10:06
Refresh 4 09/29/2014 13:47:03 09/29/2014 13:51:40 0:04:37
Refresh 5 09/29/2014 13:51:42 09/29/2014 13:56:40 0:04:58
Refresh 6 09/29/2014 13:56:40 09/29/2014 13:59:25 0:02:45
Refresh 7 09/29/2014 13:59:25 09/29/2014 14:00:10 0:00:45
Refresh 8 09/29/2014 14:00:11 09/29/2014 14:00:37 0:00:26

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 601.576975 90.803782 108.725110 177.112667 171.995572 2.098138 15.768311 152.511444
Stream 1 13.310341 32.722946 125.551415 1.912836 46.041675 13.294214 85.345068 165.424288
Stream 2 19.425885 9.248670 150.855556 7.085737 88.445566 10.490432 49.318554 322.500839
Stream 3 30.534391 14.273478 100.987791 59.341763 46.442443 9.613795 64.186196 146.324186
Stream 4 28.211213 37.134522 64.189335 10.931513 100.610673 9.929866 112.270530 108.489951
Stream 5 29.226411 18.132589 95.245160 63.100068 115.663908 6.151231 46.251309 127.742471
Stream 6 30.750930 20.888658 108.894177 55.168565 82.016828 69.451493 65.161517 103.697733
Stream 7 13.462570 18.033847 32.065492 78.910373 202.998301 10.688279 47.167022 139.601948
Stream 8 24.354314 16.711503 112.008551 8.307098 126.849630 7.127605 51.083118 98.648077
Min Qi 13.310341 9.248670 32.065492 1.912836 46.041675 6.151231 46.251309 98.648077
Max Qi 30.750930 37.134522 150.855556 78.910373 202.998301 69.451493 112.270530 322.500839
Avg Qi 23.659507 20.893277 98.724685 35.594744 101.133628 17.093364 65.097914 151.553687
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 92.991259 5.175922 42.238393 29.239879 367.805534 3.604910 15.557396 11.650267
Stream 1 149.502128 30.197806 50.786184 217.190836 283.545905 11.653171 73.321150 116.860455
Stream 2 245.783668 22.278841 50.578731 36.301810 181.405269 32.236754 57.631764 61.540533
Stream 3 377.782738 24.129319 84.097657 10.959661 171.698669 8.973519 54.532180 45.527142
Stream 4 341.148908 74.358770 85.782399 43.116347 151.146233 22.870727 74.439693 51.871535
Stream 5 72.259919 11.424035 79.310504 9.833135 562.871920 14.961209 127.861874 55.377721
Stream 6 373.301225 41.379753 81.983260 9.373200 95.039317 19.071346 76.159452 48.324504
Stream 7 449.871952 16.099152 48.047940 8.559784 211.094730 10.569071 26.710228 72.571454
Stream 8 395.771006 33.537585 54.850876 141.526389 153.763316 12.997092 127.961975 57.100346
Min Qi 72.259919 11.424035 48.047940 8.559784 95.039317 8.973519 26.710228 45.527142
Max Qi 449.871952 74.358770 85.782399 217.190836 562.871920 32.236754 127.961975 116.860455
Avg Qi 300.677693 31.675658 66.929694 59.607645 226.320670 16.666611 77.327289 63.646711
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 12.230334 70.991261 33.092797 17.517230 15.798438 19.743562 127.494687 5.893471
Stream 1 27.550293 14.970857 16.442806 111.138612 68.214095 7.884782 27.109441 6.087067
Stream 2 43.277918 12.748690 22.681844 92.835566 84.416610 14.661934 151.094498 153.285076
Stream 3 129.696125 13.435663 14.674499 129.179966 39.176513 6.286296 181.596838 416.052710
Stream 4 110.348816 7.080225 21.051910 85.758973 65.130356 7.292999 123.386514 151.000786
Stream 5 43.365006 9.847612 32.881770 94.752284 67.788314 9.035439 72.539334 223.967821
Stream 6 34.534280 36.347298 27.849276 122.736244 51.447492 25.051058 80.452175 84.519426
Stream 7 48.021860 30.594474 22.522426 99.245893 73.076698 7.260729 38.585852 5.697277
Stream 8 29.484201 12.368769 40.344043 84.137820 30.813313 4.856991 22.196547 4.600057
Min Qi 27.550293 7.080225 14.674499 84.137820 30.813313 4.856991 22.196547 4.600057
Max Qi 129.696125 36.347298 40.344043 129.179966 84.416610 25.051058 181.596838 416.052710
Avg Qi 58.284812 17.174198 24.806072 102.473170 60.007924 10.291279 87.120150 130.651277

3000G Run 2

Virt-H Executive Summary

Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 1292085.1
Virt-H Throughput 1179391.6
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
1234453.1
Measurement Interval in
Throughput Test (Ts)
1611.779000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 14:01:15 09/29/2014 14:06:48 0:05:33
Stream 1 09/29/2014 14:06:53 09/29/2014 14:30:22 0:23:29
Stream 2 09/29/2014 14:06:53 09/29/2014 14:32:30 0:25:37
Stream 3 09/29/2014 14:06:53 09/29/2014 14:31:23 0:24:30
Stream 4 09/29/2014 14:06:53 09/29/2014 14:31:34 0:24:41
Stream 5 09/29/2014 14:06:53 09/29/2014 14:32:53 0:26:00
Stream 6 09/29/2014 14:06:53 09/29/2014 14:29:51 0:22:58
Stream 7 09/29/2014 14:06:53 09/29/2014 14:31:34 0:24:41
Stream 8 09/29/2014 14:06:53 09/29/2014 14:30:35 0:23:42
Refresh 0 09/29/2014 14:01:15 09/29/2014 14:01:35 0:00:20
09/29/2014 14:06:49 09/29/2014 14:06:53 0:00:04
Refresh 1 09/29/2014 14:33:16 09/29/2014 14:33:45 0:00:29
Refresh 2 09/29/2014 14:06:55 09/29/2014 14:12:28 0:05:33
Refresh 3 09/29/2014 14:12:29 09/29/2014 14:21:55 0:09:26
Refresh 4 09/29/2014 14:21:55 09/29/2014 14:27:40 0:05:45
Refresh 5 09/29/2014 14:27:43 09/29/2014 14:31:14 0:03:31
Refresh 6 09/29/2014 14:31:14 09/29/2014 14:31:51 0:00:37
Refresh 7 09/29/2014 14:31:51 09/29/2014 14:32:52 0:01:01
Refresh 8 09/29/2014 14:32:52 09/29/2014 14:33:16 0:00:24

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 9.451169 3.644118 18.419151 1.404395 15.740525 2.085038 15.171847 25.400834
Stream 1 19.558041 6.607300 85.774410 4.503525 81.448472 11.976129 92.140470 145.743853
Stream 2 31.042019 7.877299 71.958033 8.862111 142.452144 18.489193 81.003310 85.856529
Stream 3 38.833612 12.440326 86.063103 7.165120 84.707025 16.931531 100.442710 122.411252
Stream 4 15.751913 33.026762 50.457193 7.064220 114.130257 5.992556 66.035959 84.596973
Stream 5 18.462884 28.047942 110.690543 16.566547 104.403789 5.303453 72.552640 402.383383
Stream 6 17.858339 33.988800 110.431091 7.238431 72.229953 16.850955 68.231546 180.601000
Stream 7 23.055572 17.044813 96.105520 8.941132 171.130879 8.423100 70.634541 147.261648
Stream 8 19.840798 13.860740 74.961175 16.171566 56.165875 5.904921 47.646217 125.991819
Min Qi 15.751913 6.607300 50.457193 4.503525 56.165875 5.303453 47.646217 84.596973
Max Qi 38.833612 33.988800 110.690543 16.566547 171.130879 18.489193 100.442710 402.383383
Avg Qi 23.050397 19.111748 85.805134 9.564082 103.333549 11.233980 74.835924 161.855807
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 54.766945 5.551163 29.216632 3.035008 52.816902 3.346243 15.767022 10.066112
Stream 1 130.666380 9.658277 49.332720 103.036705 194.520370 12.166344 65.144599 97.158571
Stream 2 254.754936 22.605298 38.102466 21.121168 300.467330 12.262318 108.203491 50.696657
Stream 3 283.761567 19.327164 73.414574 7.431651 183.121904 12.573854 73.814766 46.802493
Stream 4 290.341947 57.452026 58.354221 13.066162 189.263163 18.998781 121.269774 54.831406
Stream 5 81.787025 8.410538 79.822552 16.005077 190.730342 21.697136 100.456487 46.744884
Stream 6 202.558515 39.360009 74.519981 15.960756 137.321631 26.583824 57.537668 60.758997
Stream 7 226.790801 44.175536 73.992368 7.561897 182.853851 17.597471 31.128055 44.389893
Stream 8 275.423934 21.980040 60.538239 39.736622 173.574795 58.786316 95.124912 25.564108
Min Qi 81.787025 8.410538 38.102466 7.431651 137.321631 12.166344 31.128055 25.564108
Max Qi 290.341947 57.452026 79.822552 103.036705 300.467330 58.786316 121.269774 97.158571
Avg Qi 218.260638 27.871111 63.509640 27.990005 193.981673 22.583255 81.584969 53.368376

3000G Run 3

Virt-H Executive Summary

Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 13.620157 2.288504 4.166807 16.468447 9.991810 1.101775 20.152227 4.294680
Stream 1 44.026143 31.720525 25.684461 134.254716 30.797008 9.568594 24.328205 4.319533
Stream 2 40.283148 9.970277 29.731019 133.083785 29.322194 8.859556 73.251098 249.850045
Stream 3 44.288244 18.914661 38.162762 144.458624 22.556235 6.184842 117.267234 445.700238
Stream 4 67.147744 6.649451 27.876825 59.226248 69.373248 44.478703 61.381724 282.608075
Stream 5 36.403227 12.226129 21.997683 95.912670 44.219799 21.117974 106.473817 97.896971
Stream 6 42.114038 30.805969 25.929027 51.658733 26.475662 34.816500 31.309953 5.608395
Stream 7 48.601889 18.708127 18.893532 132.558026 50.476383 12.309402 22.661371 37.610815
Stream 8 34.413417 34.709883 37.058335 121.710608 44.676485 9.449332 19.311945 4.420232
Min Qi 34.413417 6.649451 18.893532 51.658733 22.556235 6.184842 19.311945 4.319533
Max Qi 67.147744 34.709883 38.162762 144.458624 69.373248 44.478703 117.267234 445.700238
Avg Qi 44.659731 20.463128 28.166705 109.107926 39.737127 18.348113 56.998168 141.001788
Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 1178534.1
Virt-H Throughput 1092936.2
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
1134928.4
Measurement Interval in
Throughput Test (Ts)
1739.269000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 14:33:48 09/29/2014 14:40:59 0:07:11
Stream 1 09/29/2014 14:41:04 09/29/2014 15:10:02 0:28:58
Stream 2 09/29/2014 14:41:04 09/29/2014 15:09:07 0:28:03
Stream 3 09/29/2014 14:41:04 09/29/2014 15:09:17 0:28:13
Stream 4 09/29/2014 14:41:04 09/29/2014 15:09:55 0:28:51
Stream 5 09/29/2014 14:41:04 09/29/2014 15:09:39 0:28:35
Stream 6 09/29/2014 14:41:04 09/29/2014 15:09:46 0:28:42
Stream 7 09/29/2014 14:41:04 09/29/2014 15:09:58 0:28:54
Stream 8 09/29/2014 14:41:04 09/29/2014 15:08:58 0:27:54
Refresh 0 09/29/2014 14:33:48 09/29/2014 14:34:07 0:00:19
09/29/2014 14:40:59 09/29/2014 14:41:04 0:00:05
Refresh 1 09/29/2014 15:06:57 09/29/2014 15:09:49 0:02:52
Refresh 2 09/29/2014 14:41:05 09/29/2014 14:47:39 0:06:34
Refresh 3 09/29/2014 14:47:40 09/29/2014 14:56:46 0:09:06
Refresh 4 09/29/2014 14:56:49 09/29/2014 15:03:19 0:06:30
Refresh 5 09/29/2014 15:03:24 09/29/2014 15:06:45 0:03:21
Refresh 6 09/29/2014 15:06:46 09/29/2014 15:06:49 0:00:03
Refresh 7 09/29/2014 15:06:50 09/29/2014 15:06:53 0:00:03
Refresh 8 09/29/2014 15:06:53 09/29/2014 15:10:04 0:03:11

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 9.393632 5.001910 17.053567 1.427500 17.813839 2.230451 13.884490 25.610995
Stream 1 12.971454 9.383520 94.257760 1.603106 127.940946 20.791892 78.869819 138.521273
Stream 2 21.428177 31.431513 96.366083 5.611843 58.394596 11.279502 47.114473 407.135077
Stream 3 23.377920 37.474814 83.640621 9.152178 71.186158 11.001543 46.763758 110.015662
Stream 4 49.580860 31.979940 87.662950 8.983661 68.052295 14.367631 59.266063 301.788652
Stream 5 13.483836 20.203772 391.980128 12.505446 77.966993 10.487869 52.989448 226.837637
Stream 6 38.104903 21.271630 84.689348 8.626460 86.620802 11.981171 69.182098 111.810485
Stream 7 20.243617 12.298692 99.547203 6.020951 151.584400 17.528287 62.037348 101.023802
Stream 8 22.808294 17.583072 59.180595 5.618565 123.108771 11.477376 42.485363 92.035709
Min Qi 12.971454 9.383520 59.180595 1.603106 58.394596 10.487869 42.485363 92.035709
Max Qi 49.580860 37.474814 391.980128 12.505446 151.584400 20.791892 78.869819 407.135077
Avg Qi 25.249883 22.703369 124.665586 7.265276 95.606870 13.614409 57.338546 186.146037
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 146.487681 6.798942 29.834475 3.177879 55.067866 4.503738 17.215591 9.333281
Stream 1 177.581204 44.178095 69.746005 12.306166 215.602727 30.443709 64.276384 45.266949
Stream 2 211.311651 27.403143 61.412478 12.173058 216.879170 18.272234 96.753886 35.587072
Stream 3 482.581456 68.663026 60.354163 13.408513 187.921639 17.469237 62.337222 31.706120
Stream 4 178.297373 23.711312 67.129677 15.216904 328.149575 20.258853 78.891201 84.852368
Stream 5 209.496498 28.346366 55.584081 9.644075 131.622351 24.171156 80.046801 43.625932
Stream 6 521.691639 24.126176 72.964805 15.311409 146.152570 34.748843 71.957130 58.470644
Stream 7 580.320149 17.054563 56.172396 7.530832 200.100326 12.444021 25.910599 75.653693
Stream 8 472.231674 15.064398 89.875570 42.394675 166.589234 12.831209 81.697881 73.821769
Min Qi 177.581204 15.064398 55.584081 7.530832 131.622351 12.444021 25.910599 31.706120
Max Qi 580.320149 68.663026 89.875570 42.394675 328.149575 34.748843 96.753886 84.852368
Avg Qi 354.188955 31.068385 66.654897 15.998204 199.127199 21.329908 70.233888 56.123068
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 12.252670 2.593733 4.115862 16.895672 10.183350 1.240096 18.679685 4.876067
Stream 1 356.740980 21.197870 30.422216 81.779038 65.468650 3.947503 63.933750 107.563796
Stream 2 54.087768 10.152604 34.940701 113.510640 70.908809 12.316233 109.091578 283.076004
Stream 3 52.807104 18.525982 13.740089 212.364908 16.413964 17.998809 58.653503 483.718271
Stream 4 42.389062 36.157809 28.909260 86.427025 21.605419 7.608729 54.910853 331.074114
Stream 5 48.214794 15.778893 20.681799 130.560005 43.846752 33.905533 54.536966 139.563667
Stream 6 84.061840 26.224851 16.546432 117.265210 34.766856 39.037423 0.710642 1.645351
Stream 7 63.034890 15.966686 31.666488 112.689765 28.661943 12.828171 1.274731 1.780452
Stream 8 43.879104 8.596666 32.585746 177.928730 26.763334 6.112333 1.187693 0.533668
Min Qi 42.389062 8.596666 13.740089 81.779038 16.413964 3.947503 0.710642 0.533668
Max Qi 356.740980 36.157809 34.940701 212.364908 70.908809 39.037423 109.091578 483.718271
Avg Qi 93.151943 19.075170 26.186591 129.065665 38.554466 16.719342 43.037465 168.619415

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/30/2014 16:33 GMT Modified: 09/30/2014 17:00 GMT
In Hoc Signo Vinces (part 19 of n): Scalability, 1000G, and 3000G [ Orri Erling ]

Scalability, specifically linear scalability, means that twice the data takes twice as long to process, or that double the gear processes the same data in half the time. This is only literally true for "embarrassingly parallel" workloads.

There are parts of TPC-H which have an embarrassingly parallel nature, like Q1 and Q7. There are parts that are almost as easy, like Q14, Q17, Q19, and Q21, where there is a big scan and a selective hash join with a hash table small enough to replicate everywhere. The scan scales linearly; building the hash does not, since it is done at single-server speed (once in each process). Some queries like Q9 and Q13 end up doing a big cross-partition join which runs into communication overheads.

This is our first look at how performance behaves with bigger data and a larger platform. The results shown here are interesting but are not final. I bet I can do better; by how much is what we'll find out soon enough.

We will here compare a 1000G setup on my desktop, and a 3000G setup at the CWI's Scilens cluster. The former is 2 boxes of dual Xeon E5 2630, and the latter is 8 boxes of dual Xeon E5 2650v2. All things run from memory and both have QDR IB interconnect. Counting cores and clock, the CWI cluster is 6x larger.

As a rough approximation, for the worst queries, 6x the gear runs 3x the data in the same amount of real time. The 1000G setup has near full platform utilization and the 3000G setup has about half platform utilization. In both cases, running two instances of the same query at the same time takes twice as long.

We use Q9 for this study. The plan makes a hash table of part with 1/14 of all parts, replicating to all processes. Then there is a hash table of partsupp with a key of ps_partkey, ps_suppkey, and a dependent of ps_supplycost. This is much larger than the part hash table and is therefore partitioned on ps_partkey. The build is for 1/14th of partsupp. Then there is a scan of lineitem filtered by the part hash table; then a cross-partition join to the partsupp hash table; then a cross partition join to orders, this time by index; then a hash join on a replicated hash table of supplier; then nation; then aggregation. The aggregation is done in each slice; then the slices are added up at the end.

The plan could be made better by one fewer partition crossing. Now there is a crossing from l_orderkey to l_partkey and back to o_orderkey. This would not be so if the cost model knew that the partsupp always hits. The cost model thinks it hits 1/14 of the time, because it does not know that the selection on the build is exactly the same as on the probe.

For the present purposes, the extra crossing just serves to make the matter of interest more visible.

So, for the 1000G setup, we have 43.6 seconds (s) and

Cluster 4 nodes, 44 s. 459 m/s 119788 KB/s  3120% cpu 0%  read 19% clw threads 1r 0w 0i buffers 17622126 68 d 0 w 0 pfs
 

For the 3000G setup, we have 49.9 s and

Cluster 16 nodes, 50 s. 49389 m/s 1801815 KB/s  7283% cpu 0%  read 18% clw threads 1r 0w 0i buffers 135122893 15895255 d 0 w 17 pfs
 

The platform utilization on the small system is better, at 31/48 (running/total threads); the large one has 73/256.

The large case is clearly network bound. If this were for CPU only, it should be done in half the time it takes the small system to do 1000G.

We confirm this by looking at write wait: 3940 seconds of thread time blocked on write over 50s of real time. The figures on the small one are 3.9s of thread time blocked for 39s of real time. The data transfer on the large one is 93 GB.

How to block less? One idea would be to write less. So we try compression; there is a Google snappy-based message compression option in Virtuoso.

We now get 39.6 s and

Cluster 16 nodes, 40 s. 65161 m/s 1239922 KB/s  10201% cpu 0%  read 21% clw threads 1r 0w 0i buffers 52828440 172 d 0 w 0 pfs
 

The write block time is 397 s of thread time over 39 s of real time, 10x better. The data transfer is 50.9 GB after compression. Snappy is somewhat effective for compression and very fast; in CPU profile, it is under 3% of Q9 on the small system. Gains on the small system are less, though, since blocking is not a big issue to start with.

This is still not full platform. But if the data transfer is further cut in half by a better plan, the situation will be quite good. Now we have 102/256 threads running, meaning that there could be another 40-50% of throughput to be added. The last 128 threads are second threads of a core, so count for roughly 30% of a real core.

The main cluster-specific operation is a send from one to many. This is now done by formulating the message to each recipient in a chain of string buffers; then, after all the messages are prepared, these are optionally compressed and sent to their recipient. This is needlessly simple: Compressing can proceed if ever there is a would-block situation on writing. If all the compression is done, then a blocked write should switch to another recipient, and only after all recipients have a would-block situation, then the thread can call-select with all descriptors and block on them collectively. There is a piece of code to this effect, but is not now being used. It has been seen to add no value in small cases, but could be useful here.

The IB fabric has been seen to do 1.8 GB/s bidirectionally on multiple independent point-to-point TCP links. This is about half the nominal 4 GB/s (40 Gbit/s with 10/8 encoding). So the aggregate throughputs that we see here are nowhere near the nominal spec of the network. Lower level interfaces and the occasional busy wait on the reading end could be tried to some advantage. We have not tried 10GbE either; but if that works at nominal speed, then 10GbE should also be good enough. We will try this at Amazon in due time.

In the meantime, there is a 3000G test made at the CWI cluster without message compression. The score is about 4x that of the single server at 300G using the same hardware. The run is with approximately half platform utilization. There are three runs of power plus throughput, the first run being cold.

Run Power Throughput Composite
Run 1 305,881.5 1,072,411.9 572,739.8
Run 2 1,292,085.1 1,179,391.6 1,234,453.1
Run 3 1,178,534.1 1,092,936.2 1,134,928.4

The numerical quantities summaries follow. One problem of the run is a high peak of query memory consumption leading to slowdown. Some parts should probably be done in multiple passes to keep the peak lower and not run into swapping. The details will have to be sorted out. This is a demonstration of capability; the perfected accomplishment is to follow.

3000G Run 1

Virt-H Executive Summary

Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 305,881.5
Virt-H Throughput 1,072,411.9
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
572,739.8
Measurement Interval in
Throughput Test (Ts)
1,772.554000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 12:54:52 09/29/2014 13:31:17 0:36:25
Stream 1 09/29/2014 13:31:24 09/29/2014 13:59:24 0:28:00
Stream 2 09/29/2014 13:31:24 09/29/2014 13:58:59 0:27:35
Stream 3 09/29/2014 13:31:24 09/29/2014 13:58:29 0:27:05
Stream 4 09/29/2014 13:31:24 09/29/2014 13:58:52 0:27:28
Stream 5 09/29/2014 13:31:24 09/29/2014 14:00:06 0:28:42
Stream 6 09/29/2014 13:31:24 09/29/2014 13:58:18 0:26:54
Stream 7 09/29/2014 13:31:24 09/29/2014 13:59:25 0:28:01
Stream 8 09/29/2014 13:31:24 09/29/2014 13:58:50 0:27:26
Refresh 0 09/29/2014 12:54:52 09/29/2014 12:56:59 0:02:07
09/29/2014 13:31:17 09/29/2014 13:31:23 0:00:06
Refresh 1 09/29/2014 14:00:38 09/29/2014 14:01:11 0:00:33
Refresh 2 09/29/2014 13:31:25 09/29/2014 13:36:57 0:05:32
Refresh 3 09/29/2014 13:36:56 09/29/2014 13:47:02 0:10:06
Refresh 4 09/29/2014 13:47:03 09/29/2014 13:51:40 0:04:37
Refresh 5 09/29/2014 13:51:42 09/29/2014 13:56:40 0:04:58
Refresh 6 09/29/2014 13:56:40 09/29/2014 13:59:25 0:02:45
Refresh 7 09/29/2014 13:59:25 09/29/2014 14:00:10 0:00:45
Refresh 8 09/29/2014 14:00:11 09/29/2014 14:00:37 0:00:26

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 601.576975 90.803782 108.725110 177.112667 171.995572 2.098138 15.768311 152.511444
Stream 1 13.310341 32.722946 125.551415 1.912836 46.041675 13.294214 85.345068 165.424288
Stream 2 19.425885 9.248670 150.855556 7.085737 88.445566 10.490432 49.318554 322.500839
Stream 3 30.534391 14.273478 100.987791 59.341763 46.442443 9.613795 64.186196 146.324186
Stream 4 28.211213 37.134522 64.189335 10.931513 100.610673 9.929866 112.270530 108.489951
Stream 5 29.226411 18.132589 95.245160 63.100068 115.663908 6.151231 46.251309 127.742471
Stream 6 30.750930 20.888658 108.894177 55.168565 82.016828 69.451493 65.161517 103.697733
Stream 7 13.462570 18.033847 32.065492 78.910373 202.998301 10.688279 47.167022 139.601948
Stream 8 24.354314 16.711503 112.008551 8.307098 126.849630 7.127605 51.083118 98.648077
Min Qi 13.310341 9.248670 32.065492 1.912836 46.041675 6.151231 46.251309 98.648077
Max Qi 30.750930 37.134522 150.855556 78.910373 202.998301 69.451493 112.270530 322.500839
Avg Qi 23.659507 20.893277 98.724685 35.594744 101.133628 17.093364 65.097914 151.553687
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 92.991259 5.175922 42.238393 29.239879 367.805534 3.604910 15.557396 11.650267
Stream 1 149.502128 30.197806 50.786184 217.190836 283.545905 11.653171 73.321150 116.860455
Stream 2 245.783668 22.278841 50.578731 36.301810 181.405269 32.236754 57.631764 61.540533
Stream 3 377.782738 24.129319 84.097657 10.959661 171.698669 8.973519 54.532180 45.527142
Stream 4 341.148908 74.358770 85.782399 43.116347 151.146233 22.870727 74.439693 51.871535
Stream 5 72.259919 11.424035 79.310504 9.833135 562.871920 14.961209 127.861874 55.377721
Stream 6 373.301225 41.379753 81.983260 9.373200 95.039317 19.071346 76.159452 48.324504
Stream 7 449.871952 16.099152 48.047940 8.559784 211.094730 10.569071 26.710228 72.571454
Stream 8 395.771006 33.537585 54.850876 141.526389 153.763316 12.997092 127.961975 57.100346
Min Qi 72.259919 11.424035 48.047940 8.559784 95.039317 8.973519 26.710228 45.527142
Max Qi 449.871952 74.358770 85.782399 217.190836 562.871920 32.236754 127.961975 116.860455
Avg Qi 300.677693 31.675658 66.929694 59.607645 226.320670 16.666611 77.327289 63.646711
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 12.230334 70.991261 33.092797 17.517230 15.798438 19.743562 127.494687 5.893471
Stream 1 27.550293 14.970857 16.442806 111.138612 68.214095 7.884782 27.109441 6.087067
Stream 2 43.277918 12.748690 22.681844 92.835566 84.416610 14.661934 151.094498 153.285076
Stream 3 129.696125 13.435663 14.674499 129.179966 39.176513 6.286296 181.596838 416.052710
Stream 4 110.348816 7.080225 21.051910 85.758973 65.130356 7.292999 123.386514 151.000786
Stream 5 43.365006 9.847612 32.881770 94.752284 67.788314 9.035439 72.539334 223.967821
Stream 6 34.534280 36.347298 27.849276 122.736244 51.447492 25.051058 80.452175 84.519426
Stream 7 48.021860 30.594474 22.522426 99.245893 73.076698 7.260729 38.585852 5.697277
Stream 8 29.484201 12.368769 40.344043 84.137820 30.813313 4.856991 22.196547 4.600057
Min Qi 27.550293 7.080225 14.674499 84.137820 30.813313 4.856991 22.196547 4.600057
Max Qi 129.696125 36.347298 40.344043 129.179966 84.416610 25.051058 181.596838 416.052710
Avg Qi 58.284812 17.174198 24.806072 102.473170 60.007924 10.291279 87.120150 130.651277

3000G Run 2

Virt-H Executive Summary

Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 1292085.1
Virt-H Throughput 1179391.6
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
1234453.1
Measurement Interval in
Throughput Test (Ts)
1611.779000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 14:01:15 09/29/2014 14:06:48 0:05:33
Stream 1 09/29/2014 14:06:53 09/29/2014 14:30:22 0:23:29
Stream 2 09/29/2014 14:06:53 09/29/2014 14:32:30 0:25:37
Stream 3 09/29/2014 14:06:53 09/29/2014 14:31:23 0:24:30
Stream 4 09/29/2014 14:06:53 09/29/2014 14:31:34 0:24:41
Stream 5 09/29/2014 14:06:53 09/29/2014 14:32:53 0:26:00
Stream 6 09/29/2014 14:06:53 09/29/2014 14:29:51 0:22:58
Stream 7 09/29/2014 14:06:53 09/29/2014 14:31:34 0:24:41
Stream 8 09/29/2014 14:06:53 09/29/2014 14:30:35 0:23:42
Refresh 0 09/29/2014 14:01:15 09/29/2014 14:01:35 0:00:20
09/29/2014 14:06:49 09/29/2014 14:06:53 0:00:04
Refresh 1 09/29/2014 14:33:16 09/29/2014 14:33:45 0:00:29
Refresh 2 09/29/2014 14:06:55 09/29/2014 14:12:28 0:05:33
Refresh 3 09/29/2014 14:12:29 09/29/2014 14:21:55 0:09:26
Refresh 4 09/29/2014 14:21:55 09/29/2014 14:27:40 0:05:45
Refresh 5 09/29/2014 14:27:43 09/29/2014 14:31:14 0:03:31
Refresh 6 09/29/2014 14:31:14 09/29/2014 14:31:51 0:00:37
Refresh 7 09/29/2014 14:31:51 09/29/2014 14:32:52 0:01:01
Refresh 8 09/29/2014 14:32:52 09/29/2014 14:33:16 0:00:24

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 9.451169 3.644118 18.419151 1.404395 15.740525 2.085038 15.171847 25.400834
Stream 1 19.558041 6.607300 85.774410 4.503525 81.448472 11.976129 92.140470 145.743853
Stream 2 31.042019 7.877299 71.958033 8.862111 142.452144 18.489193 81.003310 85.856529
Stream 3 38.833612 12.440326 86.063103 7.165120 84.707025 16.931531 100.442710 122.411252
Stream 4 15.751913 33.026762 50.457193 7.064220 114.130257 5.992556 66.035959 84.596973
Stream 5 18.462884 28.047942 110.690543 16.566547 104.403789 5.303453 72.552640 402.383383
Stream 6 17.858339 33.988800 110.431091 7.238431 72.229953 16.850955 68.231546 180.601000
Stream 7 23.055572 17.044813 96.105520 8.941132 171.130879 8.423100 70.634541 147.261648
Stream 8 19.840798 13.860740 74.961175 16.171566 56.165875 5.904921 47.646217 125.991819
Min Qi 15.751913 6.607300 50.457193 4.503525 56.165875 5.303453 47.646217 84.596973
Max Qi 38.833612 33.988800 110.690543 16.566547 171.130879 18.489193 100.442710 402.383383
Avg Qi 23.050397 19.111748 85.805134 9.564082 103.333549 11.233980 74.835924 161.855807
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 54.766945 5.551163 29.216632 3.035008 52.816902 3.346243 15.767022 10.066112
Stream 1 130.666380 9.658277 49.332720 103.036705 194.520370 12.166344 65.144599 97.158571
Stream 2 254.754936 22.605298 38.102466 21.121168 300.467330 12.262318 108.203491 50.696657
Stream 3 283.761567 19.327164 73.414574 7.431651 183.121904 12.573854 73.814766 46.802493
Stream 4 290.341947 57.452026 58.354221 13.066162 189.263163 18.998781 121.269774 54.831406
Stream 5 81.787025 8.410538 79.822552 16.005077 190.730342 21.697136 100.456487 46.744884
Stream 6 202.558515 39.360009 74.519981 15.960756 137.321631 26.583824 57.537668 60.758997
Stream 7 226.790801 44.175536 73.992368 7.561897 182.853851 17.597471 31.128055 44.389893
Stream 8 275.423934 21.980040 60.538239 39.736622 173.574795 58.786316 95.124912 25.564108
Min Qi 81.787025 8.410538 38.102466 7.431651 137.321631 12.166344 31.128055 25.564108
Max Qi 290.341947 57.452026 79.822552 103.036705 300.467330 58.786316 121.269774 97.158571
Avg Qi 218.260638 27.871111 63.509640 27.990005 193.981673 22.583255 81.584969 53.368376

3000G Run 3

Virt-H Executive Summary

Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 13.620157 2.288504 4.166807 16.468447 9.991810 1.101775 20.152227 4.294680
Stream 1 44.026143 31.720525 25.684461 134.254716 30.797008 9.568594 24.328205 4.319533
Stream 2 40.283148 9.970277 29.731019 133.083785 29.322194 8.859556 73.251098 249.850045
Stream 3 44.288244 18.914661 38.162762 144.458624 22.556235 6.184842 117.267234 445.700238
Stream 4 67.147744 6.649451 27.876825 59.226248 69.373248 44.478703 61.381724 282.608075
Stream 5 36.403227 12.226129 21.997683 95.912670 44.219799 21.117974 106.473817 97.896971
Stream 6 42.114038 30.805969 25.929027 51.658733 26.475662 34.816500 31.309953 5.608395
Stream 7 48.601889 18.708127 18.893532 132.558026 50.476383 12.309402 22.661371 37.610815
Stream 8 34.413417 34.709883 37.058335 121.710608 44.676485 9.449332 19.311945 4.420232
Min Qi 34.413417 6.649451 18.893532 51.658733 22.556235 6.184842 19.311945 4.319533
Max Qi 67.147744 34.709883 38.162762 144.458624 69.373248 44.478703 117.267234 445.700238
Avg Qi 44.659731 20.463128 28.166705 109.107926 39.737127 18.348113 56.998168 141.001788
Report Date September 29, 2014
Database Scale Factor 3000
Query Streams for
Throughput Test
8
Virt-H Power 1178534.1
Virt-H Throughput 1092936.2
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
1134928.4
Measurement Interval in
Throughput Test (Ts)
1739.269000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/29/2014 14:33:48 09/29/2014 14:40:59 0:07:11
Stream 1 09/29/2014 14:41:04 09/29/2014 15:10:02 0:28:58
Stream 2 09/29/2014 14:41:04 09/29/2014 15:09:07 0:28:03
Stream 3 09/29/2014 14:41:04 09/29/2014 15:09:17 0:28:13
Stream 4 09/29/2014 14:41:04 09/29/2014 15:09:55 0:28:51
Stream 5 09/29/2014 14:41:04 09/29/2014 15:09:39 0:28:35
Stream 6 09/29/2014 14:41:04 09/29/2014 15:09:46 0:28:42
Stream 7 09/29/2014 14:41:04 09/29/2014 15:09:58 0:28:54
Stream 8 09/29/2014 14:41:04 09/29/2014 15:08:58 0:27:54
Refresh 0 09/29/2014 14:33:48 09/29/2014 14:34:07 0:00:19
09/29/2014 14:40:59 09/29/2014 14:41:04 0:00:05
Refresh 1 09/29/2014 15:06:57 09/29/2014 15:09:49 0:02:52
Refresh 2 09/29/2014 14:41:05 09/29/2014 14:47:39 0:06:34
Refresh 3 09/29/2014 14:47:40 09/29/2014 14:56:46 0:09:06
Refresh 4 09/29/2014 14:56:49 09/29/2014 15:03:19 0:06:30
Refresh 5 09/29/2014 15:03:24 09/29/2014 15:06:45 0:03:21
Refresh 6 09/29/2014 15:06:46 09/29/2014 15:06:49 0:00:03
Refresh 7 09/29/2014 15:06:50 09/29/2014 15:06:53 0:00:03
Refresh 8 09/29/2014 15:06:53 09/29/2014 15:10:04 0:03:11

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 9.393632 5.001910 17.053567 1.427500 17.813839 2.230451 13.884490 25.610995
Stream 1 12.971454 9.383520 94.257760 1.603106 127.940946 20.791892 78.869819 138.521273
Stream 2 21.428177 31.431513 96.366083 5.611843 58.394596 11.279502 47.114473 407.135077
Stream 3 23.377920 37.474814 83.640621 9.152178 71.186158 11.001543 46.763758 110.015662
Stream 4 49.580860 31.979940 87.662950 8.983661 68.052295 14.367631 59.266063 301.788652
Stream 5 13.483836 20.203772 391.980128 12.505446 77.966993 10.487869 52.989448 226.837637
Stream 6 38.104903 21.271630 84.689348 8.626460 86.620802 11.981171 69.182098 111.810485
Stream 7 20.243617 12.298692 99.547203 6.020951 151.584400 17.528287 62.037348 101.023802
Stream 8 22.808294 17.583072 59.180595 5.618565 123.108771 11.477376 42.485363 92.035709
Min Qi 12.971454 9.383520 59.180595 1.603106 58.394596 10.487869 42.485363 92.035709
Max Qi 49.580860 37.474814 391.980128 12.505446 151.584400 20.791892 78.869819 407.135077
Avg Qi 25.249883 22.703369 124.665586 7.265276 95.606870 13.614409 57.338546 186.146037
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 146.487681 6.798942 29.834475 3.177879 55.067866 4.503738 17.215591 9.333281
Stream 1 177.581204 44.178095 69.746005 12.306166 215.602727 30.443709 64.276384 45.266949
Stream 2 211.311651 27.403143 61.412478 12.173058 216.879170 18.272234 96.753886 35.587072
Stream 3 482.581456 68.663026 60.354163 13.408513 187.921639 17.469237 62.337222 31.706120
Stream 4 178.297373 23.711312 67.129677 15.216904 328.149575 20.258853 78.891201 84.852368
Stream 5 209.496498 28.346366 55.584081 9.644075 131.622351 24.171156 80.046801 43.625932
Stream 6 521.691639 24.126176 72.964805 15.311409 146.152570 34.748843 71.957130 58.470644
Stream 7 580.320149 17.054563 56.172396 7.530832 200.100326 12.444021 25.910599 75.653693
Stream 8 472.231674 15.064398 89.875570 42.394675 166.589234 12.831209 81.697881 73.821769
Min Qi 177.581204 15.064398 55.584081 7.530832 131.622351 12.444021 25.910599 31.706120
Max Qi 580.320149 68.663026 89.875570 42.394675 328.149575 34.748843 96.753886 84.852368
Avg Qi 354.188955 31.068385 66.654897 15.998204 199.127199 21.329908 70.233888 56.123068
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 12.252670 2.593733 4.115862 16.895672 10.183350 1.240096 18.679685 4.876067
Stream 1 356.740980 21.197870 30.422216 81.779038 65.468650 3.947503 63.933750 107.563796
Stream 2 54.087768 10.152604 34.940701 113.510640 70.908809 12.316233 109.091578 283.076004
Stream 3 52.807104 18.525982 13.740089 212.364908 16.413964 17.998809 58.653503 483.718271
Stream 4 42.389062 36.157809 28.909260 86.427025 21.605419 7.608729 54.910853 331.074114
Stream 5 48.214794 15.778893 20.681799 130.560005 43.846752 33.905533 54.536966 139.563667
Stream 6 84.061840 26.224851 16.546432 117.265210 34.766856 39.037423 0.710642 1.645351
Stream 7 63.034890 15.966686 31.666488 112.689765 28.661943 12.828171 1.274731 1.780452
Stream 8 43.879104 8.596666 32.585746 177.928730 26.763334 6.112333 1.187693 0.533668
Min Qi 42.389062 8.596666 13.740089 81.779038 16.413964 3.947503 0.710642 0.533668
Max Qi 356.740980 36.157809 34.940701 212.364908 70.908809 39.037423 109.091578 483.718271
Avg Qi 93.151943 19.075170 26.186591 129.065665 38.554466 16.719342 43.037465 168.619415

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/30/2014 16:33 GMT Modified: 09/30/2014 16:37 GMT
In Hoc Signo Vinces (part 18 of n): Cluster Dynamics [ Virtuso Data Space Bot ]

This article is about how scale-out differs from single-server. This shows large effects of parameters whose very existence most would not anticipate, and some low level metrics for assessing these. The moral of the story is that this is the stuff which makes the difference between merely surviving scale-out and winning with it. The developer and DBA would not normally know about this; thus these things fall into the category of adaptive self-configuration expected from the DBMS. But since this series is about what makes performance, I will discuss the dynamics such as they are and how to play these.

We take the prototypical cross partition join in Q13: Make a hash table of all customers, partitioned by c_custkey. This is independently done with full parallelism in each partition. Scan the orders, get the customer (in a different partition), and flag the customers that had at least one order. Then, to get the customers with no orders, return the customers that were not flagged in the previous pass.

The single-server time in part 12 was 7.8 and 6.0 with a single user. We consider the better of the times. The difference is due to allocating memory on the first go; on the second go the memory is already in reserve.

With default settings, we get 4595 ms (microseconds), with per node resource utilization at:


Cluster 4 nodes, 4 s. 112405 m/s 742602 KB/s  2749% cpu 0%  read 4% clw threads 1r 0w 0i buffers 8577766 287874 d 0 w 0 pfs
cl 1: 27867 m/s 185654 KB/s  733% cpu 0%  read 4% clw threads 1r 0w 0i buffers 2144242 71757 d 0 w 0 pfs
cl 2: 28149 m/s 185372 KB/s  672% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144640 71903 d 0 w 0 pfs
cl 3: 28220 m/s 185621 KB/s  675% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144454 71962 d 0 w 0 pfs
cl 4: 28150 m/s 185837 KB/s  667% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144430 72252 d 0 w 0 pfs

 

The top line is the summary; the lines below are per-process. The m/s is messages-per-second; KB/s is interconnect traffic per second; clw % is idle time spent waiting for a reply from another process. The cluster is set up with 4 processes across 2 machines, each with 2 NUMA nodes. Each process has affinity to the NUMA node, so local memory only. The time is reasonable in light of the overall CPU of 2700%. The maximum would be 4800% with all threads of all cores busy all the time.

The catch here is that we do not have a steady half-platform utilization all the time, but full platform peaks followed by synchronization barriers with very low utilization. So, we set the batch size differently:


cl_exec ('__dbf_set (''cl_dfg_batch_bytes'', 50000000)');

 

This means that we set, on each process, the cl_dfg_batch_bytes to 50M from a default of 10M. The effect is that each scan of orders, one thread per slice, 48 slices total, will produce 50MB worth of o_custkeys to be sent to the other partition for getting the customer. After each 50M, the thread stops and will produce the next batch when all are done and a global continue message is sent by the coordinator.

The time is now 3173 ms with:


Cluster 4 nodes, 3 s. 158220 m/s 1054944 KB/s  3676% cpu 0%  read 1% clw threads 1r 0w 0i buffers 8577766 287874 d 0 w 0 pfs
cl 1: 39594 m/s 263962 KB/s  947% cpu 0%  read 1% clw threads 1r 0w 0i buffers 2144242 71757 d 0 w 0 pfs
cl 2: 39531 m/s 263476 KB/s  894% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144640 71903 d 0 w 0 pfs
cl 3: 39523 m/s 263684 KB/s  933% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144454 71962 d 0 w 0 pfs
cl 4: 39535 m/s 263586 KB/s  900% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144430 72252 d 0 w 0 pfs

 

The platform utilization is better as we see. The throughput is nearly double that of the single-server, which is pretty good for a communication-heavy query.

This was done with a vector size of 10K. In other words, each partition gets 10K o_custkeys and splits these 48 ways to go to every recipient. 1/4 are in the same process, 1/4 in a different process on the same machine, and 2/4 on a different machine. The recipient gets messages with an average of 208 o_custkey values, puts them back together in batches of 10K, and passes these to the hash join with customer.

We try different vector sizes, such as 100K:


cl_exec ('__dbf_set (''dc_batch_sz'', 100000)');

 

There are two metrics of interest here: The write block time, and the scheduling overhead. The write block time is microseconds, which increases whenever a thread must wait before it can write to a connection. The scheduling overhead is cumulative clocks spent by threads while waiting for a critical section that deals with dispatching messages to consumer threads. Long messages make blocking; short messages make frequent scheduling decisions.


SELECT cl_sys_stat ('local_cll_clk', clr=>1), 
       cl_sys_stat ('write_block_usec', clr=>1)
;

 

cl_sys_stat gets the counters from all processes and returns the sum. clr=>1 means that the counter is cleared after read.

We do Q13 with vector sizes of 10, 100, and 1000K.

Vector size msec mtx wblock
10K 3297 10,829,910,329 0
100K 3150 1,663,238,367 59,132
1000K 3876 414,631,129 4,578,003

So, 100K seems to strike the best balance between scheduling and blocking on write.

The times are measured after several samples with each setting. The times stabilize after a few runs, as the appropriate size memory blocks are in reserve. Calling mmap to allocate these on the first run with each size has a very high penalty, e.g., 60s for the first run with 1M vector size. We note that blocking on write is really bad even though 1/3 of the time there is no network and 2/3 of the time there is a fast network (QDR IB) with no other load. Further, the affinities are set so that the thread responsible for incoming messages is always on core. Result variability on consecutive runs is under 5%, which is similar to single-server behavior.

It would seem that a mutex, as bad as it is, is still better than a distributed cause for going off core (blocking on write). The latency for continuing a thread thus blocked is of course higher than the latency for continuing one that is waiting for a mutex.

We note that a cluster with more machines can take a longer vector size because a vector spreads out to more recipients. The key seems to be to set the message size so that blocking on write is not common. This is a possible adaptive execution feature. We have seen no particular benefit from SDP (Sockets Direct Protocol) and its zero copy. This is a TCP replacement that comes with the InfiniBand drivers.

We will next look at replication/partitioning tradeoffs for hash joins. Then we can look at full runs.

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/26/2014 17:07 GMT Modified: 09/30/2014 16:37 GMT
In Hoc Signo Vinces (part 17 of n): 100G and 300G Runs on Dual Xeon E5 2650v2 [ Virtuso Data Space Bot ]

This is an update presenting sample results on a newer platform for a single-server configuration. This is to verify that performance scales with the addition of cores and clock speed. Further, we note that the jump from 100G to 300G changes very little about the score. 3x larger takes approximately 3x longer, as long as things are in memory.

The platform is one node of the CWI cluster which was also used for the 500Gt RDF experiments reported on this blog. The specification is dual Xeon E5 2650v2 (8 core, 16 thread, 2.6 GHz) with 256 GB RAM. The disk setup is a RAID-0 of three 2 TB rotating disks.

For the 100G, we go from 240 to 395, which is about 1.64x. The new platform has 16 vs 12 cores and a clock of 2.6 as opposed to 2.3. This makes a multiplier of 1.5. The rest of the acceleration is probably attributable to faster memory clock. Anyway, the point of more speed from larger platform is made.

The top level scores per run are as follows; the numerical quantities summaries are appended.

100G

Run Power Throughput Composite
Run 1 391,000.1 401,029.4 395,983.0
Run 2 388,746.2 404,189.3 396,392.6

300G

Run Power Throughput Composite
Run 1 61,988.7 384,883.7 154,461.6
Run 2 423,431.8 387,248.6 404,936.3
Run 3 417,672.0 389,719.5 403,453.7

The interested may reproduce the results using the feature/analytics branch of the v7fasttrack git repository on GitHub as described in Part 13.

For the 300G runs, we note a much longer load time; see below, as this is seriously IO bound.

The first power test at 300G is a non-starter, even though this comes right after bulk load. Still, the data is not in working set and getting it from disk is simply an automatic disqualification, unless maybe one had 300 separate disks. This happens in TPC benchmarks, but not very often in the field. Looking at the first power run, the first queries take the longest, but by the time the power run starts, the working set is there. By an artifact of the metric (use of geometric mean for the power test), long queries are penalized less there than in the throughput run.

So, we run 3 executions instead of the prescribed 2, to have 2 executions from warm state.

To do 300G well in 256 GB of RAM, one needs either to use several SSDs, or to increase compression and keep all in memory, so no secondary storage at all. In order to keep all in memory, one could have stream-compression on string columns. Stream-compressing strings (e.g., o_comment, l_comment) does not pay if one is already in memory, but if stream-compressing strings eliminates going to secondary storage, then the win is sure.

As before, all caveats apply; the results are unaudited and for information only. Therefore we do not use the official metric name.

100G Run 1

Virt-H Executive Summary

Report Date September 15, 2014
Database Scale Factor 100
Start of Database Load 09/15/2014 07:04:08
End of Database Load 09/15/2014 07:15:58
Database Load Time 0:11:50
Query Streams for
Throughput Test
5
Virt-H Power 391,000.1
Virt-H Throughput 401,029.4
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
395,983.0
Measurement Interval in
Throughput Test (Ts)
98.846000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/15/2014 13:13:01 09/15/2014 13:13:28 0:00:27
Stream 1 09/15/2014 13:13:29 09/15/2014 13:15:06 0:01:37
Stream 2 09/15/2014 13:13:29 09/15/2014 13:15:07 0:01:38
Stream 3 09/15/2014 13:13:29 09/15/2014 13:15:07 0:01:38
Stream 4 09/15/2014 13:13:29 09/15/2014 13:15:04 0:01:35
Stream 5 09/15/2014 13:13:29 09/15/2014 13:15:08 0:01:39
Refresh 0 09/15/2014 13:13:01 09/15/2014 13:13:03 0:00:02
09/15/2014 13:13:28 09/15/2014 13:13:29 0:00:01
Refresh 1 09/15/2014 13:14:10 09/15/2014 13:14:16 0:00:06
Refresh 2 09/15/2014 13:13:29 09/15/2014 13:13:42 0:00:13
Refresh 3 09/15/2014 13:13:42 09/15/2014 13:13:53 0:00:11
Refresh 4 09/15/2014 13:13:53 09/15/2014 13:14:02 0:00:09
Refresh 5 09/15/2014 13:14:02 09/15/2014 13:14:10 0:00:08

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 1.442477 0.304513 0.720263 0.351285 0.979414 0.479455 0.865992 0.875236
Stream 1 3.938133 0.920533 3.738724 2.769707 3.209728 1.339146 2.759384 3.626868
Stream 2 4.104738 0.952245 4.719658 0.865586 2.139267 0.850909 2.044402 2.600373
Stream 3 3.692119 1.024876 3.430172 1.579846 4.097845 1.859468 2.312921 6.238070
Stream 4 5.419537 0.531571 2.116176 1.256836 4.787617 2.117995 3.517466 3.982180
Stream 5 5.167029 0.746720 3.157557 1.255182 3.004802 2.131963 3.648316 2.835751
Min Qi 3.692119 0.531571 2.116176 0.865586 2.139267 0.850909 2.044402 2.600373
Max Qi 5.419537 1.024876 4.719658 2.769707 4.787617 2.131963 3.648316 6.238070
Avg Qi 4.464311 0.835189 3.432457 1.545431 3.447852 1.659896 2.856498 3.856648
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 2.606044 1.117063 1.847930 0.618534 4.327600 1.110908 0.995289 0.975910
Stream 1 7.463593 4.686463 4.549733 4.168129 15.759178 5.247666 4.495030 4.075198
Stream 2 9.398552 5.170904 3.934405 1.880683 19.968787 3.767992 6.965337 3.849845
Stream 3 7.581069 4.109905 4.301159 2.123634 17.683200 5.383603 4.376887 2.854777
Stream 4 9.927887 6.913209 3.351489 2.802724 16.985827 3.925148 4.691474 4.080586
Stream 5 7.035080 3.921425 6.844778 2.899238 14.839509 4.986742 6.629664 4.089547
Min Qi 7.035080 3.921425 3.351489 1.880683 14.839509 3.767992 4.376887 2.854777
Max Qi 9.927887 6.913209 6.844778 4.168129 19.968787 5.383603 6.965337 4.089547
Avg Qi 8.281236 4.960381 4.596313 2.774882 17.047300 4.662230 5.431678 3.789991
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 1.215956 0.745257 0.699801 1.281834 1.291110 0.518425 1.827192 1.014431
Stream 1 5.779854 2.383264 2.396793 6.130511 5.002700 1.968425 4.172437 2.427047
Stream 2 7.828176 1.833416 3.175649 4.785709 5.385834 1.403290 6.383005 6.366525
Stream 3 5.880139 1.797383 3.258024 5.601364 6.373216 1.977848 5.235542 6.385010
Stream 4 3.989621 1.252891 2.478303 4.678629 3.212176 2.740586 5.037995 3.911379
Stream 5 5.030440 2.010988 4.188428 6.221990 5.418788 2.187718 3.589915 3.517380
Min Qi 3.989621 1.252891 2.396793 4.678629 3.212176 1.403290 3.589915 2.427047
Max Qi 7.828176 2.383264 4.188428 6.221990 6.373216 2.740586 6.383005 6.385010
Avg Qi 5.701646 1.855588 3.099439 5.483641 5.078543 2.055573 4.883779 4.521468

100G Run 2

Virt-H Executive Summary

Report Date September 15, 2014
Database Scale Factor 100
Total Data Storage/Database Size 87,312M
Start of Database Load 09/15/2014 07:04:08
End of Database Load 09/15/2014 07:15:58
Database Load Time 0:11:50
Query Streams for
Throughput Test
5
Virt-H Power 388,746.2
Virt-H Throughput 404,189.3
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
396,392.6
Measurement Interval in
Throughput Test (Ts)
98.074000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/15/2014 13:15:11 09/15/2014 13:15:38 0:00:27
Stream 1 09/15/2014 13:15:39 09/15/2014 13:17:13 0:01:34
Stream 2 09/15/2014 13:15:39 09/15/2014 13:17:16 0:01:37
Stream 3 09/15/2014 13:15:39 09/15/2014 13:17:15 0:01:36
Stream 4 09/15/2014 13:15:39 09/15/2014 13:17:17 0:01:38
Stream 5 09/15/2014 13:15:39 09/15/2014 13:17:15 0:01:36
Refresh 0 09/15/2014 13:15:11 09/15/2014 13:15:12 0:00:01
09/15/2014 13:15:38 09/15/2014 13:15:39 0:00:01
Refresh 1 09/15/2014 13:16:13 09/15/2014 13:16:20 0:00:07
Refresh 2 09/15/2014 13:15:39 09/15/2014 13:15:47 0:00:08
Refresh 3 09/15/2014 13:15:47 09/15/2014 13:15:56 0:00:09
Refresh 4 09/15/2014 13:15:56 09/15/2014 13:16:03 0:00:07
Refresh 5 09/15/2014 13:16:03 09/15/2014 13:16:12 0:00:09

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 1.467681 0.277665 0.766102 0.365185 0.941206 0.549381 0.938998 0.803514
Stream 1 3.883169 1.488521 3.366920 1.627478 3.632321 2.065565 2.911138 2.444544
Stream 2 3.294589 1.138066 3.260775 1.899615 5.367725 1.820374 3.655119 2.186642
Stream 3 3.797641 0.995877 3.239690 2.483035 2.737690 1.505998 4.058083 4.268644
Stream 4 4.099187 0.402685 4.704959 1.469825 5.367910 2.783018 2.706164 2.551061
Stream 5 3.651273 1.598314 2.051899 1.283754 4.711897 1.519763 2.851300 2.484093
Min Qi 3.294589 0.402685 2.051899 1.283754 2.737690 1.505998 2.706164 2.186642
Max Qi 4.099187 1.598314 4.704959 2.483035 5.367910 2.783018 4.058083 4.268644
Avg Qi 3.745172 1.124693 3.324849 1.752741 4.363509 1.938944 3.236361 2.786997
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 2.734812 1.115539 1.679910 0.633239 4.391739 1.130082 1.137284 0.919646
Stream 1 9.271071 5.664855 3.377869 2.148228 16.046021 2.935643 4.897009 2.891040
Stream 2 10.272523 4.578427 4.086788 2.312762 16.295728 2.714776 6.393897 2.414951
Stream 3 7.095213 4.544636 4.073433 2.710320 18.789088 3.903873 5.471600 2.994184
Stream 4 7.567924 3.691088 3.951049 2.207944 18.189014 4.985841 6.568935 3.965322
Stream 5 8.173577 4.959777 4.736593 3.507469 17.106990 5.405699 7.357104 3.125788
Min Qi 7.095213 3.691088 3.377869 2.148228 16.046021 2.714776 4.897009 2.414951
Max Qi 10.272523 5.664855 4.736593 3.507469 18.789088 5.405699 7.357104 3.965322
Avg Qi 8.476062 4.687757 4.045146 2.577345 17.285368 3.989166 6.137709 3.078257
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 1.206347 0.792013 0.699476 1.349182 1.505387 0.543947 1.549135 0.824344
Stream 1 5.135036 1.873195 4.978155 5.988226 4.705365 1.211049 4.175947 3.579242
Stream 2 7.656125 2.229819 2.805272 6.629781 4.138014 1.423334 5.165700 3.197300
Stream 3 6.385983 2.086301 3.450305 3.292353 5.503905 2.302992 4.860041 3.865383
Stream 4 6.514967 2.876895 3.481100 1.629007 5.715903 2.121692 3.681208 3.347289
Stream 5 4.100205 2.400816 2.142291 4.710677 5.765320 1.616445 6.095817 3.007436
Min Qi 4.100205 1.873195 2.142291 1.629007 4.138014 1.211049 3.681208 3.007436
Max Qi 7.656125 2.876895 4.978155 6.629781 5.765320 2.302992 6.095817 3.865383
Avg Qi 5.958463 2.293405 3.371425 4.450009 5.165701 1.735102 4.795743 3.399330

300G Run 1

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 61,988.7
Virt-H Throughput 384,883.7
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
154,461.6
Measurement Interval in
Throughput Test (Ts)
370.498000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:00:29 09/25/2014 19:22:25 0:21:56
Stream 1 09/25/2014 19:22:27 09/25/2014 19:28:23 0:05:56
Stream 2 09/25/2014 19:22:27 09/25/2014 19:28:23 0:05:56
Stream 3 09/25/2014 19:22:27 09/25/2014 19:28:26 0:05:59
Stream 4 09/25/2014 19:22:27 09/25/2014 19:28:13 0:05:46
Stream 5 09/25/2014 19:22:27 09/25/2014 19:28:38 0:06:11
Stream 6 09/25/2014 19:22:27 09/25/2014 19:28:38 0:06:11
Refresh 0 09/25/2014 19:00:29 09/25/2014 19:03:56 0:03:27
09/25/2014 19:22:25 09/25/2014 19:22:27 0:00:02
Refresh 1 09/25/2014 19:25:22 09/25/2014 19:25:58 0:00:36
Refresh 2 09/25/2014 19:22:27 09/25/2014 19:23:11 0:00:44
Refresh 3 09/25/2014 19:23:10 09/25/2014 19:23:40 0:00:30
Refresh 4 09/25/2014 19:23:40 09/25/2014 19:24:21 0:00:41
Refresh 5 09/25/2014 19:24:21 09/25/2014 19:24:58 0:00:37
Refresh 6 09/25/2014 19:24:59 09/25/2014 19:25:22 0:00:23

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 183.735463 95.826361 79.826802 87.603164 47.099641 1.301704 2.606488 52.667426
Stream 1 9.400003 1.983777 15.839250 3.001843 15.593335 6.067716 8.870516 11.679706
Stream 2 12.634711 3.472203 13.683075 8.057952 16.500741 5.403771 11.181661 12.393932
Stream 3 10.807287 3.793587 15.844244 3.214977 15.960600 7.099744 10.424530 21.001623
Stream 4 11.900829 3.741707 14.219904 5.616907 16.487144 14.229782 11.100193 8.769539
Stream 5 13.933423 2.916529 19.453452 5.258843 16.706269 7.948711 8.982104 17.566729
Stream 6 17.084445 0.738683 11.503079 8.324812 23.483917 20.101834 9.207737 10.311292
Min Qi 9.400003 0.738683 11.503079 3.001843 15.593335 5.403771 8.870516 8.769539
Max Qi 17.084445 3.793587 19.453452 8.324812 23.483917 20.101834 11.181661 21.001623
Avg Qi 12.626783 2.774414 15.090501 5.579222 17.455334 10.141926 9.961123 13.620470
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 41.997798 2.727870 21.651730 25.704209 293.103984 3.171437 2.886688 5.298823
Stream 1 29.662265 22.788618 12.979253 7.121358 62.774323 22.132581 22.616793 21.625334
Stream 2 28.041750 22.481172 19.262140 5.790272 58.105179 16.809177 32.813330 12.692499
Stream 3 32.534297 15.460256 12.038047 7.012926 59.413740 18.540284 25.968635 16.716208
Stream 4 28.759993 15.123651 21.734471 6.920480 63.119744 12.848884 21.372432 11.662102
Stream 5 18.315308 21.781800 26.141212 8.230858 60.985590 22.369824 27.098660 25.283066
Stream 6 31.455961 27.078707 12.954580 11.081669 72.483462 12.376376 22.129120 11.439147
Min Qi 18.315308 15.123651 12.038047 5.790272 58.105179 12.376376 21.372432 11.439147
Max Qi 32.534297 27.078707 26.141212 11.081669 72.483462 22.369824 32.813330 25.283066
Avg Qi 28.128262 20.785701 17.518284 7.692927 62.813673 17.512854 25.333162 16.569726
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 7.793403 81.545934 41.648484 4.638731 25.003179 0.536267 206.980380 2.501589
Stream 1 27.058060 3.894254 8.664394 25.315007 11.921265 3.561859 22.936601 13.235777
Stream 2 25.718500 6.140657 8.856586 14.761290 11.870351 7.728217 13.882613 29.328859
Stream 3 15.896774 8.631035 15.742406 20.621604 13.370582 5.536313 14.677463 14.772753
Stream 4 22.458327 5.319241 11.973431 22.344017 11.534642 2.402683 24.214115 16.236299
Stream 5 13.407745 5.413278 8.800650 18.055743 17.528827 4.173171 15.927165 21.636801
Stream 6 8.069721 5.531066 13.233927 21.321389 7.622026 12.064182 11.457848 12.342336
Min Qi 8.069721 3.894254 8.664394 14.761290 7.622026 2.402683 11.457848 12.342336
Max Qi 27.058060 8.631035 15.742406 25.315007 17.528827 12.064182 24.214115 29.328859
Avg Qi 18.768188 5.821588 11.211899 20.403175 12.307949 5.911071 17.182634 17.925471

300G run 2

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 423,431.8
Virt-H Throughput 387,248.6
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
404,936.3
Measurement Interval in
Throughput Test (Ts)
368.236000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:28:42 09/25/2014 19:29:58 0:01:16
Stream 1 09/25/2014 19:30:00 09/25/2014 19:36:04 0:06:04
Stream 2 09/25/2014 19:30:00 09/25/2014 19:36:00 0:06:00
Stream 3 09/25/2014 19:30:00 09/25/2014 19:36:06 0:06:06
Stream 4 09/25/2014 19:30:00 09/25/2014 19:36:07 0:06:07
Stream 5 09/25/2014 19:30:00 09/25/2014 19:35:53 0:05:53
Stream 6 09/25/2014 19:30:00 09/25/2014 19:36:08 0:06:08
Refresh 0 09/25/2014 19:28:41 09/25/2014 19:28:46 0:00:05
09/25/2014 19:29:58 09/25/2014 19:30:00 0:00:02
Refresh 1 09/25/2014 19:32:23 09/25/2014 19:32:55 0:00:32
Refresh 2 09/25/2014 19:30:00 09/25/2014 19:30:31 0:00:31
Refresh 3 09/25/2014 19:30:31 09/25/2014 19:31:00 0:00:29
Refresh 4 09/25/2014 19:31:01 09/25/2014 19:31:23 0:00:22
Refresh 5 09/25/2014 19:31:23 09/25/2014 19:31:54 0:00:31
Refresh 6 09/25/2014 19:31:55 09/25/2014 19:32:23 0:00:28

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 4.197427 1.011516 2.535959 0.858781 2.857279 1.293530 2.682266 2.260502
Stream 1 15.467757 3.517499 13.820864 4.157259 13.141556 10.902710 16.899687 8.986535
Stream 2 15.639991 6.026485 13.521624 3.918031 17.336458 1.975310 9.718194 15.165247
Stream 3 14.891929 4.481383 15.322621 5.272911 15.266543 6.771253 13.430646 20.171084
Stream 4 14.560526 2.464157 11.567112 5.526629 20.531540 5.225971 16.288606 17.209475
Stream 5 10.390577 3.549165 9.598328 8.783847 17.351211 6.308214 12.606512 13.035716
Stream 6 16.275922 4.086475 14.109963 4.385887 10.174709 6.703266 8.936217 16.798526
Min Qi 10.390577 2.464157 9.598328 3.918031 10.174709 1.975310 8.936217 8.986535
Max Qi 16.275922 6.026485 15.322621 8.783847 20.531540 10.902710 16.899687 20.171084
Avg Qi 14.537784 4.020861 12.990085 5.340761 15.633670 6.314454 12.979977 15.227764
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 8.300092 2.598145 5.168418 1.619399 11.958836 3.191672 3.097822 2.497410
Stream 1 26.412829 17.354745 12.942454 8.169447 58.600101 15.227942 32.985324 13.914978
Stream 2 34.523245 17.635531 15.193748 8.435375 62.442800 16.276300 26.533303 12.414575
Stream 3 25.334301 18.595422 11.663933 10.029387 63.664992 20.378320 24.760768 15.710589
Stream 4 36.971957 15.645673 14.672851 13.196301 58.214728 17.375053 26.581101 11.624989
Stream 5 30.891797 12.993365 14.089049 10.515091 65.232712 20.807026 26.920526 11.362095
Stream 6 38.143281 21.106772 15.152299 18.845766 66.240343 12.295624 22.510610 18.081103
Min Qi 25.334301 12.993365 11.663933 8.169447 58.214728 12.295624 22.510610 11.362095
Max Qi 38.143281 21.106772 15.193748 18.845766 66.240343 20.807026 32.985324 18.081103
Avg Qi 32.046235 17.221918 13.952389 11.531894 62.399279 17.060044 26.715272 13.851388
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 4.016212 1.603004 1.836489 3.542383 3.901876 0.515102 4.759612 2.358873
Stream 1 22.162387 10.067834 15.772705 22.091355 12.974776 8.354196 19.342171 12.771250
Stream 2 25.647926 4.263008 11.590737 19.179326 17.899770 4.137031 15.720245 14.719776
Stream 3 14.511279 7.484608 20.735250 13.041037 17.139046 6.014141 16.234122 13.454647
Stream 4 19.297494 10.110707 10.907458 19.649066 15.206251 3.423503 11.268082 11.852223
Stream 5 17.445165 5.582309 15.266324 19.788382 14.245770 2.810949 16.601461 14.019717
Stream 6 25.115339 6.896503 11.661563 21.900028 5.520025 3.093050 15.436258 13.353446
Min Qi 14.511279 4.263008 10.907458 13.041037 5.520025 2.810949 11.268082 11.852223
Max Qi 25.647926 10.110707 20.735250 22.091355 17.899770 8.354196 19.342171 14.719776
Avg Qi 20.696598 7.400828 14.322339 19.274866 13.830940 4.638812 15.767057 13.361843

300G run 3:

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Total Data Storage/Database Size 258,888M
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 417,672.0
Virt-H Throughput 389,719.5
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
403,453.7
Measurement Interval in
Throughput Test (Ts)
365.902000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:36:11 09/25/2014 19:37:29 0:01:18
Stream 1 09/25/2014 19:37:32 09/25/2014 19:43:13 0:05:41
Stream 2 09/25/2014 19:37:32 09/25/2014 19:43:31 0:05:59
Stream 3 09/25/2014 19:37:32 09/25/2014 19:43:37 0:06:05
Stream 4 09/25/2014 19:37:32 09/25/2014 19:43:33 0:06:01
Stream 5 09/25/2014 19:37:32 09/25/2014 19:43:32 0:06:00
Stream 6 09/25/2014 19:37:32 09/25/2014 19:43:37 0:06:05
Refresh 0 09/25/2014 19:36:12 09/25/2014 19:36:16 0:00:04
09/25/2014 19:37:29 09/25/2014 19:37:31 0:00:02
Refresh 1 09/25/2014 19:40:02 09/25/2014 19:40:33 0:00:31
Refresh 2 09/25/2014 19:37:31 09/25/2014 19:38:01 0:00:30
Refresh 3 09/25/2014 19:38:01 09/25/2014 19:38:30 0:00:29
Refresh 4 09/25/2014 19:38:30 09/25/2014 19:38:58 0:00:28
Refresh 5 09/25/2014 19:38:58 09/25/2014 19:39:27 0:00:29
Refresh 6 09/25/2014 19:39:27 09/25/2014 19:40:01 0:00:34

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 4.305006 1.083442 2.502758 0.845763 2.840824 1.346166 2.659511 2.233550
Stream 1 11.513360 3.732513 14.530428 3.819517 14.821291 7.561547 10.435082 8.984230
Stream 2 13.486433 3.373689 9.620363 3.914320 16.857542 5.837487 10.695443 17.901191
Stream 3 11.015942 1.780220 4.830412 9.073543 15.587709 9.661989 12.374931 15.262485
Stream 4 13.600461 0.820899 12.254226 7.799415 19.860761 13.145017 14.404345 11.807583
Stream 5 13.358000 3.885118 11.099935 4.845043 18.286721 6.424272 9.735255 15.041608
Stream 6 13.588873 3.789631 13.503399 5.130389 13.104065 3.517076 14.929079 19.831639
Min Qi 11.015942 0.820899 4.830412 3.819517 13.104065 3.517076 9.735255 8.984230
Max Qi 13.600461 3.885118 14.530428 9.073543 19.860761 13.145017 14.929079 19.831639
Avg Qi 12.760511 2.897012 10.973127 5.763705 16.419681 7.691231 12.095689 14.804789
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 8.553183 3.215484 4.652364 1.620089 11.936052 2.916132 3.219969 2.374276
Stream 1 29.441108 20.348266 9.994556 14.965432 60.537168 13.302875 30.159402 10.277570
Stream 2 41.799347 18.197400 16.773638 6.510347 67.461446 20.362328 0.109929 9.908769
Stream 3 24.306937 20.555376 17.140758 16.715188 61.724168 22.469230 27.967206 13.434167
Stream 4 34.820796 11.795664 18.015120 7.176057 63.134711 11.427374 23.959842 16.759246
Stream 5 23.139366 12.655317 13.152401 7.258740 64.273225 22.854106 28.803059 12.832364
Stream 6 27.955059 24.633526 11.046285 5.995041 74.965966 15.636579 22.803890 13.221303
Min Qi 23.139366 11.795664 9.994556 5.995041 60.537168 11.427374 0.109929 9.908769
Max Qi 41.799347 24.633526 18.015120 16.715188 74.965966 22.854106 30.159402 16.759246
Avg Qi 30.243769 18.030925 14.353793 9.770134 65.349447 17.675415 22.300555 12.738903
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 4.298092 1.702071 1.894548 4.118591 3.922889 0.491145 4.519734 2.347913
Stream 1 16.432222 6.908918 17.749058 18.756674 11.148628 5.464975 18.300673 12.972871
Stream 2 20.588544 4.387662 14.527229 23.844364 15.500462 15.543458 13.666574 15.240662
Stream 3 14.008049 6.222633 12.833421 22.811602 16.013232 9.449069 16.486111 12.974515
Stream 4 16.964699 8.106044 11.207675 22.483826 17.354675 4.641183 14.583941 13.679087
Stream 5 25.243144 7.359437 16.986615 19.855391 17.183725 5.750937 14.759597 13.052316
Stream 6 12.986721 10.160993 17.496662 19.267026 17.300224 4.955930 19.267721 15.421241
Min Qi 12.986721 4.387662 11.207675 18.756674 11.148628 4.641183 13.666574 12.972871
Max Qi 25.243144 10.160993 17.749058 23.844364 17.354675 15.543458 19.267721 15.421241
Avg Qi 17.703896 7.190948 15.133443 21.169814 15.750158 7.634259 16.177436 13.890115

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/26/2014 17:07 GMT Modified: 09/30/2014 16:37 GMT
In Hoc Signo Vinces (part 18 of n): Cluster Dynamics [ Orri Erling ]

This article is about how scale-out differs from single-server. This shows large effects of parameters whose very existence most would not anticipate, and some low level metrics for assessing these. The moral of the story is that this is the stuff which makes the difference between merely surviving scale-out and winning with it. The developer and DBA would not normally know about this; thus these things fall into the category of adaptive self-configuration expected from the DBMS. But since this series is about what makes performance, I will discuss the dynamics such as they are and how to play these.

We take the prototypical cross partition join in Q13: Make a hash table of all customers, partitioned by c_custkey. This is independently done with full parallelism in each partition. Scan the orders, get the customer (in a different partition), and flag the customers that had at least one order. Then, to get the customers with no orders, return the customers that were not flagged in the previous pass.

The single-server time in part 12 was 7.8 and 6.0 with a single user. We consider the better of the times. The difference is due to allocating memory on the first go; on the second go the memory is already in reserve.

With default settings, we get 4595 ms (microseconds), with per node resource utilization at:


Cluster 4 nodes, 4 s. 112405 m/s 742602 KB/s  2749% cpu 0%  read 4% clw threads 1r 0w 0i buffers 8577766 287874 d 0 w 0 pfs
cl 1: 27867 m/s 185654 KB/s  733% cpu 0%  read 4% clw threads 1r 0w 0i buffers 2144242 71757 d 0 w 0 pfs
cl 2: 28149 m/s 185372 KB/s  672% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144640 71903 d 0 w 0 pfs
cl 3: 28220 m/s 185621 KB/s  675% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144454 71962 d 0 w 0 pfs
cl 4: 28150 m/s 185837 KB/s  667% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144430 72252 d 0 w 0 pfs

 

The top line is the summary; the lines below are per-process. The m/s is messages-per-second; KB/s is interconnect traffic per second; clw % is idle time spent waiting for a reply from another process. The cluster is set up with 4 processes across 2 machines, each with 2 NUMA nodes. Each process has affinity to the NUMA node, so local memory only. The time is reasonable in light of the overall CPU of 2700%. The maximum would be 4800% with all threads of all cores busy all the time.

The catch here is that we do not have a steady half-platform utilization all the time, but full platform peaks followed by synchronization barriers with very low utilization. So, we set the batch size differently:


cl_exec ('__dbf_set (''cl_dfg_batch_bytes'', 50000000)');

 

This means that we set, on each process, the cl_dfg_batch_bytes to 50M from a default of 10M. The effect is that each scan of orders, one thread per slice, 48 slices total, will produce 50MB worth of o_custkeys to be sent to the other partition for getting the customer. After each 50M, the thread stops and will produce the next batch when all are done and a global continue message is sent by the coordinator.

The time is now 3173 ms with:


Cluster 4 nodes, 3 s. 158220 m/s 1054944 KB/s  3676% cpu 0%  read 1% clw threads 1r 0w 0i buffers 8577766 287874 d 0 w 0 pfs
cl 1: 39594 m/s 263962 KB/s  947% cpu 0%  read 1% clw threads 1r 0w 0i buffers 2144242 71757 d 0 w 0 pfs
cl 2: 39531 m/s 263476 KB/s  894% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144640 71903 d 0 w 0 pfs
cl 3: 39523 m/s 263684 KB/s  933% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144454 71962 d 0 w 0 pfs
cl 4: 39535 m/s 263586 KB/s  900% cpu 0%  read 0% clw threads 0r 0w 0i buffers 2144430 72252 d 0 w 0 pfs

 

The platform utilization is better as we see. The throughput is nearly double that of the single-server, which is pretty good for a communication-heavy query.

This was done with a vector size of 10K. In other words, each partition gets 10K o_custkeys and splits these 48 ways to go to every recipient. 1/4 are in the same process, 1/4 in a different process on the same machine, and 2/4 on a different machine. The recipient gets messages with an average of 208 o_custkey values, puts them back together in batches of 10K, and passes these to the hash join with customer.

We try different vector sizes, such as 100K:


cl_exec ('__dbf_set (''dc_batch_sz'', 100000)');

 

There are two metrics of interest here: The write block time, and the scheduling overhead. The write block time is microseconds, which increases whenever a thread must wait before it can write to a connection. The scheduling overhead is cumulative clocks spent by threads while waiting for a critical section that deals with dispatching messages to consumer threads. Long messages make blocking; short messages make frequent scheduling decisions.


SELECT cl_sys_stat ('local_cll_clk', clr=>1), 
       cl_sys_stat ('write_block_usec', clr=>1)
;

 

cl_sys_stat gets the counters from all processes and returns the sum. clr=>1 means that the counter is cleared after read.

We do Q13 with vector sizes of 10, 100, and 1000K.

Vector size msec mtx wblock
10K 3297 10,829,910,329 0
100K 3150 1,663,238,367 59,132
1000K 3876 414,631,129 4,578,003

So, 100K seems to strike the best balance between scheduling and blocking on write.

The times are measured after several samples with each setting. The times stabilize after a few runs, as the appropriate size memory blocks are in reserve. Calling mmap to allocate these on the first run with each size has a very high penalty, e.g., 60s for the first run with 1M vector size. We note that blocking on write is really bad even though 1/3 of the time there is no network and 2/3 of the time there is a fast network (QDR IB) with no other load. Further, the affinities are set so that the thread responsible for incoming messages is always on core. Result variability on consecutive runs is under 5%, which is similar to single-server behavior.

It would seem that a mutex, as bad as it is, is still better than a distributed cause for going off core (blocking on write). The latency for continuing a thread thus blocked is of course higher than the latency for continuing one that is waiting for a mutex.

We note that a cluster with more machines can take a longer vector size because a vector spreads out to more recipients. The key seems to be to set the message size so that blocking on write is not common. This is a possible adaptive execution feature. We have seen no particular benefit from SDP (Sockets Direct Protocol) and its zero copy. This is a TCP replacement that comes with the InfiniBand drivers.

We will next look at replication/partitioning tradeoffs for hash joins. Then we can look at full runs.

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/26/2014 17:02 GMT Modified: 09/30/2014 16:34 GMT
In Hoc Signo Vinces (part 17 of n): 100G and 300G Runs on Dual Xeon E5 2650v2 [ Orri Erling ]

This is an update presenting sample results on a newer platform for a single-server configuration. This is to verify that performance scales with the addition of cores and clock speed. Further, we note that the jump from 100G to 300G changes very little about the score. 3x larger takes approximately 3x longer, as long as things are in memory.

The platform is one node of the CWI cluster which was also used for the 500Gt RDF experiments reported on this blog. The specification is dual Xeon E5 2650v2 (8 core, 16 thread, 2.6 GHz) with 256 GB RAM. The disk setup is a RAID-0 of three 2 TB rotating disks.

For the 100G, we go from 240 to 395, which is about 1.64x. The new platform has 16 vs 12 cores and a clock of 2.6 as opposed to 2.3. This makes a multiplier of 1.5. The rest of the acceleration is probably attributable to faster memory clock. Anyway, the point of more speed from larger platform is made.

The top level scores per run are as follows; the numerical quantities summaries are appended.

100G

Run Power Throughput Composite
Run 1 391,000.1 401,029.4 395,983.0
Run 2 388,746.2 404,189.3 396,392.6

300G

Run Power Throughput Composite
Run 1 61,988.7 384,883.7 154,461.6
Run 2 423,431.8 387,248.6 404,936.3
Run 3 417,672.0 389,719.5 403,453.7

The interested may reproduce the results using the feature/analytics branch of the v7fasttrack git repository on GitHub as described in Part 13.

For the 300G runs, we note a much longer load time; see below, as this is seriously IO bound.

The first power test at 300G is a non-starter, even though this comes right after bulk load. Still, the data is not in working set and getting it from disk is simply an automatic disqualification, unless maybe one had 300 separate disks. This happens in TPC benchmarks, but not very often in the field. Looking at the first power run, the first queries take the longest, but by the time the power run starts, the working set is there. By an artifact of the metric (use of geometric mean for the power test), long queries are penalized less there than in the throughput run.

So, we run 3 executions instead of the prescribed 2, to have 2 executions from warm state.

To do 300G well in 256 GB of RAM, one needs either to use several SSDs, or to increase compression and keep all in memory, so no secondary storage at all. In order to keep all in memory, one could have stream-compression on string columns. Stream-compressing strings (e.g., o_comment, l_comment) does not pay if one is already in memory, but if stream-compressing strings eliminates going to secondary storage, then the win is sure.

As before, all caveats apply; the results are unaudited and for information only. Therefore we do not use the official metric name.

100G Run 1

Virt-H Executive Summary

Report Date September 15, 2014
Database Scale Factor 100
Start of Database Load 09/15/2014 07:04:08
End of Database Load 09/15/2014 07:15:58
Database Load Time 0:11:50
Query Streams for
Throughput Test
5
Virt-H Power 391,000.1
Virt-H Throughput 401,029.4
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
395,983.0
Measurement Interval in
Throughput Test (Ts)
98.846000 seconds

Duration of stream execution

Start Date/Time End Date/Time Duration
Stream 0 09/15/2014 13:13:01 09/15/2014 13:13:28 0:00:27
Stream 1 09/15/2014 13:13:29 09/15/2014 13:15:06 0:01:37
Stream 2 09/15/2014 13:13:29 09/15/2014 13:15:07 0:01:38
Stream 3 09/15/2014 13:13:29 09/15/2014 13:15:07 0:01:38
Stream 4 09/15/2014 13:13:29 09/15/2014 13:15:04 0:01:35
Stream 5 09/15/2014 13:13:29 09/15/2014 13:15:08 0:01:39
Refresh 0 09/15/2014 13:13:01 09/15/2014 13:13:03 0:00:02
09/15/2014 13:13:28 09/15/2014 13:13:29 0:00:01
Refresh 1 09/15/2014 13:14:10 09/15/2014 13:14:16 0:00:06
Refresh 2 09/15/2014 13:13:29 09/15/2014 13:13:42 0:00:13
Refresh 3 09/15/2014 13:13:42 09/15/2014 13:13:53 0:00:11
Refresh 4 09/15/2014 13:13:53 09/15/2014 13:14:02 0:00:09
Refresh 5 09/15/2014 13:14:02 09/15/2014 13:14:10 0:00:08

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 1.442477 0.304513 0.720263 0.351285 0.979414 0.479455 0.865992 0.875236
Stream 1 3.938133 0.920533 3.738724 2.769707 3.209728 1.339146 2.759384 3.626868
Stream 2 4.104738 0.952245 4.719658 0.865586 2.139267 0.850909 2.044402 2.600373
Stream 3 3.692119 1.024876 3.430172 1.579846 4.097845 1.859468 2.312921 6.238070
Stream 4 5.419537 0.531571 2.116176 1.256836 4.787617 2.117995 3.517466 3.982180
Stream 5 5.167029 0.746720 3.157557 1.255182 3.004802 2.131963 3.648316 2.835751
Min Qi 3.692119 0.531571 2.116176 0.865586 2.139267 0.850909 2.044402 2.600373
Max Qi 5.419537 1.024876 4.719658 2.769707 4.787617 2.131963 3.648316 6.238070
Avg Qi 4.464311 0.835189 3.432457 1.545431 3.447852 1.659896 2.856498 3.856648
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 2.606044 1.117063 1.847930 0.618534 4.327600 1.110908 0.995289 0.975910
Stream 1 7.463593 4.686463 4.549733 4.168129 15.759178 5.247666 4.495030 4.075198
Stream 2 9.398552 5.170904 3.934405 1.880683 19.968787 3.767992 6.965337 3.849845
Stream 3 7.581069 4.109905 4.301159 2.123634 17.683200 5.383603 4.376887 2.854777
Stream 4 9.927887 6.913209 3.351489 2.802724 16.985827 3.925148 4.691474 4.080586
Stream 5 7.035080 3.921425 6.844778 2.899238 14.839509 4.986742 6.629664 4.089547
Min Qi 7.035080 3.921425 3.351489 1.880683 14.839509 3.767992 4.376887 2.854777
Max Qi 9.927887 6.913209 6.844778 4.168129 19.968787 5.383603 6.965337 4.089547
Avg Qi 8.281236 4.960381 4.596313 2.774882 17.047300 4.662230 5.431678 3.789991
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 1.215956 0.745257 0.699801 1.281834 1.291110 0.518425 1.827192 1.014431
Stream 1 5.779854 2.383264 2.396793 6.130511 5.002700 1.968425 4.172437 2.427047
Stream 2 7.828176 1.833416 3.175649 4.785709 5.385834 1.403290 6.383005 6.366525
Stream 3 5.880139 1.797383 3.258024 5.601364 6.373216 1.977848 5.235542 6.385010
Stream 4 3.989621 1.252891 2.478303 4.678629 3.212176 2.740586 5.037995 3.911379
Stream 5 5.030440 2.010988 4.188428 6.221990 5.418788 2.187718 3.589915 3.517380
Min Qi 3.989621 1.252891 2.396793 4.678629 3.212176 1.403290 3.589915 2.427047
Max Qi 7.828176 2.383264 4.188428 6.221990 6.373216 2.740586 6.383005 6.385010
Avg Qi 5.701646 1.855588 3.099439 5.483641 5.078543 2.055573 4.883779 4.521468

100G Run 2

Virt-H Executive Summary

Report Date September 15, 2014
Database Scale Factor 100
Total Data Storage/Database Size 87,312M
Start of Database Load 09/15/2014 07:04:08
End of Database Load 09/15/2014 07:15:58
Database Load Time 0:11:50
Query Streams for
Throughput Test
5
Virt-H Power 388,746.2
Virt-H Throughput 404,189.3
Virt-H Composite
Query-per-Hour Metric
(Qph@100GB)
396,392.6
Measurement Interval in
Throughput Test (Ts)
98.074000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/15/2014 13:15:11 09/15/2014 13:15:38 0:00:27
Stream 1 09/15/2014 13:15:39 09/15/2014 13:17:13 0:01:34
Stream 2 09/15/2014 13:15:39 09/15/2014 13:17:16 0:01:37
Stream 3 09/15/2014 13:15:39 09/15/2014 13:17:15 0:01:36
Stream 4 09/15/2014 13:15:39 09/15/2014 13:17:17 0:01:38
Stream 5 09/15/2014 13:15:39 09/15/2014 13:17:15 0:01:36
Refresh 0 09/15/2014 13:15:11 09/15/2014 13:15:12 0:00:01
09/15/2014 13:15:38 09/15/2014 13:15:39 0:00:01
Refresh 1 09/15/2014 13:16:13 09/15/2014 13:16:20 0:00:07
Refresh 2 09/15/2014 13:15:39 09/15/2014 13:15:47 0:00:08
Refresh 3 09/15/2014 13:15:47 09/15/2014 13:15:56 0:00:09
Refresh 4 09/15/2014 13:15:56 09/15/2014 13:16:03 0:00:07
Refresh 5 09/15/2014 13:16:03 09/15/2014 13:16:12 0:00:09

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 1.467681 0.277665 0.766102 0.365185 0.941206 0.549381 0.938998 0.803514
Stream 1 3.883169 1.488521 3.366920 1.627478 3.632321 2.065565 2.911138 2.444544
Stream 2 3.294589 1.138066 3.260775 1.899615 5.367725 1.820374 3.655119 2.186642
Stream 3 3.797641 0.995877 3.239690 2.483035 2.737690 1.505998 4.058083 4.268644
Stream 4 4.099187 0.402685 4.704959 1.469825 5.367910 2.783018 2.706164 2.551061
Stream 5 3.651273 1.598314 2.051899 1.283754 4.711897 1.519763 2.851300 2.484093
Min Qi 3.294589 0.402685 2.051899 1.283754 2.737690 1.505998 2.706164 2.186642
Max Qi 4.099187 1.598314 4.704959 2.483035 5.367910 2.783018 4.058083 4.268644
Avg Qi 3.745172 1.124693 3.324849 1.752741 4.363509 1.938944 3.236361 2.786997
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 2.734812 1.115539 1.679910 0.633239 4.391739 1.130082 1.137284 0.919646
Stream 1 9.271071 5.664855 3.377869 2.148228 16.046021 2.935643 4.897009 2.891040
Stream 2 10.272523 4.578427 4.086788 2.312762 16.295728 2.714776 6.393897 2.414951
Stream 3 7.095213 4.544636 4.073433 2.710320 18.789088 3.903873 5.471600 2.994184
Stream 4 7.567924 3.691088 3.951049 2.207944 18.189014 4.985841 6.568935 3.965322
Stream 5 8.173577 4.959777 4.736593 3.507469 17.106990 5.405699 7.357104 3.125788
Min Qi 7.095213 3.691088 3.377869 2.148228 16.046021 2.714776 4.897009 2.414951
Max Qi 10.272523 5.664855 4.736593 3.507469 18.789088 5.405699 7.357104 3.965322
Avg Qi 8.476062 4.687757 4.045146 2.577345 17.285368 3.989166 6.137709 3.078257
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 1.206347 0.792013 0.699476 1.349182 1.505387 0.543947 1.549135 0.824344
Stream 1 5.135036 1.873195 4.978155 5.988226 4.705365 1.211049 4.175947 3.579242
Stream 2 7.656125 2.229819 2.805272 6.629781 4.138014 1.423334 5.165700 3.197300
Stream 3 6.385983 2.086301 3.450305 3.292353 5.503905 2.302992 4.860041 3.865383
Stream 4 6.514967 2.876895 3.481100 1.629007 5.715903 2.121692 3.681208 3.347289
Stream 5 4.100205 2.400816 2.142291 4.710677 5.765320 1.616445 6.095817 3.007436
Min Qi 4.100205 1.873195 2.142291 1.629007 4.138014 1.211049 3.681208 3.007436
Max Qi 7.656125 2.876895 4.978155 6.629781 5.765320 2.302992 6.095817 3.865383
Avg Qi 5.958463 2.293405 3.371425 4.450009 5.165701 1.735102 4.795743 3.399330

300G Run 1

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 61,988.7
Virt-H Throughput 384,883.7
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
154,461.6
Measurement Interval in
Throughput Test (Ts)
370.498000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:00:29 09/25/2014 19:22:25 0:21:56
Stream 1 09/25/2014 19:22:27 09/25/2014 19:28:23 0:05:56
Stream 2 09/25/2014 19:22:27 09/25/2014 19:28:23 0:05:56
Stream 3 09/25/2014 19:22:27 09/25/2014 19:28:26 0:05:59
Stream 4 09/25/2014 19:22:27 09/25/2014 19:28:13 0:05:46
Stream 5 09/25/2014 19:22:27 09/25/2014 19:28:38 0:06:11
Stream 6 09/25/2014 19:22:27 09/25/2014 19:28:38 0:06:11
Refresh 0 09/25/2014 19:00:29 09/25/2014 19:03:56 0:03:27
09/25/2014 19:22:25 09/25/2014 19:22:27 0:00:02
Refresh 1 09/25/2014 19:25:22 09/25/2014 19:25:58 0:00:36
Refresh 2 09/25/2014 19:22:27 09/25/2014 19:23:11 0:00:44
Refresh 3 09/25/2014 19:23:10 09/25/2014 19:23:40 0:00:30
Refresh 4 09/25/2014 19:23:40 09/25/2014 19:24:21 0:00:41
Refresh 5 09/25/2014 19:24:21 09/25/2014 19:24:58 0:00:37
Refresh 6 09/25/2014 19:24:59 09/25/2014 19:25:22 0:00:23

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 183.735463 95.826361 79.826802 87.603164 47.099641 1.301704 2.606488 52.667426
Stream 1 9.400003 1.983777 15.839250 3.001843 15.593335 6.067716 8.870516 11.679706
Stream 2 12.634711 3.472203 13.683075 8.057952 16.500741 5.403771 11.181661 12.393932
Stream 3 10.807287 3.793587 15.844244 3.214977 15.960600 7.099744 10.424530 21.001623
Stream 4 11.900829 3.741707 14.219904 5.616907 16.487144 14.229782 11.100193 8.769539
Stream 5 13.933423 2.916529 19.453452 5.258843 16.706269 7.948711 8.982104 17.566729
Stream 6 17.084445 0.738683 11.503079 8.324812 23.483917 20.101834 9.207737 10.311292
Min Qi 9.400003 0.738683 11.503079 3.001843 15.593335 5.403771 8.870516 8.769539
Max Qi 17.084445 3.793587 19.453452 8.324812 23.483917 20.101834 11.181661 21.001623
Avg Qi 12.626783 2.774414 15.090501 5.579222 17.455334 10.141926 9.961123 13.620470
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 41.997798 2.727870 21.651730 25.704209 293.103984 3.171437 2.886688 5.298823
Stream 1 29.662265 22.788618 12.979253 7.121358 62.774323 22.132581 22.616793 21.625334
Stream 2 28.041750 22.481172 19.262140 5.790272 58.105179 16.809177 32.813330 12.692499
Stream 3 32.534297 15.460256 12.038047 7.012926 59.413740 18.540284 25.968635 16.716208
Stream 4 28.759993 15.123651 21.734471 6.920480 63.119744 12.848884 21.372432 11.662102
Stream 5 18.315308 21.781800 26.141212 8.230858 60.985590 22.369824 27.098660 25.283066
Stream 6 31.455961 27.078707 12.954580 11.081669 72.483462 12.376376 22.129120 11.439147
Min Qi 18.315308 15.123651 12.038047 5.790272 58.105179 12.376376 21.372432 11.439147
Max Qi 32.534297 27.078707 26.141212 11.081669 72.483462 22.369824 32.813330 25.283066
Avg Qi 28.128262 20.785701 17.518284 7.692927 62.813673 17.512854 25.333162 16.569726
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 7.793403 81.545934 41.648484 4.638731 25.003179 0.536267 206.980380 2.501589
Stream 1 27.058060 3.894254 8.664394 25.315007 11.921265 3.561859 22.936601 13.235777
Stream 2 25.718500 6.140657 8.856586 14.761290 11.870351 7.728217 13.882613 29.328859
Stream 3 15.896774 8.631035 15.742406 20.621604 13.370582 5.536313 14.677463 14.772753
Stream 4 22.458327 5.319241 11.973431 22.344017 11.534642 2.402683 24.214115 16.236299
Stream 5 13.407745 5.413278 8.800650 18.055743 17.528827 4.173171 15.927165 21.636801
Stream 6 8.069721 5.531066 13.233927 21.321389 7.622026 12.064182 11.457848 12.342336
Min Qi 8.069721 3.894254 8.664394 14.761290 7.622026 2.402683 11.457848 12.342336
Max Qi 27.058060 8.631035 15.742406 25.315007 17.528827 12.064182 24.214115 29.328859
Avg Qi 18.768188 5.821588 11.211899 20.403175 12.307949 5.911071 17.182634 17.925471

300G run 2

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 423,431.8
Virt-H Throughput 387,248.6
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
404,936.3
Measurement Interval in
Throughput Test (Ts)
368.236000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:28:42 09/25/2014 19:29:58 0:01:16
Stream 1 09/25/2014 19:30:00 09/25/2014 19:36:04 0:06:04
Stream 2 09/25/2014 19:30:00 09/25/2014 19:36:00 0:06:00
Stream 3 09/25/2014 19:30:00 09/25/2014 19:36:06 0:06:06
Stream 4 09/25/2014 19:30:00 09/25/2014 19:36:07 0:06:07
Stream 5 09/25/2014 19:30:00 09/25/2014 19:35:53 0:05:53
Stream 6 09/25/2014 19:30:00 09/25/2014 19:36:08 0:06:08
Refresh 0 09/25/2014 19:28:41 09/25/2014 19:28:46 0:00:05
09/25/2014 19:29:58 09/25/2014 19:30:00 0:00:02
Refresh 1 09/25/2014 19:32:23 09/25/2014 19:32:55 0:00:32
Refresh 2 09/25/2014 19:30:00 09/25/2014 19:30:31 0:00:31
Refresh 3 09/25/2014 19:30:31 09/25/2014 19:31:00 0:00:29
Refresh 4 09/25/2014 19:31:01 09/25/2014 19:31:23 0:00:22
Refresh 5 09/25/2014 19:31:23 09/25/2014 19:31:54 0:00:31
Refresh 6 09/25/2014 19:31:55 09/25/2014 19:32:23 0:00:28

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 4.197427 1.011516 2.535959 0.858781 2.857279 1.293530 2.682266 2.260502
Stream 1 15.467757 3.517499 13.820864 4.157259 13.141556 10.902710 16.899687 8.986535
Stream 2 15.639991 6.026485 13.521624 3.918031 17.336458 1.975310 9.718194 15.165247
Stream 3 14.891929 4.481383 15.322621 5.272911 15.266543 6.771253 13.430646 20.171084
Stream 4 14.560526 2.464157 11.567112 5.526629 20.531540 5.225971 16.288606 17.209475
Stream 5 10.390577 3.549165 9.598328 8.783847 17.351211 6.308214 12.606512 13.035716
Stream 6 16.275922 4.086475 14.109963 4.385887 10.174709 6.703266 8.936217 16.798526
Min Qi 10.390577 2.464157 9.598328 3.918031 10.174709 1.975310 8.936217 8.986535
Max Qi 16.275922 6.026485 15.322621 8.783847 20.531540 10.902710 16.899687 20.171084
Avg Qi 14.537784 4.020861 12.990085 5.340761 15.633670 6.314454 12.979977 15.227764
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 8.300092 2.598145 5.168418 1.619399 11.958836 3.191672 3.097822 2.497410
Stream 1 26.412829 17.354745 12.942454 8.169447 58.600101 15.227942 32.985324 13.914978
Stream 2 34.523245 17.635531 15.193748 8.435375 62.442800 16.276300 26.533303 12.414575
Stream 3 25.334301 18.595422 11.663933 10.029387 63.664992 20.378320 24.760768 15.710589
Stream 4 36.971957 15.645673 14.672851 13.196301 58.214728 17.375053 26.581101 11.624989
Stream 5 30.891797 12.993365 14.089049 10.515091 65.232712 20.807026 26.920526 11.362095
Stream 6 38.143281 21.106772 15.152299 18.845766 66.240343 12.295624 22.510610 18.081103
Min Qi 25.334301 12.993365 11.663933 8.169447 58.214728 12.295624 22.510610 11.362095
Max Qi 38.143281 21.106772 15.193748 18.845766 66.240343 20.807026 32.985324 18.081103
Avg Qi 32.046235 17.221918 13.952389 11.531894 62.399279 17.060044 26.715272 13.851388
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 4.016212 1.603004 1.836489 3.542383 3.901876 0.515102 4.759612 2.358873
Stream 1 22.162387 10.067834 15.772705 22.091355 12.974776 8.354196 19.342171 12.771250
Stream 2 25.647926 4.263008 11.590737 19.179326 17.899770 4.137031 15.720245 14.719776
Stream 3 14.511279 7.484608 20.735250 13.041037 17.139046 6.014141 16.234122 13.454647
Stream 4 19.297494 10.110707 10.907458 19.649066 15.206251 3.423503 11.268082 11.852223
Stream 5 17.445165 5.582309 15.266324 19.788382 14.245770 2.810949 16.601461 14.019717
Stream 6 25.115339 6.896503 11.661563 21.900028 5.520025 3.093050 15.436258 13.353446
Min Qi 14.511279 4.263008 10.907458 13.041037 5.520025 2.810949 11.268082 11.852223
Max Qi 25.647926 10.110707 20.735250 22.091355 17.899770 8.354196 19.342171 14.719776
Avg Qi 20.696598 7.400828 14.322339 19.274866 13.830940 4.638812 15.767057 13.361843

300G run 3:

Virt-H Executive Summary

Report Date September 25, 2014
Database Scale Factor 300
Total Data Storage/Database Size 258,888M
Start of Database Load 09/25/2014 16:38:20
End of Database Load 09/25/2014 18:32:06
Database Load Time 1:53:46
Query Streams for
Throughput Test
6
Virt-H Power 417,672.0
Virt-H Throughput 389,719.5
Virt-H Composite
Query-per-Hour Metric
(Qph@300GB)
403,453.7
Measurement Interval in
Throughput Test (Ts)
365.902000 seconds

Duration of stream execution:

Start Date/Time End Date/Time Duration
Stream 0 09/25/2014 19:36:11 09/25/2014 19:37:29 0:01:18
Stream 1 09/25/2014 19:37:32 09/25/2014 19:43:13 0:05:41
Stream 2 09/25/2014 19:37:32 09/25/2014 19:43:31 0:05:59
Stream 3 09/25/2014 19:37:32 09/25/2014 19:43:37 0:06:05
Stream 4 09/25/2014 19:37:32 09/25/2014 19:43:33 0:06:01
Stream 5 09/25/2014 19:37:32 09/25/2014 19:43:32 0:06:00
Stream 6 09/25/2014 19:37:32 09/25/2014 19:43:37 0:06:05
Refresh 0 09/25/2014 19:36:12 09/25/2014 19:36:16 0:00:04
09/25/2014 19:37:29 09/25/2014 19:37:31 0:00:02
Refresh 1 09/25/2014 19:40:02 09/25/2014 19:40:33 0:00:31
Refresh 2 09/25/2014 19:37:31 09/25/2014 19:38:01 0:00:30
Refresh 3 09/25/2014 19:38:01 09/25/2014 19:38:30 0:00:29
Refresh 4 09/25/2014 19:38:30 09/25/2014 19:38:58 0:00:28
Refresh 5 09/25/2014 19:38:58 09/25/2014 19:39:27 0:00:29
Refresh 6 09/25/2014 19:39:27 09/25/2014 19:40:01 0:00:34

Numerical Quantities Summary Timing Intervals in Seconds:

Query Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Stream 0 4.305006 1.083442 2.502758 0.845763 2.840824 1.346166 2.659511 2.233550
Stream 1 11.513360 3.732513 14.530428 3.819517 14.821291 7.561547 10.435082 8.984230
Stream 2 13.486433 3.373689 9.620363 3.914320 16.857542 5.837487 10.695443 17.901191
Stream 3 11.015942 1.780220 4.830412 9.073543 15.587709 9.661989 12.374931 15.262485
Stream 4 13.600461 0.820899 12.254226 7.799415 19.860761 13.145017 14.404345 11.807583
Stream 5 13.358000 3.885118 11.099935 4.845043 18.286721 6.424272 9.735255 15.041608
Stream 6 13.588873 3.789631 13.503399 5.130389 13.104065 3.517076 14.929079 19.831639
Min Qi 11.015942 0.820899 4.830412 3.819517 13.104065 3.517076 9.735255 8.984230
Max Qi 13.600461 3.885118 14.530428 9.073543 19.860761 13.145017 14.929079 19.831639
Avg Qi 12.760511 2.897012 10.973127 5.763705 16.419681 7.691231 12.095689 14.804789
Query Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16
Stream 0 8.553183 3.215484 4.652364 1.620089 11.936052 2.916132 3.219969 2.374276
Stream 1 29.441108 20.348266 9.994556 14.965432 60.537168 13.302875 30.159402 10.277570
Stream 2 41.799347 18.197400 16.773638 6.510347 67.461446 20.362328 0.109929 9.908769
Stream 3 24.306937 20.555376 17.140758 16.715188 61.724168 22.469230 27.967206 13.434167
Stream 4 34.820796 11.795664 18.015120 7.176057 63.134711 11.427374 23.959842 16.759246
Stream 5 23.139366 12.655317 13.152401 7.258740 64.273225 22.854106 28.803059 12.832364
Stream 6 27.955059 24.633526 11.046285 5.995041 74.965966 15.636579 22.803890 13.221303
Min Qi 23.139366 11.795664 9.994556 5.995041 60.537168 11.427374 0.109929 9.908769
Max Qi 41.799347 24.633526 18.015120 16.715188 74.965966 22.854106 30.159402 16.759246
Avg Qi 30.243769 18.030925 14.353793 9.770134 65.349447 17.675415 22.300555 12.738903
Query Q17 Q18 Q19 Q20 Q21 Q22 RF1 RF2
Stream 0 4.298092 1.702071 1.894548 4.118591 3.922889 0.491145 4.519734 2.347913
Stream 1 16.432222 6.908918 17.749058 18.756674 11.148628 5.464975 18.300673 12.972871
Stream 2 20.588544 4.387662 14.527229 23.844364 15.500462 15.543458 13.666574 15.240662
Stream 3 14.008049 6.222633 12.833421 22.811602 16.013232 9.449069 16.486111 12.974515
Stream 4 16.964699 8.106044 11.207675 22.483826 17.354675 4.641183 14.583941 13.679087
Stream 5 25.243144 7.359437 16.986615 19.855391 17.183725 5.750937 14.759597 13.052316
Stream 6 12.986721 10.160993 17.496662 19.267026 17.300224 4.955930 19.267721 15.421241
Min Qi 12.986721 4.387662 11.207675 18.756674 11.148628 4.641183 13.666574 12.972871
Max Qi 25.243144 10.160993 17.749058 23.844364 17.354675 15.543458 19.267721 15.421241
Avg Qi 17.703896 7.190948 15.133443 21.169814 15.750158 7.634259 16.177436 13.890115

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/26/2014 17:02 GMT Modified: 09/30/2014 16:34 GMT
In Hoc Signo Vinces (part 16 of n): Introduction to Scale-Out [ Virtuso Data Space Bot ]

So far, we have analyzed TPC-H in a single-server, memory-only setting. We will now move to larger data and cluster implementations. In principle, TPC-H parallelizes well, so we should expect near-linear scalability; i.e., twice the gear runs twice as fast, or close enough.

In practice, things are not quite so simple. Larger data, particularly a different data-to-memory ratio, and the fact of having no shared memory, all play a role. There is also a network, so partitioned operations, which also existed in the single-server case, now have to send messages across machines, not across threads. For data loading and refreshes, there is generally no shared file system, so data distribution and parallelism have to be considered.

As an initial pass, we look at 100G and 1000G scales on the same test system as before. This is two machines, each with dual Xeon E5-2630, 192 GB RAM, 2 x 512 GB SSD, and QDR InfiniBand. We will also try other platforms, but if nothing else is said, this is the test system.

As of this writing, there is a working implementation, but it is not guaranteed to be optimal as yet. We will adjust it as we go through the workload. One outcome of the experiment will be a precise determination of the data-volume-to-RAM ratio that still gives good performance.

A priori, we know of the following things that complicate life with clusters:

  • Distributed memory — The working set must be in memory for a run to have a competitive score. A cluster can have a lot of memory, and the data is such that it partitions very evenly, so this appears at first not a problem. The difficulty comes with query memory: If each machine has 1/16th of the total RAM and a hash table would be 1/64th of the working set, on a single-server it is no problem just building the hash table. On a scale-out system, the hash table would be 1/4 of the working set if replicated on each node, which will not fit, especially if there are many such hash tables at the same time. Two main approaches exist: The hash table can be partitioned, but this will force the probe to go cross-partition, which takes time. The other possibility is to build the hash table many times, each time with a fraction of the data, and to run the probe side many times. Since hash tables often have Bloom filters, it is sometimes possible to replicate the Bloom filter and partition the hash table. One has also heard of hash tables that go to secondary storage, but should this happen, the race is already lost; so, we do not go there.

    We must evaluate different combinations of these techniques and have a cost model that accurately predicts the performance of each variant. Adding to realism is always safe but halfway difficult to do.

  • NUMA — Most servers are NUMA (non-uniform memory architecture), where each CPU socket has its own local memory. For single-server cases, we use all the memory for the process. Some implementations have special logic for memory affinity between threads. With scale-out there is the choice of having a server process per-NUMA-node or per-physical-machine. If per-NUMA-node, we are guaranteed only local memory accesses. This is a tradeoff to be evaluated.

  • Network and Scheduling — Execution on a cluster is always vectored, for the simple reason that sending single-tuple messages is unfeasible in terms of performance. With an otherwise vectored architecture, the message batching required on a cluster comes naturally. However, the larger the cluster, the more partitions there are, which rapidly gets into shorter messages. Increasing the vector size is possible and messages become longer, but indefinite increase in vector size has drawbacks for cache locality and takes memory. To run well, each thread must stay on core. There are two ways of being taken off core ahead of time: Blocking for a mutex, and blocking for network. Lots of short messages run into scheduling overhead, since the recipient must decide what to do with each, which is not really possible without some sort of critical section. This is more efficient if messages are longer, as the decision time does not depend on message length. Longer messages are however liable to block on write at the sender side. So one pays in either case. This is another tradeoff to be balanced.

  • Flow control — A query is a pipeline of producers and consumers. Sometimes the consumer is in a different partition. The producer must not get indefinitely ahead of the consumer because this would run out of memory, but it must stay sufficiently ahead so as not to stop the consumer. In practice, there are synchronization barriers to check even progress. These will decrease platform utilization, because two threads never finish at exactly the same time. The price of not having these is having no cap on transient memory consumption.

  • Un-homogenous performance — Identical machines do not always perform identically. This is seen especially with disk, where wear on SSDs can affect write speed, and where uncontrollable hazards of data placement will get uneven read speeds on rotating media. Purely memory-bound performance is quite close, though. Un-anticipatable and uncontrollable hazards of scheduling cause different times of arrival of network messages, which introduces variation in run time on consecutive runs. Single-servers have some such variation from threading, but the effects are larger with a network.

The logical side of query optimization stays the same. Pushing down predicates is always good, and all the logical tricks with moving conditions between subqueries stay the same.

Schema design stays much the same, but there is the extra question of partitioning keys. In this implementation, there are only indices on identifiers, not on dates, for example. So, for a primary key to foreign key join, if there is an index on the foreign key, the index should be partitioned the same way as the primary key. So, joining from orders to lineitem on orderkey will be co-located. Joining from customer to orders by index will be colocated for the c_custkey = o_custkey part (assuming an index on o_custkey) and cross-partition for getting the customer row on c_custkey, supposing that the query needs some property of the customer other than c_custkey or c_orderkey.

A secondary question is the partition granularity. For good compression, nearby values should be consecutive, so here we leave the low 12 bits out of the partitioning. This has effect on bulk load and refreshes, for example, so that a batch of 10,000 lineitems, ordered on l_orderkey will go to only 2 or 3 distinct destinations, thus getting longer messages and longer insert batches, which is more efficient.

This is a quick overview of the wisdom so far. In subsequent installments, we will take a quantitative look at the tradeoffs and consider actual queries. As a conclusion, we will show a full run on a couple of different platforms, and likely provide Amazon machine images for the interested to see for themselves. Virtuoso Cluster is not open source, but the cloud will provide easy access.

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/24/2014 13:05 GMT Modified: 09/30/2014 16:37 GMT
In Hoc Signo Vinces (part 16 of n): Introduction to Scale-Out [ Orri Erling ]

So far, we have analyzed TPC-H in a single-server, memory-only setting. We will now move to larger data and cluster implementations. In principle, TPC-H parallelizes well, so we should expect near-linear scalability; i.e., twice the gear runs twice as fast, or close enough.

In practice, things are not quite so simple. Larger data, particularly a different data-to-memory ratio, and the fact of having no shared memory, all play a role. There is also a network, so partitioned operations, which also existed in the single-server case, now have to send messages across machines, not across threads. For data loading and refreshes, there is generally no shared file system, so data distribution and parallelism have to be considered.

As an initial pass, we look at 100G and 1000G scales on the same test system as before. This is two machines, each with dual Xeon E5-2630, 192 GB RAM, 2 x 512 GB SSD, and QDR InfiniBand. We will also try other platforms, but if nothing else is said, this is the test system.

As of this writing, there is a working implementation, but it is not guaranteed to be optimal as yet. We will adjust it as we go through the workload. One outcome of the experiment will be a precise determination of the data-volume-to-RAM ratio that still gives good performance.

A priori, we know of the following things that complicate life with clusters:

  • Distributed memory — The working set must be in memory for a run to have a competitive score. A cluster can have a lot of memory, and the data is such that it partitions very evenly, so this appears at first not a problem. The difficulty comes with query memory: If each machine has 1/16th of the total RAM and a hash table would be 1/64th of the working set, on a single-server it is no problem just building the hash table. On a scale-out system, the hash table would be 1/4 of the working set if replicated on each node, which will not fit, especially if there are many such hash tables at the same time. Two main approaches exist: The hash table can be partitioned, but this will force the probe to go cross-partition, which takes time. The other possibility is to build the hash table many times, each time with a fraction of the data, and to run the probe side many times. Since hash tables often have Bloom filters, it is sometimes possible to replicate the Bloom filter and partition the hash table. One has also heard of hash tables that go to secondary storage, but should this happen, the race is already lost; so, we do not go there.

    We must evaluate different combinations of these techniques and have a cost model that accurately predicts the performance of each variant. Adding to realism is always safe but halfway difficult to do.

  • NUMA — Most servers are NUMA (non-uniform memory architecture), where each CPU socket has its own local memory. For single-server cases, we use all the memory for the process. Some implementations have special logic for memory affinity between threads. With scale-out there is the choice of having a server process per-NUMA-node or per-physical-machine. If per-NUMA-node, we are guaranteed only local memory accesses. This is a tradeoff to be evaluated.

  • Network and Scheduling — Execution on a cluster is always vectored, for the simple reason that sending single-tuple messages is unfeasible in terms of performance. With an otherwise vectored architecture, the message batching required on a cluster comes naturally. However, the larger the cluster, the more partitions there are, which rapidly gets into shorter messages. Increasing the vector size is possible and messages become longer, but indefinite increase in vector size has drawbacks for cache locality and takes memory. To run well, each thread must stay on core. There are two ways of being taken off core ahead of time: Blocking for a mutex, and blocking for network. Lots of short messages run into scheduling overhead, since the recipient must decide what to do with each, which is not really possible without some sort of critical section. This is more efficient if messages are longer, as the decision time does not depend on message length. Longer messages are however liable to block on write at the sender side. So one pays in either case. This is another tradeoff to be balanced.

  • Flow control — A query is a pipeline of producers and consumers. Sometimes the consumer is in a different partition. The producer must not get indefinitely ahead of the consumer because this would run out of memory, but it must stay sufficiently ahead so as not to stop the consumer. In practice, there are synchronization barriers to check even progress. These will decrease platform utilization, because two threads never finish at exactly the same time. The price of not having these is having no cap on transient memory consumption.

  • Un-homogenous performance — Identical machines do not always perform identically. This is seen especially with disk, where wear on SSDs can affect write speed, and where uncontrollable hazards of data placement will get uneven read speeds on rotating media. Purely memory-bound performance is quite close, though. Un-anticipatable and uncontrollable hazards of scheduling cause different times of arrival of network messages, which introduces variation in run time on consecutive runs. Single-servers have some such variation from threading, but the effects are larger with a network.

The logical side of query optimization stays the same. Pushing down predicates is always good, and all the logical tricks with moving conditions between subqueries stay the same.

Schema design stays much the same, but there is the extra question of partitioning keys. In this implementation, there are only indices on identifiers, not on dates, for example. So, for a primary key to foreign key join, if there is an index on the foreign key, the index should be partitioned the same way as the primary key. So, joining from orders to lineitem on orderkey will be co-located. Joining from customer to orders by index will be colocated for the c_custkey = o_custkey part (assuming an index on o_custkey) and cross-partition for getting the customer row on c_custkey, supposing that the query needs some property of the customer other than c_custkey or c_orderkey.

A secondary question is the partition granularity. For good compression, nearby values should be consecutive, so here we leave the low 12 bits out of the partitioning. This has effect on bulk load and refreshes, for example, so that a batch of 10,000 lineitems, ordered on l_orderkey will go to only 2 or 3 distinct destinations, thus getting longer messages and longer insert batches, which is more efficient.

This is a quick overview of the wisdom so far. In subsequent installments, we will take a quantitative look at the tradeoffs and consider actual queries. As a conclusion, we will show a full run on a couple of different platforms, and likely provide Amazon machine images for the interested to see for themselves. Virtuoso Cluster is not open source, but the cloud will provide easy access.

To be continued...

In Hoc Signo Vinces (TPC-H) Series

# PermaLink Comments [0]
09/24/2014 13:05 GMT Modified: 09/30/2014 16:34 GMT
SEMANTiCS 2014 (part 3 of 3): Conversations [ Orri Erling ]

I was asked for an oracular statement about the future of relational database (RDBMS) at the conference. The answer, without doubt or hesitation, is that this is forever. But this does not mean that the RDBMS world would be immutable, quite the opposite.

The specializations converge. The RDBMS becomes more adaptable and less schema-first. Of course the RDBMS also take new data models beside the relational. RDF and other property graph models, for instance.

The schema-last-ness is now well in evidence. For example, PostgreSQL has an hstore column type which is a list of key-value pairs. Vertica has a feature called flex tables where a column can be added on a row-by-row basis.

Specialized indexing for text and geometries is a well established practice. However, dedicated IR systems, often Lucene derivatives, can offer more transparency in the IR domain for things like vector-space-models and hit-scoring. There is specialized faceted search support which is quite good. I do not know of an RDBMS that would do the exact same trick as Lucene for facets, but, of course, in the forever expanding scope of RDB, this is added easily enough.

JSON is all the rage in the web developer world. Phil Archer even said in his keynote, as a parody of the web developer: " I will never touch that crap of RDF or the semantic web; this is a pipe dream of reality ignoring academics and I will not have it. I will only use JSON-LD."

XML and JSON are much the same thing. While most databases have had XML support for over a decade, there is a crop of specialized JSON systems like MongoDB. PostgreSQL also has a JSON datatype. Unsurprisingly, MarkLogic too has JSON, as this is pretty much the same thing as their core competence of XML.

Virtuoso, too, naturally has a JSON parser, and mapping this to the native XML data type is a non-issue. This should probably be done.

Stefano Bertolo of the EC, also LOD2 project officer, used the word Cambrian explosion when talking about the proliferation of new database approaches in recent years.

Hadoop is a big factor in some environments. Actian Vector (née VectorWise), for example, can use this as its file system. HDFS is singularly cumbersome for this but still not impossible and riding the Hadoop bandwagon makes this adaptation likely worthwhile.

Graphs are popular in database research. We have a good deal of exposure to this via LDBC. Going back to an API for database access, as is often done in graph database, can have its point, especially as a reaction to the opaque and sometimes hard to predict query optimization of declarative languages. This just keeps getting more complex, so a counter-reaction is understandable. APIs are good if crossed infrequently and bad otherwise. So, graph database APIs will develop vectoring, is my prediction and even recommendation in LDBC deliverables.

So, there are diverse responses to the same evolutionary pressures. These are of initial necessity one-off special-purpose systems, since the time to solution is manageable. Doing these things inside an RDBMS usually takes longer. The geek also likes to start from scratch. Well, not always, as there have been some cases of grafting some entirely non-MySQL-like functionality, e.g. Infobright and Kickfire, onto MySQL.

From the Virtuoso angle, adding new data and control structures has been done many times. There is no reason why this cannot continue. The next instances will consist of some graph processing (BSP, or Bulk Synchronous Processing) in the query languages. Another recent example is an interface for pluggable specialized content indices. One can make chemical structure indices, use alternate full text indices, etc., with this.

Most of this diversification has to do with physical design. The common logical side is a demand for more flexibility in schema and sometimes in scaling, e.g., various forms of elasticity in growing scale-out clusters, especially with the big web players.

The diversification is a fact, but the results tend to migrate into the RDBMS given enough time.

On the other hand, when a new species like the RDF store emerges, with products that do this and no other thing and are numerous enough to form a market, the RDBMS functionality seeps in. Bigdata has a sort of multicolumn table feature, if I am not mistaken. We just heard about the wish for strict schema, views, and triggers. By all means.

From the Virtuoso angle, with structure awareness, the difference of SQL and RDF gradually fades, and any advance can be exploited to equal effect on either side.

Right now, I would say we have convergence when all the experimental streams feel many of the same necessities.

Of course you cannot have a semantic tech conference without the matter of the public SPARQL end point coming up. The answer is very simple: If you have operational need for SPARQL accessible data, you must have your own infrastructure. No public end points. Public end points are for lookups and discovery; sort of a dataset demo. If operational data is in all other instances the responsibility of the one running the operation, why should it be otherwise here? Outsourcing is of course possible, either for platform (cloud) or software (SaaS). To outsource something with a service level, the service level must be specifiable. A service level cannot be specified in terms of throughput with arbitrary queries but in terms of well defined transactions; hence the services world runs via APIs, as in the case of Open PHACTS. For arbitrary queries (i.e., analytics on demand), with the huge variation in performance dependent on query plans and configuration of schema, the best is to try these things with platform on demand in a cloud. Like this, there can be a clear understanding of performance, which cannot be had with an entirely uncontrolled concurrent utilization. For systems in constant operation, having one's own equipment is cheaper, but still might be impossible to procure due to governance.

Having clarified this, the incentives for operators also become clearer. A public end point is a free evaluation; a SaaS deal or product sale is the commercial offering.

Anyway, common datasets like DBpedia are available preconfigured on AWS with a Virtuoso server. For larger data, there is a point to making ready-to-run cluster configurations available for evaluation, now that AWS has suitable equipment (e.g., dual E5 2670 with 240 GB RAM and SSD for USD 2.8 an hour). According to Amazon, up to five of these are available at a time without special request. We will try this during the fall and make the images available.

SEMANTiCS 2014 Series

# PermaLink Comments [0]
09/08/2014 16:11 GMT
SEMANTiCS 2014 (part 2 of 3): RDF Data Shapes [ Orri Erling ]

The first keynote of Semantics 2014 was by Phil Archer of the W3C, entitled "10 Years of Achievement." After my talk, in the questions, Phil brought up the matter of the upcoming W3C work group charter on RDF Data Shapes. We had discussed this already at the reception the night before and I will here give some ideas about this.

After the talk, my answer was that naturally the existence of something that expressed the same sort of thing as SQL DDL, with W3C backing, can only be a good thing and will give the structure awareness work by OpenLink in Virtuoso and probably others a more official seal of approval. Quite importantly, this will be a facilitator of interoperability and will raise this from a product specific optimization trick to a respectable, generally-approved piece of functionality.

This is the general gist of the matter and can hardly be otherwise. But underneath is a whole world of details, which we discussed at the reception.

Phil noted that there was controversy around whether a lightweight OWL-style representation or SPIN should function as the basis for data shapes.

Phil stated in the keynote that the W3C considered the RDF series of standards as good and complete, but would still have working groups for filling in gaps as these came up. This is what I had understood from my previous talks with him at the Linking Geospatial Data workshop in London earlier this year.

So, against this backdrop, as well as what I had discussed with Ralph Hodgson of Top Quadrant at a previous LDBC TUC meeting in Amsterdam, SPIN seems to me a good fit.

Now, it turns out that we are talking about two different use cases. Phil said that the RDF Data Shapes use case was about making explicit what applications required of data. For example, all products should have a unit price, and this should have one value that is a number.

The SPIN proposition on the other hand, as Ralph himself put it in the LDBC meeting, is providing to the linked data space functionality that roughly corresponds to SQL views. Well, this is one major point, but SPIN involves more than this.

So, is it DDL or views? These are quite different. I proposed to Phil that there was in fact little point in fighting over this; best to just have two profiles.

To be quite exact, even SQL DDL equivalence is tricky, since enforcing this requires a DBMS; consider, for instance, foreign key and check constraints. At the reception, Phil stressed that SPIN was certainly good but since it could not be conceived without a SPARQL implementation, it was too heavy to use as a filter for an application that, for example, just processed a stream of triples.

The point, as I see it, is that there is a wish to have data shape enforcement, at least to a level, in a form that can apply to a stream without random access capability or general purpose query language. This can make sense for some big data style applications, like an ETL-stage pre-cooking of data before the application. Applications mostly run against a DBMS, but in some cases, this could be a specialized map-reduce or graph analytics job also, so no low cost random access.

My own take is that views are quite necessary, especially for complex query; this is why Virtuoso has the SPARQL macro extension. This will do, by query expansion, a large part of what general purpose inference will do, except for complex recursive cases. Simple recursive cases come down to transitivity and still fit the profile. SPIN is a more generic thing, but has a large intersection with SPARQL macro functionality.

My other take is that structure awareness needs a way of talking about structure. This is a use case that is clearly distinct from views.

A favorite example of mine is the business rule that a good customer is one that has ordered more than 5 times in the last year, for a total of more than so much, and has no returns or complaints. This can be stated as a macro or SPIN rule with some aggregates and existences. This cannot be stated in any of the OWL profiles. When presented with this, Phil said that this was not the use case. Fair enough. I would not want to describe what amounts to SQL DDL in these terms either.

A related topic that has come up in other conversations is the equivalent of the trigger. One use case of this is enforcement of business rules and complex access rights for updates. So, we see that the whole RDBMS repertoire is getting recreated.

Now, talking from the viewpoint of the structure-aware RDF store, or the triple-stream application for that matter, I will outline some of what data shapes should do. The triggers and views matter is left out, here.

The commonality of bulk-load, ETL, and stream processing, is that they should not rely on arbitrary database access. This would slow them down. Still, they must check the following sorts of things:

  • Data types
  • Presence of some required attributes
  • Cardinality — e.g., a person has no more than one date of birth
  • Ranges — e.g., a product's price is a positive number; gender is male/female; etc.
  • Limited referential integrity — e.g., a product has one product type, and this is a subject of the RDF type product type.
  • Limited intra-subject checks — e.g.. delivery date is greater-than-or-equal-to ship date.

All these checks depend on previous triples about the subject; for example, these checks may be conditional on the subject having a certain RDF type. In a data model with a join per attribute, some joining cannot be excluded. Checking conditions that can be resolved one triple at a time is probably not enough, at least not for the structure-aware RDF store case.

But, to avoid arbitrary joins which would require a DBMS, we have to introduce a processing window. The triples in the window must be cross-checkable within the window. With RDF set semantics, some reference data may be replicated among processing windows (e.g., files) with no ill effect.

A version of foreign key declarations is useful. To fit within a processing window, complete enforcement may not be possible but the declaration should still be possible, a little like in SQL where one can turn off checking.

In SQL, it is conventional to name columns by prefixing them with an abbreviation of the table name. All the TPC schemas are like that, for example. Generally in coding, it is good to prefix names with data type or subsystem abbreviation. In RDF, this is not the practice. For reuse of vocabularies, where a property may occur in anything, the namespace or other prefix denotes where the property comes from, not where it occurs.

So, in TPC-H, l_partkey and ps_partkey are both foreign keys that refer to part, plus that l_partkey is also a part of a composite foreign key to partsupp. By RDF practices, these would be called rdfh:hasPart. So, depending on which subject type we have, rdfh:hasPart is 30:1 or 4:1. (distinct subjects:distinct objects) Due to this usage, the property's features are not dependent only on the property, but on the property plus the subject/object where it occurs.

In the relational model, when there is a parent and a child item (one to many), the child item usually has a composite key prefixed with the parent's key, with a distinguishing column appended, e.g., l_orderkey, l_linenumber. In RDF, this is rdfh:hasOrder as a property of the lineitem subject. In SQL, there is no single part lineitem subject at all, but in RDF, one must be made since everything must be referenceable with a single value. This does not have to matter very much, as long as it is possible to declare that lineitems will be primarily accessed via their order. It is either this or a scan of all lineitems. Sometimes a group of lineitems are accessed by the composite foreign key of l_partkey, l_suppkey. There could be a composite index on these. Furthermore, for each l_partkey, l_suppkey in lineitem there exists a partsupp. In an RDF translation, the rdfh:hasPart and rdfh:hasSupplier, when they occur in a lineitem subject, specify exactly one subject of type partsupp. When they occur in a partsupp subject, they are unique as a pair. Again, because names are not explicit as to where they occur and what role they play, the referential properties do not depend only on the name, but on the name plus included data shape. Declaring and checking all this is conventional in the mainstream and actually useful for query optimization also.

Take the other example of a social network where the foaf:knows edge is qualified by a date when this edge was created. This may be by reification, or more usually by an "entitized" relationship where the foaf:knows is made into a subject with the persons who know each other and the date of acquaintance as properties. In a SQL schema, this is a key person1, person2 -> date. In RDF, there are two join steps to go from person1 to person2; in SQL, 1. This is eliminated by saying that the foaf:knows entity is usually referenced by the person1 Object or person2 Object, not the Subject identifier of the foaf:knows.

This allows making the physical storage by O, S, G -> O2, O3, …. A secondary index with S, G, O still allows access by the mandatory subject identifier. In SQL, a structure like this is called a clustered table. In other words, the row is arranged contiguous with a key that is not necessarily the primary key.

So, identifying a clustering key in RDF can be important.

Identifying whether there are value-based accesses on a given Object without making the Object a clustering key is also important. This is equivalent to creating a secondary index in SQL. In the tradition of homogenous access by anything, such indexing may be on by default, except if the property is explicitly declared of low cardinality. For example, an index on gender makes no sense. The same is most often true of rdfs:type. Some properties may have many distinct values (e.g., price), but are still not good for indexing, as this makes for the extreme difference in load time between SQL and the all-indexing RDF.

Identifying whether a column will be frequently updated is another useful thing. This will turn off indexing and use an easy-to-update physical representation. Plus, properties which are frequently updated are best put physically together. This may, for example, guide the choice between row-wise and column-wise representation. A customer's account balance and orders year-to-date would be an example of such properties.

Some short string valued properties may be frequently returned or used as sorting keys. This requires accessing the literal via an ID in the dictionary table. Non-string literals, numbers, dates, etc., are always inlined (at least in most implementations), but strings are a special question. Bigdata and early versions of Virtuoso would inline short ones; later versions of Virtuoso would not. So specifying, per property/class combination, a length limit for an inlined string is very high gain and trivial to do. The BSBM explore score at large scales can get a factor of 2 gain just from inlining one label. BSBM is out of its league here, but this is still really true and yields benefits across the board. The simpler the application, the greater the win.

If there are foreign keys, then data should be loaded with the referenced entities first. This makes dimensional clustering possible at load time. If the foreign key is frequently used for accessing the referencing item (for example, if customers are often accessed by country), then loading customers so that customers of the same country end up next to each other can result in great gains. The same applies to a time dimension, which in SQL is often done as a dimension table, but rarely so in linked data. Anyhow, if date is a frequent selection criterion, physically putting items in certain date ranges together can give great gains.

The trick here is not necessarily to index on date, but rather to use zone maps (aka min/max index). If nearby values are together, then just storing a min-max value for thousands of consecutive column values is very compact and fast to check, provided that the rows have nearby values. Actian Vector's (VectorWise) prowess in TPC-H is in part from smart use of date order in this style.

To recap, the data shapes desiderata from the viewpoint of guiding physical storage is as follows:

(I will use "data shape" to mean "characteristic set," or "set of Subjects subject to the same set of constraints." A Subject belonging to a data shape may be determined either by its rdfs:type or by the fact of it having, within the processing window, all or some of a set of properties.)

  • All normal range, domain, cardinality, optionality, etc. Specifically, declaring something as single valued (as with SQL's UNIQUE constraint) and mandatory (as with SQL's NOT NULL constraint) is good.
  • Primary access path — The Properties whose Objects are dominant access criteria is important
  • No-index — Declare that no index will be made on the Object of a Property within a data shape.
  • Inlined string — String values of up to so many characters in this data shape are inlined
  • Clustering key — The Subject identifiers will be picked to be correlated with the Object of this Property in this data shape. This can be qualified by a number of buckets (e.g., if dates are from 2000 to 2020, then this interval may be 100 buckets), with an exception bucket for out of range values.
  • No full text index — A string value will not need to be full text indexed in this Property even if full text indexing is generally on.
  • Full text index desired — This means that if the value of the property is a string, then the row must be locatable via this string. The string may or may not be inlined, but an index will exist on the literal ID of the string, e.g., POSG.
  • Co-location — This is akin to clustering but specifies, for a high cardinality Object, that the Subject identifier should be picked to fall in the same partition as the Object. The Object is typically a parent of the Subject being loaded; for example, the containing assembly of a sub-assembly. Traversing the assembly created in this way will be local on a scale-out system. This can also apply to geometries or text values: If primary access is by text or geo index, then the metadata represented as triples should be in the same partition as the entry in the full text/geo index.
  • Update group — A set of properties that will often change together. Implies no index and some form of co-location, plus update-friendly physical representation. Many update groups may exist, in which case they may or may not be collocated.
  • Composite foreign/primary key. A data shape can have a multicolumn foreign key, e.g., l_partkey, l_suppkey in lineitem with the matching primary key of ps_partkey, ps_suppkey in partsupp. This can be used for checking and for query optimization: Looking at l_partkey and l_suppkey as independent properties, the guess would be that there hardly ever exists a partsupp, whereas one does always exist. The XML standards stack also has a notion of a composite key for random access on multiple attributes.

These things have the semantic of "hint for physical storage" and may all be ignored without effect on semantics, at least if the data is constraint-compliant to start with.

These things will have some degree of reference implementation through the evolution of Virtuoso structure awareness, though not necessarily immediately. These are, to the semanticist, surely dirty low-level disgraceful un-abstractions, some of the very abominations the early semanticists abhorred or were blissfully ignorant of when they first raised their revolutionary standard.

Still, these are well-established principles of the broader science of database. SQL does not standardize some of these, nor does it have much need to, as the use of these features is system-specific. The support varies widely and the performance impacts are diverse. However, since RDF excels as a reference model and as a data interchange format, giving these indications as hints to back-end systems cannot hurt, and can make a difference of night and day in load and query time.

As Phil Archer said, the idea of RDF Data Shapes is for an application to say that "it will barf if it gets data that is not like this." An extension is for the data to say what the intended usage pattern is so that the system may optimize for this.

All these things may be learned from static analysis and workload traces. The danger of this is over-fitting a particular profile. This enters a gray area in benchmarking. For big data, if RDF is to be used as the logical model and the race is about highest absolute performance, never mind what the physical model ends up being, all this and more is necessary. And if one is stretching the envelope for scale, the race is always about highest absolute performance. For this reason, these things will figure at the leading edge with or without standardization. I would say that the build-up of experience in the RDBMS world is sufficient for these things to be included as hints in a profile of data shapes. The compliance cost will be nil if these are ignored, so for the W3C, these will not make the implementation effort for compliance with an eventual data shapes recommendation prohibitive.

The use case is primarily the data warehouse to go. If many departments or organizations publish data for eventual use by their peers, users within the organization may compose different combinations of extractions for different purposes. Exhaustive indexing of everything by default makes the process slow and needlessly expensive, as we have seen. Much of such exploration is bounded by load time. Federated approaches for analytics are just not good, even though they may work for infrequent lookups. If datasets are a commodity to be plugged in and out, the load and query investment must be minimized without the user/DBA having to run workload analysis and manual schema optimization. Therefore, bundling guidelines such as these with data shapes in a dataset manifest can do no harm and can in cases provide 10-50x gains in load speeds and 2-4x in space consumption, not to mention unbounded gains in query time, as good and bad plans easily differ by 10-100x, especially in analytics.

So, here is the pitch:

  • Dramatic gains in ad hoc user experience
  • Minimal effort by data publishers, as much of the physical guidelines can be made from workload trace and dataset; the point is that the ad hoc user does not have to do this.
  • Great optimization potential for system vendors; low cost for initial compliance
  • Better understanding of the science of performance by the semantic community

To be continued...

SEMANTiCS 2014 Series

# PermaLink Comments [0]
09/08/2014 15:22 GMT Modified: 09/08/2014 16:12 GMT
 <<     | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |     >>
 
SunMonTueWedThuFriSat
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30     
< today

Powered by OpenLink Virtuoso Universal Server
Running on Linux platform