rohityadavcloud opened a new pull request, #9198: URL: https://github.com/apache/cloudstack/pull/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 Server: 8.0.36) MySQL slow query logging showed ~nearly 20M table scans of the IP address table: ``` 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); ``` Visual explain:  This may not be worse than what it already is, I'm open to more ideas and suggestions. Addresses #7910 ### Types of changes - [ ] Breaking change (fix or feature that would cause existing functionality to change) - [ ] New feature (non-breaking change which adds functionality) - [x] Bug fix (non-breaking change which fixes an issue) - [ ] Enhancement (improves an existing feature and functionality) - [ ] Cleanup (Code refactoring and cleanup, that may add test cases) - [ ] build/CI ### Feature/Enhancement Scale or Bug Severity #### Feature/Enhancement Scale - [x] Major - [ ] Minor #### Bug Severity - [ ] BLOCKER - [ ] Critical - [x] Major - [ ] Minor - [ ] Trivial -- 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: commits-unsubscr...@cloudstack.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
