Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Cloud SQL Flags Tuning

Important Notice

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


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.


  • 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.


This comment has been minimized.

Copy link

@relgisri relgisri commented Nov 26, 2020

Woah thank you for this!
I am currently looking into finetuning or CloudSQL MySQL instance and gathering information on how things happen.
This information here really helps!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment