5.6.7.9 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 the clone_status and clone_progress Performance Schema tables.

Note

The clone_status and clone_progress Performance Schema tables can be used to monitor a cloning operation on the recipient MySQL server instance only. To monitor a cloning operation on the donor MySQL server instance, use the clone stage events, as described in Monitoring Cloning Operations Using Performance Schema Stage Events.

The clone_status table provides the state of the current or last executed cloning operation. A clone operation has four possible states: Not Started, In Progress, Completed, and Failed.
The clone_progress table provides progress information for the current or last executed clone operation, by stage. The stages of a cloning operation include DROP DATA, FILE COPY, PAGE_COPY, REDO_COPY, FILE_SYNC, RESTART, and RECOVERY.

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

To check the state of a cloning operation:

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

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:

Connect to the recipient MySQL server instance.
Query the clone_progress table. For example, the following query provides state and end time data for each stage of the cloning operation:
1. mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
2. +-----------+-----------+----------------------------+
3. | stage | state | end_time |
4. +-----------+-----------+----------------------------+
5. | DROP DATA | Completed | 2019-01-27 22:45:43.141261 |
6. | FILE COPY | Completed | 2019-01-27 22:45:44.457572 |
7. | PAGE COPY | Completed | 2019-01-27 22:45:44.577330 |
8. | REDO COPY | Completed | 2019-01-27 22:45:44.679570 |
9. | FILE SYNC | Completed | 2019-01-27 22:45:44.918547 |
10. | RESTART | Completed | 2019-01-27 22:45:48.583565 |
11. | RECOVERY | Completed | 2019-01-27 22:45:49.626595 |
12. +-----------+-----------+----------------------------+
For other clone status and progress data points that you can monitor, refer to Section 26.12.16, “Performance Schema Clone Tables”.

Contents Haut

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:

stage/innodb/clone (file copy): Indicates progress of the file copy phase of the cloning operation. WORK_ESTIMATED and WORK_COMPLETED units are file chunks. The number of files to be transferred is known at the start of the file copy phase, and the number of chunks is estimated based on the number of files. WORK_ESTIMATED is set to the number of estimated file chunks. WORK_COMPLETED is updated after each chunk is sent.
stage/innodb/clone (page copy): Indicates progress of the page copy phase of cloning operation. WORK_ESTIMATED and WORK_COMPLETED units are pages. Once the file copy phase is completed, the number of pages to be transferred is known, and WORK_ESTIMATED is set to this value. WORK_COMPLETED is updated after each page is sent.
stage/innodb/clone (redo copy): Indicates progress of the redo copy phase of cloning operation. WORK_ESTIMATED and WORK_COMPLETED units are redo chunks. Once the page copy phase is completed, the number of redo chunks to be transferred is known, and WORK_ESTIMATED is set to this value. WORK_COMPLETED is updated after each chunk is sent.

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

Enable the stage/innodb/clone% instruments:
1. mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
2. WHERE NAME LIKE 'stage/innodb/clone%';
Enable the stage event consumer tables, which include events_stages_current, events_stages_history, and events_stages_history_long.
1. mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
2. WHERE NAME LIKE '%stages%';
Run a cloning operation. In this example, a local data directory is cloned to a directory named cloned_dir.
1. mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/cloned_dir';
Check the progress of the cloning operation by querying the Performance Schema events_stages_current table. The stage event shown differs depending on the cloning phase that is in progress. The WORK_COMPLETED column shows the work completed. The WORK_ESTIMATED column shows the work required in total.
1. mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current
2. WHERE EVENT_NAME LIKE 'stage/innodb/clone%';
3. +--------------------------------+----------------+----------------+
4. | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
5. +--------------------------------+----------------+----------------+
6. | stage/innodb/clone (redo copy) | 1 | 1 |
7. +--------------------------------+----------------+----------------+
The events_stages_current table returns an empty set if the cloning operation has finished. In this case, you can check the events_stages_history table to view event data for the completed operation. For example:
1. mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history
2. WHERE EVENT_NAME LIKE 'stage/innodb/clone%';
3. +--------------------------------+----------------+----------------+
4. | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
5. +--------------------------------+----------------+----------------+
6. | stage/innodb/clone (file copy) | 301 | 301 |
7. | stage/innodb/clone (page copy) | 0 | 0 |
8. | stage/innodb/clone (redo copy) | 1 | 1 |
9. +--------------------------------+----------------+----------------+

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, issue the following query:

mysql> SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE 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/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 |
+----------------------------------------------+---------+

Contents Haut

Wait Instruments

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

wait/synch/mutex/innodb/clone_snapshot_mutex: Tracks wait events for the clone snapshot mutex, which synchronizes access to the dynamic snapshot object (on the donor and recipient) between multiple clone threads.
wait/synch/mutex/innodb/clone_sys_mutex: Tracks wait events for the clone sys mutex. There is one clone system object in a MySQL server instance. This mutex synchronizes access to the clone system object on the donor and recipient. It is acquired by clone threads and other foreground and background threads.
wait/synch/mutex/innodb/clone_task_mutex: Tracks wait events for the clone task mutex, used for clone task management. The clone_task_mutex is acquired by clone threads.
wait/io/file/innodb/innodb_clone_file: Tracks all I/O wait operations for files that clone operates on.

For information about monitoring InnoDB mutex waits, see Section 15.15.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”. For information about monitoring wait events in general, see Section 26.12.4, “Performance Schema Wait Event Tables”.

Contents Haut

Stage Instruments

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

stage/innodb/clone (file copy): Indicates progress of the file copy phase of the cloning operation.
stage/innodb/clone (redo copy): Indicates progress of the redo copy phase of cloning operation.
stage/innodb/clone (page copy): Indicates progress of the page copy phase of cloning operation.

For information about monitoring cloning operations using stage events, see Monitoring Cloning Operations Using Performance Schema Stage Events. For general information about monitoring stage events, see Section 26.12.5, “Performance Schema Stage Event Tables”.

Contents Haut

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 the clone_max_concurrency and clone_autotune_concurrency settings.

Clone statement event instruments include:

statement/abstract/clone: Tracks statement events for any clone operation before it is classified as a local, client, or server operation type.
statement/clone/local: Tracks clone statement events for local clone operations; generated when executing a CLONE LOCAL statement.
statement/clone/client: Tracks remote cloning statement events that occur on the recipient MySQL server instance; generated when executing a CLONE INSTANCE statement on the recipient.
statement/clone/server: Tracks remote cloning statement events that occur on the donor MySQL server instance; generated when executing a CLONE INSTANCE statement on the recipient.

For information about monitoring Performance Schema statement events, see Section 26.12.6, “Performance Schema Statement Event Tables”.

Contents Haut

Memory Instruments

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

memory/innodb/clone: Tracks memory allocated by InnoDB for the dynamic snapshot.
memory/clone/data: Tracks memory allocated by the clone plugin during a clone operation.

For information about monitoring memory usage using Performance Schema, see Section 26.12.17.10, “Memory Summary Tables”.

Contents Haut

The Com_clone Status Variable

The Com_clone status variable provides a count of CLONE statement executions.

For more information, refer to the discussion about Com_xxx statement counter variables in Section 5.1.10, “Server Status Variables”.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-clone-plugin-monitoring.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut