by Joe Leslie

NuoDB: Index Management and Performance Improvements

save time

Everyone likes to save time, right? After all, it even feels good to gain time back. You can do something more important or fun with that new found time or just get to the next part of your day a bit sooner. We like our little time savers -- like pre-ground coffee, automatic garage door openers, or a even that magic button on your refrigerator that, when pressed, deposits ice in your drink!

Well at NuoDB, we realize the DBA has a number of tasks that periodically need to be performed throughout the day, week or month, and that some of those tasks can be automated to gain back valuable time. In fact, NuoDB has always had maintenance free indexes. This saves our customers time by not requiring DBAs to schedule special time to run index rebuilds to rebalance and reorganize indexes. Yep, that’s right, NuoDB automatically rebuilds indexes as needed in the background, making indexes rebuilds a thing of the past.

In NuoDB 3.0, we continue this great tradition of saving our DBAs more time to do other things -- by eliminating that all too common task of scheduling jobs to refresh index statistics! NuoDB’s new index processing introduced in v3.0 refreshes indexes stats auto-magically! While your NuoDB database runs, and you are accomplishing other important tasks, NuoDB is busy ensuring that all your user indexes remain up to date with current index statistics.

So Why is Refreshing Indexes Statistics So Important?

After all, this regular task of refreshing index statistics is an accepted best practice that has been passed down generationally from DBA to DBA for decades now. In real estate the three foundational pillars are well-known and accepted to be -- location, location, location. For database, it’s performance, performance, performance! We always want the best possible performance we can squeeze out the database system to keep our application users happy! And refreshing indexes statistics by running the ANALYZE TABLE|Index command is one of those tasks that helps contribute to optimal  database performance.

As your application performs DML (inserts updates and deletes), your indexes take a beating -- in more technical terms, they become fragmented. Over time, the internal index structure yields a higher percentage of free space, which results in SQL statements reading more index pages when scanning the index. Likewise, the once organized index becomes fragmented, and index pages no longer reside in contiguous pages, which results in more reads to retrieve ordered data. But no worries, as mentioned above, NuoDB is busy in the background constantly rebalancing your indexes, and now in v3.0, also refreshing the index statistics on-the-fly! Therefore, having constantly updated indexes structures -- and now index statistics -- ensures the NuoDB query plan optimizer is utilizing optimal index structures and updated index statistics to deliver the best query performance possible.

Additional Index Performance Improvements

As a bonus, NuoDB v3.0 indexes now offer improved query plan choices at run-time. Instead of a histogram bin size of 10, the new default is 256. This affords NuoDB the opportunity to make better indexing choices at runtime when using indexes with higher cardinality, further improving application performance.

To check your user indexes and their histogram bin size, run the following SQL command, and look at the MAXRESOLUTION column.

SQL> use system;
SQL > select  
     h.maxresolution, i.schema, i.tablename, i.indexname, 
     i. indextype, i.statstype, i.keycount, i.valid 
from indexstatistics i 
left join indexhistograms h on ( i.objectid = h.objectid and i.catalogid = h.catalogid)
where schema != 'SYSTEM';

index table 1

How do you take advantage of these new features you ask? It’s simple -- just upgrade to NuoDB 3.0 and rebuild your user indexes. The rebuilding of the indexes will upgrade your indexes from Ver1 to Ver2 (as indicated by STATSTYPE). From this point forward index statistics will be automatically updated periodically without user intervention. We recommend that this one-time upgrade is offline to ensure the application’s performance is not impacted.

Determining Index Version

Now you know how awesome our new indexes are, but you aren’t sure which you’re using? Here’s how you figure out whether your indexes are Ver1 or Ver2. Run the following SQL and view the STATSTYPE column:

SQL > use system;                                                                
SQL > select objectid, schema, tablename, indexname, statstype                   
   from indexstatistics                                                       
   where schema != 'SYSTEM';

You’ll be presented with a table like this one, and you’ll want to view the STATSTYPE column to determine which index version you’re using:
index table 2

To automate the process of rebuilding your user indexes after your v3.0 upgrade, please visit GitHub General Utilities for NuoDB. Click the green Download button, and follow the on-screen instructions to rebuild your user indexes. That’s it! And enjoy some time back never having to run index statistic refresh commands again!


Joe LeslieAs Senior Product Manager at NuoDB, Joe Leslie helps drive NuoDB product releases and roadmap to ensure NuoDB's database leadership position delivering elastic SQL Database scale-out and continuous availability for Hybrid Cloud Applications. Joe has over 20 years of experience delivering database products and management tools in the transactional and analytical database market place.