mongodb’s current use in the industry can generally be divided into two architectures: master-slave replica sets and sharded replica set clusters.
because the sharded replica set contains the functionality of the master-slave replica set, the sharded replica set will be used as an example later.
with the growth of data volume and the increase of business pressure, performance alarm emails of mongodb sharded clusters are often received.
the few sharded clusters i maintain sometimes get 200 or so alert emails a day, which is annoying. alarm emails are broadly divided into three categories:
1. the cpu load is too high. the cpu load average value exceeds 30 and the cpu usage exceeds 50%.
2. there is not enough free memory, and the system uses more than 50% of the swap partition.
3. The IO load is too high. IOwait exceeds 40%.
performance alarms are frequent, and front-line business personnel also report from time to time that some query services are very slow. some statements that normally execute quickly also become very slow.
how to deal with the above performance problems. how to comprehensively and quickly determine the system performance bottleneck, and then give the corresponding optimization plan to make the business recover smoothly as soon as possible?
after continuously filling the pits in actual production, the following experience in diagnosing and optimizing mongodb performance problems was summarized.
first determine the system resource usage.
the simple top command can quickly determine the current approximate resource usage of the system.
view system cpu load, io load, memory usage, and more. sometimes it’s not just the pure cpu or disk io or memory that’s tight on the one hand,
it is also possible that cpu, io, or io, and memory are all under high load at the same time. therefore, we also need to use performance monitoring tools for further in-depth analysis.
mongodb performance monitoring
use the mongostat tool to view performance metrics such as the number of reads and writes per second, execution commands, number of read and write wait queues, active read and write commands, network throughput, and connections of the mongodb instance.
execute the command as follows:
the production server is port 30000 for the master library and port 30001 for the slave library.
/apps/svr/mongodb/bin/mongostat –host 127.0.0.1:30000 -uuser_name -p’password’ –authenticationDatabase=admin
the details are as follows:
through this performance monitoring tool, you can at least understand the overall number of connections, read and write requests, and read-write ratio of mongodb instances (some services have a high proportion of read requests, and some services have a high proportion of write requests).
you need to focus on the number of qr|qw read and write wait queues. if the value exceeds 3 or exceeds the number of cpu cores, it means that cpu resources are tight and business requests have begun to backlog.
use mongotop to view the reads and writes of hotspot tables in a mongodb instance.
execute the command as follows:
/apps/svr/mongodb/bin/mongotop –port 30000 -uuser_name -p’password’ –authenticationDatabase=admin
the monitor prints out the request elapsed time of the first 10 tables sorted by request time from largest to smallest.
according to the two-eight theorem, we can choose to perform targeted performance analysis and optimization for slow queries that take up more than 80% of the request time.
view the actual slow query requests performed by the current mongodb instance.
for example, use the following command to view a slow query for the msg library that is currently greater than 3 seconds.
db.currentOp({“active” : true,”secs_running” : { “$gt” : 3 },”ns” : /^msg/})
FOR BUSINESS LIBRARIES WITH A LARGE NUMBER OF AGGREGATE ANALYSIS REQUESTS, AGGREGATE ANALYSIS STATEMENTS THAT OFTEN EXCEED 100 SECONDS ARE EXECUTING FROM TIME TO TIME. THIS RESULTS IN VERY TIGHT CPU AND IO RESOURCES. AT THIS TIME, IN ORDER NOT TO AFFECT THE NORMAL BUSINESS, YOU CAN ONLY TEMPORARILY CHOOSE TO KILL MANY OF THE ACCUMULATED SLOW QUERY STATEMENTS FIRST.
You can do this by executing db.killOp(“opid”). opid is a unique identifier for slow queries.
Queries and bulk kills for business library slow SQL have been added to the database management platform Athena. Not to elaborate here.
in the above two steps, we monitor the details of the mongodb instance load in real time. what if the system load has returned to normal by the time we start dealing with performance issues? this is where the performance graph recorded in zabbix is used for analysis.
just recently, we dealt with the performance of a mongodb cluster that sends and receives messages from andrea.
let me talk about the problem handling process in its entirety.
1. first of all, i received a number of performance alert messages with high cpu load values from the mongodb server. the business side also reported that the message of the united states is very slow, and even many messages cannot be sent.
as we began troubleshooting the mongodb problem, the system load quickly dropped. there have been several similar problems in the morning before, but they are not so serious.
2. review the cpu performance graph in zabbix. the cpu load value spikes up to 250. and it was from 8:47 to 9:00, which was just ten minutes and soared. after less than 10 minutes, the cpu load began to drop sharply again.
why is the cpu load soaring so high, judging by experience. if it is a slow query with a long connection. cpu usage and load values should grow and fall relatively smoothly, not so dramatically.
it is also noted that when cpu resources increase dramatically, io and memory usage are relatively low. therefore, the guess is that the cpu pressure spike caused by the high concurrency of the short flat fast request number.
at the same time, it is also noted that the cpu load of this node alone is particularly high, and the servers of the other two nodes are relatively normal.
to analyze what tasks the alert server performed during the exception, you chose to use the mtool tool to perform a detailed slow query analysis of the mongodb master node instance.
the default slow query time of mlogfilter is 1000ms, because the previous analysis may be more short and fast requests, so the slow query time is set here to 300ms.
mlogfilter mongod30000.log –from ‘2018-11-14 08:45:01’ –to ‘2018-11-14 09:10:00’ –slow 300 > /tmp/msg31.log
view slow query details and categorize by time consumption
mloginfo /tmp/msg31.log –queries –sort sum
as shown in the figure above, the top eight slow query response times are all update write operations. some updates based on _id, as well as updates for small tables of msg-ios (8 records), are very slow, with an average execution time of 2 to 3 seconds.
normally, both statements take around 100 milliseconds to execute. because write operations are too frequent and the cpu load value is too high, normal requests are blocked. the overall database response becomes slow.
mongodb cluster data distribution details and shard key settings
the sharded cluster is only sharded 1 nodes with a particularly high cpu load, so there is initial suspicion that the hotspot table im.msg_traces shard data distribution is also uneven, and the hotspot data is concentrated on shard 1. take a look at the shard data distribution and shard keys for the heat table msg_traces and msg. all of them use _id as the hash shard key, and the data distribution is relatively even.
This is a bit confusing, three physical machines, only this CPU load is particularly high, and the data disk io %util value is also maintained at about 90%. Compare the configuration of this node with two other physical machines. The model and configuration were found to be different. Confirm with a colleague over there in virtual machines. The problem node physical machine IO throughput is indeed more than double that of the other two physical machines.
analyze the slow query log for the shard 1 node again. as shown in the following figure, there are a number of slow query statements on the found hot table msg_traces.
after communicating with development colleagues, hot table msg_traces, queries on msg, and high concurrent update statements are related to the core business of the company’s maxim message push, viewing, message status modification and so on. it is difficult to optimize the business. therefore, consider optimizing cpu/io from the following four aspects:
1. read and write splitting on the cluster architecture. all queries take precedence over reads from the library, and write operations are executed on the master library. avoid excessive mixed read and write pressure in the main library, and reduce lock conflicts for read and write records on the main library.
ReadPreference is set to secondarypreferred in connection string, and the C++ driver version is upgraded to 3.1.3 mongo-cxx-driver (driver upgrade, read/write separation takes effect).
2. hot table msg_traces index optimization
create a federated index {“_id”: 1, “account”: 1, “app_key”: 1, “s_pc”: 1} for the slow query.
3.mongodb historical data archiving and deletion
Communicate with development colleagues and keep documents from the MSGS, msg_traces collection for about a year based on actual business needs. Create a TTL index on the timestamp field. Sets the expiration time for documents to 3153600 seconds (365*24*3600).
db.msgs.createIndex( { “timestamp “: 1 }, { expireAfterSeconds: 3153600 },{background: true} )
db.msg_traces.createIndex( { “timestamp “: 1 }, { expireAfterSeconds: 3153600 },{background: true} )
The mongodb TTL index performs a delete operation on expired data every 60 seconds. The persistence of the delete operation actually depends on the load on the mongod instance.
4.journal log commitIntervalMs parameter adjustment.
increased from the default 100ms to 500ms.
at present, after reading and writing separation and index optimization, the cpu load value of the original shard 1 during the peak business period is reduced from the highest value of 250 to less than 5, and the optimization effect is very obvious.
summary of mongodb sharded cluster optimization ideas:
a particularly high shard load occurs in a sharded cluster. (often a shard load is high, if the load of multiple shard nodes is high, you need to analyze one by one)
step 1:
first, through top, iostat, vmstat, mongostat and other tools to understand the system’s approximate concurrent load and read-write ratio, observe the specific bottleneck of the system.
step 2:
if the load is only concentrated on a node, the mongotop tool first analyzes what the hotspot tables of the mongodb instance are and records them.
step 3:
Analyze the TOP10 slow queries that occur during peak business periods with the mlogfilter/mloginfo tool.
step 4:
locate the target table that needs to be optimized and optimize the query.
usually many of the same tables appear in the second and third steps. because the hot data table and the slow query table often have some of the same tables. these tables are the target of our optimization.
the optimization of the mongodb shard table starts from the following aspects:
1. view the table sharding key, data distribution, data total, data footprint and other information. focus on whether the data sharding key setting is reasonable and whether the data distribution is uniform;
2.the mloginfo tool prints out the slow query information and has the query conditions for each slow query. verify that there are suitable indexes on the slow query table that meet the query criteria for execution. the specific execution plan of a slow query needs to be analyzed in conjunction with explain().
3. select the original logs of the mongodb instance at the peak stage of the service and search for the raw query statements related to the slow query table. record these original query statements to facilitate subsequent communication with development colleagues to see if they can optimize accordingly from the business scenario.
4. for tables of log types such as logs, events, and session information, only valid data for a certain period of time can be retained according to business requirements and event fields. usually this has to do with developing the business
Communication was clear. After you confirm the retention time, you can use the mongodb TTL index feature to create an index on a specific time field and set a record expiration time limit.
step 5:
schema optimization is done for read/write separation
If the TOP10 slow query found in the third step is often a simple query that can effectively use the index, under normal circumstances, the execution should be fast (within 200ms).
you need to consider optimizing the architecture for read/write separation. because the high concurrency of the hot table reads and writes will keep the cpu busy, the original normal queries will be blocked.
in summary, the key to mongodb optimization is to identify system bottlenecks and root causes. after locating the target table that needs to be optimized, simply adding an index or doing a read/write separation, the performance problem is often solved.
THIS IS QUITE SIMILAR TO THE DOCTOR’S MEDICAL TREATMENT, THROUGH THE DATA AND REPORTS FEEDBACK FROM VARIOUS MEDICAL TESTING EQUIPMENT, AND THEN BASED ON RICH CLINICAL EXPERIENCE TO DIAGNOSE THE CAUSE. AFTER FINDING OUT THE CAUSE OF THE DISEASE, WHAT PRESCRIPTION AND WHAT MEDICINE TO PRESCRIBE IS A MATTER OF COURSE. OF COURSE, DOCTORS ARE MUCH MORE COMPLICATED THAN DIAGNOSING THE PERFORMANCE OF THE DATABASE, AND THE CHANCE OF MISDIAGNOSIS IS NOT SMALL. MOREOVER, THERE ARE STILL MANY OPPORTUNITIES FOR TRIAL AND ERROR WHEN THE DATABASE PERFORMANCE OPTIMIZATION IS NOT ACCURATE, BUT THE COST OF DOCTORS’ TRIAL AND ERROR IS RELATIVELY HIGH. SO BEING A GOOD DOCTOR SEEMS HARDER THAN BEING A GOOD DBA!
some digressions are inserted above. in the process of optimization, it is also necessary to maintain effective communication with development colleagues. when we understand the business scenarios generated by slow queries, sometimes we ask our development colleagues to cooperate with a simple function optimization, and the performance problems of headaches can be solved.