如何实现MongoDB自动终止运行时长超10000ms的慢查询？

Absolutely, you can automate the termination of MongoDB queries that run longer than 10000ms (10 seconds). Let’s break down the most practical approaches, using built-in tools and simple scripting:

1. 使用currentOp() + 定时脚本（通用本地/自建集群方案） #

MongoDB’s currentOp() method lets you inspect all running operations, and killOp() lets you terminate them. You can wrap these in a script and schedule it to run periodically (via cron on Linux, Task Scheduler on Windows, or even a MongoDB shell script with a loop).

Here’s an example mongosh script that finds and kills queries running over 10 seconds:

// kill-long-queries.js
const thresholdMs = 10000;
const currentOps = db.adminCommand({ currentOp: 1 }).inprog;

// Filter for queries that are running longer than threshold, exclude system operations
const longRunningQueries = currentOps.filter(op => {
  return op.secs_running * 1000 > thresholdMs &&
         op.op === 'query' && // Adjust this if you want to target writes (update/remove) too
         !op['ns'].startsWith('admin.') && // Skip admin database ops
         !op['ns'].startsWith('local.'); // Skip replication-related ops
});

longRunningQueries.forEach(op => {
  print(`Killing op ${op.opid} on ${op.ns} (running for ${op.secs_running}s)`);
  db.adminCommand({ killOp: 1, opid: op.opid });
});

To run this automatically:

• Save it as kill-long-queries.js
• Schedule it with cron (e.g., run every minute):

  * * * * * /path/to/mongosh --quiet --username youruser --password yourpass --authenticationDatabase admin /path/to/kill-long-queries.js
  

• Important: Make sure the user running the script has the killOp privilege (part of the hostManager role or a custom role with this permission).

2. MongoDB Atlas托管环境的自动化方案 #

If you’re using MongoDB Atlas, you have a few low-code options:

• Atlas Triggers: Create a scheduled trigger that runs the above currentOp()/killOp() logic at regular intervals. You can write the script directly in the Atlas UI, no need for external cron jobs.
• Performance Advisor Alerts + Automation: Set up an alert for "Long Running Queries" (threshold >10s), and configure an automation hook to run the kill script when the alert fires. This is more event-driven than periodic checks.

3. 实时监听（进阶方案） #

For near-real-time termination, you can run a persistent script that polls currentOp() every few seconds (instead of scheduled intervals). Here’s a simplified example:

// real-time-kill.js
const thresholdMs = 10000;
const pollIntervalMs = 2000; // Check every 2 seconds

setInterval(() => {
  const currentOps = db.adminCommand({ currentOp: 1 }).inprog;
  const longRunningQueries = currentOps.filter(op => {
    return op.secs_running * 1000 > thresholdMs &&
           op.op === 'query' &&
           !op['ns'].startsWith('admin.') &&
           !op['ns'].startsWith('local.');
  });

  longRunningQueries.forEach(op => {
    print(`Killing long-running op ${op.opid} (${op.secs_running}s)`);
    db.adminCommand({ killOp: 1, opid: op.opid });
  });
}, pollIntervalMs);

Run this script in a background process (e.g., using nohup on Linux) to keep it running indefinitely.

关键注意事项 #

• Avoid killing critical operations: Always filter out system operations, replication tasks, or long-running batch jobs that are intentional. Adjust the filter in the script to match your workload.
• Transaction safety: Killing a query that’s part of a multi-document transaction will abort the entire transaction. Make sure your application can handle this.
• Test first: Run the script in a staging environment first to verify it doesn’t terminate unintended operations.
• Logging: Add logging to track which ops were killed, for debugging and auditing purposes.

内容的提问来源于stack exchange，提问作者kishore