Summary of ways to validate the use of indexes in Gemfire

Products

• VMware Tanzu Gemfire

Issue/Introduction

In GemFire, indexes allow faster data retrieval based on indexed fields and are a crucial component for optimizing query performance. To validate, or measure, if indexes implemented in GemFire are being properly utilized, you can use various monitoring and diagnostic tools provided by GemFire. This article summarizes practical ways to validate or measure if indexes are being fully utilized.

Environment

Product Version: 9.15

Resolution

Here are some practical ways to achieve this validation:

1. Setup TRACE level logging in your queries by either adding the "<TRACE>" flag to your queries for specific queries, or setting the system property "gemfire.Query.VERBOSE" to "true" to enable trace level logging on all queries.

 2. You can also set up trace level logging for queries, by modifying the logging configuration in log4j2.xml to enable query execution tracing. Here's how you can do it:    

(1) Open the log4j2.xml file, which is typically located in the config directory of your GemFire installation.

(2) Add the following lines to the configuration to enable query execution tracing:
 

<Logger name="org.apache.geode.cache.query.internal.DefaultQuery" level="trace" additivity="false">
  <AppenderRef ref="Console"/>
</Logger>

This configuration sets the logging level to TRACE for the org.apache.geode.cache.query.internal.DefaultQuery class, which handles query execution in GemFire. The log messages will be sent to the console appender.

After making these changes, GemFire will start logging query execution traces to the specified appender. You will see detailed information about the executed queries, including the query text, execution time, and index usage.

Keep in mind that TRACE-level logging can generate a significant amount of log data, so use it judiciously and only for debugging or diagnostic purposes. Make sure to revert the log level to an appropriate level (e.g., INFO or DEBUG) in a production environment to avoid performance impact due to excessive logging.

Here are the example output from the above settings:

[info 2023/07/19 10:00:00.000 UTC <GemFire Server Query Execution Thread> tid=0x1] Executing query: SELECT * FROM /regionName WHERE field1 = 'value'
[trace 2023/07/19 10:00:00.001 UTC <GemFire Server Query Execution Thread> tid=0x1] Query execution time: 2 ms
[trace 2023/07/19 10:00:00.001 UTC <GemFire Server Query Execution Thread> tid=0x1] Using index: field1_index for query: SELECT * FROM /regionName WHERE field1 = 'value'

3. In Gemfire statistics archive files, there are also some statistics that can indicate the utilization of the index. In particular, IndexStats “numUses” will show how many times an index was used.

The "numUses" statistic specifically tracks the number of times an index is utilized to satisfy a query since the index was created or since the last statistics reset. This metric is helpful in understanding the efficiency and effectiveness of an index in accelerating query execution.

Monitoring the "numUses" statistic can provide valuable insights into how often an index is being leveraged to improve query performance. A high value for "numUses" indicates that the index is frequently used, which suggests that the index is effective in accelerating queries. On the other hand, a low value for "numUses" might indicate that the index is not being utilized as often as expected, and further investigation may be needed to determine the reasons for underutilization.

4. Also there are a few INDEX specific JMX methods that can be used, such as number of index in use. Here are some of the key JMX attributes and operations related to indexes: 

• IndexStats: This MBean provides various statistics related to an index, including the number of times the index has been used, the number of keys it contains, and more. You can use the getNumUses() method to get the number of times the index has been utilized to accelerate queries.
• IndexMBean: This MBean allows you to manage indexes, including creating, destroying, and rebalancing indexes. You can use the createIndex() method to create a new index programmatically or through JMX.
• RegionIndexStats: This MBean provides statistics for all the indexes in a specific region. You can use the getIndexStats() method to get the statistics for all indexes in the region.
• IndexMetadata: This MBean provides metadata information for an index, such as the index name, region name, indexed expression, and more. You can use the getIndexName() and getIndexedExpression() methods to retrieve information about a specific index.

5. Finally, gfsh also has support for displaying index usage stats. You can use the following gfsh command to check the index utilization:

gfsh>list indexes --with-stats=true