This is an automated email from the ASF dual-hosted git repository.
rohit pushed a commit to branch 4.19
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/4.19 by this push:
new 0f8a839a1d9 engine/schema: force index in user_vm_view to speed up VM
instance listing (#9198)
0f8a839a1d9 is described below
commit 0f8a839a1d9fafbe9d864ccfa5326915c51f3f6f
Author: Rohit Yadav <[email protected]>
AuthorDate: Thu Jun 13 12:29:11 2024 +0530
engine/schema: force index in user_vm_view to speed up VM instance listing
(#9198)
The user_vm_view can end up not picking the right index to join against
the user_ip_address table causing full table scan on the user_ip_address
table. This could be related to a MySQL bug
https://bugs.mysql.com/bug.php?id=41220
In a test environment with 20k shared networks and over 20M IPs, the
listVirtualMachines API was found to take over 17s to return list of
just 10 VMs. However, with this fix it would now take under 200ms to
return the list.
MySQL slow query logging showed ~nearly 20M table scans of the IP
address table:
```
# User@Host: cloud[cloud] @ localhost [127.0.0.1] Id: 39
# Query_time: 8.227541 Lock_time: 0.000014 Rows_sent: 12 Rows_examined:
19,667,235
SET timestamp=1715410270;
SELECT user_vm_view.id, user_vm_view.name /*snipped*/ FROM user_vm_view
WHERE user_vm_view.id IN (4,6,7,8,9,10,11,12,13,14,15,16);
```
Signed-off-by: Rohit Yadav <[email protected]>
---
.../schema/src/main/resources/META-INF/db/views/cloud.user_vm_view.sql | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git
a/engine/schema/src/main/resources/META-INF/db/views/cloud.user_vm_view.sql
b/engine/schema/src/main/resources/META-INF/db/views/cloud.user_vm_view.sql
index 25f95709721..62294ed5d89 100644
--- a/engine/schema/src/main/resources/META-INF/db/views/cloud.user_vm_view.sql
+++ b/engine/schema/src/main/resources/META-INF/db/views/cloud.user_vm_view.sql
@@ -196,7 +196,7 @@ FROM
LEFT JOIN `networks` ON ((`nics`.`network_id` = `networks`.`id`)))
LEFT JOIN `vpc` ON (((`networks`.`vpc_id` = `vpc`.`id`)
AND ISNULL(`vpc`.`removed`))))
- LEFT JOIN `user_ip_address` ON ((`user_ip_address`.`vm_id` =
`vm_instance`.`id`)))
+ LEFT JOIN `user_ip_address` FORCE INDEX(`fk_user_ip_address__vm_id`)
ON ((`user_ip_address`.`vm_id` = `vm_instance`.`id`)))
LEFT JOIN `user_vm_details` `ssh_details` ON (((`ssh_details`.`vm_id`
= `vm_instance`.`id`)
AND (`ssh_details`.`name` = 'SSH.KeyPairNames'))))
LEFT JOIN `resource_tags` ON (((`resource_tags`.`resource_id` =
`vm_instance`.`id`)
