第7.2.1 mongodb索引优化

雨点打透心脏的1/2处 2022-02-20 03:38 263阅读 0赞

从下图可以看到我的4核CPU，已经满负荷运转，跑不动了，mongo都进行消耗了371.1%，那么怎么办呢？  
![1][]  
执行命令`db.getProfilingLevel()`,结果是0，参考[Mongodb高级篇-性能优化][Mongodb_-]，说明profile功能关闭  
执行下面的脚本，启动慢命令,默认是100ms

db.setProfilingLevel(1)

![1][1 1]  
在查询的db中执行命令`db.system.profile.find({millis:{$gt:300}})`,找到查询300ms的脚本  
![1][1 2]  
执行`db.serverStatus()`,连接数都是正常的  
![1][1 3]  
执行命令`iostat -m 2`,参考[Linux iostat命令详解][Linux iostat]  
用户写入mongo的cpu消耗大量cpu，但是磁盘io并不大，tps也就50+  
![1][1 4]  
查看日志,那么解读mongo的日志呢？

2019-04-12T15:30:20.229+0800 I WRITE    [conn6744] remove my_db.key_person query: { eId: "edb714f675e1ea4f681849f81886f26c" } ndeleted:1 keyUpdates:0 writeConflicts:0 numYields:16847 locks:{ Global: { acquireCount: { r: 16848, w: 16848 } }, Database: { acquireCount: { w: 16848 } }, Collection: { acquireCount: { w: 16848 } } } 15444ms
    2019-04-12T15:30:20.229+0800 I COMMAND  [conn6744] command my_db.$cmd command: delete { delete: "key_person", ordered: true, deletes: [ { q: { eId: "edb714f675e1ea4f681849f81886f26c" }, limit: 0 } ] } keyUpdates:0 writeConflicts:0 numYields:0 reslen:40 locks:{ Global: { acquireCount: { r: 16848, w: 16848 } }, Database: { acquireCount: { w: 16848 } }, Collection: { acquireCount: { w: 16848 } } } protocol:op_query 15444ms
    2019-04-12T15:30:20.297+0800 I WRITE    [conn6818] remove my_db.key_person query: { eId: "7406c37d112d302bf377fe6a8d944d5c" } ndeleted:0 keyUpdates:0 writeConflicts:0 numYields:16843 locks:{ Global: { acquireCount: { r: 16844, w: 16844 } }, Database: { acquireCount: { w: 16844 } }, Collection: { acquireCount: { w: 16844 } } } 15881ms
    2019-04-12T15:30:20.297+0800 I COMMAND  [conn6818] command my_db.$cmd command: delete { delete: "key_person", ordered: true, deletes: [ { q: { eId: "7406c37d112d302bf377fe6a8d944d5c" }, limit: 0 } ] } keyUpdates:0 writeConflicts:0 numYields:0 reslen:40 locks:{ Global: { acquireCount: { r: 16844, w: 16844 } }, Database: { acquireCount: { w: 16844 } }, Collection: { acquireCount: { w: 16844 } } } protocol:op_query 15881ms
    2019-04-12T15:30:20.316+0800 I WRITE    [conn6824] remove my_db.key_person query: { eId: "3ef65efba64828acc5a77f075bf998dc" } ndeleted:0 keyUpdates:0 writeConflicts:0 numYields:16876 locks:{ Global: { acquireCount: { r: 16877, w: 16877 } }, Database: { acquireCount: { w: 16877 } }, Collection: { acquireCount: { w: 16877 } } } 15762ms
    2019-04-12T15:30:20.316+0800 I COMMAND  [conn6824] command my_db.$cmd command: delete { delete: "key_person", ordered: true, deletes: [ { q: { eId: "3ef65efba64828acc5a77f075bf998dc" }, limit: 0 } ] } keyUpdates:0 writeConflicts:0 numYields:0 reslen:40 locks:{ Global: { acquireCount: { r: 16877, w: 16877 } }, Database: { acquireCount: { w: 16877 } }, Collection: { acquireCount: { w: 16877 } } } protocol:op_query 15762ms

mongo的操作时间太长了，要15+s，于是根据字段创建索引`db.key_person.createIndex({eId:1})`,mongo的操作时间可以提升300倍  
再监控cpu，可以发现cpu已经大幅度下降了。  
![1][1 5]  
再次监控磁盘io,tps已经可以达到2311了，这样的话，就可以再多启动进程了。对于数据库来说，创建索引简直就是王道，剩下关键的问题，就是怎么合理的创建索引，和利用资源了  
![1][1 6]

[1]: /images/20220220/45fb8b63d9f34adf8fac050e0d5a9bb1.png
[Mongodb_-]: https://www.jianshu.com/p/b77a33fbe824
[1 1]: /images/20220220/427bb5cf13c04a11b44e69e5944fd66d.png
[1 2]: /images/20220220/5f743bf2c8d947a0a32207a118322d32.png
[1 3]: /images/20220220/f0ea37b7ca5a4c19b0c18f6788fe6e7b.png
[Linux iostat]: https://www.cnblogs.com/ftl1012/p/iostat.html
[1 4]: /images/20220220/847656749cad4ba7981b16bc5c53312b.png
[1 5]: /images/20220220/1d76297a11534d69b45a52129d107034.png
[1 6]: /images/20220220/61f6bb0c71c743b9b023581829921ff2.png