1

I would like to know if there's anyone in here having experience with google cloud SQL platform.

We are experimenting a very annoying issue which is very hard to troubleshoot.

Randomly, there's an "mysql server has gone away" popping out.

Our architecture is using Compute engines machine connected to a google cloud SQL database.

We don't have that many possibility to customize the my.cnf, since it's a managed service.

Here is a copy of the show variables: https://pastebin.com/Xaswtt72

I will add an example of an incident and the corresponding screenshot in New Relic

Screenshot new relic

Screenshot new relic2

As you can see, the connect takes forever. While i checked in the cloud SQL plateform logs, the CPU was not charged at all (less than 10%), neither the RAM/network/I/O

We know that our code can be highly optimized, for sure, but still, we don't understand why with such a oversized architecture (10 front servers, database has 8vcpu and 30go of ram, and 500GO of SSD storage) we are experimenting those kind of issues, specially when our website is not loaded that much (less than 60RPM average).

Google support can't find anything. If one of you would have any kind of lead it would be already a big help.

Thanks in advance!

6
  • Please post to pastebin.com TEXT results of SHOW GLOBAL STATUS; for basic server workload analysis. Jan 23, 2020 at 20:11
  • Please post here, TEXT results of A) SHOW CREATE TABLE aw_arp_blocks; and B) SHOW CREATE TABLE cms_block. Is there any reason the WHERE has two identical 'FIND_IN_SET' requests? Jan 23, 2020 at 20:29
  • 1
    Hello @WilsonHauck and thanks for your help. Here is the pastebin for SHOW GLOBAL STATUS. pastebin.com/q26CcNT0 Regarding the other things, I just posted an example, but this could happen with any queries, since it's the connectivity issue. I did not posted what happened before. I will have a call with Google today regarding this issue, if they are able to help, i'll keep you updated, but in the meanwhile, if you have some hints, i'd take them gracefully. Thank you!
    – AnthonyMAP
    Jan 24, 2020 at 10:28
  • Thanks for the SGS data. Analysis in process. Please post here, TEXT results of A) SHOW CREATE TABLE aw_arp_blocks; and B) SHOW CREATE TABLE cms_block; . Is there any reason the WHERE has two identical 'FIND_IN_SET' requests? Jan 24, 2020 at 15:20
  • Any chance you can post the two requested SCT's? Feb 7, 2020 at 16:39

1 Answer 1

0

Rate Per Second = RPS

Suggestions to consider for your Google Cloud Database Flags, some may require Google Fully Managed Assistance, most you can dynamically change.

innodb_lru_scan_depth=100  # from 2048 to conserve 90% of CPU cycles used for function
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 299,879
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS of 492,665
join_buffer_size=1M  # from 8M to conserve RAM per connection
sort_buffer_size=2M  # from 8M to conserve RAM per connection - sort_merge_passes will go up
table_definition_cache=8000  # from 1400 to support your opened_table_definitions RPhr of 2,886
table_open_cache=60000  # from 2000 to support your opened_tables RPhr of 36,187
max_connections=250 # from 4030 to conserve RAM and support your max_used_connections of 138 with room to grow
thread_cache_size=100  # from 48 to reduce threads_created count of 163 in 2 days
innodb_change_buffer_max_size=15  # from 25 (percent) set aside from buffer pool

Observations, A) RAM needs to be doubled to support Delete, Insert, Update RPS of 2,949 B) select_scan RPS of 134 indicates the need for indexes to avoid table scans.

For additional assistance, view profile, Network profile for free Utility Scripts to assist with performance tuning. Disclosure; I am the author of the website mentioned.

4
  • I will try all of your recommendations, thanks a lot. In the meanwhile, i'm waiting for Google cloud's support feedback, because with the informations I have, I don't see why we got so many outages. I did not know your website, i'll make a good use of it. In the meanwhile, I noticed kind of a memory issue on Mysql Cloud. The memory usage is set at 2/3 of the total ram a few hours after the database restarted and never goes down. That does not look like a standard Mysql behavior and they are investigated on it. I'll let you know!
    – AnthonyMAP
    Jan 27, 2020 at 16:11
  • @AnthonyMAP Thanks for your comment. Skype chat works well to cover this many questions and suggestions. Jan 27, 2020 at 18:22
  • @AnthonyMAP Any progress with implementing Suggestions of Jan 24, 2020? Jan 31, 2020 at 15:29
  • @AnthonyMAP I see you have not been back here since Feb 10, are you OK now? Apr 1, 2020 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.