Magento 1 and Google cloud platform SQL: Mysqlserver has gone away

Question

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

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!

Answer 1

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.