The MySQL optimization pack allows the user to monitor a MySQL instance and explore the configuration space of its parameters. The optimization pack provides parameters and metrics that can be leveraged to reach, among others, two main goals:
Throughput optimization - increasing the capacity of a MySQL deployment to serve clients
Cost optimization - decreasing the size of a MySQL deployment while guaranteeing the same service level
To reach the aforementioned goals, the optimization pack focuses on three key areas of tuning of the InnoDB, the storage engine for MySQL:
Buffer management
Threading
Paging
The following table describes the supported component types by the MySQL optimization pack.
MySQL 8.0 Database, deployed on-premises.
Here’s the command to install the MySQL optimization-pack using the Akamas CLI:
mysql_aborted_connection
connections
The number of failed attempts to connect to the MySQL
mysql_connections_current
connections
The current number of connection opened towards MySql
mysql_connections_max
connections
The maximum number of connections that can be opened towards MySQL
mysql_innodb_buffer_pool_size
bytes
The size of the memory area where InnoDB caches tables and indexes
mysql_mem_usage
bytes
MySQL instance memory consumption divided by type (innodb_buffer_pool_data, innodb_log_buffer, query_cache, key_buffer_size)
mysql_query_throughput
queries/s
The number of queries per second processed by mysql
mysql_slow_query_rate
querys/s
The rate of queries that are considered slow based on parameters mysql_long_query_time and mysql_long_query_min_examined_row
mysql_statements_rate
statements/s
The rate at which each type of statement (select, insert, update, delete) executed per second.
mysql_threads_running
threads
The number of threads running in the MySQL instance
mysql_transactions_rate
transactions
The rate at which each type of transaction (handler label) is executed (commit. rollback, prepare, savepoint)
network_in_bytes_rate
bytes/s
The number of network inbound data in bytes per second
network_out_bytes_rate
bytes/s
The number of network outbound data in bytes per second
mysql_innodb_buffer_pool_size
integer
bytes
134217728
5242880 → 25769803776
The size of the buffer pool used by InnoDB to cache tables and indexes in memory
no
mysql_innodb_buffer_pool_instances
integer
regions
8
1→64
The number of regions that the InnoDB buffer pool is divided into
no
mysql_innodb_thread_sleep_delay
integer
milliseconds
10000
0 → 1000000
The number of milliseconds each InnoDB thread sleeps before joining the InnoDB queue
no
mysql_innodb_flush_method
string
-
fsync
fsync
O_DSYNC
littlesync
nosync
O_DIRECT
O_DIRECT_NO_FSYNC
The method used to flush data to InnoDB's datafiles and log files
yes
mysql_innodb_log_file_size
integer
bytes
50331648
4194304 →5368709120
The size of each log file in each log group maintained by InnoDB. The total size of log files cannot exceed 4GB.
yes
mysql_innodb_thread_concurrency
integer
threads
0
0 → 1000
The limit on the number of os threads used by InnoDB to serve user requests
no
mysql_innodb_max_dirty_pages_pct
real
percentage
10.0
0.0 → 99.99
The limit on the percentage of dirty pages in the buffer pool of InnoDB
no
mysql_innodb_read_ahead_threshold
integer
pages
56
0 → 64
The number of sequentially read pages after which MySQL initiates an async read of the following extend (a group of pages within a tablespace)
no
mysql_innodb_adaptive_hash_index
-
ON
ON
OFF
Whether or not enable the adaptive hash index optimization for InnoDB tables
no
mysql_innodb_fill_factor
integer
percentage
100
10→100
The percentage of each B-tree page that is filled during a sorted index build
no