Sybase IQ Tiered HG Indexes Recommendation

[william-paul_philibert]

Bonjour,

we are in the process of upgrading our IQ 15.4 database. We have had some performance gain by simply upgrading and then by rebuilding FP indexes into NBIT.

We have also had substantial gain in migrating HG indexes into TIERED HG Indexes.

We have recently read that SAP does NOT recommend that we tier all HG indexes and I was wondering why such a recommendation?

What are the drawbacks of having HG Tiered index?

Why not tier every HG indexes in the database?

Thanks

[c_baker]

From the IQ 16 Best Practices Guide:

"In IQ 15, load throughput could degrade as the amount of data in an HG index increased. As the index grew, loading the same amount of data could take more time. The new tiered structure decouples load performance from the HG index size to increase throughput."

Essentially, before Tiered-HG indexes, if an HG index (e.g. primary key) contained a high degree of unique values, then incremental loads into a table would take longer and longer over time (phase 2 of a traditional HG index load - when the new index values were merged into the index in the main store). Tiered-HG indexes allow the update of the index to complete in the background, after a load, so over time, incremental loading of a table (and expansion of a tiered-HG index) takes a similar amount of time as the table grows. The control here is the 'CREATE_HG_WITH_EXACT_DISTINCT' option. HG indexes created when the option is set 'on' for the user will be Tiered, otherwise they will be non-tiered (exact).

The trade-off for a faster/consistent load here is with queries. When a non-tiered HG index is used, it contains the exact distinct count of the values in the index metadata, so queries requiring that information can take advantage of the metadata in the HG index, as it is complete. For a tiered-HG index, which will complete the index update process in the background after the associated table has data added to it, the metadata may not be completely up-to-date, so additional work may be required to complete the query, increasing the query time.

There may be benefits to mixing both HG index types, but it will really depend on your load and query requirements. High cardinality indexes (e.g. PKeys) may benefit from being tiered, while some low cardinaliy HG indexes may benefit from being non-tiered. There may also be benefits for having PKeys on parent tables for RI purposes (when RI is defined in on IQ tables) be non-tiered HG so the final RI checking during the load of a child is faster.

The other thing here is also the management of the indexes. You can use sp_iqindexmetadata to check if an HG index is tiered or not, and use sp_iqrebuildindex with the 'retier' option to switch back and forth, but whether you want to do this is up to you as having 2 different types of HG indexes available may be a management headache.

As you have seen, tiered-HG is performing well for you and any advice you may have heard of probably relates more to the above discussion on the trade-offs. If IQ is performing well for you for loads and queries, then you are fine for now. I would suggest looking to Best Practices and the Sizing Guide (e.g. memory configuration) before worrying about the differences in HG index types.

Chris

[former_member199543]

I do not have an answer to your question, but where did you read that SAP does not recommend to implement this? In our scenario we do not achieve any performance leak or benefit, but I/O store now is less consumed, nut much, but still we see the benefit.