Skip to content

Instantly share code, notes, and snippets.

@chiragnayyar

chiragnayyar/cloud_sql_flags.md

Created May 27, 2017 14:29
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save chiragnayyar/36731bd20f6105f275461ad6dd1e037a to your computer and use it in GitHub Desktop.
Save chiragnayyar/36731bd20f6105f275461ad6dd1e037a to your computer and use it in GitHub Desktop.
Download ZIP
Cloud SQL Second Generation Tuned Flags for MySQL InnoDB
Raw
cloud_sql_flags.md

Important Notice

  • ❗ All flags and settings described here are subject to change without any notice.
  • Last update: March 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.

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