Performance Schema implementation Internals: Registering instruments

This is the very first post in the series of Performance Schema Implementation Internals. This series is for MySQL Developers to understand implementation of Performance Schema. For user point of view of performance Schema, please refer to mysql documentation link : http://dev.mysql.com/doc/refman/5.7/en/performance-schema-quick-start.html

This post talks about the mechanism to register instruments in Performance Schema (referred as P_S from hereon) so that their statistics could be collected at runtime.

To understand this, lets first understand different instrument classes in P_S.

Instrument Classes in P_S:

An enum PFS_class_type is there to keep track of all possible classes of instruments.

enum PFS_class_type                                    

{                                                     
    PFS_CLASS_NONE=        0,                             

  PFS_CLASS_MUTEX=       1,                           
  PFS_CLASS_RWLOCK=      2,                           
  PFS_CLASS_COND=        3,                           
  PFS_CLASS_FILE=        4,                           
  PFS_CLASS_TABLE=       5,                           
  PFS_CLASS_STAGE=       6,                           
  PFS_CLASS_STATEMENT=   7,                           

  ...                                                 
};                                                           

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".

mysql> show status like "%performance%";
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Performance_schema_cond_classes_lost          | 0     |
| Performance_schema_file_classes_lost          | 0     |
 ...

Instrument Class Array 

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:

1. for all elements in sql_statement_info
1. make a formatted string to be stored for instrument name (eg. statement/sql/select)
2. call register_statement_class(), which would stores information of this instrument in statement_class_array and return its index.
3. store this index into m_key.

Now lets see what function register_statement_class() does.

1. call REGISTER_CLASS_BODY_PART to see if this instrument already registered. If yes, then return that index.
2. search for first empty index in  statement_class_array and populate information by calling init_instr_class().
3. populate event_name_index with index.
4. call configure_instr_class(), to set the user specified values (if any).
5. 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.

Performance Schema Implementation Internals : To be continued ...

Report on MySQL User Camp on 19th June, 2013

So we had MySQL User Camp on 19th June, 2013. Turn out of the people were less than what we expected based on the registration but even then it was a good gathering. Among the many familiar faces from the last MySQL User Camp in March 2013, many new faces could also be seen this time who missed MySQL User Camp last time.
 

Event started at its scheduled time sharp at 5:00 pm with a welcome speech by Sanjay Manwani, Director of MySQL India. He announced (tentative) date of next MySQL User Camp to be on 9th October, 2013.

It was followed by a talk on "InnoDB Storage Engine overview" by Annamalai Gurusami (MySQL India Sustenance Team Engineer). Talk went very well. Interest of people in InnoDB was evident from the number of questions they asked during the talk. Due to interesting questions and answer session, it took a bit longer and finished at 6:00 pm.

After this, we had a guest speaker, Karthik P.R. Karthik is MySQL DBA and is working with Wipro. He is a MySQL veteran and has explored MySQL in depth. He wanted to talk about his experience with MySQL. He spoke for only 15 mins but I must say, very encouraging talk which clearly pointed out benefits if a DBA or user chooses MySQL. There were few interesting take aways from development perspective as well.

Then it was 6:15 pm. I got a chance to speak about Performance Schema. Talk was aimed to give introduction of Performance Schema, what it is, how it works and what new we have in 5.6. It went very well with number of questions asked during and at the end of the session. People found Performance Schema very interesting and a tool which they always wished for. I could see the excitement on their face to use Performance Schema in 5.6. One of them asked if the source code of Performance Schema is available on-line (coz he wanted to play around with the code) and was happy to know that its source is open.

Talks finished at 7:00 pm. It was the time for informal networking with community users and refreshment. I got a chance to speak to community users over tea & Samosa (An Indian spicy snack). They seemed very excited about latest MySQL release - 5.6 and were willing to give it a try.

Feedback from attendees
I was having a look at feedback forms today morning. It felt very good to see such a positive response. People also suggested topics to be included in next talk like Replication, Optimization and database tuning and best practices. We would definitely take their suggestions into consideration.

Follow us on following group to participate in next MySQL User Camp:

Facebook group : MySQL User Camp
Linked-in group: MySQL India 

Google Group   : bangalore-mysql-user-camp

MySQL User Camp happening @ Bangalore

Yet another MySQL User Camp is here ... Last MySQL user camp was held on 22nd of March, 2013 and it went very well. Introduction of MySQL India Team followed by MySQL 5.6 Presentation was very well received by attendees.

Best part was informal discussion with MySQL enthusiasts (with donuts and juices in our plate ;-) ) who are keen to know more and more about MySQL. I got to meet people who were very interested in MySQL Performance Schema and were willing to know how to use it and what benefits it provides to users. And more importantly "How it works".

Looking at the attendees response, it was decided to have this "MySQL User Camp" in every 3 months. So here is the time, 19th June 2013, A Wednesday, we are having another MySQL User camp. And looking at curiosity of attendees last time, I am speaking about MySQL Performance Schema, area where I work in MySQL.

And there is more. A session about MySQL InnoDB storage Engine which is default storage engine in MySQL now (earlier MyISAM was MySQL default storage engine).

And ofcourse, all these followed by informal discussion over snacks. :)


Here is the link having detailed information about MySQL User Camp.
https://groups.google.com/forum/?fromgroups#!topic/bangalore-mysql-user-camp/gnqGPLKDB0s

So explore this opportunity to come and meet MySQL Developers and Users. Registration is free. Don't forget to send mail to register yourself.


See you all there ....

MySQL 5.6 : Performance Schema - Statement Digest

Along with lots of new features in performance schema, a new summary table, event_statement_summary_by_digest, has been added to MySQL 5.6. This tables gives a consolidated view of different statements executed/executing on MySQL server.

WHAT IS DIGEST?
Digest is a hash calculated on normalized form of statement text which removes general property of a statement but keeps specific property. For ex. for two statements X and Y,

Removing general property means
normalized (X) == normalized (Y)
Preserving specific property after normalization means:
normalized (X) != normalized (Y)

On these normalized statement, a hash is calculated which is called DIGEST. So digest is a unique representation of statements having same structure.


Example :
SELECT 1 FROM t1; 
SELECT 2 FROM t1; 
Above two statements have same general structure so they would end up having same digest.

SELECT 1 FROM t1;
SELECT 1 FROM t2.
Above two statements don't have same general structure as they have different table names (specific property), so they would be having different digests.

NEED FOR AGGREGATION BASED ON DIGEST?
As it is clear that aggregation based on digest will put together all statements having similar structure, this gives MySQL user (like DBA) a way to investigate what is happening with statement of specific types.

NEW TABLE(S) AND COLUMN(S)?
To store statements statistics based on digest, a new summary table is introduced named event_statements_summary_by_digest. And new columns DIGEST and DIGEST_TEXT are introduced in events_statements_current, events_statements_history and events_statements_history_long. DDL of tables look like following:

-->

CREATE TABLE `events_statements_summary_by_digest` (

`DIGEST` varchar(32),

`DIGEST_TEXT` longtext,

`COUNT_STAR` bigint(20) unsigned NOT NULL,

`SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,

`MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,

`AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,

`MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,

`SUM_LOCK_TIME` bigint(20) unsigned NOT NULL,

`SUM_ERRORS` bigint(20) unsigned NOT NULL,

`SUM_WARNINGS` bigint(20) unsigned NOT NULL,

`SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL,

`SUM_ROWS_SENT` bigint(20) unsigned NOT NULL,

`SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL,

`SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,

`SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,

`SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,

`SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,

`SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL,

`SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,

`SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL,

`SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,

`SUM_SORT_RANGE` bigint(20) unsigned NOT NULL,

`SUM_SORT_ROWS` bigint(20) unsigned NOT NULL,

`SUM_SORT_SCAN` bigint(20) unsigned NOT NULL,

`SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL,

`SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL,

'FIRST_SEEN TIMESTAMP(0) default 0,',

'LAST_SEEN TIMESTAMP(0) default 0,'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

And

CREATE TABLE `events_statements_current` (

...

`SQL_TEXT` longtext,

`DIGEST` varchar(32),

`DIGEST_TEXT` longtext,

...

`CURRENT_SCHEMA` varchar(64) DEFAULT NULL,

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

and similar to events_statements_history and events_statements_history_long.
A new consumer is added in setup_consumer table.

mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
...
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)
By default this consumer is enabled.
 
ANY NEW SERVER VARIABLES?
One new server variable is added which could be specified while starting mysql server:

performance-schema-digests-size
     Indicates the number of digest to be collected.
     Default value is 1000.

CHANGES ON USER'S SCREEN?
When server is started with performance schema, user sees following changes:

mysql> show variables like '%performance%';
+--------------------------------------------------------+---------+
| Variable_name                                          | Value   |
+--------------------------------------------------------+---------+
| performance_schema                                     | ON      |
...
| performance_schema_digests_size                        | 1000    |
...
+--------------------------------------------------------+---------+
30 rows in set (0.05 sec)

mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
...
| events_statements_summary_by_digest                |
...
+----------------------------------------------------+
52 rows in set (0.01 sec)

mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
...
| statements_digest              | YES     |
...
+--------------------------------+---------+
12 rows in set (0.00 sec)
All statements executed will be captured and their statistics will be stored in evens_statements_summary_by_digest. This table can be queried to see statements captured.
mysql> select DIGEST, DIGEST_TEXT, COUNT_STAR from performance_schema.events_statements_summary_by_digest;
+----------------------------------+-------------------------------------+------------+
| DIGEST                           | DIGEST_TEXT                         | COUNT_STAR |
+----------------------------------+-------------------------------------+------------+
| 021d13e766a40ee278c0d30dfcffbc58 | SELECT @ @ version_comment LIMIT ?  |          1 |
| ed3bec8844f8b8ac0632d5409e07e104 | SELECT SCHEMA ( )                   |          1 |
| 42b93d481e96b9c9b4049b9407900194 | SHOW SCHEMAS                        |          1 |
| a32df38ba4db0890b236e490d15889e2 | SHOW TABLES                         |          2 |
| 8204f53e4eae088fe292d09d13e8049d | SELECT VARIABLES LIKE ?             |          1 |
| 1c465973a09e28ad9da5f65303c66343 | SHOW VARIABLES LIKE ?               |          2 |
| b92f1ec95780e5dc6820a951f93af825 | SELECT * FROM `setup_consumer`      |          1 |
| f92a5f6103d7a9da0da846b5c5830fa0 | SELECT * FROM `setup_consumers`     |          2 |
+----------------------------------+-------------------------------------+------------+
8 rows in set (0.00 sec)
 
If number of different statements exceeds performance-schema-digests-size, statistics of all further new statements will be captured and stored at index 0 on table and as all different statements are captured there, DIGEST/DIGEST_TEXT will be NULL for this index. Based on number of statements captured in this index, DBA can decide upon an optimal value of performance-schema-digests-size.

mysql> show variables like "%digest%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 2     |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> select DIGEST, DIGEST_TEXT, COUNT_STAR from performance_schema.events_statements_summary_by_digest;
+----------------------------------+-------------------------------------+------------+
| DIGEST                           | DIGEST_TEXT                         | COUNT_STAR |
+----------------------------------+-------------------------------------+------------+
| NULL                             | NULL                                |          6 |
| 021d13e766a40ee278c0d30dfcffbc58 | SELECT @ @ version_comment LIMIT ?  |          1 |
+----------------------------------+-------------------------------------+------------+
2 rows in set (0.00 sec)

MySQL Performance Schema - OSI-Days, 2012

Here is the MySQL-Performance Schema presentation which I used at OSI, 2012.

http://www.slideshare.net/mayankmysql/osimysqlperformance-schema