Return-Path: 
 <issues-return-61606-apmail-cloudstack-issues-archive=cloudstack.apache.org@cloudstack.apache.org>
X-Original-To: apmail-cloudstack-issues-archive@www.apache.org
Delivered-To: apmail-cloudstack-issues-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 846AD173A5
	for <apmail-cloudstack-issues-archive@www.apache.org>;
 Mon, 28 Sep 2015 09:16:04 +0000 (UTC)
Received: (qmail 1674 invoked by uid 500); 28 Sep 2015 09:16:04 -0000
Delivered-To: apmail-cloudstack-issues-archive@cloudstack.apache.org
Received: (qmail 1494 invoked by uid 500); 28 Sep 2015 09:16:04 -0000
Mailing-List: contact issues-help@cloudstack.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:issues-help@cloudstack.apache.org>
List-Unsubscribe: <mailto:issues-unsubscribe@cloudstack.apache.org>
List-Post: <mailto:issues@cloudstack.apache.org>
List-Id: <issues.cloudstack.apache.org>
Reply-To: dev@cloudstack.apache.org
Delivered-To: mailing list issues@cloudstack.apache.org
Received: (qmail 1379 invoked by uid 500); 28 Sep 2015 09:16:04 -0000
Delivered-To: apmail-incubator-cloudstack-issues@incubator.apache.org
Received: (qmail 1376 invoked by uid 99); 28 Sep 2015 09:16:04 -0000
Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Sep 2015 09:16:04 +0000
Date: Mon, 28 Sep 2015 09:16:04 +0000 (UTC)
From: "Sudhansu Sahu (JIRA)" <jira@apache.org>
To: cloudstack-issues@incubator.apache.org
Message-ID: <JIRA.12897569.1443431480000.88466.1443431764232@Atlassian.JIRA>
In-Reply-To: <JIRA.12897569.1443431480000@Atlassian.JIRA>
References: <JIRA.12897569.1443431480000@Atlassian.JIRA>
 <JIRA.12897569.1443431480629@arcas>
Subject: [jira] [Assigned] (CLOUDSTACK-8917) Instance tab takes long time to
 load with 12K active VM (total vms: 40K)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


     [ https://issues.apache.org/jira/browse/CLOUDSTACK-8917?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Sudhansu Sahu reassigned CLOUDSTACK-8917:
-----------------------------------------

    Assignee: Sudhansu Sahu

> Instance tab takes long time to load with 12K active VM (total vms: 40K)
> ------------------------------------------------------------------------
>
>                 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 40K.
> 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)