The Performance Schema provides instrumentation for prepared statements, for which there are two protocols:
The binary protocol. This is accessed through the MySQL C API and maps onto underlying server commands as shown in the following table.
C API Function Corresponding Server Command mysql_stmt_prepare()
COM_STMT_PREPARE
mysql_stmt_execute()
COM_STMT_EXECUTE
mysql_stmt_close()
COM_STMT_CLOSE
The text protocol. This is accessed using SQL statements and maps onto underlying server commands as shown in the following table.
SQL Statement Corresponding Server Command PREPARE
SQLCOM_PREPARE
EXECUTE
SQLCOM_EXECUTE
DEALLOCATE PREPARE
,DROP PREPARE
SQLCOM_DEALLOCATE PREPARE
Performance Schema prepared statement instrumentation covers both protocols. The following discussion refers to the server commands rather than the C API functions or SQL statements.
Information about prepared statements is available in the
prepared_statements_instances
table. This table enables inspection of prepared statements
used in the server and provides aggregated statistics about
them. To control the size of this table, set the
performance_schema_max_prepared_statements_instances
system variable at server startup.
Collection of prepared statement information depends on the
statement instruments shown in the following table. These
instruments are enabled by default. To modify them, update the
setup_instruments
table.
Instrument | Server Command |
---|---|
statement/com/Prepare |
COM_STMT_PREPARE |
statement/com/Execute |
COM_STMT_EXECUTE |
statement/sql/prepare_sql |
SQLCOM_PREPARE |
statement/sql/execute_sql |
SQLCOM_EXECUTE |
The Performance Schema manages the contents of the
prepared_statements_instances
table as follows:
Statement preparation
A
COM_STMT_PREPARE
orSQLCOM_PREPARE
command creates a prepared statement in the server. If the statement is successfully instrumented, a new row is added to theprepared_statements_instances
table. If the statement cannot be instrumented,Performance_schema_prepared_statements_lost
status variable is incremented.Prepared statement execution
Execution of a
COM_STMT_EXECUTE
orSQLCOM_PREPARE
command for an instrumented prepared statement instance updates the correspondingprepared_statements_instances
table row.Prepared statement deallocation
Execution of a
COM_STMT_CLOSE
orSQLCOM_DEALLOCATE_PREPARE
command for an instrumented prepared statement instance removes the correspondingprepared_statements_instances
table row. To avoid resource leaks, removal occurs even if the prepared statement instruments described previously are disabled.
The prepared_statements_instances
table has these columns:
OBJECT_INSTANCE_BEGIN
The address in memory of the instrumented prepared statement.
STATEMENT_ID
The internal statement ID assigned by the server. The text and binary protocols both use statement IDs.
STATEMENT_NAME
For the binary protocol, this column is
NULL
. For the text protocol, this column is the external statement name assigned by the user. For example, for the following SQL statement, the name of the prepared statement isstmt
:PREPARE stmt FROM 'SELECT 1';
SQL_TEXT
The prepared statement text, with
?
placeholder markers.OWNER_THREAD_ID
,OWNER_EVENT_ID
These columns indicate the event that created the prepared statement.
OWNER_OBJECT_TYPE
,OWNER_OBJECT_SCHEMA
,OWNER_OBJECT_NAME
For a prepared statement created by a client session, these columns are
NULL
. For a prepared statement created by a stored program, these columns point to the stored program. A typical user error is forgetting to deallocate prepared statements. These columns can be used to find stored programs that leak prepared statements:SELECT OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME, STATEMENT_NAME, SQL_TEXT FROM performance_schema.prepared_statements_instances WHERE OWNER_OBJECT_TYPE IS NOT NULL;
The query execution engine. The value is either
PRIMARY
orSECONDARY
. For use with HeatWave Service and HeatWave, where thePRIMARY
engine isInnoDB
and theSECONDARY
engine is HeatWave (RAPID
). For MySQL Community Edition Server, MySQL Enterprise Edition Server (on-premise), and HeatWave Service without HeatWave, the value is alwaysPRIMARY
.TIMER_PREPARE
The time spent executing the statement preparation itself.
COUNT_REPREPARE
The number of times the statement was reprepared internally (see Section 10.10.3, “Caching of Prepared Statements and Stored Programs”). Timing statistics for repreparation are not available because it is counted as part of statement execution, not as a separate operation.
COUNT_EXECUTE
,SUM_TIMER_EXECUTE
,MIN_TIMER_EXECUTE
,AVG_TIMER_EXECUTE
,MAX_TIMER_EXECUTE
Aggregated statistics for executions of the prepared statement.
SUM_
xxx
The remaining
SUM_
columns are the same as for the statement summary tables (see Section 29.12.20.3, “Statement Summary Tables”).xxx
MAX_CONTROLLED_MEMORY
Reports the maximum amount of controlled memory used by a prepared statement during execution.
MAX_TOTAL_MEMORY
Reports the maximum amount of memory used by a prepared statement during execution.
The prepared_statements_instances
table has these indexes:
Primary key on (
OBJECT_INSTANCE_BEGIN
)Index on (
STATEMENT_ID
)Index on (
STATEMENT_NAME
)Index on (
OWNER_THREAD_ID
,OWNER_EVENT_ID
)Index on (
OWNER_OBJECT_TYPE
,OWNER_OBJECT_SCHEMA
,OWNER_OBJECT_NAME
)
TRUNCATE TABLE
resets the
statistics columns of the
prepared_statements_instances
table.