cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sudhansu7 <...@git.apache.org>
Subject [GitHub] cloudstack pull request: CLOUDSTACK-8917 : Instance tab takes long...
Date Mon, 28 Sep 2015 11:13:25 GMT
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:
    ![screen shot 2015-09-28 at 2 19 55 pm](https://cloud.githubusercontent.com/assets/1062642/10133848/66af7c40-65fe-11e5-9ef5-ec6489c0fc06.png)
    
    After
    ![screen shot 2015-09-28 at 2 33 38 pm](https://cloud.githubusercontent.com/assets/1062642/10133852/6f512c9a-65fe-11e5-9ea1-890cf84d02b4.png)
    
    
    


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 <sudhansu.sahu@citrix.com>
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message