How do I work around MySQL Bug #69721 (Block Nested Loop making things slower)

[Krzysztof Drys]

Recently we experienced problems with our MySQL instance. One hour our db was running fine (CPU usage at about 20%), next hour CPU is at 100% and queries are stuck at "creating sort index" phase. After restarting DB everything was normal again.

We suspect that it was MySQL Bug #69721 (https://bugs.mysql.com/bug.php?id=69721). As suggested here (https://community.sugarcrm.com/thread/33079-queries-getting-stuck-on-creating-sort-index-slowing-sugar-down), one option to mitigate this bug is to set optimizer-switch=block_nested_loop=off. 

However, as far as I can see, it is not possible to set this option in Cloud MySQL. Do you know any other way to work around this bug?

Thanks,

Krzysztof Dryś 

[Harmit Rishi (Cloud Platform Support)]

Thank you for using Google Groups.

Considering that Cloud SQL offers MySQL instances, I believe this issue should be brought to the attention of the engineering team behind the Cloud SQL product. Due to this, I encourage you to follow the steps to file a Public Issue Tracker here[1].

Also, doing so will ensure that this issue is getting the appropriate exposure to the engineering team as Google Groups is meant for discussion purposes. Let me know if you are able to file a Public Issue Tracker.

[1] Public Issue Tracker: https://cloud.google.com/support/docs/issue-trackers

[Krzysztof Drys]

Thanks, I issued https://issuetracker.google.com/issues/129897196

On Thu, Apr 4, 2019 at 1:57 AM 'Harmit Rishi (Cloud Platform Support)'
via Google Cloud SQL discuss

<google-cloud...@googlegroups.com> wrote:
>
> Thank you for using Google Groups.
>
>
> Considering that Cloud SQL offers MySQL instances, I believe this issue should be brought to the attention of the engineering team behind the Cloud SQL product. Due to this, I encourage you to follow the steps to file a Public Issue Tracker here[1].
>
>

> Also, doing so will ensure that this issue is getting the appropriate exposure to the engineering team as Google Groups is meant for discussion purposes. Let me know if you are able to file a Public Issue Tracker for this.

>
>
> [1] Public Issue Tracker: https://cloud.google.com/support/docs/issue-trackers
>
> On Wednesday, February 27, 2019 at 2:26:49 PM UTC-5, Krzysztof Drys wrote:
>>
>> Recently we experienced problems with our MySQL instance. One hour our db was running fine (CPU usage at about 20%), next hour CPU is at 100% and queries are stuck at "creating sort index" phase. After restarting DB everything was normal again.
>>
>> We suspect that it was MySQL Bug #69721 (https://bugs.mysql.com/bug.php?id=69721). As suggested here (https://community.sugarcrm.com/thread/33079-queries-getting-stuck-on-creating-sort-index-slowing-sugar-down), one option to mitigate this bug is to set optimizer-switch=block_nested_loop=off.
>>
>> However, as far as I can see, it is not possible to set this option in Cloud MySQL. Do you know any other way to work around this bug?
>>
>> Thanks,
>> Krzysztof Dryś
>

> --
> You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/cPeyrQ4Rbz0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/68475f91-8f40-4dbb-80ff-64e76e5f0dc0%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Femi jacob]

Hi,

Please how were you able to resolve this eventually, I am also stuck in the same situation.

Thanks

[yananc]

Hi, 

As seen on the public issue and the internal one, the feature for setting optimizer-switch=block_nested_loop=off is still under the evaluation of the Cloud SQL Engineering team. Please kindly note that there is no ETA or guarantee on the implementation. Thank you.

[Ahmed Lotfy]

Hi,

We are facing this issue as well and it impacts our business, the only solution we have now is to move our databases to AWS, there should be a workaround for this at least from Google.