Category Archives: Performance Schema

MySQL 8.0: Performance Schema Instrumentation of Server Errors

September 19, 2016MySQL, Performance SchemaMayank Prasad

In MySQL 8.0.0, the Performance Schema can now instrument server errors. There are 5 new summary tables introduced:

mysql> show tables like "%by_error%";
+-------------------------------------------+
| Tables_in_performance_schema (%by_error%) |
+-------------------------------------------+
| events_errors_summary_by_account_by_error |
| events_errors_summary_by_host_by_error    |
| events_errors_summary_by_thread_by_error  |
| events_errors_summary_by_user_by_error    |
| events_errors_summary_global_by_error     |
+-------------------------------------------+
5 rows in set (0.02 sec)

mysql> show tables like "%by_error%";

+-------------------------------------------+

| Tables_in_performance_schema (%by_error%) |

+-------------------------------------------+

| events_errors_summary_by_account_by_error |

| events_errors_summary_by_host_by_error |

| events_errors_summary_by_thread_by_error |

| events_errors_summary_by_user_by_error |

| events_errors_summary_global_by_error |

+-------------------------------------------+

5 rows in set (0.02 sec)

As names suggest, statistics of errors in these tables are aggregated by error.…

MySQL 8.0: Performance Schema, now with indexes!

September 16, 2016MySQL, Performance SchemaMarc Alff

In MySQL 8.0 we are improving the Performance Schema to support indexes. This increases the flexibility and ease of use of using performance_schema, as a number of monitoring queries will see a dramatic speed up in performance.

Background

Since it’s initial launch in MySQL 5.5, the Performance Schema has grown to 93 tables, with some of these exposing a lot of data.…

Extending the SYS schema to show metadata locks

April 6, 2016MySQL, Performance Schema, Sys SchemaMatt Lord

Starting with MySQL 5.7, performance_schema has been enhanced to instrument metadata locks, and can be turned on by adding the following line to your my.cnf file:

performance-schema-instrument='wait/lock/metadata/sql/%=ON'

1	performance-schema-instrument='wait/lock/metadata/sql/%=ON'

(At runtime, it can also be enabled by modifying the setup_instruments table in performance_schema.)

From here, you can now query performance_schema.metadata_locks to reveal all currently open metadata locks on your server:

mysql> select * from performance_schema.metadata_locks\G
*************************** 1.

…

New in MySQL 5.7: Performance Schema scalable memory allocation

February 11, 2016MySQL, Performance SchemaMayank Prasad

Performance Schema is a mechanism to collect and report run time statistics for running MySQL server. These statistics are stored-in and fetched-from internal memory buffers. In MySQL 5.6 GA, memory for these buffers is allocated during MySQL server startup with either user specified configuration values or with default values that autosize.…

Track and Optimize Server Connection Methods

September 9, 2015Audit, Enterprise, Logging, News, Performance Schema, Security, Windowsaudit, connections, general, log, monitoring, protocol, tcpGeorgi Kodinov

The MySQL server supports a variety of client connection methods. To summarize: you have TCP/IP (v4 and v6) on all OSes (with or without TLS/SSL encryption), Unix Domain Sockets on Unix/Linux, and Named Pipes and/or Shared Memory on Windows.

Each of these connection methods has its own set of pros and cons: speed, security, portability, and ease-of-use.…

Identifying Insecure Connections

August 27, 2015MySQL, Performance Schema, Security, SSL, Upgradesmysql, performance schema, SecurityMatt Lord

A key theme of the MySQL Server 5.7 release is much improved security. Earlier releases of MySQL 5.7 have introduced features supporting this initiative including automatic generation and detection of TLS key material and client-side preference for TLS connections. The recent MySQL 5.7.8 release builds upon this and provides additional monitoring and audit capabilities that make it easy to answer the question: “How secure are my client connections?”.…

Performance Schema: Great Power Comes Without Great Cost

April 17, 2015Benchmarking, MySQL, Performance SchemaTarique Saleem

Performance Schema is used extensively both internally and within the MySQL community, and I expect even more usage with the new SYS Schema and the Performance Schema enhancements in 5.7. Performance Schema is the single best tool available for monitoring MySQL Server internals and execution details at a lower level.…

MySQL Statement Digests

March 25, 2015MySQL, Performance Schemaperformance schemaMayank Prasad

Decoupling Statement Digests From Performance Schema

MySQL Statement Digests are a feature originally introduced as part of the MySQL Performance Schema in MySQL 5.6 to aggregate statement statistics based on the normalized statements executed within the server (for additional info, see here).…

MySQL Statement Digests: Configurable Max Lengths

March 24, 2015MySQL, Performance Schemaperformance schemaMayank Prasad

Digest calculations are done based on the tokens found in the statement text.…

MySQL Performance Schema: Instrumentation Exceptions

March 23, 2015MySQL, Performance Schemaperformance schemaMayank Prasad

The setup_actors table in MySQL Performance Schema can be used to specify what users and hosts one wants to have instrumentation enabled for. By default, connections from all users and hosts are instrumented:

mysql> select * from performance_schema.setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.setup_actors;

+------+------+------+

| HOST | USER | ROLE |

+------+------+------+

| % | % | % |

+------+------+------+

1 row in set (0.00 sec)

You can then use standard SQL against this setup_actors table in order to specify what users and hosts you want to have instrumentation enabled for.

…

MySQL Server Blog

News from the MySQL Server Team

Category Archives: Performance Schema

MySQL 8.0: Performance Schema Instrumentation of Server Errors

MySQL 8.0: Performance Schema, now with indexes!

Background

Extending the SYS schema to show metadata locks

New in MySQL 5.7: Performance Schema scalable memory allocation

Track and Optimize Server Connection Methods

Identifying Insecure Connections

Performance Schema: Great Power Comes Without Great Cost

MySQL Statement Digests

Decoupling Statement Digests From Performance Schema

MySQL Statement Digests: Configurable Max Lengths

MySQL Performance Schema: Instrumentation Exceptions