Improve performance: database indexes and slow queries

[Ankit K]

Performance Degradation of a transaction could possibly reside on the Network, Browser or the Server. When the slowness is on the database server it is usually slow due to non optimal queries which take most of the transaction time. A quick way to optimize these queries is by creating indexes on them to improve query response time. Creating indexes on the queries will improve the overall transaction time. Adding the appropriate indexes to the slow queries can drastically improve the overall instance slowness issues.

How to add database indexes in ServiceNow

The process of Creating a Database index post Fuji is easy. In terms of performance though, one of the most important question for ServiceNow admins is how or what index should be applied to help improve transaction response times. Lets take a step back and ask ourselves what is a database index?

"A Database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure."

Slow queries is what we are targeting on which you would applying the indexes.

DO's

1. Always test your indexes in a sub production instance first and see if it helps improve performance before applying in production.
2. Always consider using "active" in your filter condition as part of your business logic. This enables the query to process on a smaller result set and helps improve query response time.
3. Use "is", "starts with" in your filter/searches wherever possible with "AND" in the query.
4. Date fields such as "sys_created_on" or "sys_updated_on" should always be considered in filters/searches which enhances the performance of the query.
5. Adding a composite/compound index on single large flattened tables to the "active" and "date_time" fields is bound to give you improved query response time if those are the two fields being used in the query.

DO NOT's

1. Indexes occupy space on disk in memory buffers and there can only be 64 INNODB MySQL indexes per table. Hence do not index every field as that defeats the purpose.
2. It is not recommended to index data type such as TEXT, MEDIUM TEXT, LONG TEXT if your search string is going to be long. These are fields which can hold multibyte characters such as short description, comments, and work-notes. Index on these fields may not useful because it only indexes first few characters. But if your search string is small for example you are searching for incidents with"memory low" the index could be beneficial.
3. Avoid using "contains" , "OR's" in your filters/searches wherever possible as the MySQL optimizer may end up doing a full table scan.

Just a side note, MySQL is a command prompt shell deducing the syntax of the relational query language. It is the storage engine which is the underlying layer that has the commit, rollback, and crash-recovery capabilities to protect user data. ServiceNow MySQL customer databases are on InnoDB storage engine which is ACID compliant. Most MySQL indexes are stored in B-trees as the underlying data structure.

How to verify if there is a slow query involved responsible for the slowness:

1. In the Filter navigator type "SQL."
2. Click on "Debug SQL DETAILED."

Navigate to the form/list/report that is slow and scroll down until you notice the slow query which is taking a long time.

Here, the response time is approximately 68 secs seconds out of which 67 seconds or 1.07 minutes were spent executing the slow query. If you hover the mouse over the periods next to "SELECT" it should tell you the columns being selected. If this query has made it to the slow query log you should be able to the see the explain plan and determine what index it is using if it is using one at all.

How to view the Explain Plan to determine the slow query

An explain plan is a representation of a query execution. MySQL optimizer considers different execution plans before devising the optimal one. It helps us read exactly what possible indexes/keys MySQL considered and what it actually went with finally. For further details Use a slow query log for reference.

1. 1. Navigate to Slow Queries.
   2. Use the Filter to retrieve your query (Ex: Created on and the Example URL)

      

   3. Click on the Record and and click on Explain Plan on the right hand corner

      

   4. Scroll down and click on Related Lists to see the explain plan.

Notice the possible keys (indexes) available for the MySQL optimizer but the one being used is task_index1(active,sys_class_name,number) for the task table going over 60K rows. How do I know what columns are in the index key? You can find the indexes for a give table by navigating to the Tables and Columns module and selecting the table.

For example, here we want to figure out the columns included for TASK table(key: task_index1)

This query can be optimized by using a better index which iterates through a smaller set of rows and retrieves the result faster.

Note: If reference fields are involved in the database query ex(a_ref_1) which are not visible in Tables and Column module, contact support for assistance as this cannot be done via the Index Creation Module.

Now here comes the crucial part. Depending on the distribution of data and the query plan above, there are a few options we can think of

Option 1. Follow the order of the fields in the where clause and created an index key(sys_class_name,active,sys_created_by); This is called a covering index.

Option 2. Create an index based on the distribution of data to narrow the subset and create an index key (active,sys_class_name,sys_created_by);

Option 3. A higher cardinality column especially for Range Comparisons ( sys_created_on   for "> "or "<") is always a preferred choice as it means less efficient storage, but faster read performance. This due to the fact that it navigates through less number of branches of the B- tree and narrows down the result set. Hence index key(sys_created_by,sys_class_name,active); can be considered

Note: A rule of thumb as far as ServiceNow is concerned is to use "active" as part of you query option and part of the index to reduce the result set and increase the query efficiency

Why do I say the above statement? I tested my theory and based on the results, Option 3 was faster and better having a smaller set to process with higher cardinality

Table: Task

Total number of rows: 150K

Option 1. The execution time was 29 secs (index key: index_compostite(sys_class_name,active,sys_created_by))

Option 2) Here the response time was 30 secs (index key: index_alternative(active,sys_class_name,sys_created_by))

Option 3. Execution time was 0.12ms (index key: index_cardinality(sys_created_by,sys_class_name,active,))

Its not just about the WHERE Clause - consider Selectivity and Cardinality

There is more to indexing than just using the "where" clause and selecting the columns. Determining the right index comes down to two major factors: selectivity and cardinality. These should be considered when creating an index.

Selectivity is calculated by: Selectivity of index = cardinality/(number of rows) * 100%

Selectivity is the variety in the values for a column of a table by the total number of rows in it. Cardinality is the uniqueness in the values. Cardinality of a column can be found using the "DISTINCT" command in MySQL. It is easier when you can do a sql command directly from MySQL Command box in Background Scripts. This is unavailable for admins post Geneva so you may need to use an alternative.

Using this concept, an index could be applied when the number of matching rows that need to be selected is small in relation to the total number of rows. In other words index the columns with high cardinality first to narrow down the result set. In this case, the difference is hardly any to make a significant difference. In this scenario, our index key (index key: index_alternative(active,sys_class_name,sys_created_by)) was better as it opted for a binary search kind of scenario which shortened the result set into half and broke it down for faster retrieval. Hence, as I mentioned before it all depends on the number of rows in the MySQl table and how they are distributed.

The more indexes you have the harder MySQL optimizer has to work and may incorrectly use/ignore an index and use a table scan instead. The above article is for ServiceNow customer admins to understand how indexes are administered by Service Now engineers and things they consider before making a decision.

Thank you database guru Gurnish Anand for your guidance.