Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

25.12.6.4 The prepared_statements_instances Table

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 FunctionCorresponding 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 StatementCorresponding Server Command
    PREPARESQLCOM_PREPARE
    EXECUTESQLCOM_EXECUTE
    DEALLOCATE PREPARE,DROP PREPARESQLCOM_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 theprepared_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 theperformance_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 thesetup_instruments table.

InstrumentServer Command
statement/com/PrepareCOM_STMT_PREPARE
statement/com/ExecuteCOM_STMT_EXECUTE
statement/sql/prepare_sqlSQLCOM_PREPARE
statement/sql/execute_sqlSQLCOM_EXECUTE

The Performance Schema manages the contents of theprepared_statements_instances table as follows:

  • Statement preparation

    ACOM_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 aCOM_STMT_EXECUTE orSQLCOM_PREPARE command for an instrumented prepared statement instance updates the correspondingprepared_statements_instances table row.

  • Prepared statement deallocation

    Execution of aCOM_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.

Theprepared_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 isNULL. 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 areNULL. 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_TEXTFROM performance_schema.prepared_statements_instancesWHERE OWNER_OBJECT_TYPE IS NOT NULL;

  • TIMER_PREPARE

    The time spent executing the statement preparation itself.

  • COUNT_REPREPARE

    The number of times the statement was reprepared internally (seeSection 8.10.4, “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 remainingSUM_xxx columns are the same as for the statement summary tables (seeSection 25.12.15.3, “Statement Summary Tables”).

TRUNCATE TABLE resets the statistics columns of theprepared_statements_instances table.