Skip to content

Instantly share code, notes, and snippets.

@zkenstein
Forked from stfeng2/cloud_sql_flags.md
Created January 8, 2024 06:47
Show Gist options
  • Save zkenstein/96cfbb72f07d0a4d6ad5ad904772e501 to your computer and use it in GitHub Desktop.
Save zkenstein/96cfbb72f07d0a4d6ad5ad904772e501 to your computer and use it in GitHub Desktop.
Cloud SQL Flags Tuning

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.

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