A while back, I wrote a blog post explaining how PERFORMANCE_SCHEMA improvements in MySQL Server 5.7 provides new visibility into the SSL/TLS status of each running client configuration.  An excellent recent post from Frederic Descamps at Percona covers similar territory.  Both of us use PERFORMANCE_SCHEMA tables directly – a powerful interface, but one that requires a query joining multiple tables.  Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this same information is made far easier via the SYS schema.

I overlooked the SYS …

When trying out new software there are many other questions you may ask and one of those is going to be the one above. The answer requires you to have built your software to capture and record low level database metrics and often the focus of application developers is slightly different: they focus on how … Continue reading Is MySQL X faster than MySQL Y? – Ask queryprofiler

The post Is MySQL X faster than MySQL Y? – Ask queryprofiler first appeared on Simon J Mudd's Blog.

Performance Schema

In many types of database workloads, using a multi-threaded slave from 5.6+ helps improve replication performance. I’ve had a number of users enable this feature, but have not seen anyone ask how each thread is performing. Here’s a quick way with Performance_Schema to measure the amount of multi-threaded slave activity on each thread (after you have already configured MTS on your slave of course ).

First, we need to enable the 

statements

 instruments:

slave1> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements_%';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

Next, let’s find the

THREAD_ID

 for our slave workers:

slave1> SELECT THREAD_ID, …

MySQL Connector/Java supports connection attributes since version 5.1.25.  This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments.  One useful piece of information exposed is the version and vendor of the JVM in use by the client.  This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.

The metadata including the Java runtime environment version and vendor can be found in PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table.  Here’s the full contents of that table for a single connection from Connector/Java:

mysql> SELECT *
    -> FROM PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS
    -> WHERE processlist_id = 31\G
*************************** 1. row ***************************
  PROCESSLIST_ID: 31
       ATTR_NAME: _runtime_version
      ATTR_VALUE: …

Previous episodes:

• MySQL replication monitoring 101

MySQL replication in action - Part 1: GTID & CoMySQL replication in action - Part 2 - Fan-in topology


In the previous article, we saw the basics of establishing replication from multiple origins to the same destination. By extending that concept, we can deploy more complex topologies, such as the point-to-point (P2P) all-masters topology, a robust and …

MySQL 5.7 comes with many changes. Some of them are better explained than others.

I wanted to see how many changes I could get by comparing SHOW VARIABLES in MySQL 5.6 and 5.7.
The most notable ones are:

• binlog_format: the default is now ROW. This variable affects the format of the binary log, whether you use it as a backup complement or for replication, the change means bigger binary logs and possibly side effects.
• …

I thought it was worth a moment to reiterate on the new Performance Schema related defaults that MySQL 5.7.7 brings to the table, for various reasons.

For one, most of you might have noticed that profiling was marked as deprecated in MySQL 5.6.7. So it is expected that you invest into learning more about Performance Schema (and Mark’s sys schema!).

Second, there are lots of virtual environments and appliances out there running Community Edition MySQL where Performance Schema can be a useful tool for analyzing performance. Thus, expect to see more articles about using PERFORMANCE_SCHEMA and SYS_SCHEMA from us!

Third, we have more and more junior readers who might benefit from light reads such as this.

The new defaults that I wanted to highlight are mentioned in the …

As noted in an earlier post, MySQL Server 5.7 prefers and enables SSL/TLS connections by default.  That’s great and useful progress towards secure connections, but we know that not all SSL/TLS ciphers are created equal – some are older and more vulnerable.  Furthermore, some recent vulnerabilities rely on the ability to negotiate less-secure ciphers during the handshake.  Monitoring which ciphers are used can help identify connections using low-grade ciphers, but also to build an appropriate restricted cipher list.  Using improvements to PERFORMANCE_SCHEMA introduced in 5.7, you can now easily do this – and this post will show you how.

The cipher used for each TLS connection is stored in a …

With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure. Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:

mysql> UPDATE setup_consumers SET ENABLED="YES" 
           WHERE NAME = "events_statements_history_long"; 

Then let's call the stored procedure that we want to inspect:

mysql> CALL …

The performance_schema is a powerful tool for analyzing MySQL performance and behavior. One aspect of the performance_schema is that the view of the data is "right now", and very little historical information is present. You can see that there are 10 threads connected right now, but what about five minutes ago?

ps_history
ps_history is a set of stored routines and events for periodically collecting the data in the performance_schema into another schema called ps_history. The ps_history schema contains a copy of each performance_schema view as a real table, and timestamp and server_id columns have been added to each table. Periodically (by default every 30 seconds) the performance_schema data is written into the history tables.

ps_history comes as one script (setup.sql) which will create the ps_history schema, the tables within it, and …