Skip to content

Instantly share code, notes, and snippets.

@stfeng2
Last active January 8, 2024 06:47
Show Gist options
  • Save stfeng2/7143a08e8921ef4a88b3404e827390c9 to your computer and use it in GitHub Desktop.
Save stfeng2/7143a08e8921ef4a88b3404e827390c9 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.

@dorgan
Copy link

dorgan commented Feb 8, 2023

Are there any updates to this for MySQL 8.0?

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