MySQL 9.7, a Long-Term Support (LTS) release, incorporates a variety of potential features spanning across multiple technical domains. This article covers some of the primary features introduced and evaluates their practical utility within the MySQL database environment.
Following the End-of-Life (EOL) status of MySQL 8.0, this subsequent LTS release is designed to provide enhanced stability alongside significant architectural innovations.
Let’s discuss each of these features below with some examples and usage.
Flow control monitoring has been improved and provides more granularity by introducing the additional status variables listed below.
To use these status variables, we must install the “Group Replication Flow Control Statistics” component.
|
1 |
mysql> Install component 'file://component_group_replication_flow_control_stats'; |
After the component is installed, the statistics will be visible.
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Gr_flow_control%'; +--------------------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------------------------------+----------------+ | Gr_flow_control_throttle_active_count | 0 | | Gr_flow_control_throttle_count | 0 | | Gr_flow_control_throttle_last_throttle_timestamp | | | Gr_flow_control_throttle_time_sum | 0 | +--------------------------------------------------+----------------+ |
We now have additional verbosity for the Applier threads for both Asynchronous and Group Replication topologies. This means we can get more details of the transactions or potential misbehaviours during the transactions applier stage. This feature is particularly useful for troubleshooting performance bottlenecks in multi-threaded replication environments, where understanding the specific cause of lag can be challenging.
This requires installing the “Replication Applier Metrics” component.
|
1 |
mysql> Install component 'file://component_replication_applier_metrics'; |
Upon successful installation of the requisite component, the performance schema tables facilitate tracking of transaction details and various performance metrics during the replication applier phase. For instance, monitoring the table “replication_applier_metrics” enables observing channel-specific operations.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> SELECT * FROM performance_schema.replication_applier_metrics where CHANNEL_NAME='group_replication_applier'\G; *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier TOTAL_ACTIVE_TIME_DURATION: 0 LAST_APPLIER_START: 0000-00-00 00:00:00 TRANSACTIONS_COMMITTED_COUNT: 0 TRANSACTIONS_ONGOING_COUNT: 0 TRANSACTIONS_PENDING_COUNT: 0 TRANSACTIONS_COMMITTED_SIZE_BYTES_SUM: 0 TRANSACTIONS_ONGOING_FULL_SIZE_BYTES_SUM: 0 TRANSACTIONS_ONGOING_PROGRESS_SIZE_BYTES_SUM: 0 TRANSACTIONS_PENDING_SIZE_BYTES_SUM: NULL EVENTS_COMMITTED_COUNT: 0 WAITS_FOR_WORK_FROM_SOURCE_COUNT: 0 WAITS_FOR_WORK_FROM_SOURCE_SUM_TIME: 0 WAITS_FOR_AVAILABLE_WORKER_COUNT: 0 WAITS_FOR_AVAILABLE_WORKER_SUM_TIME: 0 WAITS_COMMIT_SCHEDULE_DEPENDENCY_COUNT: 0 WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME: 0 WAITS_FOR_WORKER_QUEUE_MEMORY_COUNT: 0 WAITS_FOR_WORKER_QUEUE_MEMORY_SUM_TIME: 0 WAITS_WORKER_QUEUES_FULL_COUNT: 0 WAITS_WORKER_QUEUES_FULL_SUM_TIME: 0 WAITS_DUE_TO_COMMIT_ORDER_COUNT: 0 WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME: 0 TIME_TO_READ_FROM_RELAY_LOG_SUM_TIME: 0 |
In addition to aggregate metrics, MySQL 9.7 provides a way to inspect the progress of individual worker threads via monitoring stats in the “replication_applier_progress_by_worker” table. This level of detail helps administrators identify if a single transaction is monopolising a specific worker, causing overall replication delay.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> SELECT * FROM performance_schema.replication_applier_progress_by_worker\G; *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier WORKER_ID: 0 THREAD_ID: 62 ONGOING_TRANSACTION_TYPE: UNASSIGNED ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0 ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier WORKER_ID: 1 THREAD_ID: 63 ONGOING_TRANSACTION_TYPE: UNASSIGNED ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0 ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier WORKER_ID: 2 THREAD_ID: 64 ONGOING_TRANSACTION_TYPE: UNASSIGNED ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0 ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0 *************************** 4. row *************************** CHANNEL_NAME: group_replication_applier WORKER_ID: 3 THREAD_ID: 65 ONGOING_TRANSACTION_TYPE: UNASSIGNED ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0 ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0 |
The Group Replication resource manager now provides auto-eviction functionality, which we can configure using the available options. This basically ensures that the unhealthy node is removed from the Group to maintain the cluster’s high availability and overall performance.
This requires installing the “group replication resource manager” component.
|
1 |
mysql> INSTALL COMPONENT 'file://component_group_replication_resource_manager'; |
Once the component is available, we can use various options to decide the node expulsion policy.
1) Applier channel
We can set the applier channel replication lag threshold values using the configuration parameter below.
|
1 |
mysql> set global group_replication_resource_manager.applier_channel_lag = <value>; |
If lag exceeds “applier_channel_lag” threshold 10 times or more in a row, this server is expelled from the group. The status variable below is used for tracking the lag exceed rate.
|
1 2 3 4 5 6 |
mysql> show global status like 'Gr_resource_manager_applier_channel_lag'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | Gr_resource_manager_applier_channel_lag | 0 | +-----------------------------------------+-------+ |
2) Recovery Channel
Similarly, we can define a threshold for the group member recovery process to attempt to rejoin the cluster.
|
1 |
mysql> set global group_replication_resource_manager.recovery_channel_lag = <value>; |
If the secondary’s recovery lag exceeds “recovery_channel_lag”, 10 times or more in succession, the server is expelled from the group.
|
1 2 3 4 5 6 |
mysql show global status like 'Gr_resource_manager_recovery_channel_lag'; +------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------+-------+ | Gr_resource_manager_recovery_channel_lag | 0 | +------------------------------------------+-------+ |
3) Memory/Resource Usage
We can also define an expelled condition based on the group member’s memory or resource usage %.
|
1 |
mysql> set global group_replication_resource_manager.memory_used_limit = 10; |
If the memory usage exceeds memory_used_limit % by 10 or more consecutive times, the node will be expelled from the group.
|
1 2 3 4 5 6 7 |
mysql> show global status like 'Gr_resource_manager_memory_used%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | Gr_resource_manager_memory_used | 78 | +---------------------------------+-------+ 1 row in set (0.002 sec) |
In addition to the discussed options above, we can also track various server status variables to monitor group replication and the resource manager component.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from performance_schema.global_status where variable_name in ('Gr_resource_manager_applier_channel_threshold_hits','Gr_resource_manager_applier_channel_eviction_timestamp','Gr_resource_manager_recovery_channel_threshold_hits','Gr_resource_manager_recovery_channel_eviction_timestamp','Gr_resource_manager_memory_threshold_hits','Gr_resource_manager_memory_eviction_timestamp'); +---------------------------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------------------------------------------------+----------------+ | Gr_resource_manager_applier_channel_eviction_timestamp | | | Gr_resource_manager_applier_channel_threshold_hits | 0 | | Gr_resource_manager_memory_eviction_timestamp | | | Gr_resource_manager_memory_threshold_hits | 6703 | | Gr_resource_manager_recovery_channel_eviction_timestamp | | | Gr_resource_manager_recovery_channel_threshold_hits | 0 | +---------------------------------------------------------+----------------+ 6 rows in set (0.003 sec) |
The expelled node can attempt to automatically rejoin based on the value of the group_replication_autorejoin_tries variable.
|
1 2 3 4 5 6 7 |
mysql> show variables like '%group_replication_autorejoin_tries%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | group_replication_autorejoin_tries | 3 | +------------------------------------+-------+ 1 row in set (0.006 sec) |
If the node cannot join, it will perform the behaviour specified in the group_replication_exit_state_action variable.
|
1 2 3 4 5 6 7 |
mysql> show variables like '%group_replication_exit_state_action%'; +-------------------------------------+--------------+ | Variable_name | Value | +-------------------------------------+--------------+ | group_replication_exit_state_action | OFFLINE_MODE | +-------------------------------------+--------------+ 1 row in set (0.005 sec) |
After a server is evicted from the group (for whatever reason), it gets a grace period (group_replication_resource_manager) when it rejoins. During this period, the Resource Manager won’t immediately kick it out again, even if it’s still lagging or breaching the defined threshold as discussed above.
|
1 2 3 4 5 6 |
mysql> show variables like '%group_replication_resource_manager.quarantine_time%'; +----------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------+-------+ | group_replication_resource_manager.quarantine_time | 3600 | +----------------------------------------------------+-------+ |
The Primary election process is more mature and cohesive. The Group Replication Manager now uses the most up-to-date status as a criterion for selecting the new primary.
Here is how the Group Replication Manager performs the most up-to-date primary selection prior to MySQL v9.7.
In MySQL version 9.7, “group_replication_elect_prefers_most_updated” was introduced, so the failover will be determined by how many transactions are in the secondary backlog. Basically the secondary with the least backlog will be selected as Primary.
Now, it will consider the “most up-to-date” node first, then “weight” and then “UUID”.
To use “group_replication_elect_prefers_most_updated”, we need to install the “Group Replication Primary Election” component listed below on each Group Member.
|
1 |
mysql> Install component 'file://component_group_replication_elect_prefers_most_updated'; |
By default, the most up-to-date group member selection is enabled. We need to make sure it’s enabled on all Group Members.
|
1 2 3 4 5 6 7 |
mysql> select @@group_replication_elect_prefers_most_updated.enabled; +--------------------------------------------------------+ | @@group_replication_elect_prefers_most_updated.enabled | +--------------------------------------------------------+ | 1 | +--------------------------------------------------------+ 1 row in set (0.007 sec) |
In the event that a new primary is elected via the most up-to-date selection mechanism, this metric represents the transaction processing differential between the newly designated primary and the secondary node with the highest level of synchronisation.
|
1 2 3 4 5 6 |
mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_members_trx_delta'; +---------------------------------------------------------------+-------+ | Variable_name | Value | +---------------------------------------------------------------+-------+ | Gr_latest_primary_election_by_most_uptodate_members_trx_delta | 0 | +---------------------------------------------------------------+-------+ |
Also, we can track the timestamp of the most recent primary election on the most up-to-date node.
|
1 2 3 4 5 6 7 |
mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_member_timestamp'; +--------------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------------+-------+ | Gr_latest_primary_election_by_most_uptodate_member_timestamp | | +--------------------------------------------------------------+-------+ 1 row in set (0.005 sec) |
The database logs also tell exactly what criteria the primary member selected during failover.
|
1 |
2026-06-14T10:04:02.243809Z 0 [System] [MY-015575] [Repl] Plugin group_replication reported: 'Member with uuid 00021702-2222-2222-2222-222222222222 was elected primary since it was the most up-to-date member with 2755 transactions more than second most up-to-date member 00021703-3333-3333-3333-333333333333. In case of a tie member weight and then uuid lexical order was used over the most updated members.' |
With the introduction of JSON duality views, we can leverage a single unified JSON document for both relational and hierarchical JSON data. This provides a common, structured JSON format for the application, allowing it to perform both read and write operations.
Let’s see a quick scenario below on how it works.
Below are two relational tables from which we obtain aggregated information in JSON format.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CREATE TABLE products ( product_id INT PRIMARY KEY, product_type VARCHAR(100) ); mysql> CREATE TABLE products_details ( product_detail_id INT PRIMARY KEY, product_id INT, name VARCHAR(100), active varchar(10) ); |
|
1 2 |
mysql> INSERT INTO products (product_id,product_type) VALUES (1,'IT'), (2,'TEL'); mysql> INSERT INTO products_details (product_detail_id,product_id,name,active) VALUES (1,1,'Laptop','Yes'), (2,2,'Mobile','Yes'); |
Here is the exact Json View which fetch the columns from the relation table based on the join condition. Each of those relational table columns is mapped with a JSON data structure (_id,v_product_type,v_product_type ), and the complete details of the product details table are fetched into the (product) array.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> CREATE JSON RELATIONAL DUALITY VIEW view_product AS SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE) '_id': product_id, 'v_product_type': product_type, 'product': ( SELECT JSON_ARRAYAGG( JSON_DUALITY_OBJECT(WITH(INSERT,UPDATE,DELETE) 'v_product_detail_id': product_detail_id, 'v_name': name, 'v_active': active ) ) FROM products_details WHERE products_details.product_id = products.product_id ) ) FROM products; |
|
1 2 3 4 5 6 7 8 |
mysql> select * from view_product; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | data | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"} | | {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.002 sec) |
Once the duality view is created, we can perform both read/write operations.
Reading the duality view
|
1 2 3 4 5 6 7 |
mysql> select * from view_product; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | data | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"} | | {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
Writing the underlying table in the duality view
|
1 2 3 4 5 6 7 |
mysql> UPDATE view_product SET data = JSON_SET( data, '$.product[0].v_name', 'Notepad' ) WHERE JSON_EXTRACT(data, '$._id') = 1; |
|
1 2 3 4 5 6 7 |
mysql> select * from products_details; +-------------------+------------+---------+--------+ | product_detail_id | product_id | name | active | +-------------------+------------+---------+--------+ | 1 | 1 | Notepad | Yes | | 2 | 2 | Mobile | Yes | +-------------------+------------+---------+--------+ |
After performing the above write operations, we can see that the view now shows the updated data.
|
1 2 3 4 5 6 7 |
mysql > select * from view_product; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | data | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"_id": 1, "product": [{"v_name": "Notepad", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "72c4368420cdc698842d0ab4bd9315ab"}, "v_product_type": "IT"} | | {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
With the Hypergraph Optimiser, we now have more advanced optimisation for complex queries and a broader set of Join plans than the older traditional method, missing earlier. By using “Join hypergraph”, the optimiser now has better reach to all tables in the join condition.
Hypergraph Optimiser is OFF
|
1 |
mysql> SET optimizer_switch='hypergraph_optimizer=off'; |
|
1 2 3 4 5 6 7 8 |
mysql> SELECT t1.k, COUNT(*) AS cnt FROM sbtest1 t1 JOIN sbtest2 t2 ON t1.id = t2.id JOIN sbtest3 t3 ON t1.id = t3.id WHERE t1.k BETWEEN 200000 AND 500000 GROUP BY t1.k ORDER BY cnt DESC LIMIT 100; |
Output:
|
1 2 3 4 5 6 |
| 498870 | 119 | | 498729 | 119 | | 497668 | 119 | | 498076 | 119 | +--------+-----+ 100 rows in set (4.000 sec) |
Explain output:
|
1 2 3 4 5 6 7 8 9 10 |
-> Limit: 100 row(s) -> Sort: cnt DESC, limit input to 100 row(s) per chunk -> Stream results (cost=1.22e+6 rows=175136) -> Group aggregate: count(0) (cost=1.22e+6 rows=175136) -> Nested loop inner join (cost=1.1e+6 rows=493200) -> Nested loop inner join (cost=601547 rows=493200) -> Filter: (t1.k between 200000 and 500000) (cost=99122 rows=493200) -> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000) (cost=99122 rows=493200) -> Single-row covering index lookup on t2 using PRIMARY (id = t1.id) (cost=0.919 rows=1) -> Single-row covering index lookup on t3 using PRIMARY (id = t1.id) (cost=0.919 rows=1) |
Hypergraph Optimiser is ON
|
1 |
mysql> SET optimizer_switch='hypergraph_optimizer=on'; |
|
1 2 3 4 5 6 7 8 |
mysql> SELECT t1.k, COUNT(*) AS cnt FROM sbtest1 t1 JOIN sbtest2 t2 ON t1.id = t2.id JOIN sbtest3 t3 ON t1.id = t3.id WHERE t1.k BETWEEN 200000 AND 500000 GROUP BY t1.k ORDER BY cnt DESC LIMIT 100; |
Output:
|
1 2 3 4 5 6 |
| 499721 | 119 | | 499052 | 119 | | 498870 | 119 | | 498384 | 119 | +--------+-----+ 100 rows in set (0.498 sec) |
Explain output:
|
1 2 3 4 5 6 7 8 9 10 11 |
-> Sort: cnt DESC, limit input to 100 row(s) per chunk (cost=1.96e+6..1.96e+6 rows=100) -> Table scan on <temporary> (cost=1.87e+6..1.9e+6 rows=175136) -> Aggregate using temporary table (cost=1.87e+6..1.87e+6 rows=175136) -> Inner hash join (t2.id = t3.id) (cost=990754..1.44e+6 rows=493200) -> Covering index scan on t3 using k_1 (cost=0.312..308240 rows=986400) -> Hash -> Inner hash join (t1.id = t2.id) (cost=370988..824021 rows=493200) -> Covering index scan on t2 using k_1 (cost=0.312..308240 rows=986400) -> Hash -> Filter: (t1.k between 200000 and 500000) (cost=0.416..205287 rows=493200) -> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000) (cost=0.359..176877 rows=493200) |
We can see that with “hypergraph_optimizer=enabled”, the query execution time is almost 8x faster.
The performance difference might not be noticeable with a few joins or a smaller table’s data set, but with more complex joins, it can yield better performance. In the above example, we can see that when “hypergraph_optimizer=enabled”, the optimiser replaces “Nested loop inner join” with “Inner hash join”, which is generally better for large datasets.
Now, it’s possible that a lower version replica can connect to a higher version source when the major versions differ. That means we don’t have to rely on all replicas being upgraded in one go; we can just upgrade the source, verify it, and later perform rolling upgrades on lower-version replicas as per our own timelines and convenience.
Of course, we have to be cautious not to run any such feature or change on the source that doesn’t support lower-version replicas.
Please note – This won’t be applicable to previous releases, say (8.4, 8.0), as they didn’t restrict such replication connectivity. It would be useful for 9.7 or the next major release.
To enable this functionality, we need to ensure the following variable is enabled on the Replica. By default its enabled on 9.7
|
1 2 3 4 5 6 7 |
mysql> show variables like 'replica_allow_higher_version_source'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | replica_allow_higher_version_source | ON | +-------------------------------------+-------+ 1 row in set (0.008 sec) |
The above discussion highlights key advancements in MySQL 9.7 LTS, ranging from some innovative or operational improvements to developer-centric features such as “JSON Duality” Views. Also, the “Hypergraph Optimiser” is now available for community release, which was previously exclusive to MySQL Heatwave/Enterprise. As a Long-Term Support (LTS) release, MySQL 9.7 is structured to provide a stable and consistent environment, prioritising architectural reliability over frequent experimental changes.
One more important mention here: It’s suggested to use MySQL 9.7.1, or the next sub-releases, as 9.7.0 has some higer severity CVE’s. If you are using Percona Server for MySQL (PS), we skipped 9.7.0 and are shipping the fixed 9.7.1 version directly.
Still, it’s highly recommended to test any new component or changes in your lower/staging environment before deploying in production to better assess the overall impact on existing workload, queries, and database behaviour.
Resources
RELATED POSTS
