MySQL :: MySQL 8.4 Reference Manual :: 7.6.7.10 Monitoring Cloning Operations (original) (raw)

7.6.7.10 Monitoring Cloning Operations

This section describes options for monitoring cloning operations.

Monitoring Cloning Operations using Performance Schema Clone Tables

A cloning operation may take some time to complete, depending on the amount of data and other factors related to data transfer. You can monitor the status and progress of a cloning operation on the recipient MySQL server instance using theclone_status andclone_progress Performance Schema tables.

The SELECT andEXECUTE privileges on the Performance Schema is required to access the Performance Schema clone tables.

To check the state of a cloning operation:

  1. Connect to the recipient MySQL server instance.
  2. Query the clone_status table:
mysql> SELECT STATE FROM performance_schema.clone_status;  
+-----------+  
| STATE     |  
+-----------+  
| Completed |  
+-----------+

Should a failure occur during a cloning operation, you can query the clone_status table for error information:

mysql> SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;
+-----------+----------+---------------+
| STATE     | ERROR_NO | ERROR_MESSAGE |
+-----------+----------+---------------+
| Failed    |      xxx | "xxxxxxxxxxx" |
+-----------+----------+---------------+

To review the details of each stage of a cloning operation:

  1. Connect to the recipient MySQL server instance.
  2. Query the clone_progress table. For example, the following query provides state and end time data for each stage of the cloning operation:
mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;  
+-----------+-----------+----------------------------+  
| stage     | state     | end_time                   |  
+-----------+-----------+----------------------------+  
| DROP DATA | Completed | 2019-01-27 22:45:43.141261 |  
| FILE COPY | Completed | 2019-01-27 22:45:44.457572 |  
| PAGE COPY | Completed | 2019-01-27 22:45:44.577330 |  
| REDO COPY | Completed | 2019-01-27 22:45:44.679570 |  
| FILE SYNC | Completed | 2019-01-27 22:45:44.918547 |  
| RESTART   | Completed | 2019-01-27 22:45:48.583565 |  
| RECOVERY  | Completed | 2019-01-27 22:45:49.626595 |  
+-----------+-----------+----------------------------+

For other clone status and progress data points that you can monitor, refer toSection 29.12.19, “Performance Schema Clone Tables”.

Monitoring Cloning Operations Using Performance Schema Stage Events

A cloning operation may take some time to complete, depending on the amount of data and other factors related to data transfer. There are three stage events for monitoring the progress of a cloning operation. Each stage event reports`WORK_COMPLETED` and`WORK_ESTIMATED` values. Reported values are revised as the operation progresses.

This method of monitoring a cloning operation can be used on the donor or recipient MySQL server instance.

In order of occurrence, cloning operation stage events include:

The following example demonstrates how to enablestage/innodb/clone% event instruments and related consumer tables to monitor a cloning operation. For information about Performance Schema stage event instruments and related consumers, seeSection 29.12.5, “Performance Schema Stage Event Tables”.

  1. Enable the stage/innodb/clone% instruments:
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'  
       WHERE NAME LIKE 'stage/innodb/clone%';
  1. Enable the stage event consumer tables, which includeevents_stages_current,events_stages_history, andevents_stages_history_long.
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'  
       WHERE NAME LIKE '%stages%';
  1. Run a cloning operation. In this example, a local data directory is cloned to a directory namedcloned_dir.
mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/cloned_dir';
  1. Check the progress of the cloning operation by querying the Performance Schemaevents_stages_current table. The stage event shown differs depending on the cloning phase that is in progress. TheWORK_COMPLETED column shows the work completed. The WORK_ESTIMATED column shows the work required in total.
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current  
       WHERE EVENT_NAME LIKE 'stage/innodb/clone%';  
+--------------------------------+----------------+----------------+  
| EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |  
+--------------------------------+----------------+----------------+  
| stage/innodb/clone (redo copy) |              1 |              1 |  
+--------------------------------+----------------+----------------+

The events_stages_current table returns an empty set if the cloning operation has finished. In this case, you can check theevents_stages_history table to view event data for the completed operation. For example: 

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history  
       WHERE EVENT_NAME LIKE 'stage/innodb/clone%';  
+--------------------------------+----------------+----------------+  
| EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |  
+--------------------------------+----------------+----------------+  
| stage/innodb/clone (file copy) |            301 |            301 |  
| stage/innodb/clone (page copy) |              0 |              0 |  
| stage/innodb/clone (redo copy) |              1 |              1 |  
+--------------------------------+----------------+----------------+
Monitoring Cloning Operations Using Performance Schema Clone Instrumentation

Performance Schema provides instrumentation for advanced performance monitoring of clone operations. To view the available clone instrumentation, and issue the following query:

mysql> SELECT NAME,ENABLED FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%clone%';
+---------------------------------------------------+---------+
| NAME                                              | ENABLED |
+---------------------------------------------------+---------+
| wait/synch/mutex/innodb/clone_snapshot_mutex      | NO      |
| wait/synch/mutex/innodb/clone_sys_mutex           | NO      |
| wait/synch/mutex/innodb/clone_task_mutex          | NO      |
| wait/synch/mutex/group_rpl/LOCK_clone_donor_list  | NO      |
| wait/synch/mutex/group_rpl/LOCK_clone_handler_run | NO      |
| wait/synch/mutex/group_rpl/LOCK_clone_query       | NO      |
| wait/synch/mutex/group_rpl/LOCK_clone_read_mode   | NO      |
| wait/synch/cond/group_rpl/COND_clone_handler_run  | NO      |
| wait/io/file/innodb/innodb_clone_file             | YES     |
| stage/innodb/clone (file copy)                    | YES     |
| stage/innodb/clone (redo copy)                    | YES     |
| stage/innodb/clone (page copy)                    | YES     |
| statement/abstract/clone                          | YES     |
| statement/clone/local                             | YES     |
| statement/clone/client                            | YES     |
| statement/clone/server                            | YES     |
| memory/innodb/clone                               | YES     |
| memory/clone/data                                 | YES     |
+---------------------------------------------------+---------+
Wait Instruments

Performance schema wait instruments track events that take time. Clone wait event instruments include:

For information about monitoring InnoDB mutex waits, seeSection 17.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”. For information about monitoring wait events in general, seeSection 29.12.4, “Performance Schema Wait Event Tables”.

Stage Instruments

Performance Schema stage events track steps that occur during the statement-execution process. Clone stage event instruments include:

For information about monitoring cloning operations using stage events, seeMonitoring Cloning Operations Using Performance Schema Stage Events. For general information about monitoring stage events, seeSection 29.12.5, “Performance Schema Stage Event Tables”.

Statement Instruments

Performance Schema statement events track statement execution. When a clone operation is initiated, the different statement types tracked by clone statement instruments may be executed in parallel. You can observe these statement events in the Performance Schema statement event tables. The number of statements that execute depends on theclone_max_concurrency andclone_autotune_concurrency settings.

Clone statement event instruments include:

For information about monitoring Performance Schema statement events, seeSection 29.12.6, “Performance Schema Statement Event Tables”.

Memory Instruments

Performance Schema memory instruments track memory usage. Clone memory usage instruments include:

For information about monitoring memory usage using Performance Schema, seeSection 29.12.20.10, “Memory Summary Tables”.