Long running table partitioning job

[Former Member]

Dear HANA grus,

I've just finished table partitioning jobs for CDPOS(change document item) with 4 partitions by hash with 3 columns.

Total data volumn is around 340GB and the table size was 32GB !!!!!

(migration job was done without disabling CD, so currently deleting data on the table with RSCDOK99)

Before partitioning, the data volumn of the table was around 32GB.

After partitioning, the size has changed to 25GB.

It took around One and half hour with exclusive lock as mentioned in the HANA adminitration guide.

(It is QA DB, so less complaints)

I thought that I might not can do this in the production DB.

Does anyone hava any idea for accelerating this task?? (This is the fastest DBMS HANA!!!!)

Or Do you have any plan for online table partitioning functionality??(To HANA Development team)

Any comments would be appreciate.

Cheers,

- Jason

[Former Member]

After partitioning the CDPOS table, deleting/selecting transactions time increased almost twice.

(checked from st12 performance trace)

I'm wondering if I choosed bad columns for hashing,
but I can't understand performance degradation.

Does it cost more time for hashed selection or deletion?

[lbreddemann]

Some questions need to be answered here:

• What was your rationale to partition the table in the first place?
• How many unloads happened during the re-partitioning?
• How do the now longer running SQL statements look like?
• What are the three columns you picked for partitioning?
• How did you come up with 4 partitions? Why not 13, 72 or 213?

Concerning the online partitioning into a HASH partition scheme: this is not easy to do in a lock free manner since all of the old data needs to be touched as well. For online modification of partitions the range partitioning is the better choice as it allows for simply adding a new partition without touching the old data (if the new partition covers a new group of data, e.g. a new range of dates).

- Lars

[Former Member]

Dear Lars,

• What was your rationale to partition the table in the first place?

       => To reduce deleting time of CDPOS

            (As I mentioned it was almost 10% quantity of whole Data volume, So I would like to save deleting time of the table from any pros of partitioning table like partitioning pruning)

• How many unloads happened during the re-partitioning?

       => It was fully uploaded in the memory before partitioning the table by myself.(from HANA studio)

• How do the now longer running SQL statements look like?

       => As i mentioned selecting/deleting increased almost twice.

• What are the three columns you picked for partitioning?

       => mandant, objectclas, tabname(QA has 2 clients and each of them have nearly same rows of the table)

• How did you come up with 4 partitions? Why not 13, 72 or 213?

       => I thought each partitions' size would be 8GB(32GB/4) if they are divided into same size(just simple thought), and 8GB size is almost same size like other largest top20 tables in the HANA DB.

For the last comment of your reply, most people would do partition for their existing large tables to get any benefit of partitioning(just like me). I think your comment can be applied for the newly inserting data.

Cheers,

-Jason

[lbreddemann]

Jason,

looks like we're cross talking here...

What was your rationale to partition the table in the first place?

       => To reduce deleting time of CDPOS            (As I mentioned it was almost 10% quantity of whole Data volume, So I would like to save deleting time of the table from any pros of partitioning table like partitioning pruning)

Ok, I see where you're coming from, but did you ever try out if your idea would actually work?

As deletion of data is heavily related with locating the records to be deleted, creating an index would have probably be the better choice.

Thinking about it... you want to get rid of 10% of your data and in order to speed the overall process up, you decide to move 100% of the data into sets of 25% of the data - equally holding their 25% share of the 10% records to be deleted.

The deletion then should run along these 4 sets of 25% of data.

It's surely me, but where is the speedup potential here?

How many unloads happened during the re-partitioning?

       => It was fully uploaded in the memory before partitioning the table by myself.(from HANA studio)

I was actually asking about unloads _during_ the re-partitioning process. Check M_CS_UNLOADS for the time frame in question.

How do the now longer running SQL statements look like?

       => As i mentioned selecting/deleting increased almost twice.

That's not what I asked.

Post the SQL statement text that was taking longer.

What are the three columns you picked for partitioning?

       => mandant, objectclas, tabname(QA has 2 clients and each of them have nearly same rows of the table)

Why those? Because these are the primary key?

I wouldn't be surprised if the SQL statements only refer to e.g. MANDT and TABNAME in the WHERE clause.

In that case the partition pruning cannot work and all partitions have to be searched.

How did you come up with 4 partitions? Why not 13, 72 or 213?
       => I thought each partitions' size would be 8GB(32GB/4) if they are divided into same size(just simple thought), and 8GB size is almost same size like other largest top20 tables in the HANA DB.

Alright, so basically that was arbitrary.

For the last comment of your reply, most people would do partition for their existing large tables to get any benefit of partitioning(just like me). I think your comment can be applied for the newly inserting data.

Well, not sure what "most people" would do.

HASH partitioning a large existing table certainly is not an activity that is just triggered off in a production system. Adding partitions to a range partitions table however happens all the time.

- Lars