cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-8917) Instance tab takes long time to load with 12K active VM (total vms: 190K)
Date Mon, 28 Sep 2015 11:14:04 GMT

    [ 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:
    ![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 .

----


> 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)

Mime
View raw message