Cloud SQL Flags Tuning

cloud_sql_flags.md

Important Notice

• ❗ All flags and settings described here are subject to change without any notice.
• Last update: September 2017

Introduction

This document describes a set of MySQL flags that have been tuned in Google Cloud SQL (Second Generation) to make the out-of-box experience better on Google Cloud. This is for your reference only, with the hope that some of the settings might also be useful outside the Google Cloud environment.

innodb_buffer_pool_size

• Set according to VM available RAM.
• First assign 92% of total VM RAM to MySQL (“mysql_ram”).
• Then assign 80% of “mysql_ram” to innodb buffer pool.
• Therefore, innodb_buffer_pool_size = 0.92 * 0.8 of total available RAM.
• Above rules apply to "large" VMs only (>= n1-standard-8)
  • for smaller VMs, this number is proportionately lower.

Buffer Pool Initialization and Shutdown

• We enable buffer pool load and dump at startup and shutdown, that is:
  • Innodb_buffer_pool_dump_at_shutdown = ON
  • Innodb_buffer_pool_load_at_startup = ON
• This helps warming-up the buffer pool and reduces performance variation across DB reboots.

Redo Log Size

• We set the default redo log size to be 512MB
  • Innodb_log_file_size = 512MB
• Helps with write heavy workload, less likely to hit the "sync flush" state.

Networking Related Settings

• We allow larger BLOB columns and query results. We also allow more concurrent connections.
  • max_allowed_packet = 32MB
  • max_connections = 4000

InnoDB Metrics Monitoring

• By default we enable all innodb metrics in the INFORMATION_SCHEMA.INNODB_METRICS table.
  • innodb_monitor_enable = all

Dirty Page Flush (part 1)

• Use O_DIRECT to open data file and increase the io_capacity properly.
  • Innodb_flush_method = O_DIRECT
  • Innodb_io_capacity = 5000

Dirty Page Flush (part 2, for MySQL 5.7 only at the moment)

• Tune various aspects of adaptive flushing algorithm for write heavy workload
• Effectively avoid hitting the "sync flush" state for Sysbench test load up to 256 threads.
  • Innodb_flush_neighbors = 2
  • Innodb_lru_scan_depth = 2048
  • Innodb_flushing_avg_loops = 5
  • Innodb_cleaner_threads = 8

Finally, once more: please keep in mind that all above settings are subject to change without any notice.