In this blog, I will answer questions posed in my recent introduction to MySQL troubleshooting basic techniques webinar.
Thank you for attending my March 10 MySQL troubleshooting webinar “Introduction to MySQL troubleshooting: basic techniques,” which is the second in the “MySQL Troubleshooting Webinar” series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with responses.
Q: Any known issues with auditing? We see a real slowdown with insert/update performance. Best way to track it down?
A: Sorry, I did not completely understand your question. Do you see a real slowdown when turning auditing on? In this case: which type of auditing? Percona Audit plugin or something else? Or do you simply have slow inserts and updates and want to track them down?
Regarding the effect of auditing: nothing comes without a price. Any auditing means you will, at least, store queries and write them to disk. This will take CPU and disk resources. Therefore, on some very busy servers keeping the general query log or audit log always ON isn’t an option. But you can turn them ON for limited periods of time when an issue occurs. If you create custom auditing, you can setup better filtering rules and log only those queries that you’re interested in. In any case, if the binary log is ON, all inserts and updates will be there. As I mentioned during the webinar, it can miss some information about the environment when an insert or update was executed, but it will still give you idea of what happened. Then, if needed, you can turn ON more verbose auditing for a short period.
If you’re asking if it is possible to track down a reason for insert and update performance degrading: yes, it is possible. I will discuss this topic in the next webinar: Introduction to Troubleshooting Performance: What Affects Query Execution? I also plan more detailed webinars about optimizing slow queries, locking and storage engine issues – both of which should be helpful in this case as well. (The dates for these webinars are still to be decided.) But in general, you need to check first if you can repeat slowdown in single thread environment. If yes: you need to tune inserts and updates. When you will do so note what UPDATE statements are optimized in a similar way as SELECT statements. This means that you can run EXPLAIN on them, probably adding indexes, so that the WHERE condition can be optimized more effectively. For single-thread INSERT slowdowns, you need to work with table and storage engine options, such as removing unused indexes or making IO and CPU-related options optimal. If instead, the slowdown is repeatable in a multiple-connection environment only this means you hit locking issue. You need to study how table, metadata and storage engine locks work, find when they set in your application and implement ways to workaround this issue (in simplest case just ensure what no two parallel updates update the same row same time in case of InnoDB).
Q: Will we see relative performance differences on 5.6 vs. 5.7 queries?
A: Yes, this is possible. When Oracle announced GA for 5.7, they claimed that some queries are three times faster than in 5.6 (proof). You can also find benchmarks, which prove that performance in 5.7 is much better than in 5.6 at Dimitry’s Kravtchuk blog: 1, 2, 3, 4, 5.
At the same time, I already worked on a few support tickets where the user claimed that some of their queries work slower in 5.7 than in 5.6. There are also a public bug reports about a similar issue: 1, 2. This is normal during any upgrade. The reason for these slowdowns is the fact what while the MySQL Optimizer developers are consistently working on making it better, they also change the heuristics that chose an optimal plan. Sometimes queries, optimized for earlier versions, need to be rewritten after an upgrade – especially ones that use index hints.
I’ve seen a few issues (between 5.5->5.6 and 5.1->5.5 upgrades) where customers claimed an overall application performance slowdown when keeping index hints that were created to improve performance in previous versions. After the index hints were removed, the application started working much faster than it did with the earlier MySQL version.
Q: The system variables are “policies” controlling InnoDB optimization that change by version.
A: I did not understand this question, sorry. Did you just want to say that “the system variables are ‘policies’ controlling InnoDB optimization”? Well, there aren’t just InnoDB-related variables that can affect the performance and behavior of the server. Regarding version upgrades, I would this isn’t true. From a support point of view, the MySQL Server team does not change defaults for the system variables often, as these variables are required by the market. Changes introduced by version 5.7 to the defaults are just “written in stone” minimalistic recommendations that have been tested by MySQL Support engineers and customers for years. Server development makes these changes
Server development makes these changes very conservatively because the MySQL and Percona user base are very large: laptop users (including students), database application developers, support engineers and consultants (who travel a lot), to big shops that can afford most powerful hardware on the market. Of course, the settings will be different for each of these groups. The defaults are just appropriate for a modern, but not the most powerful, laptop with 4-8G of RAM and a spinning disk.