mlsorensen opened a new pull request, #7922:
URL: https://github.com/apache/cloudstack/pull/7922
### Description
This PR addresses slowness in FirstFitPlanner, looking for a cluster with
capacity for new VM.
Adding an index to `cluster_details.name` yields a 5-10x speed improvement
in this query, and 2-3x improvement in overall time to create 1000 VMs in
parallel during scale testing.
Using slow query logging, creating 1000 VMs with 100 parallel workers:
```
# Query_time: 19.002664
SELECT DISTINCT capacity.cluster_id FROM `cloud`.`op_host_capacity`
capacity INNER JOIN `cloud`.`cluster` cluster on (cluster.id =
capacity.cluster_id AND cluster.removed is NULL) INNER JOIN
`cloud`.`cluster_details` cluster_details ON (cluster.id =
cluster_details.cluster_id ) WHERE capacity.data_center_id = 1 AND
capacity_type = 1 AND cluster_details.name= 'cpuOvercommitRatio' AND
((total_capacity * cluster_details.value ) - used_capacity + reserved_capacity)
>= 100 AND capacity.cluster_id IN (SELECT distinct capacity.cluster_id FROM
`cloud`.`op_host_capacity` capacity INNER JOIN `cloud`.`cluster_details`
cluster_details ON (capacity.cluster_id = cluster_details.cluster_id ) WHERE
capacity.data_center_id = 1 AND capacity_type = 0 AND cluster_details.name=
'memoryOvercommitRatio' AND ((total_capacity * cluster_details.value) -
used_capacity + reserved_capacity) >= 1073741824);
# Query_time: 20.131617
SELECT DISTINCT capacity.cluster_id FROM `cloud`.`op_host_capacity`
capacity INNER JOIN `cloud`.`cluster` cluster on (cluster.id =
capacity.cluster_id AND cluster.removed is NULL) INNER JOIN
`cloud`.`cluster_details` cluster_details ON (cluster.id =
cluster_details.cluster_id ) WHERE capacity.data_center_id = 1 AND
capacity_type = 1 AND cluster_details.name= 'cpuOvercommitRatio' AND
((total_capacity * cluster_details.value ) - used_capacity + reserved_capacity)
>= 100 AND capacity.cluster_id IN (SELECT distinct capacity.cluster_id FROM
`cloud`.`op_host_capacity` capacity INNER JOIN `cloud`.`cluster_details`
cluster_details ON (capacity.cluster_id = cluster_details.cluster_id ) WHERE
capacity.data_center_id = 1 AND capacity_type = 0 AND cluster_details.name=
'memoryOvercommitRatio' AND ((total_capacity * cluster_details.value) -
used_capacity + reserved_capacity) >= 1073741824);
```
Same test after index:
```
# Query_time: 1.139716
SELECT DISTINCT capacity.cluster_id FROM `cloud`.`op_host_capacity`
capacity INNER JOIN `cloud`.`cluster` cluster on (cluster.id =
capacity.cluster_id AND cluster.removed is NULL) INNER JOIN
`cloud`.`cluster_details` cluster_details ON (cluster.id =
cluster_details.cluster_id ) WHERE capacity.data_center_id = 1 AND
capacity_type = 1 AND cluster_details.name= 'cpuOvercommitRatio' AND
((total_capacity * cluster_details.value ) - used_capacity + reserved_capacity)
>= 100 AND capacity.cluster_id IN (SELECT distinct capacity.cluster_id FROM
`cloud`.`op_host_capacity` capacity INNER JOIN `cloud`.`cluster_details`
cluster_details ON (capacity.cluster_id = cluster_details.cluster_id ) WHERE
capacity.data_center_id = 1 AND capacity_type = 0 AND cluster_details.name=
'memoryOvercommitRatio' AND ((total_capacity * cluster_details.value) -
used_capacity + reserved_capacity) >= 1073741824);
# Query_time: 1.025688
SELECT DISTINCT capacity.cluster_id FROM `cloud`.`op_host_capacity`
capacity INNER JOIN `cloud`.`cluster` cluster on (cluster.id =
capacity.cluster_id AND cluster.removed is NULL) INNER JOIN
`cloud`.`cluster_details` cluster_details ON (cluster.id =
cluster_details.cluster_id ) WHERE capacity.data_center_id = 1 AND
capacity_type = 1 AND cluster_details.name= 'cpuOvercommitRatio' AND
((total_capacity * cluster_details.value ) - used_capacity + reserved_capacity)
>= 100 AND capacity.cluster_id IN (SELECT distinct capacity.cluster_id FROM
`cloud`.`op_host_capacity` capacity INNER JOIN `cloud`.`cluster_details`
cluster_details ON (capacity.cluster_id = cluster_details.cluster_id ) WHERE
capacity.data_center_id = 1 AND capacity_type = 0 AND cluster_details.name=
'memoryOvercommitRatio' AND ((total_capacity * cluster_details.value) -
used_capacity + reserved_capacity) >= 1073741824);
```
### Types of changes
- [ ] Breaking change (fix or feature that would cause existing
functionality to change)
- [ ] New feature (non-breaking change which adds functionality)
- [ ] Bug fix (non-breaking change which fixes an issue)
- [x] Enhancement (improves an existing feature and functionality)
- [ ] Cleanup (Code refactoring and cleanup, that may add test cases)
### Feature/Enhancement Scale or Bug Severity
#### Feature/Enhancement Scale
- [ ] Major
- x ] Minor
#### Bug Severity
- [ ] BLOCKER
- [ ] Critical
- [ ] Major
- [x] Minor
- [ ] Trivial
### Screenshots (if appropriate):
### How Has This Been Tested?
Tested upgrade path locally:
```
4.18.0.0 to 4.18.1.0:
DEBUG [c.c.u.d.DatabaseAccessObject] (main:null) (logid:) Created index
i_cluster_details__name
...
DEBUG [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Upgrade completed
for version 4.18.1.0
mysql> show indexes from cluster_details where
Key_name="i_cluster_details__name";
+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment | Index_comment | Visible | Expression |
+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| cluster_details | 1 | i_cluster_details__name | 1 |
name | A | 2 | NULL | NULL | | BTREE |
| | YES | NULL |
+-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
```
In case index already exists:
```
4.18.0.0 to 4.18.1.0 where index already exists:
DEBUG [c.c.u.d.DatabaseAccessObject] (main:null) (logid:) Index
i_cluster_details__name already exists
...
DEBUG [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Upgrade completed
for version 4.18.1.0
```
<!-- Please read the
[CONTRIBUTING](https://github.com/apache/cloudstack/blob/main/CONTRIBUTING.md)
document -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
