Aliyun Account for Sale: MongoDB Instance Connection Pool Full and Slow Query Blocking Troubleshooting Caused by Not Building Indexes
In the process of SEO optimization and performance tuning of the website, I encountered many frightening "accidents": the website was running smoothly one second ago, suddenly the response time of API interface soared to several seconds or even more than ten seconds, then the front end threw a large number of 504 Gateway Timeout, and the search engine Spider (such as Googlebot and Baidubot) captured the success rate instantly avalanche.
As an SEO website optimizer, I am well aware of the fatal impact of database performance on website SEO. Search engines are extremely demanding on page load speed (Core Web Vitals metrics such as TTFB, LCP, etc.). If the database is stuck, causing the front-end interface to time out, the search engine will quickly determine that your website is "unreliable" and directly cut off the crawling frequency, which will lead to the suspension of inclusion and the precipitous decline of keyword ranking.
while in use
Alibaba Cloud ApsaraDB for MongoDB
The most common "culprit" that leads to this type of performance disaster is
Chain blocking caused by slow query (Slow Query) and full connection pool (Connection Pool Exhaustion)
.
Today, I will thoroughly explain how to investigate and solve this problem from actual combat investigation, principle analysis to code and architecture optimization.
1. why does "unindexed" instantly detonate the "connection pool"?
To solve the problem, we must first make clear the relationship between the two.
snowball effect (avalanche effect)
.
1. Full table scan (COLLSCAN) runs out of CPU and I/O.
When your app initiates a query request to a MongoDB (such as retrieving by user ID, article classification, or tag), if the corresponding field
No indexing
MongoDB have to execute.
COLLSCAN
(full table scan).
It needs to read and compare the entire collection (Collection) on disk one by one. If there are hundreds of thousands or even millions of pieces of data in the collection, one query will consume a lot of CPU computing resources and disk I/O.
2. Slow query jammed the connection, and the connection pool was quickly exploded.
Web frameworks (such as Node.js, Java Spring Boot, Python Django, etc.) typically use a database connection pool (Connection Pool) to reuse connections.
Normal situation: a query is completed in 2 milliseconds, the connection is released, recycled to the connection pool, and used by the next request.
Exception: An unindexed slow query takes 3 seconds to complete. During these 3 seconds, the connection is exclusive.
The avalanche begins: as users visit or search engine Spider concurrently crawl, new requests keep pouring in. Since all previous connections are stuck by slow queries, new requests are only forced to create new ones.
Connection. In a very short period of time, the number of connections will reach the upper limit (Max Connections) of the application layer or Alibaba Cloud MongoDB instance type.
Eventually, the connection pool is completely full and all subsequent attempts to obtain a database connection are thrown.
Timeout waiting for connection from pool
, causing the entire station business paralysis.
2. Step 1: How to Use Alibaba Cloud Console and Commands to Quickly Locate Slow Queries"
When the connection pool is full, blindly restart the Web service or upgrade the MongoDB instance configuration often treat the symptoms but not the root cause. You need to follow the following standardized process to pinpoint the "culprit".
1. Use the "slow log" function of Aliyun MongoDB console
Log on to the Alibaba Cloud MongoDB console.
In the top navigation bar, select the region where the instance is located and click the target instance ID.
In the left-side navigation pane, choose CloudMonitor and Logs-> Slow Logs (or Log Management).
Set the time range for troubleshooting (that is, the start time of business error reporting), focusing on the following parameters: Execution Time (execution time): all queries over 100ms need to be vigilant, while those over thousands of milliseconds are fatal slow queries. Docs Examined (number of scanned documents) and Docs Returned (number of returned documents): if Docs Examined tens of thousands or even hundreds of thousands, and Docs Returned only a few or a dozen, this is a typical lack of index features!
Operation and Infrastructure Tips: When checking the performance of the cloud database and preparing for temporary specification changes (such as increasing CPU/memory to stop bleeding urgently) or activating Log Service (SLS) for deep log retrieval, be sure to keep the account operation and maintenance status normal. It is recommended to arrange budget planning in advance and complete the recharge of Aliyun account in the daily operation and structure adjustment to ensure that the account balance is sufficient. In this way, the advanced monitoring function of the console is limited, backup is interrupted, and even the cloud database is forcibly downgraded or locked due to arrears, which affects the golden rescue time for troubleshooting.
2. Login MongoDB execution
currentOp()
Diagnose real-time blocking
If you have read and write permissions on the MongoDB database, you can log in to the database directly through Mongo Shell or Data Management Service (DMS), and run the following command to view the currently executing and time-consuming operation:
JavaScript
// Query non-system operations that take more than 2 seconds to execute and are running
db.currentOp ({
"active": true
"secs_running": { "$gt": 2}
"ns ": {
"$ne": "local.oplog.rs"}
})
Among the returned results, focus on:
planSummary: If COLLSCAN is displayed, the statement is performing a full table scan.
client: the IP address of the application server that initiates the slow query.
command: The specific query JSON statement.
3. Step 2: Diagnosis and Index Optimization of Slow Query
After locating the specific slow query statement, we need to use the MongoDB
explain()
The analyzer diagnoses it and accurately indexes it.
1. Use
explain("executionStats")
Analyzing Query Plans
In DMS or client tools, add after the slow query statement
.explain("executionStats")
:
JavaScript
db.articles.find({ "category": "seo", "status": "published" }).sort({ "created_at": -1 }).explain("executionStats")
Focus on the core metrics in the output:
stage: If it is an COLLSCAN, an index must be added. If it is an IXSCAN, an index is used.
totalDocsExamined: The total number of documents scanned.
nReturned: Number of documents actually matched. Ideally, the totalDocsExamined should be as close to the nReturned as possible.
Stage: "SORT": If this stage occurs, it means that hard sorting (In-memory Sort) MongoDB be performed in memory, and CPU will be extremely consumed when the amount of data is large.
2. Build a composite index (Compound Index) following ESR principles
For multi-condition queries and scenarios with sorting, the creation of a composite index must follow
Principles of ESR
:
E-Equality (equal value match): Place a field that does an exact lookup (e. g. status: "published").
S-Sort (Sort): Place the field for sort() (e. g. created_at: -1).
R-Range (range query): Place the field to do range lookup (such as views: { $gt: 100 }).
Example of creating an index:
JavaScript
// Create an ESR-compliant composite index for the article collection
db.articles.createIndex (
{"status
": 1, "created_at": -1, "views": 1}
{background: true, name: "idx_status_created_views"}
)
Note: When creating an index in a production environment, it is recommended to add {background: true }(the default is background optimization build in MongoDB version 4.2) to avoid new Catton caused by locking the table during index creation.
4. Step 3: Connection Pool Parameter Tuning and Avalanche Prevention Mechanism
After solving the index problem, we also need to configure reasonable database connection pool parameters at the application layer to prevent the connection pool from being full again due to sudden traffic in the future.
1. Scientifically configure the application layer connection pool size (Max Pool Size)
Many developers mistakenly think that "the larger the connection pool, the better", which is actually a huge misunderstanding. Blind will
maxPoolSize
Setting it to 500 or even 1000 will not only consume a lot of server memory, but also reduce the overall throughput due to frequent context switching (Context Switch) by the CPU.
Recommended formula: Max Connections = (number of CPU cores * 2) number of concurrent disks
General application configuration: A single web application node with a maxPoolSize of 20 to 50 is usually sufficient to cope with high concurrency. If there are multiple application nodes, make sure that the sum of the maxPoolSize of all nodes is less than the maximum number of connections supported by the Alibaba Cloud MongoDB instance type.
Recommended connection configuration for Node.js Mongoose:
JavaScript
const mongoose = require('mongoose');
const options = {
maxPoolSize: 30, // Limit the maximum number of connections to prevent the database from being full.
minPoolSize: 5, // maintain the minimum number of idle connections
serverSelectionTimeoutMS: 5000, // Timeout for finding available servers (5 seconds)
socketTimeoutMS: 45000, // Socket read/write timeout
family: 4 // Force IPv4
};
mongoose.connect('mongodb://root:[email protected]
om:3717/admin?replicaSet=mgset-xxx', options);
2. Introduce timeout and fuse protection
When initiating database queries at the application tier, be sure to set a reasonable timeout (e. g.
maxTimeMS
) To ensure that even if you encounter complex queries, you can throw an exception and release the connection within a specified time, instead of blocking the connection pool indefinitely.
JavaScript
// Limit the maximum execution time of a single query to 2000 milliseconds.
db.articles.find({ category: "seo" }).maxTimeMS(2000);
5. SEO website optimizer summary: performance is the lifeline of SEO
As an SEO website optimizer, I often say:"
Any SEO that is out of the technical base and loading speed is a castle in the air.
”
Guardian search engine crawl budget (Crawl Budget): When the MongoDB frequently times out (504) due to unindexed and full connection pool, the crawl efficiency of search engine Spider will be greatly reduced. Timely cleaning up slow queries and optimizing the connection pool can keep the website interface responding in milliseconds and directly improve the crawl volume and collection speed of search engines.
Guaranteed Core Web Vitals Experience: Extremely fast database response is fundamental to reducing TTFB (first byte time). The faster the page is rendered, the lower the user's bounce rate, and the higher the comprehensive weight of the page in the search engine.
Focus on cloud infrastructure management: Database tuning is not only about writing good code and building good indexes, but also reflected in the daily fine operation and maintenance of cloud resources. When expanding MongoDB specifications, enabling DAS (database automatic tuning service) or log auditing, maintain good budget planning and Alibaba Cloud account recharge habits to ensure that cloud monitoring and alarm and database autonomous services are always online to prevent problems before they occur.
Through"
Slow log location-> explain() analysis-> ESR indexing-> optimize connection pool parameters
"With this set of standard closed-loop troubleshooting, you can completely solve the persistent problems of slow query blocking and full connection pool in Aliyun MongoDB, and build a fast and stable underlying database architecture for the website!
