THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Columnar Databases

Ingres just published a TPC-H benchmark for VectorWise, an analytic database technology employing
  1) SIMD processing (Intel SSE 4.2),
  2) better memory optimizations to leverage on-chip cache,
  3) compression,
  4) Column-based storage.

Ingres originated as a research project at UC Berkeley (see Wikipedia) in the 1970s, and has since become a commercially supported, open source database system. Apparently, Ingres project people later founded Sybase. So Ingres in a sense, is the grandfather (or perhap grand-step-uncle?) to SQL Server. According to the Ingres whitepaper, VectorWise spun off from CWI in Amstersdam, with some roots in MonetDB? For the time being, Ingres VectorWise is not open-source. The interesting aspect of VectorWise among other column store database products is use of the SIMD, as in utilizing the special instructions and registers built into recent generation microprocessors. (In a previous blog, I proposed special SIMD instructions to improve the efficiency on accessing pages in traditional row-storage database engines.)

Below are TPC-H results at scale factor 100GB on HP ProLiant DL380 G7 with two Xeon 5680 six-core 3.33GHz processors for SQL Server 2008 R2 and Ingres VectorWise 1.5.

VectorWise

  Power Throughput QphH
SQL Server 99,626.3 55,038.2 73,974.6
VectorWise 257,142.9 246,101.7 251,561.7

Overall, the VectorWise columnar database has about 2.5X higher score on Power, and 5X on Throughput. The glaring weak point of SQL Server is that the throughput is (almost 2X) lower than the power. The SQL Server parallel execution engine is very powerful running a single stream with no other concurrent queries competing for CPU. It does not have a good strategy for sharing compute resources with other concurrent long running queries.

The general expectation set by all the new technology, columnar storage, memory optimization and vector processing was 10X, not 3X. However, we should consider that the TPC-H benchmark score is a composite geometric mean of 22 individual queries. The individual query performance differences between SQL Server and VectorWise are shown below, on a logarithmic scale (base 2, not 10, a cool feature in Excel 2007?).

VectorWise

The full order of magnitude gain is definitely achieved in certain queries (1, 4, 6, & 12), with good gains in additional queries. Many other queries are comparable with row based storage.

Columnar storage is expected in SQL Server 2011. Search for the SQL Server Technical Article: "Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0" by Eric Hanson. The Columnstore indexes cannot be updated, so a partitioning strategy to separate activate and static data is necessary.

ps
since we all love calculations, in the SQL Server TPC-H report, Q1 ran in 21.0 sec (presumably with all 12 cores near 100%, and there are 6M rows per SF 1GB, of which 98.4% are touched in Q1, we can work out the following

 Q1 secCPU-cycles per row 
SQL Server 21.0 1398  
VectorWise 1.9 126  

On my laptop, with 2 Core 2 architecture cores at 2.2GHz, I measured 2944 CPU-cycles per row on Q1. Without the Group By, the cost is 1982 CPU-cycles per row. Just to touch each row (in SELECT COUNT(*)) is 420 CPU-cycles. 

The TPC-H SF100 LineItem table with the 3 byte DATE data type is 87.5GB (w/o compression). So Q1 21 sec means 4.17GB/s data consumption rate. The SQL Server TPC-H result was configured with 4 SATA SSD drives. I believe these are the 3Gbit/s SATA interface (6Gb/s models coming soon?) meaning 300MB/sec per drive tops, or 1.2GB/s total for 4 drives. So to achieve Q1 in 21 sec, the data must be in memory. However, with 16 (3Gb/s) SATA SSDs  or 8 6Gb/s SATA SSDs distributed over 2 controllers, 4GB/s is easily achievable.

For VectorWise, given that I have not actually used it myself, I will have to make "reasonable estimations". Lets disregard compression for the moment. Assuming VectorWise employs 8 byte values to store Quantity, ExtendedPrice, Discount, and Tax, 4 bytes for Date, and 1 byte each for the 2 flag values, then VectorWise must process 38 bytes per row, or about 24GB for TPC-H Q1. To support Q1 in 2 seconds, the disk system would need to deliver 12GB/sec. With compression, the rate could probably be brought down to 6GB/s. The actual storage system employed, 16 15K SAS disks over 2 controllers, can deliver 16 x 150MB/s = 2.4GB/s. The 2 x Xeon 5680 (2 IOH) system can easily deliver 6GB/s (and probably 12GB/s if this is really necessary). So to access data on disk at that rate, the storage system should be 40 disk drives over 2 controllers (probably capped at 2.8GB/sec per controller today).

Published Monday, February 14, 2011 10:24 AM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Geoff said:

The founder of Ingres, Michael Stonebreaker, founded Vertica, a columnar database. I've worked with it for a couple of years. Amazingly fast.

Only to be used for Datawarehousing/BI type of stuff. You don't want to use column-based databases for transactional activity.

It's good that SQL Server is adding this feature, but I would like to hear more. Vertica puts columnar thinking and engineering throughout their product. From the ground up. I would worry if SQL Server has it on as an ad hoc feature. I'll read the article you mentioned to find out more.

I'm fully convinced that normal SQL Server and Oracle shouldn't be used for large amounts of data in a BI/datawarehousing environment. Just not the right tool for the job.

February 14, 2011 10:29 AM
 

Marcin Zukowski said:

[Disclaimer: I'm a co-founder of VectorWise]

Hi Joe,

Thanks for a great analysis. Indeed, the benefit of VectorWise comes not only from being a column store (actually, internally we're a hybrid), but mostly from the new execution infrastructure.

I acknowledge on a few queries we are not better than SQLServer. This is mostly due to a bigger number of indices SQLServer is using. But, on computational queries like query 1, where you can't really play any tricks, you can really see the potential of this technology.

We continuously improve our system, so I hope we will be able to impress you again with more results in the future.

As for the SQLServer column storage. From what I've read, this will be functional, but somewhat primitive (don't tell my friends from the SQLServer team I said that;)). Inability to update the data, not even in the append-only way. But, I guess if you're Microsoft, such things are forgiven...

Best,

Marcin

February 14, 2011 12:44 PM
 

jchang said:

No one ever denied that transactional databases (with row storage) carried alot of overhead in analytics. And yet, SQL Server, Oracle etc are still used for this, while specialty products have come and gone. I am inclined to believe that people do not want separate database technologies between an Inmon DW and a Kimbal DW. For that matter, there seems to be a preference for a single database from transactions to DW to BI (sure, this is  actually Analysis Server, or Oracle equivalent).

I am also not convinced that a product like Ingres needs to be better on each individual TPC-H query. The important point if for users to understand which technology should be used in each circumstance. It would help if people stopped buying grossly overpriced storage systems, so that they could afford to deploy all technologies that are justified

February 14, 2011 1:08 PM
 

Geoff said:

What would be nice to see would be different benchmarks between differing columnar databases. What's also important is the amount of hardware costs between different solutions. Does it really matter that one is 35x faster and another is 50x? Maybe.

But, as I mentioned earlier, that in this DW/BI space columnar is the way to go right now. As you mentioned, I do think people would be interested in a hybrid system. But I'm not entirely sure what the benefit of that would be besides easier transitions between databases of both people and knowledge.

February 14, 2011 1:37 PM
 

jim slim said:

"It does not have a good strategy...."

There goes your SQLBits invite...

"somewhat primitive"

MS is known for that

Ask Russinovich and Tom(Ive seen the source code) Keyser...

Nice analysis

February 14, 2011 5:02 PM
 

Roy said:

@Geoff

Geoff points out that "What's also important is the amount of hardware costs between different solutions."

VectorWise depends mainly on the bandwidth from the disk subsystem to the CPU.  It really doesn't care how many random I/Os per second the disks can do; all it cares about is keeping the pipeline from stalling.  It turns out that very inexpensive hardware will yield the kind of performance figures reported in TPC-H.  

http://www.rationalcommerce.com/V16-Appliance.htm

February 15, 2011 9:10 AM
 

jchang said:

like I have said in other posts, don't waste time arguing about the hardware cost in TPC benchmark reports. Sometimes the hardware is used because it was already available, sometimes because a vendor or group sponsored it. So it may not represent a price-optimized configuration. Frequently, TPC configurations are over-configured. It is a lot of work to generate a publication quality result, there may not be time to find the price-optimized configuration.

With the exception of Oracle Database Machine, most solutions are purchased a la carte, not as a package, and even ODM has options. In the US, however much people complain about hardware cost, the storage solution that ends up being purchased is usually much more expensive than the TPC config, and usually also much lower in performance. I will point out that the SQL Server optimizer employs a rigid random-to-sequential IO ratio not suited to modern disk drive characteristics, which is why certain SQL Server configurations have more disk drives than other reasonably equivalent config on another RDBMS.

I would also advise caution in TPC-H 100GB results with 144GB+ system memory with regard to the disk configuration, and other SF where the data is essentially in memory. If this reflects your situation, then fine. But normally, I would prefer to look at TPC-H reports where the SF is much larger than memory for disk config strategy.

February 15, 2011 10:41 AM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement