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`)

Reply via email to