Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

获取用户授权的节点太慢了 #3043

Open
jasper-zhang opened this issue Jul 29, 2019 · 4 comments

Comments

@jasper-zhang
Copy link

commented Jul 29, 2019

[简述你的问题]
获取用户授权的节点太慢了

使用版本

[请提供你使用的Jumpserver版本 1.x.x 注: 0.3.x不再提供支持]
1.5.2

问题复现步骤
  1. 给用户授权了一些节点
  2. 界面或者是ssh终端查看用户所有的节点列表时,耗时几分钟
具体表现[截图可能会更好些,最好能截全]
其他

看了下MySQL的慢查询,是如下这条语句耗时很久:

SELECT DISTINCT `assets_asset`.`id` FROM `assets_asset` INNER JOIN `assets_asset_nodes` ON (`assets_asset`.`id` = `assets_asset_nodes`.`asset_id`) INNER JOIN `assets_node` ON (`assets_asset_nodes`.`node_id` = `assets_node`.`id`) WHERE (`assets_node`.`key` REGEXP BINARY '^1:4757:17:1$|^1:4757:17:1:|^1:4810:7:0:1$|^1:4810:7:0:1:|^1:4760:0:37$|^1:4760:0:37:|^1:4760:24:0$|^1:4760:24:0:|^1:4760:1:1:3$|^1:4760:1:1:3:|^1:4774:4:37$|^1:4774:4:37:|^1:4774:4:55$|^1:4774:4:55:|^1:4799:6:0$|^1:4799:6:0:|^1:4823:2:0$|^1:4823:2:0:|^1:4760:0:105$|^1:4760:0:105:|^1:4769:8:7$|^1:4769:8:7:|^1:4950:0:1$|^1:4950:0:1:|^1:4760:0:128$|^1:4760:0:128:|^1:4780:0:19$|^1:4780:0:19:|^1:4769:22:1$|^1:4769:22:1:|^1:4792:20:0$|^1:4792:20:0:|^1:4933:2:0$|^1:4933:2:0:|^1:4804:1:1$|^1:4804:1:1:|^1:4760:26:0:0$|^1:4760:26:0:0:|^1:4775:14:2$|^1:4775:14:2:|^1:4774:16:1$|^1:4774:16:1:|^1:4769:3:4:0$|^1:4769:3:4:0:|^1:4769:7:24$|^1:4769:7:24:|^1:4760:12:3$|^1:4760:12:3:|^1:4915:1$|^1:4915:1:|^1:4789:0:10$|^1:4789:0:10:|^1:47

是为了查当前节点和其子节点时的正则查询效率很差,看能否优化下?

[注:] 完成后请关闭 issue

@hzbd

This comment has been minimized.

Copy link

commented Jul 29, 2019

同问

@ibuler

This comment has been minimized.

Copy link
Member

commented Jul 30, 2019

已修复,push 到 master分支了

@jasper-zhang

This comment has been minimized.

Copy link
Author

commented Jul 30, 2019

好的,已经看到了;现在发现瓶颈不在数据库了,而是在后面的函数add_assets_without_system_users上,其对每一个资产做信息的补充,如下:

    def add_assets_without_system_users(self, assets_ids):
        for asset_id in assets_ids:
            self.add_asset(asset_id, {})

其中每一个资产执行add_asset的平均耗时在0.7ms左右,如果我有两万台机器,就需要耗时十几秒钟,这个还能再优化一下么?

@ibuler

This comment has been minimized.

Copy link
Member

commented Aug 8, 2019

这里暂时没有想到好的办法,不过获取节点资产时优化过一次,之前是用的正则,后来改成了id__in

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.