[IQUG] [iqug] 16.0 index rebuilds

Mumy, Mark mark.mumy at sap.com
Mon Oct 7 09:34:21 MST 2019


For a move to IQ 16.0, you only need to rebuild the FPs to get the new n-bit stuff immediately.  The HG structure is mostly the same in IQ 16 except when it comes to tiered HG indexes.  Those are structures done in addition to the regular HG which is why it doesn’t take much to enable it.

With respect to HG indexes, there are two things to consider:

  *   Tiered or not – generally, a minor operation to enable or disable the tiering feature.  Should not rebuild the entire HG index.
  *   Rebuild the HG – This is usually done to reclaim space on the HG structures as over time you can end up with very sparse pages that a rebuild will make denser, more rows per page.

HNG and LF indexes are not used much anymore.  In fact, any HNG should be removed.  A lot of the HNG and LF logic was put into the HG indexes so that low cardinality fields could have an HG but not slow down like an HG used to and that range searches could be done on the same HG.  For most operations an LF was single threaded while having an HG on the same field would be parallel.

In my opinion, I wouldn’t just convert all the LF indexes to an HG.  With IQ 16.0 and especially with 16.1, you should find that you don’t need nearly as many indexes as you did in the past.  My recommendation is to split your columns (and searching) into 2 categories: primary and secondary.  A “primary” column is something that is used on nearly every query.  A date field, for instance.  A secondary column is something that is used by some people with some queries, but not all the time.  Columns that carry the “primary” designation should have an HG index (and one of the date indexes where appropriate).  Secondary columns should not have indexes.  This is very counter to prior IQ thinking.  As systems grow, data volumes grow, etc carrying extra indexes can become burdensome.

At an Indian telco, I’ve seen them have just 3-5 indexes per table on their largest tables (20 billion or more rows).  These would be the “primary” columns I referred to above.  Of course the query plans scream for missing indexes all over the place.  Guess what…. We added the indexes and it did very little to help with performance.  See, the primary columns had indexes that covered 80-90% of all use cases so that adding indexes to cover the other uses was just adding overhead and not much in terms of performance.  With the size of your system and that massive table, anything you can do to reduce the size without sacrificing performance is a good thing.  Imagine if you could take that 30TB table and cut it down to 20TB by just removing some unused indexes….  Not out of the realm of possibility depending on how many indexes are on it today in 15.4 and the compression that varchar fields get in 16.

Mark

Mark Mumy
SAP Platform and Technologies Global Center of Excellence
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 David Louie <David.Louie at blackrock.com>
Date: Monday, October 7, 2019 at 9:27 AM
To: "markdmumy at gmail.com" <markdmumy at gmail.com>
Cc: "Venuturapalli, Sharan" <sharan.venuturapalli at blackrock.com>, IQ Group <iqug at dssolutions.com>
Subject: Re: [IQUG] [iqug] 16.0 index rebuilds


So at min. for 16.0 we’d need to rebuild FP’s only.  No need to rebuild HG to tiered unless tested as it can cause issue.

Tech support also said not to leave a mixture of rebuilt and not rebuilt FP’s on a table which would be impossible to do especially on the larger tables.  Does this sound illogical that we should not leave the mixture?

What’s the story on LF to HG.  I think we said we’d drop and recreate LFs to HG which leads to the question on how much temp space we would require.

Thanks
David


From: Mark Mumy <markdmumy at gmail.com>
Sent: Monday, October 7, 2019 10:12 AM
To: Louie, David <David.Louie at blackrock.com>
Cc: iqug at dssolutions.com; Venuturapalli, Sharan <sharan.venuturapalli at blackrock.com>
Subject: Re: [IQUG] [iqug] 16.0 index rebuilds


External Email: Use caution with links and attachments
You sure can.  However, without prior testing I wouldn’t recommend turning it on in production.
Mark

========================

Sent from my mobile device



On Oct 7, 2019, at 09:09, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
All we did we FP and tested with this.

Can we flip the bits back if we do run into load issues?

Thanks Mark.

David

From: Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Sent: Monday, October 7, 2019 9:57 AM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: iqug at dssolutions.com<mailto:iqug at dssolutions.com>; Venuturapalli, Sharan <sharan.venuturapalli at blackrock.com<mailto:sharan.venuturapalli at blackrock.com>>
Subject: Re: [IQUG] [iqug] 16.0 index rebuilds


External Email: Use caution with links and attachments
Tiered HGs don’t help with compression.  They only affect load performance in some situations.   It’s not a guarantee and I’ve seen it add more load and overhead.  Like anything it must be tested to make sure that it gives you a gain and doesn’t hurt performance.

All a retier does is flip a few bits on the HG structure to tell it to use the tiered structure the next time a load happens.  The base HG stays the same which is why it runs quickly.

If you haven’t tested this yet, I would not do this.  It really does require testing on your part to make sure you don’t see a degradation.  It has happened so I am always cautious with this.
Mark

========================

Sent from my mobile device




On Oct 7, 2019, at 08:51, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
Hi Mark,

We tested on 16.0 and it’s late in the game to target 16.1 as it’s an unknown to us unfortunately.

SAP support says we need to change all HG indices to tiered to take advantage of the efficiencies in 16.0 which include better load efficiencies and compression.

This certainly is contrary to the approach we talked about last year which was to just rebuild all FPs.

Why does the retier only take seconds and if it does only take seconds why not do them ( what’s a miss on this?)

Thanks
David


From: Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Sent: Monday, October 7, 2019 9:37 AM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: iqug at dssolutions.com<mailto:iqug at dssolutions.com>; Venuturapalli, Sharan <sharan.venuturapalli at blackrock.com<mailto:sharan.venuturapalli at blackrock.com>>
Subject: Re: [IQUG] [iqug] 16.0 index rebuilds


External Email: Use caution with links and attachments
Just to plant the seed....    if you don’t go to 16.1 sp03 or later you will need to do another rebuild then too.

Why are you going to tiered HG?   First, that operation takes seconds.   More importantly are you sure that you need them?  I know that the benefit is hit or miss.  I’ve not seen much help with them but some have.

Other than that, the process looks fine.
Mark

========================

Sent from my mobile device





On Oct 7, 2019, at 08:09, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
Hello All,

Another 16.0 migration set of questions.

Our 15.4 db is 50+ TB and contains a large 30 TB table.  We know we need to rebuild FP indices post upgrade and then rebuild HG as tiered,  (sp_iqrebuildindex(<table>, HG, retier).   We know we will need to do a phased approach as it is impossible to do this much work over 1 weekend.


  *   Weekend 1   upgrade in place to 16.0,  rebuild all critical tables FP indices
  *   Weekend 2   rebuild all large tables FP indices  (30TB table included)   Not all of the FP indices on the 30 TB table will be done and will need to be phased
  *   Weekend 3  and beyond   continue to rebuild the 30 TB FP indices and misc HG indices as tiered.

First is the approach correct and is what most shops are doing?

Second would there be a performance hit as we cannot get all HG indices Tiered and partial FP rebuilds on various table ( like the 30 TB table)

We have a problem creating HG indices on the 30 TB table. ( dropping LF indices to make them HG).  Ran out of temp space and our server stack traced and needed to be rebooted.

Is there a way to determine how much temp space is required to recreate an HG index on a table?

Are LF indices completely deprecated in 16.0 and will we need to recreate them as HG’s day 1?

Thanks
David


This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/about-us/contacts-locations.

© 2019 BlackRock, Inc. All rights reserved.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug<https://urldefense.proofpoint.com/v2/url?u=http-3A__iqug.org_mailman_listinfo_iqug&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=arUtzb-yBy0xIltyDhDd2IvViSKamsuP6rS9Rqflfpk&s=EP4sOdpefHwQzmzq8pPpq0HQqH6Z0OKEy9TLFDW8dsI&e=>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20191007/e5e63b45/attachment-0001.html>


More information about the IQUG mailing list