You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by ra...@apache.org on 2015/10/27 07:49:24 UTC
[1/2] git commit: updated refs/heads/master to fb4e6ed
Repository: cloudstack
Updated Branches:
refs/heads/master 4fe56daf4 -> fb4e6ed6b
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 .
Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo
Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/c28a58a8
Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/c28a58a8
Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/c28a58a8
Branch: refs/heads/master
Commit: c28a58a8ff4ddde7b86e151ffee35ad26645e584
Parents: 3ded3e9
Author: Sudhansu <su...@citrix.com>
Authored: Mon Sep 28 16:24:26 2015 +0530
Committer: Sudhansu <su...@citrix.com>
Committed: Mon Sep 28 16:24:26 2015 +0530
----------------------------------------------------------------------
.../db/src/com/cloud/utils/db/GenericDao.java | 7 ++
.../src/com/cloud/utils/db/GenericDaoBase.java | 79 ++++++++++++++++++++
.../db/src/com/cloud/utils/db/SqlGenerator.java | 6 ++
.../com/cloud/api/query/QueryManagerImpl.java | 2 +-
4 files changed, 93 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/GenericDao.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/GenericDao.java b/framework/db/src/com/cloud/utils/db/GenericDao.java
index cb401cd..121d65d 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDao.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDao.java
@@ -268,5 +268,12 @@ public interface GenericDao<T, ID extends Serializable> {
*/
Pair<List<T>, Integer> searchAndCount(SearchCriteria<T> sc, Filter filter);
+ /**
+ * @param sc
+ * @param filter
+ * @return
+ */
+ Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T> sc, final Filter filter);
+
Map<String, Attribute> getAllAttributes();
}
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
index e75646a..29adbe2 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
@@ -135,6 +135,7 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
protected Map<String, Object> _discriminatorValues;
protected String _selectByIdSql;
protected String _count;
+ protected String _distinctIdSql;
protected Field _idField;
@@ -212,6 +213,7 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
final SqlGenerator generator = new SqlGenerator(_entityBeanType);
_partialSelectSql = generator.buildSelectSql(false);
_count = generator.buildCountSql();
+ _distinctIdSql= generator.buildDistinctIdSql();
_partialQueryCacheSelectSql = generator.buildSelectSql(true);
_embeddedFields = generator.getEmbeddedFields();
_insertSqls = generator.buildInsertSqls();
@@ -1300,6 +1302,14 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
@Override
@DB()
+ public Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T> sc, final Filter filter) {
+ List<T> objects = search(sc, filter, null, false);
+ Integer count = getDistinctCount(sc);
+ return new Pair<List<T>, Integer>(objects, count);
+ }
+
+ @Override
+ @DB()
public List<T> search(final SearchCriteria<T> sc, final Filter filter, final boolean enableQueryCache) {
return search(sc, filter, null, false, enableQueryCache);
}
@@ -1841,6 +1851,64 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
return builder.create();
}
+ public Integer getDistinctCount(SearchCriteria<T> sc) {
+ String clause = sc != null ? sc.getWhereClause() : null;
+ if (clause != null && clause.length() == 0) {
+ clause = null;
+ }
+
+ final StringBuilder str = createDistinctIdSelect(sc, clause != null);
+ if (clause != null) {
+ str.append(clause);
+ }
+
+ Collection<JoinBuilder<SearchCriteria<?>>> joins = null;
+ if (sc != null) {
+ joins = sc.getJoins();
+ if (joins != null) {
+ addJoins(str, joins);
+ }
+ }
+
+ // we have to disable group by in getting count, since count for groupBy clause will be different.
+ //List<Object> groupByValues = addGroupBy(str, sc);
+ final TransactionLegacy txn = TransactionLegacy.currentTxn();
+ final String sql = "SELECT COUNT(*) FROM (" + str.toString() + ") AS tmp";
+
+ PreparedStatement pstmt = null;
+ try {
+ pstmt = txn.prepareAutoCloseStatement(sql);
+ int i = 1;
+ if (clause != null) {
+ for (final Pair<Attribute, Object> value : sc.getValues()) {
+ prepareAttribute(i++, pstmt, value.first(), value.second());
+ }
+ }
+
+ if (joins != null) {
+ i = addJoinAttributes(i, pstmt, joins);
+ }
+
+ /*
+ if (groupByValues != null) {
+ for (Object value : groupByValues) {
+ pstmt.setObject(i++, value);
+ }
+ }
+ */
+
+ final ResultSet rs = pstmt.executeQuery();
+ while (rs.next()) {
+ return rs.getInt(1);
+ }
+ return 0;
+ } catch (final SQLException e) {
+ throw new CloudRuntimeException("DB Exception on: " + pstmt, e);
+ } catch (final Throwable e) {
+ throw new CloudRuntimeException("Caught: " + pstmt, e);
+ }
+ }
+
public Integer getCount(SearchCriteria<T> sc) {
String clause = sc != null ? sc.getWhereClause() : null;
if (clause != null && clause.length() == 0) {
@@ -1911,6 +1979,17 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
}
@DB()
+ protected StringBuilder createDistinctIdSelect(SearchCriteria<?> sc, final boolean whereClause) {
+ StringBuilder sql = new StringBuilder(_distinctIdSql);
+
+ if (!whereClause) {
+ sql.delete(sql.length() - (_discriminatorClause == null ? 6 : 4), sql.length());
+ }
+
+ return sql;
+ }
+
+ @DB()
protected Pair<List<T>, Integer> listAndCountIncludingRemovedBy(final SearchCriteria<T> sc, final Filter filter) {
List<T> objects = searchIncludingRemoved(sc, filter, null, false);
Integer count = getCount(sc);
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/SqlGenerator.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/SqlGenerator.java b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
index befe34b..864ee73 100644
--- a/framework/db/src/com/cloud/utils/db/SqlGenerator.java
+++ b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
@@ -663,4 +663,10 @@ public class SqlGenerator {
return sql.append("SELECT COUNT(*) FROM ").append(buildTableReferences()).append(" WHERE ").append(buildDiscriminatorClause().first()).toString();
}
+
+ public String buildDistinctIdSql() {
+ StringBuilder sql = new StringBuilder();
+
+ return sql.append("SELECT DISTINCT id FROM ").append(buildTableReferences()).append(" WHERE ").append(buildDiscriminatorClause().first()).toString();
+ }
}
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/server/src/com/cloud/api/query/QueryManagerImpl.java
----------------------------------------------------------------------
diff --git a/server/src/com/cloud/api/query/QueryManagerImpl.java b/server/src/com/cloud/api/query/QueryManagerImpl.java
index 6994b27..e90f213 100644
--- a/server/src/com/cloud/api/query/QueryManagerImpl.java
+++ b/server/src/com/cloud/api/query/QueryManagerImpl.java
@@ -1021,7 +1021,7 @@ public class QueryManagerImpl extends ManagerBase implements QueryService, Confi
sc.setParameters("displayVm", 1);
}
// search vm details by ids
- Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndCount(sc, searchFilter);
+ Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndDistinctCount(sc, searchFilter);
Integer count = uniqueVmPair.second();
if (count.intValue() == 0) {
// handle empty result cases
[2/2] git commit: updated refs/heads/master to fb4e6ed
Posted by ra...@apache.org.
Merge pull request #894 from sudhansu7/CLOUDSTACK-8917
CLOUDSTACK-8917 : Instance tab takes long time to load with 12K Vmsmodified 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
* pr/894:
CLOUDSTACK-8917 : Instance tab takes long time to load with 12K active VM (total vms: 190K)
Signed-off-by: Rajani Karuturi <ra...@citrix.com>
Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo
Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/fb4e6ed6
Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/fb4e6ed6
Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/fb4e6ed6
Branch: refs/heads/master
Commit: fb4e6ed6ba80605a14b30f067443b991260b8758
Parents: 4fe56da c28a58a
Author: Rajani Karuturi <ra...@citrix.com>
Authored: Tue Oct 27 12:17:55 2015 +0530
Committer: Rajani Karuturi <ra...@citrix.com>
Committed: Tue Oct 27 12:17:55 2015 +0530
----------------------------------------------------------------------
.../db/src/com/cloud/utils/db/GenericDao.java | 7 ++
.../src/com/cloud/utils/db/GenericDaoBase.java | 79 ++++++++++++++++++++
.../db/src/com/cloud/utils/db/SqlGenerator.java | 6 ++
.../com/cloud/api/query/QueryManagerImpl.java | 2 +-
4 files changed, 93 insertions(+), 1 deletion(-)
----------------------------------------------------------------------