MySQL Performance Parameters

Complete list of parameters that Releem optimizes
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.