When a new version of MySQL is about to be released we read a lot of blog posts about the performance and scalability improvements. That’s good but sometimes we miss some small features that can help us a lot in our day-to-day tasks. One good example is the blog post that Aurimas wrote about a new small feature in MySQL 5.6 that I didn’t know about until I read it: the Automatic InnoDB transaction log file size change. How cool is that?
I plan to write a series of blog posts that will show some of those small new features in MySQL 5.7 that are going to be really useful. I’m going to start with EXPLAIN FOR CONNECTION.
This feature allows us to run an EXPLAIN for an already running statement. Let’s say that you find a query that has been running for a long time and you want to check why that could be happening. In 5.7 you can just ask MySQL to EXPLAIN the query that a particular connection is running and get the execution path. You can use it if the query is a SELECT, DELETE, INSERT, REPLACE or UPDATE. Won’t work if the query is a prepared statement though.
Let me show you an example of how it works.
We have a long running join.
mysql [localhost] {msandbox} ((none)) > show processlist G
*************************** 1. row ***************************
Id: 9
User: msandbox
Host: localhost
db: employees
Command: Query
Time: 49
State: Sending data
Info: select count(*) from employees, salaries where employees.emp_no = salaries.emp_noLet’s see the execution plan for the query:
mysql [localhost] {msandbox} ((none)) > explain for connection 9 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 299540
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: salaries
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2803840
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)The join between those tables is not using any index at all so there is some room for improvement here 
Conclusion
You can use this feature to see why a query is running for too long and based on the info decide how to fix it and how to proceed. This is going to be a very useful feature for DBAs who want to diagnose performance problems and slow queries.
The post Getting EXPLAIN information from already running queries in MySQL 5.7 appeared first on MySQL Performance Blog.
About Percona Monitoring Plugins
