Issue with multilevel partitioning HASH-Range

[Former Member]

Hi Guys,

In current architecture we have 15 nodes in our cluster. For big tables (14 billion rows) we have implemented the partitioning.

Approach 1:

We have created Hash partition on a key column with 60 partitions. Thus every node has 4 partitions on every HANA Node and query execution time is 800 ms, With this methodology whenever we are executing queries the entire partition was getting loaded in the main memory thus utilizing significant space.

Table create statement:

CREATE COLUMN TABLE "RAM"."Z_BIG_TRN_HASH" ("CAL_DATE" DAYDATE CS_DAYDATE,

  "DIM" VARCHAR(500),

  "BATCH_ID" INTEGER CS_INT,

  "MATRIX_ID" INTEGER CS_INT,

  "MATRIX_ACTUAL" DECIMAL(18,

  6) CS_FIXED,

  "HISTORY_ACTUAL" DECIMAL(18,

  6) CS_FIXED,

  "M1_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M2_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M3_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M4_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M1_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M2_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M3_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M4_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "SEQ_ID" INTEGER CS_INT,

  "INSERT_TIMESTAMP" LONGDATE CS_LONGDATE,

  "CREATED_USER" VARCHAR(100),

  "GLBL_GRP" INTEGER CS_INT) UNLOAD PRIORITY 5 NO AUTO MERGE WITH PARAMETERS ('PARTITION_SPEC' = 'HASH 60 DIM')

Approach 2:

So we have come up with HASH-Range partition

We have implemented a new approach HASH-Range, where we have created multi level partitioning (Hash-Range) on the main table.
We have created 15 hash partitions on the key column and range partition on month (calendar date) i.e 60 monthly partitions for 5
years (2011-2015) so with this new approach(HASH -Range) each node has one partition on key column and 60 monthly sub-partition on calendar date column so in total we  have 900 partition of one table on 15 HANA Nodes and query time is 3.8 sec.

Table create statement:

CREATE COLUMN TABLE "RAM_AD"."Y_BIG_TRN_HASH_RANGE" ("CAL_DATE" DAYDATE CS_DAYDATE,

  "DIM_GRP_ID" VARCHAR(500),

  "BATCH_ID" INTEGER CS_INT,

  "METRIX_ID" INTEGER CS_INT,

  "METRIX_ACTUAL" DECIMAL(18,

  6) CS_FIXED,

  "HISTORY_ACTUAL" DECIMAL(18,

  6) CS_FIXED,

  "M1_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M2_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M3_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M4_PRED" DECIMAL(18,

  6) CS_FIXED,

  "M1_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M2_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M3_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "M4_IQRD" DECIMAL(18,

  6) CS_FIXED,

  "SEQ_ID" INTEGER CS_INT,

  "INSERT_TIMESTAMP" LONGDATE CS_LONGDATE,

  "CREATED_USER" VARCHAR(100),

  "GLBL_GRP" INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE WITH PARAMETERS ('PARTITION_SPEC' = 'HASH 15 DIM_GRP_ID; RANGE CAL_DATE 20110101-20110201,20110201-20110301,20110301-20110401,20110401-20110501,20110501-20110601,20110601-20110701,20110701-20110801,20110801-20110901,20110901-20111001,20111001-20111101,20111101-20111201,20111201-20120101,20120101-20120201,20120201-20120301,20120301-20120401,20120401-20120501,20120501-20120601,20120601-20120701,20120701-20120801,20120801-20120901,20120901-20121001,20121001-20121101,20121101-20121201,20121201-20130101,20130101-20130201,20130201-20130301,20130301-20130401,20130401-20130501,20130501-20130601,20130601-20130701,20130701-20130801,20130801-20130901,20130901-20131001,20131001-20131101,20131101-20131201,20131201-20140101,20140101-20140201,20140201-20140301,20140301-20140401,20140401-20140501,20140501-20140601,20140601-20140701,20140701-20140801,20140801-20140901,20140901-20141001,20141001-20141101,20141101-20141201,20141201-20150101,20150101-20150201,20150201-20150301,20150301-20150401,20150401-20150501,20150501-20150601,20150601-20150701,20150701-20150801,20150801-20150901,20150901-20151001,20151001-20151101,20151101-20151201,20151201-20160101,*')

Issue 1:
But while reading the results on this table HASH-Range the execution time has increased 3 folds with HASH –Range partition.

Issue 2:

While execution query on HASH-Range partitioned table, we are getting below errors.

Error Type 1 “SAP DBTech JDBC: [2048]: column store error: search table error:  [2613] executor: communication problem”

Error Type 2 “SAP DBTech JDBC: [139]: current operation cancelled by request and transaction rolled back: search table error:  [2625] execution plan “

Error Type 3  “SAP DBTech JDBC: [2048]: column store error: search table error: [23017] Index syncpoint mismatch”

Can anyone help us for HASH –Range multilevel partitioning.

Regards,

Satya

[Former Member]

Hi Satya,

As there are many partitions of the table, the issue that you are facing could be related to creation of

many internal translation table which on exceeding the threshold, remove old ones from the cache, which would still be needed in the processing of the table query.

There are SAP notes on Translation table by increasing the cache, or other feasible solutions, for ex. SAP Note 1998599

This is one of the problem due to which you are facing the issue.

Thanks and Regards,

Anjali.

[lbreddemann]

@Issue 1:

Whether or not a specific partitioning scheme will improve the performance of a specific query depends on that query. You definitively should check the PlanViz to see which partitions actually get touched here.

To be honest, 900 partitions is a setup for _a_lot_ of data.

How many records are stored in this table?

@Issue 2:

This one sounds like a known bug to me.

Please open a support incident and have the colleagues check this.

- Lars

[Former Member]

Hi Lars,

Thanks for the response !!

1: I have already mentioned that, this table has 14 Billion Records, So we have to do partitions.

    HASH partition is giving good query time 800 ms but table occupy 430 GB space in memory.

    But if we sue HASH-Range partition query time goes 3.1 sec , which is not expected , but table 

    occupy 190 GB space in memory.

2: So far we are not sure that it's a bug or not , have message open with SAP for the same.

Any other idea , what we can do ?

I also thought that SCN is larger community and lots of experts so started this discussion.

Regards,
Satya

[lbreddemann]

Well the error message is a bug.

It's not something that you can influence by using the software in any more correct way.

Concerning the runtime: not sure where you base your expectations on but I again recommend to check which partitions get hit and why.

- Lars