The Path to In-Memory Database Technology

re: The Path to In-Memory Database Technology

Chris Adkin — Tue, 12 Feb 2013 12:20:33 GMT

Ji Joe,

I doth my cap to you, a tremendous amount of effort must have gone into this posting.

Regarding CPU architectures, max clock speeds will probably top out somewhere around 4.5 Ghz-ish. This has lead to a trend in design with increasingly numerous cores per socket. I wonder when the penny will drop and CPU designer will realise there will come a point when dedicating more die real estate to L1/2/3 caches rather than extra cores will make a real performance difference.

Chris

re: The Path to In-Memory Database Technology

Mr Jones — Tue, 12 Feb 2013 18:40:50 GMT

"Note to storage vendors: this is why the claim that caching solves IO performance problems is totally stupid. "

Amen

Like, Duh,

So, Mr Storage Vendor (AKA MS (arent ALL caching stategies basically vending the "storage" of something not close)), what happens after EVERYTHING IS CACHED? AKA no spinning storage.

I know, maybe that better design thingy....?

AKA, Caching is to make up for....unoptimized code perhaps...wink

thus restarts the old war where the Hardware guys pickup the slack for poor software design, Not saying there is any....wink

Intel Core 12: SQL Server storage engine on the Die....

No wait, isnt it already...if its....cached? wink

re: The Path to In-Memory Database Technology

jchang — Tue, 12 Feb 2013 19:23:21 GMT

Chris:

I do not believe that there is a fundamental limit to CPU clock frequency, but clearly Intel has stopped bothering to pursue this aspect. The IDF2012 discussion on Haswell cites going to 8 ports on the superscalar side.

Cache is a complex subject considering the objective is performance over a broad range of applications and considering that each market segment has their own priorities. Also consider that once an application is in cache, there will be no further improvement, and that some applications cycle memory with poor (spatial and temporal) locality.

In the old days of single core, per Moore's Law, the objective for doubling the number of transistors is 1.4X performance. Because logic and cache transistors have different density, I like to account for logic and cache separately on this aspect. To be more accurate, the objective for doubling the die size is 1.4X.

So as a rought approximation in determining cache size, we want each 10% we add to contribute 4% improvement. The first element of cache contributes far more than that, but then it falls off rapidly.

Increasing L1 has implications on L1 latency, ie, the number of pipeline stages for pre-fetch. L3 latency is already so high it does not really matter. L2 might have some impact, but the cost might not be worth the value.

Now with multi-core, if our application scales with cores, ie, little synchronization effort and no resource contention, then scaling is linear with the number of cores, die area or transistors, ie, better than Moore's Law.

Hence there is no single answer. Ideally, Intel should have a range of products, with different numbers of cores and with different types of cores. A further complexity with cache is what market segment benefits from cache and how is it valued.

Several year ago, the main impact was multi-processor scalability. That is, it had negligible impact at single core, but did improve scaling at 4+ cores. This was highly valued in the database server market to the degree that people would happily (no joke!) pay thousands $$ for big cache over the few hundred dollar desktop processor.

So my personal opinion is that the cache today is probably good enough. I prefer segmenting the memory system to get small super low latency.

re: The Path to In-Memory Database Technology

jchang — Tue, 12 Feb 2013 19:29:18 GMT

Mr J: my object to storage vendors and their caching claims is primarily to the fact that the database engine is a cache. On modern server systems, we might have 256-384GB in a 2-socket and 1024GB in a 4-scoket.

If the SAN vendor sales rep talks about their 32GB SAN controller cache, I would have a to-scale representation of 1024GB vs 32GB ready to show them.

Of course they will also try to sell their SSD caching solution.

Clearly they have never heard of database server FileGroups and Partitioning?

So here goes: our database server has 256-1024GB memory, of which 85% is buffer cache. What data is it that is not buffer but will be on the SAN?

re: The Path to In-Memory Database Technology

Thomas Kejser — Mon, 11 Mar 2013 02:49:36 GMT

Hi Joe

I did some measurements of PAGE compression recently. I measure an overhead of up to 60% in CPU cycles to do updates in place.

Measuring the lock times (By using XEvent and getting the CPU cycle count for each event) there is a HUGE overhead for compression as lock are held a lot longer (4x longer). This indicates that you should never compress tables that have a lot of lock contention.

re: The Path to In-Memory Database Technology

jchang — Tue, 12 Mar 2013 15:39:43 GMT

hey Thomas,

at SQLBits, I reported page compression as having 40% overhead on a single thread read, but in parallel queries it could be essentially free. Given that compression burns (much) more cpu than decompression, 60% for write seems cheap. Of course MS probably picked a lower overhead compression algorithm. And the 8KB page is probably too small to get really good compression. Too bad there is not a full extent high compression option. I have noticed that creating nonclustered indexes at high very row count is painfully slow, perhaps 3-4X overhead in elapsed time, perhaps this is inability to do parallelism in the final step?

By update in place, I assume you are updating a fix length value with another fixed length value? Without compression, it really will be in place. With page compression, I assume we must decompress the entire page and recompress the same, hence effectively we must lock the entire page?

Now what if the new value does not compress into the same size? there would a page split? we could build are indexes with appropriate fill factor? I suppose a column with sequentially increasing or otherwise predictable values compresses well, while random values do not? So we should test the updates on compressible and non-compressible values?