My eighteen MySQL 5.6 favorite troubleshooting improvements
By Sveta Smirnova on Jan 12, 2013
MySQL 5.6 is in RC state now which means it is going to be GA sooner or later.
This release contains a lot of improvements. However, since I am a support engineer, I most amazed by those which make troubleshooting easier.
So here is the list of my favorite troubleshooting improvements.
1. EXPLAIN for UPDATE/INSERT/DELETE.
This is extremely useful feature.
Although prior version 5.6 we, theoretically, could have some kind of explain for them too, for example, if convert DML queries to their SELECT equivalents, optimizer can optimize them differently.
We still could execute DELETE or UPDATE, then query Hadler_% status variables, but who wants to execute update just for testing on live database? And anyway, querying Handler_% variables we could only know if some index was used or not, but can not identify which one.
2. INFORMATION SCHEMA.OPTIMIZER_TRACE table.
This table contains trace of last few queries, number of which is specified by configuration option optimizer_trace_limit.
Actually we could have similar information prior version 5.6: just use debug server and start it with option --debug. In this case MySQL server creates trace file where it writes information about functions and methods executed during server run. Problem with such files they are extremely verbose and large. It is hard to find necessary information there if you are not MySQL developer. As support engineer I use them when need to test a bug, but I use as follow: create very short script which executes as less queries as it can, then start MySQL server with debug option and run the test. Then stop debugging. Even with such short single-threaded tests size of resulting file is thousands of rows. It is almost not possible to use such files in production.
With optimizer trace we can have similar information just for optimizer, but in much compact way.
Hmm... I want similar feature for all parts of the server!
3. EXPLAIN output in JSON format.
This is actually simply syntax sugar around normal EXPLAIN output. It prints exactly same information like normal, table-view EXPLAIN, but can be used for some automations.
4. New InnoDB tables in Information Schema.
Table INNODB_METRICS contains a lot of information about InnoDB performance, starting from InnoDB buffer usage and up to number of transactions and records.
Tables INNODB_SYS_* contains information about InnoDB internal dictionary and its objects. Particularly about tables, fields, keys, etc.
Table INNODB_SYS_TABLESTATS contains information about InnoDB performance statistics.
Tables INNODB_BUFFER_POOL_* store information about InnoDB Buffer Pool usage.
5. Option to log all InnoDB deadlocks into error log file: innodb_print_all_deadlocks.
Currently we can use InnoDB Monitor output for the same purpose, but it prints latest deadlock only, not everyone since server startup. This feature should be very handy to troubleshoot issues, repeatable on production only.
6. Persistent statistics for InnoDB tables.
It is also OFF by default and can be turned ON with help of option innodb_analyze_is_persistent.
It was originally OFF. Now this option has name innodb_stats_persistent and is ON by default.
But this is performance improvement and how does it relate to troubleshooting?
In version 5.5 by default when you run query ANALYZE TABLE and after each MySQL server restart InnoDB renews table statistics which optimizer uses to generate better execution plans. Unfortunately such often statistics renewal does not fit for all data and tables. Sometimes fresh statistics can lead to choice of not the best plan. Now, if innodb_stats_persistent is ON, statistics get renewed only after ANALYZE TABLE and stays same after server restart.
In past we sometimes get complains from people who initially had fast perfectly running query, but performance decreased after inserting few millions of rows. As you can imagine rebuilding the table does not help in this case. For such cases we offered FORCE/IGNORE INDEX as a workaround which usually means customers had to rewrite dozens of queries.
Now we can start recommending to load data for which optimizer chooses best plan, then ANALYZE TABLE and never update statistics again.
7. InnoDB read-only transactions.
Every query on InnoDB table is part of transaction. This does not depend if you SELECT or modify the table. For example, for TRANSACTION ISOLATION levels REPEATABLE-READ and SERIALIZABLE, InnoDB creates a snapshot for data which was received at the first time. This is necessary for multiversion concurrency control. At the same time such snapshots slow down those transactions which are used only for reads. For example, many users complained about catastrophic performance decrease after InnoDB reached 256 connections. And if for transactions, which modify data, such slowdown is necessary for better stability, for read-only transactions this is not fair.
To solve this issue in version 5.6 new access modificator for transactions was introduced: READ ONLY or READ WRITE. Default is READ WRITE. We can modify it with help of START TRANSACTION READ ONLY, SET TRANSACTION READ ONLY queries or variables. InnoDB run tests which showed that usage of READ ONLY transaction completely solves 256 threads issue.
If InnoDB is running in autocommit mode it considers data snapshot for SELECT queries is not needed and does not create it. This means SELECT performance in autocommit mode is same as if READ ONLY transactions are used.
InnoDB Team published in their blog nice graphs with results of benchmarks of this new feature. Blog post is available here.
8. Support of backup/restore of InnoDB Buffer Pool during restart and on demand.
This feature can noticably speed up first few running hours of installations which use large InnoDB Buffer Pool. Earlier, when application with large InnoDB Buffer Pool started, some time, until queries filled the buffer, performance could be not so fast as it should. Now, we should not wait until the application execute all queries and fills up the pool: just backup it at shutdown, then restore at startup or in any time while MySQL server is running.
9. Multithreaded slave.
Prior version 5.6 slave could be catching up the master very slowly not because it runs on slow hardware, but due to its single-threaded nature. Now it can use up to 1024 parallel threads and catch up the master more easily.
10. Possibility to execute events from binary log with N-seconds delay.
This feature is opposite to previous one. Why did I put it in my troubleshooting improvements favorites list than? Because in addition to its main purposes, such as possibility to rollback DROP, DELETE (and other) operations we can also use this technique when master sends updates which slow down parallel reads executed by slave. For example, we can delay expensive ALTER TABLE until time when peak load on a web site passed. Because this option is set up on a slave, we can spread such expensive queries among them, so the application can use another slave for reads while one is busy.
11. Partial images in RBR.
This feature can increase replication performance dramatically.
Today one of most popular row-based replication performance issues is long transferring time of large events. For example, if you have a row with LONGBLOB column, any modification of this row, even of an integer field, will require to send whole row. In other words, to modify 4 bytes master will send to slave up to 4G. Since version 5.6 it is possible to setup how to send rows in case of row-based replication.
binlog_row_image=full will mimic 5.5 and earlier behavior: full row will be stored in the binary log file.
binlog_row_image=minimal will store only modified data
binlog_row_image=noblob will store full row for all types except blobs
12. GET DIAGNOSTICS queries and access to Diagnostic Area.
Diagnostic Area is a structure which filled after each query execution. It contains two kinds of information: query results, such as number of affected rows, and information about warnings and notes.
Now we can access Diagnostic Area through GET DIAGNOSTICS queries.
13. HANDLERs processing is more like SQL standard now.
I wrote about this two times already: here and here
So I would not repeat my second post, just mention again I am finally happy with how HANDLERs work now.
These are huge improvements!
In version 5.5, when performance schema was introduced, I'd say it was mostly designed for MySQL developers themselves or at least for people who know MySQL source code very well. Now performance schema is for everybody.
14. New instruments to watch IO operations.
We can even watch IO operations for specific table. There are also new aggregation tables by these operations.
15. New tables events_statements_* instrument statements.
Now we can know what happens during query execution.
Things like SQL text of the query, event name, corresponding to particular code and statistics of the query execution.
16. Instruments for operation stages: events_stages_* tables.
EVENT_NAME field of these tables contains information similar to content of SHOW PROCESSLIST, but field SOURCE has exact source code row there operations executed. This is very useful when you want to know what is going on.
I strongly recommend you to study events_statements_* and events_stages_* tables
17. New digests: for operators by account, user, host and so on.
These are very useful for work with historical data.
Of course, there are history tables, which we know since version 5.5, exist for both events_stages and events_statements.
We can also filter information by users, sessions and tables.
18. HOST_CACHE table.
This table contains information about client host and IP address which are stored in the host cache, so MySQL server should not query DNS server second time.
I am not sure why it is in Performance Schema and not in Information Schema, but maybe this is only me.
I expect to get use of it when diagnosing connection failures.