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