mankuworks@Mysql

Tuesday, March 10, 2015

performance_schema.setup_actors is a table in MySQL Performance schema which could be used to specify what users/hosts one wants to have instrumentation on for. By default connection from all users and hosts are enabled to be instrumented for. Here is the default configuration :

So in the above configuration, user name 'mayank', be it from any host, is not instrumented and any user from host 'localhost' is also not instrumented. Instrumentation is ON for rest all users/hosts combinations.

Now, it can be seen above that 'stmt1' is prepared in thread with id '20', and the event which prepared it has id '4'. This information is shown in preapred_statements_instances table i.e. from this table we can relate that this particular prepared statement is prepared by which statement execution and from which thread. TIMER_PREPARE shows the time it took to prepare the statement. And once executed, its execution stats get collected (like COUNT_EXECUTE here).

Prepared statement within Stored Procedure
Things to be noted in above examples are the columns : OWNER_OBJECT_TYPE/SCHEMA/NAME.These columns are shown as NULL here because this stmt1 is not prepared inside any stored procedure. Had it been prepared inside a stored procedure, these column would have been populated with its details. For example : mysql> delimiter |mysql> CREATE PROCEDURE test.p1 () BEGIN prepare stmt1 from "SELECT * FROM t1"; END|Query OK, 0 rows affected (0.00 sec)

Note that event which created the procedure is '4' but the event which prepared the statement stmt1 i.e. call to procedure p1 event is '33' and that is the OWNER_EVENT_ID for stmt1 in prepared_statements_instances.

COUNT_REPREPARE Column
When metadata of tables or views, which are referred
to by a prepared statement, changes, it causes automatic
repreparation of the prepared statement when it is next executed. COUNT_REPREPARE column here denotes how many time this prepared statement has been reprepared. For Ex:mysql> create table test.t1 (c1 int, c2 int);Query OK, 0 rows affected (0.25 sec)

Deleting a Prepared Statement
Dropping a prepared statement: Once a prepared statement is created/executed, its statistics are captured in P_S. Now when that statement is dropped (deleted) corresponding statistics are deleted from P_S table. For ex:

New server/status variables:
A new server variable added:performance_schema_max_prepared_statements_instances:
It denotes the maximum number of prepared statements which could be instrumented.

A new status variable added: performance_schema_prepared_statements_lost:
It denotes how many prepared statements could not be instrumented.

Collection of Statistics:
It depends on existing instrumentations' value:
- "statement/sql/prepare_sql" for SQLCOM_PREPARE
If enabled, prepare statistics of statements prepared from SQL query would be instrumented.
- "statement/com/prepare" for COM_STMT_PREPARE
If enabled, prepare statistics of statements prepared from C API would be instrumented.
- "statement/sql/execute_sql" for SQLCOM_EXECUTE
If enabled, execute statistics of statements executed from SQL query would be instrumented.
- "statement/com/execute" for COM_STMT_EXECUTE
If enabled, execute statistics of statements executed from C API would be instrumented.

For eg. here PFS_CLASS_STATEMENT is type of a class which is used for all statement related instruments (eg. statement/sql/select, statement/sql/insert etc.).

Now there is a base class PFS_instr_class from which all other instrument classes are derived. Following figure shows the relationship.

Each instrument in P_S is instantiated with one of the above class.

For every set of instruments (Stages, Waits, Statements etc.) we
maintain arrays which we name as class array. An array of this kind keeps records of
all the instruments in that particular class and their configurations
(i.e. name, enabled, timed etc.). For example: PFS_mutex_class *mutex_class_array : to hold information of all mutex instruments. PFS_statement_class *statement_class_array : to hold information of all statement instruments.

During initialization of P_S, memory is allocated to these arrays based on their sizing values eg. mutex_class_max, statement_class_max. Default values of these sizing are set internally, but user can also specify them while starting MySQL server. If user specify a sizing value less then the number of instruments available in that specific class, few instruments of that class won't be registered to be tracked. These lost instrument information can be seen in "show status command as follow".

Now, lets see how does an array of an instrument class look like and what does each element represent in that array.

Above figure shows statement_class_array. Each element of this array represent an instrument (statement/sql/select in this case) and contains information about that instrument. In this case, select instrument is enabled and it is to be timed as well. This m_enabled/m_timed could be seen for instruments (statement/sql/select in this case) on the fly when MySQL server is running with P_S.

Similarly there are arrays for other class types as well and each element of those arrays represents an instrument of its respective class.

Registering Instruments:

As it might have been obvious by now that this is the array which P_S use during execution to find out information of instruments. So it is necessary for each instrument (which we intend to instrument) should be in one of these array i.e. should be registered.

Lets see how do we register instruments (i.e. keep their information in the
class array). Here I took example of statement instruments.

Hold on!!
Before that lets understand one more structure. I call it info structure.

struct PSI_statement_info_v1 {

/** The registered statement key. */ PSI_statement_key m_key; /** The name of the statement instrument to register. */

const char *m_name;

/** The flags of the statement instrument to register. */

int m_flags;

};

This is a structure which keeps "where to look" information (m_key) of an instrument which would be used at run time to get other information about that instrument. m_name : keeps the name of the instrument to be registered ( say select)m_key : is an index in the statement_class_array where the information of this
instrument is stored. m_flags: flag for that instrument.

Similar to PSI_statement_info_v1, there are info structure for other classes as well like PSI_stage_info_v1, PSI_mutex_info_v1 etc.

An array, sql_statement_info, is made of this structure and each element of array represent a statement instrument of SQL Statement. Information (m_name) in this array is filled up by function init_sql_statement_info() which does nothing but populate m_name from all possible sql statements in MySQL and reset flags to '0'.

Still with me?

Now we have sql_statement_info populated but its still not linked to statement_class_array.To do that, a call to mysql_statement_register() is made with this sql_statement_info array.

This function, mysql_statement_register() which finally maps to pfs_statement_register_v1() (pfs.cc) does following:

for all elements in sql_statement_info

make a formatted string to be stored for instrument name (eg. statement/sql/select)

call register_statement_class(), which would stores information of this instrument in statement_class_array and return its index.

store this index into m_key.

Now lets see what function register_statement_class() does.

call REGISTER_CLASS_BODY_PART to see if this instrument already registered. If yes, then return that index.

search for first empty index in statement_class_array and populate information by calling init_instr_class().

populate event_name_index with index.

call configure_instr_class(), to set the user specified values (if any).

return index+1 to be stored in m_key.

Thats it !!! Its done.

Now for a particular instrument in sql_statement_info, this m_key is populated with the index in statement_class_array where information about this instrument is stored. During execution, P_S use this m_key to see the information of that instrument in statement_class_array.

This entire flow could be depicted from following figure:

Similarly registration mechanism exists for all the instruments available in P_S. Once registered, instruments become ready to be collected statistics for in P_S.