Thursday, 16 October 2014

MySQL 5.7.5- More variables in replication performance_schema tables

At MySQL, replication usability is of utmost importance to us. Replication information has long been part of SHOW commands, SHOW SLAVE STATUS occupying a major chunk of it. The other sources of replication information being:
As the replication module grows further, there is a lot more monitoring information, so much that the present interfaces seem too rigid to accommodate all the information we would like to present. So we need to organize them in a more structured manner. In MySQL-5.7.2, we introduced replication performance_schema (P_S) tables providing an SQL interface to monitor replication configuration and status partitioned into different tables, each table grouping logically related information. You can read more about the contents of these tables from official MySQL documentation.
In MySQL-5.7.5 we added some more status variables to these performance_schema tables to enable monitoring the latest replication features viz. multi-source replication in labs. Multi-source allows a MySQL slave to replicate from multiple sources (masters) directly. Talking of multi-source, one needs the replication information per source. So we added global variables that would be useful to extend to per-source scope to the replication performance\_schema tables to help monitor multi-source replication. Note that these variables still work for the single sourced replication and can still be accessed as:
Show status like 'Slave_running';
Show status like 'Slave_retried_transactions';
Show status like 'Slave_last_heartbeat';
Show status like 'Slave_received_heartbeats';
show status like 'Slave_heartbeat_period';
Note though that the status variables are now mostly useful in single-source mode ONLY. If more sources are added, the status variables still just apply to the first source. For other replication sources (masters), the only way to access these variables is to use the replication performance_schema tables as named in the table below. Here is how the names of server variables map to the names in the replication performance_schema tables:
VARIABLE NAMEP_S TABLE NAMEP_S FIELD NAME
 SLAVE_HEARTBEAT_PERIOD replication_connection_configuration HEARTBEAT_INTERVAL
 SLAVE_RECEIVED_HEARTBEATS replication_connection_status COUNT_RECEIVED_HEARTBEATS
 SLAVE_LAST_HEARTBEAT replication_connection_status LAST_HEARTBEAT_TIMESTAMP
 SLAVE_RETRIED_TRANSACTIONS replication_execute_status COUNT_TRANSACTIONS_RETRIES
The variable 'slave_running' reports whether the slave is running or not. This can be found by inspecting the two replication components (receiver and applier) separately to see if the receiver module is running or not by executing
SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;
And execute module is running or not by executing
SERVICE_STATE FROM performance_schema.replication_execute_status;
Please try out multi-source replication and our new monitoring interface in the form of replication performance_schema tables. As always, your feedback is very valuable to us.

No comments:

Post a Comment