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.
Here’s the command to install the MySQL optimization-pack using the Akamas CLI:
Component Type | Description |
---|---|
MySQL 8.0 Database, deployed on-premises.
Metric | Unit | Description |
---|
Parameter | Type | Unit | Default value | Domain | Description | Restart |
---|
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 |
|
| The size of the buffer pool used by InnoDB to cache tables and indexes in memory | no |
mysql_innodb_buffer_pool_instances | integer | regions |
|
| The number of regions that the InnoDB buffer pool is divided into | no |
mysql_innodb_thread_sleep_delay | integer | milliseconds |
|
| The number of milliseconds each InnoDB thread sleeps before joining the InnoDB queue | no |
mysql_innodb_flush_method | string | - |
|
| The method used to flush data to InnoDB's datafiles and log files | yes |
mysql_innodb_log_file_size | integer | bytes |
|
| 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 |
|
| The limit on the number of os threads used by InnoDB to serve user requests | no |
mysql_innodb_max_dirty_pages_pct | real | percentage |
|
| The limit on the percentage of dirty pages in the buffer pool of InnoDB | no |
mysql_innodb_read_ahead_threshold | integer | pages |
|
| 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 | - |
|
| Whether or not enable the adaptive hash index optimization for InnoDB tables | no |
mysql_innodb_fill_factor | integer | percentage |
|
| The percentage of each B-tree page that is filled during a sorted index build | no |