[IQUG] [iqug] 16.0 index rebuilds

Mumy, Mark mark.mumy at sap.com
Thu Oct 10 08:00:39 MST 2019


I’m shuddering at what you were told….

An FP in IQ 15 is no different than an FP in IQ 16.0.  The difference is in the low cardinality columns where we can convert from an FP to n-bit.  However, the support logic is flawed.  Support you have a high cardinality column in IQ and migrate to 16.x.  When you rebuild that column, you end up with the same flat FP structure that existed before.  OK, not 100% the same but very very close.  And our optimizer knows how to handle this because we give you the ability to totally turn off n-bits in IQ 16, not that I would recommend it.

Here's my take…

  *   Rebuild all columns (FP/n-bit) where the cardinality is 2^20 or less (20 bits).  20 bits is the default size for an n-bit in IQ 16.  If a column has 1048576 values or fewer, then we can use an n-bit.  We want to use an n-bit.  This will have the most impact on performance.
  *   For anything that is current a flat FP with a cardinality of more than 2^20, then there’s not the pressing need to rebuild it in IQ 16.0.  In IQ 16.1 SP03 and later, we want to rebuild ALL columns so that we can get zone maps implemented across the board.
  *   Using Zone Maps requires that the FP be rebuilt for ALL cardinality types.  We want to use this for performance and the possibility of reducing other indexes.
  *   Ultimately, we do want to rebuild high cardinality columns, too, but it is not a pressing need.  The main reason is two-fold:
     *   Variable fields get written in variable lengths rather than the fixed width they were in IQ 15.x (read this as smaller on disk)
     *   We introduces a statistic on the varchar fields that tracks the max USED size as opposed to the max DEFINED size.  This is critical for good performance an optimal memory usage.  We use the USED size to calculate memory consumption so that we don’t over allocate caches during execution.  Imagine a field that was defined as a varchar(250) with 250 bytes DEFINED as compared to the data that only ever used 10 bytes  in that field.  The USED is 10 bytes while the DEFINED is 250.  When trying to allocate RAM for processing, IQ 15 would use something to the effect of ROWS x DEFINED.  For 1 million rows, it would be 250 MB (250 DEFINED x 1 million rows).  In IQ 16 with the enhancement, IQ would only allocate 10 MB (10 USED x 1 million rows).  That’s a huge difference in the impact to RAM and the processing time that IQ needs to shuffle all that data around.

The second part of this bothers me too.  When IQ rebuilds anything, we do so in a new part of the cache/disk.  Read that as NEW pages.  This is the core of IQ’s MVCC (multi-version concurrency control).  All changes happen on new pages so that the old pages, the old structures, the old data can still be used.  Killing an index rebuild should not be catastrophic.  It should simply rollback the new pages being built and leave you with the old structure.  If it were to lead to corruption, that’s on us.  You can never guarantee that IQ will always be running and never quit while an operation is in flight.  Suppose the machine/rack/data center lost power during a rebuild.  Totally unforeseen and unexpected, but what support says is that it can lead to corruption?  If so, then how can anyone ever expect to rebuild indexes if an unplanned outage could happen.  Oh and there is this little thing called ACID compliance, transactional integrity, MVCC, etc that should all cover those cases.  I wouldn’t be so worried about this.  I wouldn’t do it all the time, but if you need to kill something, you need to kill it.  Protect yourself with running an sp_iqcheckdb on the column/index after the kill and rollback to just to make sure that the structure is intact.

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: David Louie <David.Louie at blackrock.com>
Date: Thursday, October 10, 2019 at 7:14 AM
To: John Anthony <chirayithaj at gmail.com>, Mark Mumy <mark.mumy at sap.com>
Cc: "markdmumy at gmail.com" <markdmumy at gmail.com>, IQ Group <iqug at dssolutions.com>, "Venuturapalli, Sharan" <sharan.venuturapalli at blackrock.com>
Subject: RE: [IQUG] [iqug] 16.0 index rebuilds

Mark,

Regarding the FP index rebuilds there are some tables which cannot do to 100% completion but Tech Support is telling us we must not leave a 16.0 table with partial rebuilt FPs (some cols done and others not).  What fantasy world are they living in this can’t possibly be true.

Also to confirm another fact  we should not be killing the FP sp_iqindexrebuild as it may lead to table corruption.

Thanks
David


From: John Anthony <chirayithaj at gmail.com>
Sent: Monday, October 7, 2019 2:07 PM
To: Mumy, Mark <mark.mumy at sap.com>
Cc: Louie, David <David.Louie at blackrock.com>; Mark Mumy <markdmumy at gmail.com>; 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
or a new one - IQ Indexing for Big Data !

On Mon, Oct 7, 2019 at 1:02 PM Mumy, Mark <mark.mumy at sap.com<mailto:mark.mumy at sap.com>> wrote:
What I need to do is change my index blog from 2018…..  😊. That would be the smarter move.

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://urldefense.proofpoint.com/v2/url?u=https-3A__blogs.sap.com_author_markmumy_&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=LvAmb4762E8YTwLwlpfz_xD5J47msYvHmOw8fXG-XW0&s=Lc0R9syQVnDlSyFhs6I1aDjwst3PcaUPKUmXYLAZr-o&e=>

https://sap.na.pgiconnect.com/I825063<https://urldefense.proofpoint.com/v2/url?u=https-3A__sap.na.pgiconnect.com_I825063&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=LvAmb4762E8YTwLwlpfz_xD5J47msYvHmOw8fXG-XW0&s=1XyY1SV2PkyJx_3dB9hsPZx9kUAlePmULwliVYPA1_k&e=>
Conference tel: 18663127353,,8035340905#

From: John Anthony <chirayithaj at gmail.com<mailto:chirayithaj at gmail.com>>
Date: Monday, October 7, 2019 at 12:06 PM
To: Mark Mumy <mark.mumy at sap.com<mailto:mark.mumy at sap.com>>
Cc: David Louie <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>, "markdmumy at gmail.com<mailto:markdmumy at gmail.com>" <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>, IQ Group <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

...and keep this write up handy to show Tech Support when they when they question you about missing indices :-) !

On Mon, Oct 7, 2019 at 11:53 AM Mumy, Mark <mark.mumy at sap.com<mailto:mark.mumy at sap.com>> wrote:
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://urldefense.proofpoint.com/v2/url?u=https-3A__blogs.sap.com_author_markmumy_&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=LvAmb4762E8YTwLwlpfz_xD5J47msYvHmOw8fXG-XW0&s=Lc0R9syQVnDlSyFhs6I1aDjwst3PcaUPKUmXYLAZr-o&e=>

https://sap.na.pgiconnect.com/I825063<https://urldefense.proofpoint.com/v2/url?u=https-3A__sap.na.pgiconnect.com_I825063&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=LvAmb4762E8YTwLwlpfz_xD5J47msYvHmOw8fXG-XW0&s=1XyY1SV2PkyJx_3dB9hsPZx9kUAlePmULwliVYPA1_k&e=>
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>" <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> on behalf of David Louie <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Date: Monday, October 7, 2019 at 9:27 AM
To: "markdmumy at gmail.com<mailto:markdmumy at gmail.com>" <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Cc: "Venuturapalli, Sharan" <sharan.venuturapalli at blackrock.com<mailto:sharan.venuturapalli at blackrock.com>>, IQ Group <iqug at dssolutions.com<mailto: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<mailto:markdmumy at gmail.com>>
Sent: Monday, October 7, 2019 10:12 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
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=>
_______________________________________________
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=LvAmb4762E8YTwLwlpfz_xD5J47msYvHmOw8fXG-XW0&s=gEg1xjjoscvIN0Y_X5iPJK1uz23GgR03tLVoCXBpXyQ&e=>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20191010/9955da61/attachment-0001.html>


More information about the IQUG mailing list