Skip to Content
avatar image
Former Member

Issue with multilevel partitioning HASH-Range

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Mar 25, 2015 at 05:59 AM

    @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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Mar 25, 2015 at 04:36 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded