Depending on the version of MariaDB/MySQL/Percona, Releem recommends values for the following variables:
[Free tier] - tagged variables tuned on the Free plan.
Memory
Connections
Logs
Cache
Disk
Threads
Others
optimizer_search_depth - Maximum depth of search performed by the query optimizer.
transaction_prealloc_size [Free tier] - The size of memory block preallocated when a transaction is started.
[Free tier] - tagged variables tuned on the Free plan.
Memory
- bulk_insert_buffer_size - The size of the cache tree in bytes per thread for MyISAM bulk inserts.
- innodb_adaptive_flushing_lwm - The percentage of the InnoDB buffer pool below which no adaptive flushing occurs.
- innodb_autoextend_increment - The increment size (in MB) for extending the size of an autoextending InnoDB system tablespace file when it becomes full.
- innodb_buffer_pool_chunk_size [Free tier] - Defines the chunk size for InnoDB buffer pool resizing operations.
- innodb_buffer_pool_instances [Free tier] - The number of regions that the InnoDB buffer pool is divided into.
- innodb_buffer_pool_size [Free tier] - The size in bytes of the memory area where InnoDB caches data.
- innodb_change_buffer_max_size - The maximum size of the change buffer as a percentage of the InnoDB buffer pool.
- innodb_change_buffering - Controls which types of operations are buffered in the InnoDB change buffer to enhance performance.
- innodb_max_dirty_pages_pct [Free tier] - Specifies the target percentage of dirty (modified but not yet written to disk) pages in the InnoDB buffer pool.
- join_buffer_size - The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
- key_buffer_size [Free tier] - Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
- max_heap_table_size [Free tier] - The maximum size to which user-created MEMORY tables are permitted to grow.
- myisam_sort_buffer_size - The size of the buffer that is allocated when sorting MyISAM indexes.
- read_rnd_buffer_size - The size of memory for reads from MyISAM tables, and, for any storage engine.
- sort_buffer_size - The size in bytes of the memory for sort operations.
- tmp_table_size [Free tier] - The maximum size of internal in-memory temporary tables
Connections
- max_allowed_packet - The maximum size of one packet or any generated/intermediate string.
- max_connections [Free tier] - The maximum permitted number of simultaneous client connections.
Logs
- innodb_log_buffer_size - Size in bytes of the buffer for writing InnoDB redo log files to disk.
- innodb_log_file_size [Free tier] - The size in bytes of each log file in a log group.
- innodb_log_files_in_group - The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.
- innodb_redo_log_capacity [Free tier] - The size of the redo log files.
Cache
- Table_open_cache - The number of open tables for all threads.
- query_cache_limit - Do not cache results that are larger than this number of bytes.
- query_cache_min_res_unit - The minimum size (in bytes) for blocks allocated by the query cache.
- query_cache_size - The amount of memory allocated for caching query results.
- table_definition_cache - The number of table definitions (from .frm files) that can be stored in the definition cache.
Disk
- innodb_file_per_table - When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default.
- innodb_flush_log_at_trx_commit - Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
- innodb_flush_method - Defines the method used to flush data to InnoDB data files and log files, which can affect I/O Throughput.
- innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
- innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.
Threads
- innodb_page_cleaners - Number of page cleaner threads.
- innodb_purge_threads - Number of background threads dedicated to InnoDB purge operations.
- innodb_thread_concurrency - Defines the maximum number of threads permitted inside of InnoDB.
- thread_cache_size - The count of threads the server should cache for reuse.
- thread_handling - The thread-handling model used by the server for connection threads.
- thread_pool_size - The number of thread groups in the thread pool, which determines how many statements can execute simultaneously.
- thread_stack [Free tier] - The size of stack for each thread
Others
optimizer_search_depth - Maximum depth of search performed by the query optimizer.
transaction_prealloc_size [Free tier] - The size of memory block preallocated when a transaction is started.
Learn more
Database optimization is the practice of enhancing your MySQL database to improve its efficiency, speed, and reliability. This process entails a detailed examination and fine-tuning of critical components, including queries, indexes, storage configurations, to eliminate performance constraints.
This guide unpacks the key contrasts between user-defined variables, which are dynamic parameters assigned within your queries, and system variables, which control the broader performance and settings of your MySQL database.
Proactive optimization shifts the focus to preventing issues, catching bottlenecks early, and keeping performance steady under load. Instead of waiting for slowdowns, it’s time to discover advanced tuning techniques – like continuous monitoring, automation, machine learning, refined indexing, and chaos engineering.