The next wave in BI


TPC-H fun with Greenplum (single node edition)

27/01/2010 23:12

Introduction

There's a nice quote on the Greenplum site from Brian Dolan, Fox Interactive Media which says “Very impressed with the speed... 3 minutes to do a sum on 100 million rows of data”. That pretty much sums it up. I wouldn't consider that anything to be proud of; 3 minutes to do a simple sum over a measly 100 million rows of data is SLOW nowadays. A quick look at the TPC site will tell you that more complex queries on at least 600 million records run in seconds, not in minutes. Heck, with Excel 2010 PowerPivot summing 100 million rows takes less than a second! And you don't need expensive hardware or software to do any of this. My own benchmark machine is a dual Xeon 5520 with 64 GB ram and 12 Intel X-25M SSD drives of 80 GB each, connected to 2 Adaptec 5805 Raid controllers. The system is running Centos 5.4 and total cost is way below $10K. Anyway, it's always nice to see how a database behaves in practice, so I downloaded the free, single node Greenplum edition, installed it and ran the TPC-H sf100 benchmark. By the way, typing that last sentence took a lot less time than the actual work...

 

Installation

Before you install the Greenplum (GP) database it's good to first plan your disk layout. GP likes to distribute its data over so called data segments, and each data segment is tied to a GP process which is tied to a CPU core. So in my case I had 8 cores (in fact with hyperthreading it is 16) which allowed me to use 8 disks or partitions. No raid, no failover (although GP has facilities for that), I was just interested in testing load and query speed. The installation process itself is pretty straightforward and well explained in the single node install manual. What's not explained at all, not even in the more voluminous admin guide, is how the system should be tuned. Remember that GP is based on PostgreSQL so all the same settings are available. However, the settings advised for PostgreSQL are for OLTP systems, not for data warehousing. So in my first run the system used only about 3GB of the available 64GB ram. It turned out that the most important configuration parameter (shared_buffers, which tells how much memory a process can use) was set at only 32 MB (!). I toyed around with several parameters, including the system kernel settings, but couldn't use a value over 1920MB (GP won't start when it's set to e.g. 2048), so I suspect that value is capped somewhere.

 

Database creation

Again, if you're familiar with PostgreSQL, GP's DDL and DML shouldn't have a lot of secrets. They did add a few extra's though, such as the ability to define a table as column oriented instead of the standard row orientation, and the ability to compress data. But, unlike modern column stores as Vertica or Paraccel, the DBA is responsible for thinking out the best storage and compression strategy as everything has to be explicitly created using DDL statements. What's more, the column orientation and compression are only available for append-only tables. That's right: if you want columns and/or compression, it's dump and reload if you want to update your data. In that respect it's a bit similar to Infobright Community Edition which lacks all DML capabilities. For the benchmarks I ran I used went with the default row orientation.

 

Loading & database size

The data loaded into the database is generated using the dbgen tool which can be compiled from the source available from www.tpc.org. Dbgen will create 8 ASCII files that comprise the TPC schema which can then be loaded into the created database. GP lets you use the standard COPY statement to load files into a table, but also offers a facility called 'external tables' to support fast, parallel data loading. I didn't use external tables but the plain COPY which took about 45 minutes to load the 100 GB dataset. The tables already had indexes defined so that's actually pretty good. What's not so good is the resulting database size. As I couldn't use compression, the resulting database size was about 160 GB, which is 10 times as much as you need when loading the same dataset into an Infobright database. It's also about two to three times as much as less aggressivily compressed databases need.

 

Running queries

I first started by starting some individual queries to test whether the system was working and whether is was fast. It was working, but fast? TPC-H query 1 took 7 minutes and 7 seconds, which is more than 60 times slower than the fastest result (5.5 secs) I got with another database on this machine . I decided to fire up my TPC-H script anyway which runs the official TPC-H bechmark consisting of a power test (a single stream) and a throughput test (5 parallel streams). After a couple of minutes I got this message:

 

ERROR: Greenplum Database does not yet support that query. DETAIL: The query contains a correlated subquery

 

Ouch, that hurts. Had the same problem with InfoBright some time ago but haven't tested recent editions. What's funny is that Query 4 runs fine (that one also contains a CSQ but is probably easier to rewrite) but Q2, Q17, Q20, Q21 and Q22 return an error which also invalidates the TPC results I got. I also got a couple of 'out of memory' errors while running the trhoughput test, even though the memory utilization never exceeded 45GB. So as disappointing as it may be, I'm not going to publish the results here. What I can tell is that if you're running PostgreSQL and want a similar but faster database to run your data warehouse, GP single node edition will be a major improvement. It's also much faster than MySQL for typical BI queries. On the other hand, it's also (a lot) slower than SQL Server 2008, Kickfire, or Sybase IQ, the current leaders in the single node SF100 benchmarks.

 

Conclusion

Greenplum might be an interesting product if you are using the MPP version and can invest time and money in optimizing and tuning the system, but I'm not very impressed with the single node edition. Some of the issues probably also apply to the MPP version. It lacks intelligent auto tuning capabilities, query optimization seems to work not too well, it does a bad job of utilizing available memory (which might be due to my lack of experience with tuning PostgreSQL db's too, btw). Note though that TPC-H is just 'a' benchmark: you should always do your own testing with your own data! Overall I would say it's 'not bad', but not very good either.

—————

Back


Topic: TPC-H fun with Greenplum

Date: 17/12/2013

By: Tusadidarix

Subject: yf92


—————

Date: 17/03/2011

By: Anitta chan

Subject: Mixing storage types

I am surprised at no CSQs. I thought they were better than that. Given the types of queries needed for BI, it's hard to position a product as an analytic database if CSQs can't be handled.
<a href="http://prowowlevelingguide.com">Wow leveling guide</a>

—————

Date: 11/02/2011

By: Randolph

Subject: TCP-H

The TPC-H has some tests in it that rule out many MPP systems (ie transactions and true ACID compliance). In fact I'm not sure that the TPC-H is a relevant test of these systems while these transactional tests are retained in the benchmark. These systems do not try to be transactional systems and do not require these slow sequential features.

If you want to see good TPC-H figures, apparently Vectorwise has just blitzed the TPC-H with a very significant result.

We are using Vectorwise on our MPP nodes and are finding staggering performance improvements. But like GP (although Luke would probably never admit this) we cant run the TPC-H legally either yet.

For more info look here: www.deepcloud.co

—————

Date: 21/12/2010

By: Brian Dolan

Subject: Don't Quote Me!

Marketing people never hear that. I said immediately beforehand that those were bad numbers and I needed to look it up. Thanks for the mention, though! Hope you got your issues sorted out, and if you didn't, go ahead and ping me: brian@discovix.com

—————

Date: 03/11/2010

By: GP Guy

Subject: Forums

It would probably be best to take this discussion to a better formatted area, http://www.gpdba.com

—————

Date: 08/07/2010

By: current state assessment

Subject: TPC-H fun with Greenplum

Implement Enterprise Performance Management (EPM), Business Intelligence (BI), and other applications to support the strategy. For existing EPM and BI applications, identify ways to improve their performance. <a href="http://www.column5.com/csa.php">current state assessment</a>

—————

Date: 07/06/2010

By: Peter Guarino

Subject: Not sure what you are trying to prove

Are you evaluating the performance of free databases for hosting large databases or just databases that run only on a single node? Wouldn't it be more instructive to compare Greenplum SNE versus other free databases? Otherwise, shouldn't you be comparing Greenplum's full version against similarly priced implementations from other vendors, single node or not. The real advantage of Greenplum, and other databases like it, is its superior cost performance ratio and ability to scale inexpensively. OK, so you might have to run Greenplum on two or three servers to get the same performance as Sybase IQ on a single node, if its cheaper so what?

A database that largely fits into main memory and resides completely on ssd storage is really just a toy example and of no particular value. Worse yet, to make general inferences from a single data point would lead to flawed conclusions. Better conclusions could be drawn by varying the data set sizes to factor out accidental advantages that one engine might have over another at a particular instance.
* include large data sets that greatly exceed main memory, 10-100x
* examples that stress io, cpu and memory in various ways

It is important to have enough knowledge about the databases involved to avoid badly tuned engines that would skew results. In particular the "out of memory" errors you encountered most likely pertain to Postgres' work memory, not main memory or swap, a tuning parameter that will lead to particularly bad performance if improperly set. Bad query plans indicate that you did not run analyze on the tables after loading them - a must for Greenplum as it does not run autovacuum as the Postgres 8.3 and 8.4 engines do.

—————

Date: 19/05/2010

By: epm consulting

Subject: Comments all good

Some of the issues apparently as well administer to the MPP version. It lacks able auto affability capabilities, concern enhancement seems to plan not too well, and it does a bad job of utilizing accessible memory.

—————

Date: 12/03/2010

By: Nicolas Doye

Subject: Greenplum SNE is Greenplum's baby product

Your audience may confuse Greenplum SNE with Greenplum DB. SNE is parallel across CPUs in your box. The full DB is parallel across CPUs in several servers (with the segmented data close to each CPU).

This is what companies like Fox use. You have 8 real cores (which may work like 10ish? See http://ossipedia.ipa.go.jp/en/capacity/EV0603300122EN/) and not 16. Our GP cluster has 64 real cores across 4 segment servers _plus_ 8 in the master server, so that's 72. Plus our IO is spread across 4 separate SCSI controllers Ours is _tiny_ compared with Fox's or ebay's.

As another data point for SNE compared with PostgreSQL is available here: http://community.greenplum.com/showthread.php?t=113

—————

Date: 10/03/2010

By: David Gruzman

Subject: TPCH on vanilla PostgresSQL

After a bit of tuning I was able to run TPCH with scale factor 1 on the Postgresql. So it is strange that GP not supports queries supported by the postgres. Details are in my blog www.dbaspects.com

—————