Currently Being Moderated

So late last year, I had the opportunity to load some reasonable data volumes into SAP HANA. We got about 16GB of data or 160m records, and ran some tests.

Well now I have closer to 256GB of the same data to load, and I only have a 512GB SAP HANA appliance to spare, which already has a SAP BW system and a bunch of other things on it! So I thought it was time to try to optimise the data model and make it slicker.

We cut a few corners last time around because some of the data was a bit corrupt and so we had some text fields where there could otherwise be dates - and in addition, we were not judicious with the use of things like smaller integer data types, where there were only small numbers of distinct values.

I'm not sure how much value this has in SAP HANA because of the way it creates indexes, but text fields certainly take up a lot of unneccessary space. Today I'm going to do a bunch of testing with a 42m row test data set, and then use this for the basis of the final model - loading the full 2bn+ rows in-memory. And we'll see how it performs!

Step 1: Data Model optimisation

I now have no text values in my main fact table - only TINYINT, SMALLINT, INT, REAL, DATE and TIME. I'm going to load this file into the old fact table which isn't optimised, and compare for space to see how much we have saved. The model is already well normalised so I'm not expecting to be able to reduce this any further.

So we are moving from:

create column table "POC"."FACT"( "Col1" INTEGER, "Col2" INTEGER, "Col3" INTEGER, "Col4" INTEGER, "Col5" INTEGER, "Col6" INTEGER, "Col7" INTEGER, "Col8" INTEGER, "Col9" REAL, "Col10" INTEGER, "Col11" INTEGER, "Col12" INTEGER, "Col13" VARCHAR (32) default '', "Col14" VARCHAR (16) default '', "Col15" INTEGER, "Col16" INTEGER, "Col17" INTEGER, "Col18" INTEGER, "Col19" INTEGER);

To:

create column table "POC"."FACT2"( "Col1" INTEGER, "Col2" DATE, "Col3" TINYINT, "Col4" TINYINT, "Col5" SMALLINT, "Col6" SMALLINT, "Col7" TINYINT, "Col8" TINYINT, "Col9" REAL, "Col10" INTEGER, "Col11" TINYINT, "Col12" TINYINT, "Col13" REAL, "Col14" TIME, "Col15" SMALLINT, "Col16" TINYINT, "Col17" TINYINT, "Col18" SMALLINT, "Col19" DATE);

Now I've loaded the same data into both fact tables.

Fact Table 1: 1,327,747kb

Fact Table 2: 778,737kb

Wow - I'm really surprised by this. I'm guessing it's the text fields which were the killer, but this has halved the table size. This will make a big difference! I checked this and of course the column store has limited data types so things like TINYINT don't have an impact as integer datatypes are already compressed.

What's even more interesting is that we have reduced the load time from 88 seconds down to 26 seconds. I guess those text inserts were expensive in the column store.

Step 2: Load optimisation

HANA has two main load optimisation steps - the number of parallel processes and the amount of data consumed before commit. This SAP HANA box is a Medium, which means 40 CPUs and 512GB RAM. Theory should mean that you will get an improvement up to 40 threads, which will tail off.

This is really important right now because I know that the original 160m row test case takes 9 minutes to load. So we're looking at at least 2 hours to load the full set. Do you think we can make this much less?

 

Default

10k

50k

200k

500k

1 Thread

7m11s

 

 

 

 

2 Threads

3m33s

 

 

 

 

4 Threads

1m57s

 

 

 

 

8 Threads

1m13s

 

 

38s

 

16 Threads

44s

34s

28s

25s

26s

32 Threads

47s

32s

27s

25s

27s

40 Threads

47s

32s

43s

25s

42s

The learning here is really clear - it's really easy to get down from 7m to about 30 seconds without being very clever and then another 10-15% performance can be had by fine-tuning. It also looks like there is no benefit of moving past about 16 load threads or a Batch of more than 200k, at least in this system. What's more when you move to 40 threads, the results start becoming inconsistent. I have no idea why.

Step 3: HANA Database Version

So I'm interested to see what happens if we update our HANA Database version. I'm told that there improvements both in compression and performance as new releases come out. What do you think?

Well I looked into it and unfortunately the current version of HANA SP03 is Patch 25, which has a problem with data load performance. Guess this is going to have to wait until another day.

Update: well we were on HANA SP03 Patch 20 and it wasn't as stable as I'd have liked, so Lloyd upgraded us to Patch 25. This seems to be so much better and I'd really recommend keeping up to date. I'm not sure it actually fixed any of our problems but it stopped a whole load of errors from appearing in the logs.

Step 4: The main load

OK - now we're ready to do the main load. I don't think this system can be optimised any further and by my calculations of 40m records in 25 seconds, we should be able to load the full 2bn in 20 minutes or so. Are you ready? Let's see.

The load is split up into 17 files - details for the load are below.

 

Size

Rows

Time

File 1

3925MB

42m

25s

File 2

1510MB

16m

11s

File 3

14304MB

152m

120s

File 4

13196MB

140m

144s

File 5

14239MB

152m

164s

File 6

13049MB

139m

107s

File 7

13569MB

144m

105s

File 8

15511MB

166m

177s

File 9

14156MB

151m

144s

File 10

14960MB

160m

156s

File 11

13449MB

146m

218s

File 12

15316MB

166m

419s

File 13

19843MB

214m

163s

File 14

17037MB

176m

415s

File 15

16399MB

183m

247s

File 16

15275MB

163m

198s

File 17

11959MB

128m

178s

Total

227697MB

2312m

2991s

 

As you can see, the performance is great initially and then starts to tail off in the later files. This leads to a fairly disappointing load performance (still massively faster than any other database remember!!!) of 2991s or 50 minutes to load 2.3bn rows into memory. I was hoping for over double this.

The reason for this seems to be that HANA puts the rows initially into a delta store, and after that has loaded, it then automaticaly merges them into the main column store. This happens concurrently with the load and seems to kill performance. So what I did was to join all 17 files into one big file, and then try loading it. Let's see what happens:

 

Size

Rows

Time

File 1

227690

2312m

51m

Curiously it's not any faster, and this turns out it's because it does the merge delta during the load. You can disable this with the following statement:

ALTER TABLE "POC"."FACT2" WITH PARAMETERS ('AUTO_MERGE' = 'OFF);

Note that whilst auto merge is off (turn it on when you're done loading) you can do manual merges doing:

MERGE DELTA OF "POC"."FACT2";

Step 5: Learning about partitions

It's at this point that i learnt there is a 2bn row limit in a single SAP HANA database partition. Curiously it allows you to load more than 2bn rows but then fails on the subsequent merge delta. This sounds like a pain but it's actually a blessing in disguise for two reasons. First, it turns out that as the amount of data in the main store increases, so does the cost of a merge delta and second, using partitions allows you to get the best out of HANA. If you turn automatic merge off and do it manually, look what happens:

An early merge takes just a minute:

Statement 'IMPORT FROM '/hana/FACT_05_2010.ctl' WITH THREADS 16 BATCH 200000' successfully executed in 1:50.816 minutes  - Rows Affected: 0

Statement 'MERGE DELTA OF "POC"."FACT2"' successfully executed in 1:04.592 minutes  - Rows Affected: 0

Wait a while and you see that the merge time has increased by a factor of 4 whilst the load time into the merge store is more or less linear. This makes sense of course because the merge process has to insert the records into a compressed store, which is computationally expensive. It appears to increase at O(m.log(n)) where n is the size of the main store and m is the size of the delta store, which more or less makes sense based on my knowledge of search and sort algorithms.

Statement 'IMPORT FROM '/hana/FACT_09_2011.ctl' WITH THREADS 16 BATCH 200000' successfully executed in 2:23.475 minutes  - Rows Affected: 0

Statement 'MERGE DELTA OF "POC"."FACT2"' successfully executed in 4:11.611 minutes  - Rows Affected: 0

And since it turns out that you can partition easily by month, I emptied the table and decided to repartition it like this. I now have 26 partitions, one for each month, plus an extra partition for anything else that doesn't fit.

ALTER TABLE "POC"."FACT2" PARTITION BY RANGE (DATE)

(PARTITION '2010-01-01' <= VALUES < '2010-02-01',

……

PARTITION '2012-02-01' <= VALUES < '2012-03-01',

PARTITION OTHERS);

Note that HANA makes it easy to add new partitions and move data between partitions. Data management even in large volumes won't be a problem, you will be glad to know. And look at the load times - it is completely linear for the delta merge right up until the last partition. This is a major improvement compared to a legacy RDBMS where you get very slow batch load times unless you drop indexes - massively slowing concurrent read performance.

Statement 'IMPORT FROM '/hana/FACT_02_2012.ctl' WITH THREADS 16 BATCH 200000' successfully executed in 1:46.066 minutes  - Rows Affected: 0

Statement 'MERGE DELTA OF "POC"."FACT2"' successfully executed in 53.618 seconds  - Rows Affected: 0

What's more it loads a total of 3.7bn rows in 73 minutes - including the merge delta exercise, which I wasn't even counting before.

Step 6 - Using Table locks

Another HANA tip is to spend some time reading the SQL Reference Manual. It has lots of stuff in it, much of which you won't find documented anywhere else. I found a little function called TABLE lock which should allow you to load data faster. Let's try it, the SQL Syntax looks like this:

IMPORT FROM '/hana/FACT_02_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

Note that you only want to do this on initial batch loads because it locks the entire table, but it's unlikely you will want to load billions of rows in one go after the initial load. What's really interesting here is that this feature puts the data directly into the main table - bypassing the delta table and the need to do a merge delta - but it is some slower 16% than a regular load followed by a merge delta. Why? Who knows!

Step 7 - Conclusions

Well I'm sure you have figured out a lot of this as you read this blog, but here's the takeaways that I got out of this exercise. But first and foremost, despite being fast, it is definitely worth your time to optimise your SAP HANA scenario.`

1) Spend time on optimising your HANA Data Model. It will reduce the size of your database for the big tables, improve performance and also reduce cost, because HANA is licensed by appliance memory size.

2) Test to optimise your load parameters. But don't spend too much time here. It's not that sensitive to detail changes so get it nearly right and move on.

3) Choose your partitioning scheme carefully. And then load partitions into individual files and do manual merges in-between partitions, if you are loading a lot of data. You don't have to but it will speed end-end load performance and allow for speedier issue resolution.

SAP HANA is pretty amazing technology and if you have worked with any other kind of RDBMS you will know that even the slowest of these times is far faster than anything else.

But first and foremost two things are persistent for me. First, tuning SAP HANA is just as important as with any other system - with a bit of work you can achieve performance that you wouldn't have believed before. And second, performance tuning SAP HANA is different to other systems - you tune for parallelisation and memory usage rather than for I/O. But remember this: performance tuning is about finding the performance envelope of a system and working around the weakest point. In that sense SAP HANA is no different to any other computer system in the world.

And in case you are in any way confused, here is the final SQL I used to create and load the table optimally:

drop  table "POC"."FACT2";

create column table "POC"."FACT2"(

          "Col1" INTEGER,

          "Col2" DATE,

          "Col3" TINYINT,

          "Col4" TINYINT,

          "Col5" SMALLINT,

          "Col6" SMALLINT,

          "Col7" TINYINT,

          "Col8" TINYINT,

          "Col9" REAL,

          "Col10" INTEGER,

          "Col11" TINYINT,

          "Col12" TINYINT,

          "Col13" REAL,

          "Col14" TIME,

          "Col15" SMALLINT,

          "Col16" TINYINT,

          "Col17" TINYINT,

          "Col18" SMALLINT,

          "Col19" DATE) NO AUTO MERGE;

ALTER TABLE "POC"."FACT2" PARTITION BY RANGE (DATE)

(PARTITION '2010-01-01' <= VALUES < '2010-02-01',

PARTITION '2010-02-01' <= VALUES < '2010-03-01',

PARTITION '2010-03-01' <= VALUES < '2010-04-01',

PARTITION '2010-04-01' <= VALUES < '2010-05-01',

PARTITION '2010-05-01' <= VALUES < '2010-06-01',

PARTITION '2010-06-01' <= VALUES < '2010-07-01',

PARTITION '2010-07-01' <= VALUES < '2010-08-01',

PARTITION '2010-08-01' <= VALUES < '2010-09-01',

PARTITION '2010-09-01' <= VALUES < '2010-10-01',

PARTITION '2010-10-01' <= VALUES < '2010-11-01',

PARTITION '2010-11-01' <= VALUES < '2010-12-01',

PARTITION '2010-12-01' <= VALUES < '2011-01-01',

PARTITION '2011-01-01' <= VALUES < '2011-02-01',

PARTITION '2011-02-01' <= VALUES < '2011-03-01',

PARTITION '2011-03-01' <= VALUES < '2011-04-01',

PARTITION '2011-04-01' <= VALUES < '2011-05-01',

PARTITION '2011-05-01' <= VALUES < '2011-06-01',

PARTITION '2011-06-01' <= VALUES < '2011-07-01',

PARTITION '2011-07-01' <= VALUES < '2011-08-01',

PARTITION '2011-08-01' <= VALUES < '2011-09-01',

PARTITION '2011-09-01' <= VALUES < '2011-10-01',

PARTITION '2011-10-01' <= VALUES < '2011-11-01',

PARTITION '2011-11-01' <= VALUES < '2011-12-01',

PARTITION '2011-12-01' <= VALUES < '2012-01-01',

PARTITION '2012-01-01' <= VALUES < '2012-02-01',

PARTITION '2012-02-01' <= VALUES < '2012-03-01',

partition others);

IMPORT FROM '/hana/FACT_02_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_03_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_05_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_06_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_07_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_08_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_09_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_10_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_11_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_12_2010.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_01_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_02_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_03_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_04_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_05_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_06_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_07_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_08_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_09_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_10_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_11_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_12_2011.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_01_2012.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

IMPORT FROM '/hana/FACT_02_2012.ctl' WITH THREADS 16 BATCH 200000 WITH TABLE LOCK;

MERGE DELTA OF "POC"."FACT2";

ALTER TABLE "POC"."FACT2" WITH PARAMETERS ('AUTO MERGE' = ON);

Next Steps

Well what's next? I think you know - I've optimised this data model for load performance, but how did I do for query performance? I'm already wondering about this because HANA is a parallel processing engine. If you put 27 months of partitions and then do a query over a wide date range then it should kick off a bunch of parallel processes. If you query on a single month you will hit less data but with fewer parallel processes?

What's the optimal data model for query performance and how does how you setup your joins, CE Functions and SQL Script affect this?

Acknowledgements and thank yous.

As is often the case with this sort of post, there are thanks to dole around. Lloyd Palfrey from Bluefin for being the HANA Ops guru and fixing the database every time I filled up disks, corrupted it and tuning the box. Vijay Vijaysankar, Vitaliy Rudnytskiy and David Hull from IBM, HP and SAP for bouncing ideas. Margaret Anderson and Serge Mutts from SAP CSA for helping with issue resolution. Markus Friedel from SAP AGS for helping resolve errors - mostly between my keyboard and chair.

And let's not forget Aleks Barilko, Marilyn Pratt, Gail Kling Schneider and the rest of the SCN crew for recovering most of this blog, which got lost in the move from the old to the new SCN whilst I was on vacation


Comments

Actions

Filter Blog

By author:
By date:
By tag: