[IQUG] performance problems when converted int to unsigned bigint

Mumy, Mark mark.mumy at sap.com
Sun Jun 3 13:01:50 MST 2018


Post execution, HTML plans would help the best.

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of John KIRKWOOD <john.kirkwood at uk.bnpparibas.com>
Date: Tuesday, May 29, 2018 at 7:35 AM
To: Chris Baker <c.baker at sap.com>, "iqug at iqug.org" <iqug at iqug.org>
Subject: Re: [IQUG] performance problems when converted int to unsigned bigint

Thanks Chris

Yes we have individual indexes on the columns - HG on clc_dt; cpty_inl_no; cpty_hsh_id

Part of the exercise I did not mention was to eliminate internal allocation of primary keys so we can regenerate our key from the data itself and do not have to rely on mapping between any external ids and our internal ids
So we need a new column

Interesting bit about the ‘randomness’ of the hash id. We do not decode the hash – it is a one-way identifying method – so I do not think the HG indexes will be any different as key info will be held in sequence
It might make the maintenance of the index a bit longer although we are not seeing that at the moment – problem is just on joins
But I will have a think about this one

JOhn



From: Baker, Chris [mailto:c.baker at sap.com]
Sent: 29 May 2018 13:09
To: John KIRKWOOD; iqug at iqug.org
Subject: [EXTERNAL] RE: performance problems when converted int to unsigned bigint

Just a shot here – when you were using int for (cpty_inl_no) the number was increasing, so within the HG-U of the composite key, the values were increasing under the first part of the key.

Now you are hashing, so any ‘natural sort’ now goes out the window as the second part of the key is sorted by the hash value, not the original integer value.  Depending on how you are decoding the hash, you probably require more sorting.

In addition, you appear to only have an HG-U on the composite key.  Do you have HG indexes on the individual columns as well.  This might help.

My suggestion would be to first try individual HG indexes on the separate columns, as well as the primary key, and if that doesn’t work, then compromise – go back to the old key (clc_dt, cpty_inl_no) but this time make cpty_inl_no an unsigned bigint instead of an int.

Also, what does the index advisor say?

Chris

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

https://sap.na.pgiconnect.com/I826572
Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of John KIRKWOOD
Sent: Tuesday, May 29, 2018 6:46 AM
To: iqug at iqug.org
Subject: [IQUG] performance problems when converted int to unsigned bigint

Apologies for the length of this
The basic problem is that I have migrated from an integer column to an unsigned bigint column for identity columns
And performance has degraded so much that we are on the verge of rolling back
Yes I do have a case open with SAP but I am getting nowhere with this and wondered if anyone had the same problem

We have a 5 node multiplex cluster running 1 coordinator/writer; 1 writer and three read nodes
We are running SAP IQ/16.0.110.2528/10554/P/sp11.11/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2017-08-16 03:56:06
The database is DATA: 2.1T; SYSTEM_MAIN: 135GB; SYSTEM_TEMP: 540G
Writer nodes: Main buffers: 750G; TEMP buffers: 270G; Large memory space: 180G
Reader nodes: Main buffers: 750G; TEMP buffers: 350G; large memory: 100G

Each host: 1.5T  memory and 16 cpus

An example table and sample columns
The table pm_cpty has 6 calculation dates
We used an internal number (next number in series) to identify these – with a primary key of (clc_dt, cpty_inl_no)
We used an integer datatype for cpty_inl_no

We migrated the internal number to a hash id (hashing a combination of character columns on the input data) to identify these - with a primary key of (clc_dt, cpty_hsh_id)
We used an unsigned bigint datatype for this (to get more numbers). We cater for collisions to allow us a unique primary key

After we switched from using the internal number to using the hash id in all of our joins our performance went through the floor when the execution plans changed for the worst

Queries that ran in 3 seconds with internal numbers timed out after 30 minutes with hash ids
Some of these queries also generated such bad plans that the group by phases generated > 100G TEMP work tables and we exceeded available TEMP space multiple times as we had 5-10 of these running in parallel
We have a 200G TEMP limit per connection.

Our data distribution is ‘identical’ – well as near as it gets

Cpty_inl_no
Number of occurrences                unique_identity_values
1                                                              81502
2                                                              55832
3                                                              9819
4                                                              56888
5                                                              28590
6                                                              2037700

Cpty_hsh_id
Number of occurrences                unique_identity_values
1                                                              81474
2                                                              55813
3                                                              9810
4                                                              56859
5                                                              28585
6                                                              2037739

This shows that over our 6 calc dates we have 2M rows in each column with unique values
We have 81K unique values for each column which occur only in one calc date
And so on …..

If we look at an sp_iqindexsize of the columns we get
Cpty_inl_no: 55712KBytes; 254 pages; 253 compressed pages  all in barray
Cpty_hsh_id: 112752 Kbytes; 447 pages; 19 compressed pages all in barray

Cpty_inl_no_HG index (clc_dt, cpty_inl_no)
Total      157824  1313       1305
  vdo      0              0              0
  bt          33872    503         502
  garray 118896  783         780
  bm       5056       27           23

cpty_hsh_id_HG (clc_dt, cpty_hsh_id)
Total      133696  786         777
  vdo      0              0              0
  bt          36896    328         327
  garray 91760    426         422
  bm       5040       32           28

and sp_iqindexfragmentation on the HG indexes

mrx_owner.pm_cpty.cpty_inl_no_HG  HG          2              25
SQLCODE:            0
Fill Percent          btree pages        garray pages      bitmap pages
0-10%                    2                              9                              26
11-20%                 0                              172                         0
21-30%                 0                              121                         0
31-40%                 5                              287                         0
41-50%                 121                         56                           0
51-60%                 131                         1                              0
61-70%                 69                           10                           0
71-80%                 61                           126                         0
81-90%                 53                           0                              0
91-100%               60                           0                              0
========           ========           ========           ========

mrx_owner.pm_cpty.cpty_hsh_id_HG HG          2              25
SQLCODE:            0
Fill Percent          btree pages        garray pages      bitmap pages
0-10%                    3                              0                              31
11-20%                 3                              3                              0
21-30%                 0                              43                           0
31-40%                 0                              100                         0
41-50%                 7                              14                           0
51-60%                 117                         2                              0
61-70%                 46                           0                              0
71-80%                 42                           263                         0
81-90%                 15                           0                              0
91-100%               94                           0                              0
========           ========           ========           ========

John




___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for additional disclosures.

___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for additional disclosures.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180603/8b1583b5/attachment-0001.html>


More information about the IQUG mailing list