Embedded Files
Check
Check
To see the current values used by a running server ...
SHOW VARIABLES;
You can modify it further like any query:
SHOW VARIABLES LIKE '%pool%';
Or query the information schema with something like...
SELECT @@variable_name
or...
SELECT variable_name,
variable_value
FROM performance_schema.global_variables
WHERE variable_name LIKE '%pool%';
To see the values that a server uses based on its compiled-in defaults and any option files that it reads...
mysqld --verbose --help
To see the values that a server uses based on only its compiled-in defaults, ignoring any option files...
mysqld --no-defaults --verbose --help
Variables
Variables
- innodb_flush_log_at_trx_commit
- innodb_log_buffer_size
- innodb_log_file_size
- innodb_log_files_in_group
- innodb_log_group_home_dir
- innodb_rollback_segments
- long_query_time
- slow_query_log
- slow_query_log_always_write_time
- slow_query_log_file
- slow_query_log_use_global_control
- log_slow_admin_statements
- log_slow_filter
- log_slow_rate_limit
- log_slow_rate_type
- log_slow_slave_statements
- log_slow_sp_statements
- log_slow_verbosity
- log_queries_not_using_indexes
- log_throttle_queries_not_using_indexes
- binlog_expire_logs_seconds
- expire_log_days (deprecated)
- max_binlog_size
- max_binlog_files
- log_replica_updates
- log_slave_updates (deprecated)
- innodb_default_row_format
- innodb_file_per_table
- innodb_checksum_algorithm
- max_heap_table_size
- innodb_buffer_pool_size
- innodb_buffer_pool_chunk_size
- innodb_buffer_pool_instances
- max_execution_time
- max_connections
- interactive_timeout
- wait_timeout
- innodb_file_format
- open_files_limit
- innodb_io_capacity
- innodb_io_capacity_max
- innodb_flush_method
- innodb_fsync_threshold
- innodb_use_fdatasync
- innodb_use_native_aio
- innodb_write_io_threads
- innodb_read_io_threads
- ssl_ca
- ssl_cert
- ssl_key
- ssl_version
- generated_random_password_length
- default_authentication_plugin
- secure_log_path
- binlog_transaction_dependency_tracking
- enforce_gtid_consistency
- gtid_mode
- server_id
- slave_parallel_type
- slave_preserve_commit_order
Update
Update
SET GLOBAL parameter=value;
To make the change permanent you should also update /etc/my.cnf
Sample my.cnf
Sample my.cnf
Memory safetyThese configs will not Out Of Memory (OOM) under normal use because:
- Per-connection buffers are controlled
- Buffer pool is capped
- Temp tables are limited
One-size-per-tier is intentionalAvoid per-application tuning at build time. Tune after observing:
- Buffer pool hit ratio
- CPU saturation
- Disk latency
- Slow query log
Common Settings
Common Settings
Ready-made starter my.cnf files are available later on this page.
[mysqld]# Basicuser = mysqldatadir = /var/lib/mysqlsocket = /var/lib/mysql/mysql.sockpid-file = /var/run/mysqld/mysqld.piddefault-storage-engine = InnoDB
# Character setcharacter-set-server = utf8mb4collation-server = utf8mb4_0900_ai_ci
# Networkingbind-address = 0.0.0.0skip-name-resolve = ON
# Logginglog-error = /var/log/mysql/error.logslow_query_log = ONslow_query_log_file = /var/log/mysql/slow.loglong_query_time = 1log_queries_not_using_indexes = OFF
# Durability / safetyinnodb_flush_log_at_trx_commit = 1sync_binlog = 1
# Binary logging (safe default even if unused)server-id = 1log-bin = mysql-binbinlog_format = ROWbinlog_expire_logs_seconds = 604800 # 7 days
# Tablesinnodb_file_per_table = ON
# Limitsopen_files_limit = 65535table_open_cache = 4000table_definition_cache = 4000
# Character setcharacter-set-server = utf8mb4collation-server = utf8mb4_0900_ai_ci
# Networkingbind-address = 0.0.0.0skip-name-resolve = ON
# Logginglog-error = /var/log/mysql/error.logslow_query_log = ONslow_query_log_file = /var/log/mysql/slow.loglong_query_time = 1log_queries_not_using_indexes = OFF
# Durability / safetyinnodb_flush_log_at_trx_commit = 1sync_binlog = 1
# Binary logging (safe default even if unused)server-id = 1log-bin = mysql-binbinlog_format = ROWbinlog_expire_logs_seconds = 604800 # 7 days
# Tablesinnodb_file_per_table = ON
# Limitsopen_files_limit = 65535table_open_cache = 4000table_definition_cache = 4000
Small
Small
Ready-made starter my.cnf files are available later on this page.
# Memoryinnodb_buffer_pool_size = 4Ginnodb_buffer_pool_instances = 1innodb_log_file_size = 1Ginnodb_log_buffer_size = 64M
# Connectionsmax_connections = 100thread_cache_size = 50
# Per-connection buffers (keep tight)sort_buffer_size = 2Mjoin_buffer_size = 2Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mtmp_table_size = 64Mmax_heap_table_size = 64M
# I/Oinnodb_io_capacity = 1000innodb_io_capacity_max = 2000innodb_read_io_threads = 4innodb_write_io_threads = 4
# Connectionsmax_connections = 100thread_cache_size = 50
# Per-connection buffers (keep tight)sort_buffer_size = 2Mjoin_buffer_size = 2Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mtmp_table_size = 64Mmax_heap_table_size = 64M
# I/Oinnodb_io_capacity = 1000innodb_io_capacity_max = 2000innodb_read_io_threads = 4innodb_write_io_threads = 4
Medium
Medium
Ready-made starter my.cnf files are available later on this page.
# Memoryinnodb_buffer_pool_size = 10Ginnodb_buffer_pool_instances = 2innodb_log_file_size = 2Ginnodb_log_buffer_size = 128M
# Connectionsmax_connections = 250thread_cache_size = 100
# Per-connection bufferssort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mtmp_table_size = 128Mmax_heap_table_size = 128M
# I/Oinnodb_io_capacity = 2000innodb_io_capacity_max = 4000innodb_read_io_threads = 8innodb_write_io_threads = 8
# Connectionsmax_connections = 250thread_cache_size = 100
# Per-connection bufferssort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mtmp_table_size = 128Mmax_heap_table_size = 128M
# I/Oinnodb_io_capacity = 2000innodb_io_capacity_max = 4000innodb_read_io_threads = 8innodb_write_io_threads = 8
Large
Large
Ready-made starter my.cnf files are available later on this page.
# Memoryinnodb_buffer_pool_size = 22Ginnodb_buffer_pool_instances = 4innodb_log_file_size = 4Ginnodb_log_buffer_size = 256M
# Connectionsmax_connections = 500thread_cache_size = 200
# Per-connection bufferssort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mtmp_table_size = 256Mmax_heap_table_size = 256M
# I/Oinnodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_read_io_threads = 8innodb_write_io_threads = 8
# Connectionsmax_connections = 500thread_cache_size = 200
# Per-connection bufferssort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mtmp_table_size = 256Mmax_heap_table_size = 256M
# I/Oinnodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_read_io_threads = 8innodb_write_io_threads = 8
Other
Other
Percona Only
Percona Only
secure-log-path = /var/log
Cookie Cutter Configs
Cookie Cutter Configs
Small
Small
CPU: 2RAM: 8Storage: SSD
Max Connections: 50-100
Suitable for:
Notes:
Max Connections: 50-100
Suitable for:
- Internal Tools
- Low-Traffic Apps
- PoC
- Non-Critical Workloads
Notes:
- Easy to starve if connections spike
- Avoid reporting queries
- Expect to resize
Medium
Medium
CPU: 4RAM: 16Storage: SSD/NVMe
Max Connections: 200-300
Suitable for:
Notes:
Max Connections: 200-300
Suitable for:
- Most production OLTP systems
- Line-of-business apps
- APIs and web backends
- Unknown or evolving workloads
Notes:
- Handles most OLTP workloads comfortably
- Good balance of memory and concurrency
- Excellent “cookie-cutter” default
Large
Large
CPU: 8RAM: 32Storage: NVMe
Max Connections: 500+
Suitable for:
Notes:
Max Connections: 500+
Suitable for:
- High-traffic OLTP
- Mixed OLTP + reporting
- Larger datasets
- Latency-sensitive systems
Notes:
- Still conservative on per-thread memory
- Beyond this size, query design and architecture matter more
- Consider replicas before going bigger
Bibliography
Bibliography
https://dev.mysql.com/doc/refman/5.7/en/server-system-variable-reference.htmlhttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.htmhttps://dev.mysql.com/blog-archive/mysql-8-0-persisting-configuration-variables/
https://gist.github.com/rhtyd/d59078be4dc88123104ehttps://stackoverflow.com/questions/14597884/mysql-my-ini-location https://stackoverflow.com/questions/1493722/mysql-command-for-showing-current-configuration-variableshttps://dev.mysql.com/doc/refman/8.0/en/option-files.htmlhttps://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.htmlhttps://bugs.mysql.com/bug.php?id=15684https://dev.mysql.com/doc/refman/8.0/en/performance-schema-variables-info-table.html
https://gist.github.com/rhtyd/d59078be4dc88123104ehttps://stackoverflow.com/questions/14597884/mysql-my-ini-location https://stackoverflow.com/questions/1493722/mysql-command-for-showing-current-configuration-variableshttps://dev.mysql.com/doc/refman/8.0/en/option-files.htmlhttps://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.htmlhttps://bugs.mysql.com/bug.php?id=15684https://dev.mysql.com/doc/refman/8.0/en/performance-schema-variables-info-table.html
Page updated
Google Sites
Report abuse