日志如下有没有大佬帮忙分析一下是什么原因导致的
2021-01-12T19:58:20.403+0800 I COMMAND [conn78] command zt_monitor_test.monitor_data command: count { count: “monitor_data”, query: { timestamp: { $gte: 1513937112, $lte: 1608984427 }, MN: “SZYS20200430000030”, CN: “2011” }, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test”, $readPreference: { mode: “primaryPreferred” } } planSummary: IXSCAN { MN: -1, CN: -1, DataTime: -1 } keysExamined:123139 docsExamined:123139 fromMultiPlanner:1 numYields:972 reslen:45 locks:{ Global: { acquireCount: { r: 973 } }, Database: { acquireCount: { r: 973 } }, Collection: { acquireCount: { r: 973 } } } storage:{ data: { bytesRead: 631218950, timeReadingMicros: 1527873 } } protocol:op_msg 1904ms
2021-01-12T19:58:20.713+0800 I COMMAND [conn78] command zt_monitor_test.monitor_data command: find { find: “monitor_data”, filter: { timestamp: { $gte: 1513937112, $lte: 1608984427 }, MN: “SZYS20200430000030”, CN: “2011” }, sort: { timestamp: -1 }, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test”, $readPreference: { mode: “primaryPreferred” } } planSummary: IXSCAN { timestamp: -1, MN: -1, CN: -1 } cursorid:133859187081 keysExamined:103 docsExamined:101 fromMultiPlanner:1 numYields:3 nreturned:101 reslen:36064 locks:{ Global: { acquireCount: { r: 4 } }, Database: { acquireCount: { r: 4 } }, Collection: { acquireCount: { r: 4 } } } storage:{ data: { bytesRead: 555434, timeReadingMicros: 90417 } } protocol:op_msg 199ms
2021-01-12T19:58:21.016+0800 I COMMAND [conn78] command zt_monitor_test.monitor_data command: getMore { getMore: 133859187081, collection: “monitor_data”, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test” } originatingCommand: { find: “monitor_data”, filter: { timestamp: { $gte: 1513937112, $lte: 1608984427 }, MN: “SZYS20200430000030”, CN: “2011” }, sort: { timestamp: -1 }, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test”, $readPreference: { mode: “primaryPreferred” } } planSummary: IXSCAN { timestamp: -1, MN: -1, CN: -1 } cursorid:133859187081 keysExamined:48517 docsExamined:46765 fromMultiPlanner:1 numYields:380 nreturned:46764 reslen:16777282 locks:{ Global: { acquireCount: { r: 381 } }, Database: { acquireCount: { r: 381 } }, Collection: { acquireCount: { r: 381 } } } storage:{ data: { bytesRead: 6561600, timeReadingMicros: 158725 } } protocol:op_msg 297ms
2021-01-12T19:58:23.537+0800 I COMMAND [conn78] command zt_monitor_test.monitor_data command: getMore { getMore: 133859187081, collection: “monitor_data”, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test” } originatingCommand: { find: “monitor_data”, filter: { timestamp: { $gte: 1513937112, $lte: 1608984427 }, MN: “SZYS20200430000030”, CN: “2011” }, sort: { timestamp: -1 }, lsid: { id: UUID(“b423da01-d677-4254-ad87-79739af71e12”) }, $db: “zt_monitor_test”, $readPreference: { mode: “primaryPreferred” } } planSummary: IXSCAN { timestamp: -1, MN: -1, CN: -1 } cursorid:133859187081 keysExamined:196514 docsExamined:46714 fromMultiPlanner:1 numYields:1539 nreturned:46714 reslen:16777260 locks:{ Global: { acquireCount: { r: 1540 } }, Database: { acquireCount: { r: 1540 } }, Collection: { acquireCount: { r: 1540 } } } storage:{ data: { bytesRead: 16736075, timeReadingMicros: 439881 } } protocol:op_msg 956ms
看了你的这几个操作,zt_monitor_test.monitor_data,存在 getMore、count 操作,尽管用到了索引(走的是 IXSCAN),但一次操作遍历的数据太大了(例如123139)。如果短时间有这类大量的扫表任务对性能影响会很大。建议:
- getMore 意味着一次查询的结果集可能太大,需要多个批次才能返回,当前的语句显示查询结果集可能达到了10W以上,应用上应避免一个SQL的拖表操作。考虑设置 limit 条件,每次小批次返回数据(例如200-500)
- count 在数据量很大时对CPU 损耗也很大,尽量也避免频繁使用
根据您的建议成功的解决了问题,非常感谢