Add EVENTS_STATEMENTS viewed tables in the performance schema.   
Rows in the tables will have information about latest statements   
(in progress or completed) for active users.   

Enabling
--------

Start the server with mysqld --performance-schema-enabled=true.

There are rows in SETUP_INSTRUMENTS which contain:
NAME:        statement/.../.../.../...
ENABLED:     YES or NO
TIMED:       YES or NO
So to enable instrumenting for all statements, say
UPDATE setup_instruments WHERE name like 'statement%' SET enabled='yes';
To disable instrumenting or all statements, say:
UPDATE setup_instruments WHERE name like 'statement%' SET enabled='no';

See WL#2360 for description of performance_schema variables,   
requirements, effects, and dangers.
   
Columns in EVENTS_STATEMENTS_CURRENT
------------------------------------

/* Columns that are like other PERFORMANCE_SCHEMA.EVENTS table columns */
   
THREAD_ID             INTEGER             as in WL#2360
EVENT_ID              BIGINT              as in WL#2360
EVENT_NAME            VARCHAR(128)        "statement/..."
SOURCE                VARCHAR(64)         "sql_parse.cc:9999"
TIMER_START           BIGINT              as in WL#2360
TIMER_END             BIGINT              as in WL#2360
TIMER_WAIT            BIGINT              as in WL#2360
SPINS                 INTEGER             NULL
OBJECT_SCHEMA         VARCHAR(64)         NULL
OBJECT_NAME           VARCHAR(64)         NULL
OBJECT_TYPE           VARCHAR(64)         NULL
OBJECT_INSTANCE_BEGIN VARCHAR(512)        NULL
NESTING_EVENT_ID      BIGINT              Transaction? Or Containing Statement?
OPERATION             VARCHAR(16)         "INSERT", "DELETE", etc.
NUMBER_OF_BYTES       BIGINT              NULL

/* Columns that are needed because statements are "nesting" (WL#4816) */

NESTED_1_LEVEL_BELOW  80*42  occurrences of 'aggregators' struc for stages
NESTED_2_LEVELS_BELOW 80*42  occurrences of 'aggregators' struc for waits

/* Columns that are already visible via SHOW PROCESSLIST */

ID                    BIGINT
USER                  VARCHAR(16)
HOST                  VARCHAR(64)
DB                    VARCHAR(64)
COMMAND               VARCHAR(16)         probably redundant
TIME                  BIGINT
STATE                 VARCHAR(64)
INFO                  LONGTEXT

/* Additional columns that are in mysql.general_log */

SERVER_ID             INT

/* Additional columns that are in mysql.slow_log */

LOCK_TIME             TIME
ROWS_SENT             INTEGER             /* probably redundant */
ROWS_EXAMINED         INTEGER             /* probably redundant */
LAST_INSERT_ID        INTEGER
INSERT_ID             INTEGER

/* Columns that describe state of the statement when completed */

LAST_ERROR_SQLSTATE     CHAR     SQLSTATE of last error
LAST_ERROR_MESSAGE      CHAR     Message of last error   
"Deadlock Columns"      multi    as in WL#4689 Deadlock Monitor

Timers
------

The TIMER_START / TIMER_END / TIMER_WAIT column data comes from a
high-precision timer as with other Performance Schema instrumentations.
However, notice what we have now with PERFORMANCE_SCHEMA.SETUP_TIMERS:
  mysql> select * from SETUP_TIMERS;
  +------+------------+
  | NAME | TIMER_NAME |
  +------+------------+
  | Wait | CYCLE      |
  +------+------------+
  1 row in set (0.00 sec)
Conceivably we will want to add a new row here, Name='statement',
with a different TIMER_NAME. The TIMER_NAME column is updatable.
So users could use CYCLE for waits, and something else for statements.

Perhaps getrusage() comes into play here. See the mention of getrusage()
in WL#4813 PERFORMANCE_SCHEMA Instrumenting Stages.

TIME and LOCK_TIME are legacy columns which we preserve. The hope is
that eventually we can deprecate the older ways of looking at statement
information. We have to start by providing everything that the older ways
provide.

Some observations
-----------------

Some information is already in the thd.

There is one row per thread.

The TIMER_END, LAST_ERROR_SQLSTATE, and LAST_ERROR_MESSAGE columns are
cleared when the statement starts, so it will be obvious if a statement
has not ended: TIMER_END will be NULL.
  
Any user may SELECT from the STATEMENTS table. But the user will  
only see "self", unless the user has PROCESS privilege.  
The intent is that the user should not be able to see something  
in the table that the user isn't able to see with SHOW PROCESSLIST.  

Description of another product can be found by clicking on 'Progress'
and looking at what was edited on 2009-04-13.

We might want to add more columns with redundant information, to
avoid joining/unioning with other tables.

If prepared statements are "objects" they might appear in object summaries too.

Historical data   
---------------   

We'll have EVENTS_STATEMENTS tables analogous to EVENTS_WAITS tables.
EVENTS_STATEMENTS_HISTORY     
EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_TYPE     
EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_TIME   
EVENTS_STATEMENTS_HISTORY_METRIC   
EVENTS_STATEMENTS_HISTORY_BY_THREAD_BY_TYPE   

In the original task description, there was a mention of
WL#1019 "SELECT FROM slow_query logs", which at the time
was just a suggestion. It has nothing that won't be
available via performance schema, but its history is longer.
If we ever have "tracing" for performance schema (WL#4878), the
slow query log table becomes redundant and should be deprecated.
We were waiting to find out what historical-data   
retention requirements are, according to [user name suppressed].

See also   
--------   
   
WL#2360 performance.session_waits   
WL#1282 Add log file which would log error codes together with queries.
WL#4689	Deadlock Monitor
WL#4813 PERFORMANCE_SCHEMA Instrumenting Stages

• WL#2360: Performance Schema

You must be logged in to tag this worklog

No Comments yet