Orri Erling's Weblog

In the introduction to scale out piece, I promised to address the matter of data-to-memory ratio, and to talk about when scale-out makes sense. Here we will see that scale-out makes sense whenever data does not fit in memory on a single commodity server. The gains in processing power are immediate, even when going from one box to just two, with both systems having all in memory.

As an initial take on the issue we run 100 GB and 1000 GB on the test system. 100 GB is trivially in memory, 1000 GB is not, as the memory is 384 GB total, of which 360 GB may be used for the processes.

We run 2 workloads on the 100 GB database, having pre-loaded the data in memory:

This is directly comparable to the 100 GB single-server results. Comparing the second runs, we see a 1.53x gain in power and a 1.8x gain in throughput from 2x the platform. This is fully on the level for a workload that is not trivially parallel, as we have seen in the previous articles. The difference between the first and second runs at 100 GB comes, for both single-server and cluster, from the latency of allocating transient query memory. For an official run, where the weakest link is the first power test, this would simply have to be pre-allocated.

We run 2 workloads on the 1000 GB database, starting from cold.

The result is:

The 1000 GB result is not for competition with this platform; more memory would be needed. For actual applications, the numbers are still in the usable range, though.

The 1000 GB setup uses 4 SSDs for storage, one per server process. The server processes are each bound to their own physical CPU.

We look at the meters: 32M pages (8M per process) are in memory at each time. Over the 2 benchmark executions there are a total of 494M disk reads. The total CPU time is 165,674 seconds of CPU, of which about 10% are system, over 10,063 seconds of real-time. Cumulative disk-read wait-time is 130,177 s. This gives an average disk read throughput of 384 MB/s.

This is easily sustained by 4 SSDs; in practice, the maximum throughput we see for reading is 1 GB/s (256 MB/s per SSD). Newer SSDs would do maybe twice that. Using rotating media would not be an option.

Without the drop in CPU caused by waiting for SSD, we would have numbers very close to the 100 GB numbers.

The interconnect traffic for the two runs was 1,077 GB with no message compression. The write block time was 448 seconds of thread-time. So we see that blocking on write hurts platform utilization when running under optimal conditions, but compared to going to secondary storage, it is not a large factor.

The 1000 GB scale has a transient peak memory consumption of 42 GB. This consists of hash-join build sides and GROUP BYs. The greatest memory consumers are Q9 with 9 GB, Q13 with 11 GB, and Q16 with 7 GB. Having many of these at a time drives up the transient peak. The peak gets higher as the scale grows, also because a larger scale requires more concurrent query streams. At the 384 GB for 1000 GB ratio, we do not yet get into memory saving plans like hash joins in many passes or index use instead of hash. When the data size grows, replicated hash build sides will become less convenient, and communication will increase. Q9 and Q13 can be done by index with almost no transient memory, but these plans are easily 3x less efficient for CPU. These will probably help at 3000 GB and be necessary at least part of the time at 10,000 GB.

The I/O volume in MB per index over the 2 executions is:

Of this, maybe 600 GB could be saved by stream compressing o_comment. Otherwise this cannot be helped without adding memory. The lineitem reads are mostly for l_extendedprice, which is not compressible. If compressing o_comment made l_extendedprice always fit in memory, then there would be a radical drop in I/O. Also, as a matter of fact, the buffer management policy of least-recently-used works the very worst for big scans, specifically those of l_extendedprice: If the head is replaced when reading the tail, and the next read starts from the head, then the whole table/column is read all over again. Caching policies that specially recognized scans of this sort could further reduce I/O. Clustering lineitems/orders on date, as Actian Vector TPC-H implementations do, also starts yielding a greater gain when not running from memory: One column (e.g., l_shipdate) may be scanned for the whole table but, if the matches are bunched together, then most of l_extendedprice will not be read at all. Still, if going for top ranks in the races, all will be from memory, or at least there will be SSDs with read throughput around 150 MB/s per core, so these tricks become relatively less important.

In the 100 GB numerical quantities summaries, we see much the same picture as in the single-server. Queries get faster, but their relative times are not radically different. The throughput test (many queries at a time) times are more or less multiples of the power (single user) times. This picture breaks at 1000 GB where I/O first drops the performance to under half and introduces huge variation in execution times within a single query. The time entirely depends on which queries are running along with or right before the execution and on whether these have the same or different working sets. All the streams have the same queries with different parameters, but the query order in each stream is different.

The numerical quantities follow for all the runs. Note that the first 1000 GB run is cold. A competition grade 1000 GB result can be made with double the memory, and the more CPU the better. We will try one at Amazon in a bit.

***

The conclusion is that scale-out pays from the get-go. At present prices, a system with twice the power of a single node of the test system is cost effective. Scales of up to 500 GB are single commodity server, under $10K. Rather than going from a mid-to-large dual-socket box to a quad-socket box, one is likely to be better off having two cheaper dual-socket boxes. These are also readily available on clouds, whereas scale-up configurations are not. Onwards of 1 TB, a cluster is expected to clearly win. At 3 TB, a commodity cluster will clearly be the better deal for both price and absolute performance.

100 GB Run 1

Virt-H Executive Summary

Duration of stream execution

Numerical Quantities Summary Timing Intervals in Seconds:

100 GB Run 2

Virt-H Executive Summary

Duration of stream execution

Numerical Quantities Summary Timing Intervals in Seconds:

1000 GB Run 1

Virt-H Executive Summary

Duration of stream execution

Numerical Quantities Summary Timing Intervals in Seconds:

1000 GB Run 2

Virt-H Executive Summary

Duration of stream execution

Numerical Quantities Summary Timing Intervals in Seconds:

To be continued...

In Hoc Signo Vinces (TPC-H) Series

• In Hoc Signo Vinces (part 1): Virtuoso meets TPC-H
• In Hoc Signo Vinces (part 2): TPC-H Schema Choices
• In Hoc Signo Vinces (part 3): Benchmark Configuration Settings
• In Hoc Signo Vinces (part 4): Bulk Load and Refresh
• In Hoc Signo Vinces (part 5): The Return of SQL Federation
• In Hoc Signo Vinces (part 6): TPC-H Q1 and Q3: An Introduction to Query Plans
• In Hoc Signo Vinces (part 7): TPC-H Q13: The Good and the Bad Plans
• In Hoc Signo Vinces (part 8): TPC-H: INs, Expressions, ORs
• In Hoc Signo Vinces (part 9): TPC-H Q18, Ordered Aggregation, and Top K
• In Hoc Signo Vinces (part 10): TPC-H Q9, Q17, Q20 - Predicate Games
• In Hoc Signo Vinces (part 11): TPC-H Q2, Q10 - Late Projection
• In Hoc Signo Vinces (part 12): TPC-H: Result Preview
• In Hoc Signo Vinces (part 13): Virtuoso TPC-H Kit Now on V7 Fast Track
• In Hoc Signo Vinces (part 14): Virtuoso TPC-H Implementation Analysis
• In Hoc Signo Vinces (part 15): TPC-H and the Science of Hash
• In Hoc Signo Vinces (part 16): Introduction to Scale-Out
• In Hoc Signo Vinces (part 17): 100G and 300G Runs on Dual Xeon E5 2650v2
• In Hoc Signo Vinces (part 18): Cluster Dynamics
• In Hoc Signo Vinces (part 19): Scalability, 1000G, and 3000G
• In Hoc Signo Vinces (part 20): 100G and 1000G With Cluster; When is Cluster Worthwhile; Effects of I/O (this post)
• In Hoc Signo Vinces (part 21): Running TPC-H on Virtuoso Cluster on Amazon EC2