Maintained Temporary Indexes briefly explained
In part 1 of this series, we described the kinds of temporary storage that the SQL optimizer uses. We showed how the QSYS2.SYSTMPSTG system view provides a high-level overview of temporary storage across the system. There are five global buckets that directly reflect usage by the SQL optimizer. Of these buckets, this article focuses on the one labeled, *DATABASE DSI SQE MTI. This bucket reports the amount of storage used across the system by maintained temporary indexes.
Maintained Temporary Indexes (MTIs) are indexes that the optimizer creates without any user intervention. They function like any other index on the system. Internally, they are radix indexes, just like permanent (that is, user created) indexes, and they provide the ability to read rows in keyed order or to probe for specific values. Like any permanent index, the actual size of an MTI is heavily dependent on the keys included in the index and on the underlying data. They are actively maintained, meaning that any change in the dataspace is immediately reflected in the MTI. In many cases, MTIs can be shared between queries, just like permanent indexes. But they differ from permanent indexes in two important ways: they use temporary storage, and they are managed entirely by the optimizer. They are created whenever the optimizer needs them and deleted when the optimizer is finished with them.
In simplest terms, there is only one reason for the optimizer to build an MTI: no suitable permanent index exists to meet the requirements of a query. If you have the right indexes on your tables, the optimizer won’t need to build an MTI. But, if we drill down into this single basic reason, we’ll find a couple of distinct circumstances that cause the optimizer to need an index. Understanding each circumstance—and whether it applies to your workload—is crucial to effectively managing your usage of MTIs.
The first circumstance that might produce MTIs is when the optimizer decides that an MTI can provide a more efficient implementation for a query than existing indexes can provide. If the estimated cost to build the MTI seems reasonable to the optimizer, it will generate the MTI and then use it to run the query. These MTIs are shared: they will be seen by and can be used by other queries. The lifecycle of shared MTIs is tied to the existence of the underlying dataspace and to the presence in the SQL plan cache of one or more queries that use the MTI. If the dataspace is deleted or if all related plans are evicted from the plan cache, the MTI will also be deleted. (Because they use temporary storage, MTIs also do not exist beyond the current IPL.)
The second circumstance that might produce MTIs is when queries perform ordering or grouping in an environment that uses a sensitive cursor or that has the
ALWCPYDTA parameter set to
*NO. When the query also has a predicate (
WHERE clause), the optimizer may build the MTI as a sparse index, meaning that only those rows that match the predicate are included in the index. Sparse MTIs have a lifetime that is tied to the query that created them. When that query is hard closed, the MTI is deleted. While this behavior might seem beneficial in preventing MTIs from accumulating, it also means that sparse MTIs cannot be shared across queries. This can be a problem if a workload has hundreds or thousands of these queries. Each query will build its own sparse MTI, filling temporary storage. This becomes even more problematic if the applications do not close their cursors promptly. IBM support has seen more than one critical client situation like this, which could easily have been prevented by modifying the execution environment or by building a handful of permanent indexes.
Most of the time MTIs just work and require little attention from a database engineer or a system administrator. MTIs are just one more tool that the optimizer uses as it silently pursues its goal of giving your queries the fastest possible execution. Still, there are two important reasons to monitor MTI usage on your system.
Monitoring for temporary storage
The first reason for monitoring MTIs is because too many and too large MTIs can have a negative effect on system capacity. Because MTIs are built on temporary storage, they take up space in the system auxiliary storage pool (ASP), even when the associated dataspace resides in another storage pool. As your system nears its temporary storage limits, processes slow or stop and effective recovery might require an initial program load (IPL).
Monitoring MTIs is especially important if your system allows users to run custom queries. Is your workload limited to applications running a predictable set of queries, or can some users generate their own ad hoc queries, in raw SQL or through use of a reporting or BI tool? If ad hoc queries are permitted, your system is more susceptible to variation in MTI usage. A query that is poorly written—or that simply does not have adequate supporting indexes—might kick off the creation of an MTI. The more this happens, the larger your MTI temporary storage usage will grow.
You can monitor your current MTI usage by consulting the QSYS2.SYSTMPSTG view and looking for the GLOBALBUCKET_NAME of
*DATABASE DSI SQE MTI. This will tell you _how much storage MTIs are using.
You can also evaluate how many MTIs are currently active on your system. Point in time information is available from the Plan Cache tab of the IBM i Access Client Solutions SQL Performance Center, under the Plan Usage Summary section. The Total Number of Temporary Indexes Created entry counts the number of times an MTI has been created since the last IPL. The Current number of Temporary Indexes entry tells how many MTIs are currently active on the system.
Figure 1. Plan cache details from the SQL Performance Center
You can find historical information about the number of MTIs with the performance data available from IBM Navigator for i. Data is gathered by collection services. So, make sure that this is enabled on any system you want to monitor. Within IBM Navigator for i, the following path will give you a graph as shown in Figure 2.
Performance -> Investigate Data -> Database -> SQL Performance Data -> Collection Services -> Maintained Temporary Indexes (MTIs).
Figure 2. MTI graph from IBM Navigator for i
Note: The MTIs Deleted (shown in Figure 2) is not accurate in releases prior to IBM i 7.3.
By looking at data across multiple days and weeks you can get an idea of the baseline behavior for your system. The bar graph shows MTIs as they are created and deleted, and the line graph displays the total number of active MTIs. Because the size of MTIs might vary from workload to workload, there is no correct or optimal number of MTIs to achieve. But if your system is running low on temporary storage and you see that
*DATABASE DSI SQE MTI is a large contributor, this graph is a good place to start. The performance data helps you understand and identify spikes in MTI usage that correlate to increased storage usage. Once you identify a time interval with an anomalous number of MTI creations, you may then be able to identify certain jobs or applications that triggered the MTI activity.
Monitoring for optimal query performance
The second reason for monitoring MTIs is that they can be a clue that the optimizer is running under unnecessary constraints. This constraint can be a cursor sensitivity or an ALWCPYDTA setting that is overly strict. It may also be that the optimizer does not have the permanent indexes that it needs to do its job well. Addressing these constraints in depth is beyond the scope of this article, but the strategies and resources in the following sections are a good place to start.
Drilling down for details
After determining that your system has a problem with over-use of MTIs, a good place to start is the Index Advisor. This is because index advice is generated every time an MTI is created or used. (Advice may be generated under other conditions too, but those are not of immediate interest here.) The following steps show you how to use IBM i Access Client Solutions (Index Advisor to find the most frequently used MTIs. (Make sure you’re running the latest version of IBM i Access Client Solutions. See the resources section on the right side of this article. IBM Navigator for i shows similar information in the Database -> All Tasks -> Health and Performance ->**Index Advisor** path.
Within IBM i Access Client Solutions, select your system and click the Actions. Then, under Database, click Schemas.
Figure 3. IBM i Access Client Solutions
On the menu bar, click Actions -> Index Advisor -> Advised Indexes.
Figure 4. Schemas window within IBM i Access Client Solutions
Within the list of advised indexes, the MTI Used, MTI Created, and MTI Last Used columns will help you determine which schemas and tables are most frequently using MTIs. If the performance graph (shown in Figure 2) is showing spikes in MTI creation, finding correlated times in the First Advised for Query Use or the MTI Last Used columns may help you identify new or changed workloads that have begun using MTIs.
Figure 5. Index Advisor
Note: In the Index advisor (shown in Figure 5), some of the columns have been hidden for clarity.
If this is your first time looking at the Index Advisor, you may find yourself overwhelmed by the amount of information it displays. Because the Index Advisor is continually accumulating advice as queries run, it is possible to have multiple years’ worth of data to dig through. Helpfully, there are filtering options available to narrow down the list (for example, certain time intervals). A better option, if you don’t need the index advice right now, is to clear the index advice (click the Clear All Advised Indexes… menu option shown in Figure 4) and then to let the advice accumulate over several days or weeks, as appropriate for the system’s workload. This gives you a clearer picture of how your system is performing now and a more accurate picture of the MTIs that are affecting your current configuration.
The system does not provide details on individual MTIs, but you can see the total size of all MTIs associated with a specific table. Right-click the Index Advisor entry naming the table you are interested in and click Table ->**Work With ->**Indexes. At the top of the list, above any permanent indexes belonging to the table, you may see a line summarizing the MTIs. (Remember that the index advice is a historical record and not a snapshot of the current system state. This means you may sometimes find MTIs listed in the advice but see no corresponding MTIs on a table.)
Figure 6. The size of all the MTIs associated with QSMART.BATCH_MONITOR
Note: Some of the columns in Figure 6 have been hidden for clarity.
Combining this information with the MTI usage information and the historical graph described earlier can help you decide which MTIs are the most egregious consumers of temporary storage.
Because MTIs are entirely managed by the optimizer and have no user-accessible controls, reducing MTI temporary storage usage is largely a matter of indirectly influencing the optimizer away from MTIs. Remember that the optimizer only builds MTIs when it can’t find a better way to do its job.
For example, you cannot explicitly drop MTIs (without dropping the underlying table—rarely a feasible solution.) But you can provide a permanent index that can take the place of the MTI. Index Advisor makes this easy to do by right-clicking the line of interest and clicking Create Index. The next time the optimizer processes a query against the underlying dataspace, it will see the permanent index and use this instead of the MTI. As the MTI falls out of use, it will eventually be deleted. However, note that in the case of shareable MTIs “eventually” may be a relatively long time. In the meantime, you will be paying the storage cost of both the permanent index and the MTI. Permanent indexes should not be considered a silver bullet for a system that is at or near capacity. In such critical conditions, the best strategy is to end or hold the workloads that are generating the MTIs. You can use the information gathered with the steps above together with other system data to identify and gain control of these jobs.
What this ultimately means is that the best time to recover from an MTI storage consumption problem is long before it happens. A well-considered indexing strategy combined with SQL best practices will go a long way to eliminating an over-reliance on MTIs. You can find more information about optimization settings and index advice in the database performance section in IBM Knowledge Center.
Solve a scenario
Before we end this article, let’s put these pieces together in an example scenario.
At 4:00 PM you receive an alert that your production system is reaching a critical level of temporary storage. You quickly connect to the system and query QSYS2.SYSTMPSTG. The *DATABASE DSI SQE MTI is clearly the largest bucket, and it is bigger than what you’ve ever seen before. Your next stop is the Performance view of IBM Navigator for i where you open the graph for Maintained Temporary Indexes. You see a small but noticeable upward tick in the MTIs created beginning about 2:00 PM. Moving over to Index Advisor, you scan the list for MTIs first advised around 2:00 PM. There are several MTIs, and they all cover columns belonging to your ERP application’s main fact table. You’ve spent a lot of time creating good indexes for this table, so it is surprising to see index advice here, but you notice that the advised keys are for columns that you don’t normally see together. To confirm that these are really the source of your storage problem, you show indexes for this table. The top line shows 20 MTIs totaling 150 GB in storage on your system, which is enough to push your temporary storage over the critical line.
Now you need to figure out who or what is causing these MTIs to be built. This requires a trip to the SQL Plan Cache statements, available from the IBM i Access Client Solutions SQL Performance Center. There, you can add filters for statements that ran after 2:00 PM and that referenced the fact table you identified earlier. That’s still too much to dig through, so you also filter for statements for which indexes have been advised. That narrows it down to a little over 20 queries, all of which have been run by the same job and user. With this information in hand, you track down the user behind these queries. You find that the user is trying out some analytics reports over the ERP data. But unknown to the user, the reporting tool is running with a sensitive cursor and is leaving the cursor open as the user opens a new browser tab for each report. This is the source of the MTI problem.
The first thing to do is to convince the user to stop running new reports, and (if possible) close the existing ones. Next, (if possible) you change the cursor sensitivity to asensitive. Then you can determine whether these queries will be run frequently in the future. If so, the best plan may be to identify common sets of keys and create permanent indexes to cover these keys.
Now that you understand how and why MTIs appear and what tools you possess to manage them, you’re better equipped to keep ahead of your system’s indexing needs. This in turn means better use of your system’s temporary storage resources. With the right understanding, you can help guide the IBM Db2® for i SQL optimizer as it works to make your system run as efficiently as possible.