22.5.7 Monitoring X Plugin

For general X Plugin monitoring, use the status variables that it exposes. See Section 22.5.6.3, “X Plugin Status Variables”. For information specifically about monitoring the effects of message compression, see Monitoring Connection Compression for X Plugin.

Monitoring SQL Generated by X Plugin

This section describes how to monitor the SQL statements which X Plugin generates when you run X DevAPI operations. When you execute a CRUD statement, it is translated into SQL and executed against the server. To be able to monitor the generated SQL, the Performance Schema tables must be enabled. The SQL is registered under the performance_schema.events_statements_current, performance_schema.events_statements_history, and performance_schema.events_statements_history_long tables. The following example uses the world_x schema, imported as part of the quickstart tutorials in this section. We use MySQL Shell in Python mode, and the \sql command which enables you to issue SQL statements without changing to SQL mode. This is important, because if you instead try to switch to SQL mode, the procedure shows the result of this operation rather than the X DevAPI operation. The \sql command is used in the same way if you are using MySQL Shell in JavaScript mode.

1. Check if the events_statements_history consumer is enabled. Issue:

   mysql-py> \sql SELECT enabled FROM performance_schema.setup_consumers WHERE NAME = 'events_statements_history'
   +---------+
   | enabled |
   +---------+
   | YES     |
   +---------+

2. Check if all instruments report data to the consumer. Issue:

   mysql-py> \sql SELECT NAME, ENABLED, TIMED FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%' AND NOT (ENABLED and TIMED)

   If this statement reports at least one row, you need to enable the instruments. See Section 29.4, “Performance Schema Runtime Configuration”.

3. Get the thread ID of the current connection. Issue:

   mysql-py> \sql SELECT thread_id INTO @id FROM performance_schema.threads WHERE processlist_id=connection_id()

4. Execute the X DevAPI CRUD operation for which you want to see the generated SQL. For example, issue:

   mysql-py> db.CountryInfo.find("Name = :country").bind("country", "Italy")

   You must not issue any further operations for the next step to show the correct result.

5. Show the last SQL query made by this thread ID. Issue:

   mysql-py> \sql SELECT THREAD_ID, MYSQL_ERRNO,SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID=@id ORDER BY TIMER_START DESC LIMIT 1;
   +-----------+-------------+--------------------------------------------------------------------------------------+
   | THREAD_ID | MYSQL_ERRNO | SQL_TEXT                                                                             |
   +-----------+-------------+--------------------------------------------------------------------------------------+
   |        29 |           0 | SELECT doc FROM `world_x`.`CountryInfo` WHERE (JSON_EXTRACT(doc,'$.Name') = 'Italy') |
   +-----------+-------------+--------------------------------------------------------------------------------------+

   The result shows the SQL generated by X Plugin based on the most recent statement, in this case the X DevAPI CRUD operation from the previous step.