Possible slow query

[colins]

We (the MySQL team) have received a customer support query about a slow query. The query is taking 18 seconds. JIRA The query is doing a large join.

SELECT count(*) AS `count` FROM (SELECT DISTINCT `service_instances`.* FROM `service_instances` INNER JOIN `spaces` ON (`spaces`.`id` = `service_instances`.`space_id`) LEFT JOIN `service_instance_shares` ON (`service_instance_shares`.`service_instance_guid` = `service_instances`.`guid`)) AS `t1` LIMIT 1;
# Time: 2022-09-01T09:04:11.389209Z
# Schema: ccdb  Last_errno: 0  Killed: 0
# Query_time: 18.541919  Lock_time: 0.000479  Rows_sent: 1  Rows_examined: 144329  Rows_affected: 0  Bytes_sent: 60

This appears to be the corresponding model https://github.com/cloudfoundry/cloud_controller_ng/blob/0645a640be62cef0a524164d15aaf93957ee5458/app/models/services/service_instance.rb#L37

[philippthun]

Hi @colins,

What is the CAPI version? Which endpoint is called (what parameters)? Is this the exact SQL query? The LEFT JOIN without WHERE condition does not make sense...

[colins]

What is the CAPI version? name: capi version: 1.117.7

Which endpoint is called (what parameters)? mysql_proxy VM.

Is this the exact SQL query? Yes. We only trimmed out the IP addresses from the log lines.

I'm guessing if you run this spec you'll see that query in the rails log https://github.com/cloudfoundry/cloud_controller_ng/blob/5b358a0e69213bfcb24e401f52e6f6339f660c6e/spec/unit/actions/service_instance_share_spec.rb#L13

Here's the JIRA ticket for reference: https://pivotal-io.atlassian.net/browse/MYSQL-74

[philippthun]

I ran the mentioned spec file but don't see such an SQL statement in the logs. To reproduce the issue, we would need to know which API endpoint is called (e.g. /v2/service_instances or /v3/service_instances) and which parameters are provided (e.g. space_guids=...). This would allow us to check if the SQL statement looks the same in current CAPI versions or has already been changed; there have been quite some significant performance improvements / query optimizations been taking place in the last year.