[IQUG] [iqug] 16.0 index rebuilds

John Anthony chirayithaj at gmail.com
Mon Oct 7 11:06:33 MST 2019


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> 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
>
> My Blogs: https://blogs.sap.com/author/markmumy/
>
>
>
> https://sap.na.pgiconnect.com/I825063
>
> Conference tel: 18663127353,,8035340905#
>
>
>
> *From: *John Anthony <chirayithaj at gmail.com>
> *Date: *Monday, October 7, 2019 at 12:06 PM
> *To: *Mark Mumy <mark.mumy at sap.com>
> *Cc: *David Louie <David.Louie at blackrock.com>, "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
>
>
>
> ...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> 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
>
> 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> 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>
> *Sent:* Monday, October 7, 2019 9:57 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
>
> 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> 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>
> *Sent:* Monday, October 7, 2019 9:37 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
>
> 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> 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
> 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
> http://iqug.org/mailman/listinfo/iqug
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20191007/642bc95c/attachment-0001.html>


More information about the IQUG mailing list