Index is a key factor affecting the MongoDB database query performance. Meeting your query needs with a minimal number of indexes can greatly improve the database performance and reduce the storage costs. This document describes how to analyze and optimize indexes to help you break through the bottleneck in database read/write performance.
Problem Description
In daily Ops, you can log in to the TencentDB for MongoDB console and click the instance ID to enter the Instance Details page and view the following information:
Select the System Monitoring tab to view the instance monitoring data:
The CPU usage of the mongod node is too high. The CPU utilization is close to 90% or even 100%.
The disk reads/writes per second stays high continuously, and the I/O resource usage of a node accounts for 60% of that of the entire server.
The instance has a high number of slow logs, which include many find and update requests of different types. Thousands of such requests are received per second during peak hours.
Slow logs are of diverse types and have various query conditions, and all slow queries have matching indexes. Below is the log content:
By analyzing the slow logs, it is found that all query requests use the { alxxxId: 1.0, itemTagList: 1.0 } index. keysExamined and docsExamined are both set to 1498 for scan by index; however, the number of returned documents (nreturned) is only 3; that is, only 3 data entries out of the 1498 rows of data and indexes scanned meet the conditions. As can be seen, the key cause compromising the read/write performance is unreasonable index configuration.
Note:
keysExamined and docsExamined indicate the numbers of index entries and documents scanned respectively. Larger keysExamined and docsExamined values indicate that no index is created or the created index is less distinctive.
Index Optimization Process
Step 1. Collect the SQL statements
Common business query and update SQL statements as listed below:
Query based on `AlxxxId`(user ID) and `itxxxId`(one or multiple values).
`count` query based on `AlxxxId`.
Paginated query based on `AlxxxId` by time range (`createTime`). In some queries, `state` and other fields will be concatenated.
Query based on `AlxxxId`, `ParentAlxxxId`, `parentItxxxId`, and `state`.
Query based on `ItxxxId`(one or multiple values).
Query based on `AlxxxId`, `state`, and `updateTime`.
Query based on `AlxxxId`, `state`, `createTime`, and `totalStock`(number of inventory items).
Query based on `AlxxxId`(user ID), `itxxxId`(one or multiple values), and any other fields.
Query based on `AlxxxId`, `digitalxxxrmarkId`(watermark ID), and `state`.
Query based on `AlxxxId`, `itemTagList`(tag ID), and `state`.
Query based on `AlxxxId`, `itxxxId`(one or multiple values), and any other fields.
Other queries
Common business statistics count SQL statements as listed below:
Query based on `AlxxxId`, `state`, and `persxxal`.
Query based on `AlxxxId`, `state`, and `itemType`.
Query based on `AlxxxId`(user ID), `itxxxId`(one or multiple values), and any other fields.
Step 2. Get the existing indexes of the cluster
Use db.xxx.getindex() to get the collection index information. The query is complex, and there are 30 indexes in total as listed below:
name: The name of the index for which to collect statistics.
ops: The number of index hits, i.e., the number of times query requests hit an index. If the value of an index is zero or very small, the index is seldom selected as the optimal index and can be considered useless.
Use the index statistics command to get the numbers of hits of all indexes as shown below. If the value of an index is zero or very small, directly delete the index. In addition, as the business has been operated for a period of time, indexes with an ops value smaller than 10,000 can also be deleted. At this point, 30 - 11 = 19 useful indexes are retained.
Duplicate indexes caused by the query sequence
Different developers of the business have written two SQL indexes as shown below. Analysis finds that the two indexes have the same purpose, so only one of them is needed.
Analyzing the index uniqueness to remove duplicate indexes
By analyzing the combination of field modules in the collection data, you can find that the alxxxId and itxxxId fields are frequently used. By analyzing the schema information and extracting random data, you can find that the combination of these two fields are unique.
It is confirmed that any combination of the two fields represents a unique data entry. Therefore, all combinations of the two fields and any other fields are unique, and the following indexes can be combined into { itxxxId:1, alxxxId:1 }.
Optimizing useless indexes caused by non-equi query
As can be seen from the above 30 indexes, some are time fields, such as createTime and updateTime, which are used for various range queries. Range queries are non-equi queries. If range query fields are placed before index fields, index fields will fail to be indexed as shown below:
Both queries contain the updateTime field and are range queries. Except the updateTime fields, all other fields are equi queries, and fields on the right of updateTime cannot use indexes; that is, the persxxal and srcItxxxId fields of the first index and the persxxal field of the second index cannot be matched with indexes.
Set the following two indexes for the two queries:
As the fields of the two indexes are basically the same, the indexes can be optimized into the following index to ensure that more fields can be matched:
Indexes with less than 10,000 hits are removed when you delete useless indexes. However, compared with indexes with billions of hits, some indexes still have a relatively lower number of hits (like hundreds of thousands). Such indexes contain image and videoCover fields respectively as shown below:
Log in to the TencentDB for MongoDB console. On the Slow Log Query tab, lower the slow log latency threshold and analyze the logs of the two queries as shown below:
image field: It is used together with alxxxId and itxxxId for combined query. However, the combination of alxxxId and itxxxId is already unique, and the image field is totally not indexed, so the { "alxxxId" : 1, "ixxxge" : 1 } index can be deleted.
videoCover field: By analyzing logs, it can be found that videoCover is not in the query conditions, only part of queries match the { alxxxId: 1, videoCover: 1 } index, and keysExamined and docsExamined are different from nreturned. Therefore, it can be confirmed that only the alxxxId index field is matched, and the { alxxxId: 1, videoCover: 1 } index can also be deleted.
Analyzing frequent queries in logs to add optimal index
Log in to the TencentDB for MongoDB console. On the Slow Log Query tab, lower the slow log latency threshold. Use mtools to analyze queries for a period of time, and you can get the following information about frequent queries:
These frequent queries account for more than 99% of queries. By analyzing their logs, you can get information similar to the following:
As can be seen from the log, the frequent query matches the { alxxxId: 1.0, itexxagList: 1.0 } index, and there is a huge difference between the numbers of scanned data rows and returned rows: 1327 vs. 3.
The index is sub-optimal. The frequent query is a four-field equi query, and only two fields are indexed. In this case, you can optimize the index as follows: { alxxxId: 1.0, itexxagList: 1.0 , persxxal:1.0, stat:1.0}.
In addition, the log also shows that the frequent query actually has a sort and a limit. Below is the entire raw query SQL statement:
The query model consists of a common multi-field equi query, sort query, and limit, so the optimal index of the query can be one of the following two indexes:
Index 1: Index for a common multi-field equi query
Analyze the query conditions:
All four fields of the SQL statement are equi queries. Create the optimal index based on the hash, and place fields from left to right by value hash. You can get the following optimal index:
If you use the index as the optimal index, the execution process of the entire common multi-field equi query, sort query, and limit is as detailed below:
Use the { alxxxId: 1.0, itexxagList: 1.0 , persxxal:1.0, stat:1.0} index to find all data entries meeting the conditions specified by { $and: [ { alxxxId:"xxxx" }, { state: 0 }, { itexxagList: { $in: [ xxxx ] } }, { persxxal: 0 } ] }.
Perform memory sorting on the data entries meeting the conditions.
Get the first three data entries after sorting.
Index 2: Optimal index of the equi query and sort
The sort query has a limit. Find the frequent sorting SQL statement, which is as shown below:
CPU resource usage is reduced by over 90%.
After optimization, the peak CPU utilization is reduced from over 90% to below 10%.
Disk I/O resource usage is reduced by over 85%.
Disk I/O utilization is reduced from 60%–70% to below 10%.
Disk storage costs are reduced by 20%.
Each index has an index file in the disk. After 30 indexes are reduced to 8, the final actual disk usage of data entries and indexes is reduced by about 20%.
Slow logs are reduced by 99%.
Before index optimization, thousands of slow logs are generated per second. After optimization, only dozens are generated per second.
