[
https://issues.apache.org/jira/browse/CLOUDSTACK-8917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14933170#comment-14933170
]
ASF GitHub Bot commented on CLOUDSTACK-8917:
--------------------------------------------
GitHub user sudhansu7 opened a pull request:
https://github.com/apache/cloudstack/pull/894
CLOUDSTACK-8917 : Instance tab takes long time to load with 12K Vms
modified sql that is used for retrieving vm count .
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:
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
Query 2 is a problematic query.
If we rewrite the query as mentioned below then it will be ~2x faster.
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;
Mysql Test result:
With 134 active Vms (total rows 349)
mysql> select count(*) from vm_instance;
+----------+
| count(*) |
+----------+
| 349 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from user_vm_view;
+----------+
| count(*) |
+----------+
| 135 |
+----------+
1 row in set (0.02 sec)
mysql> 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;
+--------------------+
| count(distinct id) |
+--------------------+
| 134 |
+--------------------+
1 row in set (0.02 sec)
mysql> 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;
+----------+
| count(*) |
+----------+
| 134 |
+----------+
1 row in set (0.01 sec)
With 14326 active Vms (total rows 195660)
mysql> select count(*) from vm_instance;
+----------+
| count(*) |
+----------+
| 195660 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from user_vm_view;
+----------+
| count(*) |
+----------+
| 41313 |
+----------+
1 row in set (4.55 sec)
mysql> 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;
+--------------------+
| count(distinct id) |
+--------------------+
| 14326 |
+--------------------+
1 row in set (7.39 sec)
mysql> 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;
+----------+
| count(*) |
+----------+
| 14326 |
+----------+
1 row in set (2.08 sec)
UI test Results:
Before:
After
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/sudhansu7/cloudstack CLOUDSTACK-8917
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/cloudstack/pull/894.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #894
----
commit c28a58a8ff4ddde7b86e151ffee35ad26645e584
Author: Sudhansu <[email protected]>
Date: 2015-09-28T10:54:26Z
CLOUDSTACK-8917 : Instance tab takes long time to load with 12K active VM
(total vms: 190K)
modified sql that is used for retrieving vm count .
----
> 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
>
> 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)
