stfeng2/cloud_sql_flags.md

## cloud_sql_flags.md

      
    Raw
  

              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.