Depending on the version of MariaDB/MySQL/Percona, Releem recommends values for the following variables :
Memory - 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_buffer_pool_chunk_size - Defines the chunk size for
InnoDB buffer pool resizing operations.
- sort_buffer_size - The size in bytes of the memory for sort operations.
- read_rnd_buffer_size - The size of memory for reads from
MyISAM tables, and, for any storage engine.
- bulk_insert_buffer_size - The size of the cache tree in bytes per thread for MyISAM bulk inserts.
- myisam_sort_buffer_size - The size of the buffer that is allocated when sorting
MyISAM indexes.
- max_heap_table_size - The maximum size to which user-created
MEMORY tables are permitted to grow.
- tmp_table_size - The maximum size of internal in-memory temporary tables.
- 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.
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_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.
Cache- Table_open_cache - The number of open tables for all threads.
- 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_write_io_threads - The number of I/O threads for write operations in
InnoDB.
- innodb_read_io_threads - The number of I/O threads for read operations in
InnoDB
Threads
- thread_cache_size - The count of threads the server should cache for reuse.
- innodb_thread_concurrency - Defines the maximum number of threads permitted inside of
InnoDB.