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 <rohit.ya...@shapeblue.com> 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 <rohit.ya...@shapeblue.com> --- .../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`)