Monday, November 5, 2012

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,

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.