MySQL Performance Parameters

Depending on the version of MariaDB/MySQL/Percona, Releem recommends values for the following variables :

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 - Defines the chunk size for InnoDB buffer pool resizing operations.
• innodb_buffer_pool_instances - The number of regions that the InnoDB buffer pool is divided into.
• innodb_buffer_pool_size - 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 - 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 - 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 - 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 - 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 - 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 - 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 - 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 - The size of stack for each thread

Others
optimizer_search_depth - Maximum depth of search performed by the query optimizer.
transaction_prealloc_size - The size of memory block preallocated when a transaction is started.