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 .
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
