You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@cloudstack.apache.org by "Rajani Karuturi (JIRA)" <ji...@apache.org> on 2015/10/27 07:50:28 UTC
[jira] [Resolved] (CLOUDSTACK-8917) Instance tab takes long time to
load with 12K active VM (total vms: 190K)
[ https://issues.apache.org/jira/browse/CLOUDSTACK-8917?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rajani Karuturi resolved CLOUDSTACK-8917.
-----------------------------------------
Resolution: Fixed
> Instance tab takes long time to load with 12K active VM (total vms: 190K)
> -------------------------------------------------------------------------
>
> Key: CLOUDSTACK-8917
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8917
> Project: CloudStack
> Issue Type: Bug
> Security Level: Public(Anyone can view this level - this is the default.)
> Components: Management Server
> Affects Versions: 4.6.0
> Reporter: Sudhansu Sahu
> Assignee: Sudhansu Sahu
> Fix For: 4.6.0
>
>
> In load test environment listVirtualmachine takes 8-11 sec to load. This environment has around 12k active VMs. Total number of rows is 190K.
> Performance bottleneck in listVirtualmachine command is fetching the count and distinct vms.
> {noformat}
> // search vm details by ids
> Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndCount(sc, searchFilter);
> Integer count = uniqueVmPair.second();
> {noformat}
>
> This takes 95% of the total time.
> To fetch the count and distinct vms we are using below sqls.
>
> Query 1:
> {noformat}
> SELECT DISTINCT(user_vm_view.id) FROM user_vm_view WHERE user_vm_view.account_type != 5 AND user_vm_view.display_vm = 1 AND user_vm_view.removed IS NULL ORDER BY user_vm_view.id ASC LIMIT 0, 20
> {noformat}
> Query 2:
> {noformat}
> select count(distinct id) from user_vm_view WHERE user_vm_view.account_type != 5 AND user_vm_view.display_vm = 1 AND user_vm_view.removed IS NULL
> {noformat}
> Query 2 is a problematic query.
> If we rewrite the query as mentioned below then it will be ~2x faster.
> {noformat}
> select count(*) from (select distinct id from user_vm_view WHERE user_vm_view.account_type != 5 AND user_vm_view.display_vm = 1 AND user_vm_view.removed IS NULL) as temp;
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)