Understand better the surprises I've met in the past with Linux I/O It was a long time now that I wanted to run some benchmark tests to You should -)Īny comments and feedbacks are very welcome!.Įxecution Plan descriptions beyond EXPLAIN Solution for the most straight forward adoption on the user land.ĭid you try already these features?. Persistent Statistics in InnoDB is the must! It just needs to have a Sometimes may give you even more surprises -)) MySQL may save you days of debugging! Then, even 20% of performanceĭegradation is nothing comparing to difference in bad response time.Īnd if I'm tracing only one user session, the global performanceĭegradation will be less than 1% -) while 20% difference on a webĪpplication (for ex.) you may even not see, as the network latency Tracing on demand! - such a feature added in to any application using
#UNDERSTAND MYSQL OPTIMIZER TRACE CODE#
So, keeping in mind these degradation levels, I'm pretty ready today toĪdd ahead in my code of all perf tools an option to enable Optimizer With enabled optimizer tracing: 41100 TPS => 16% degradation. With enabled optimizer tracing: 28500 TPS => 20% degradation. Optimizer tracing is enabled within all user sessionsĪll tests were executed with 32 concurrent users on 12 cores server. Persistent statistics are enabled on InnoDB Worse" case when the workload is more CPU-bound and queries response time So, what will be an overhead in the "most Near no degradation at all when InnoDB persistent statistics wereĪnd finally I was able quickly find the source of my problem! -))īut my "problematic" workload was more I/O-centric rather CPU-bound (evenĬPU was used near 100%). Was enabled on all(!) 32 concurrent sessions Less than 10% overall performance degradation once Optimizer Tracing The very positive from my observations was: To avoid such kind of problems, MySQL 5.6 introduced InnoDB persistent statistics - once enabled, the statistics will be updated only on the next time of ANALYZE TABLE execution! So, if your table is changed frequently and widely, you have to plan regular ANALYZE of all such tables (via cron or MySQL Event Scheduler, depending what is a more simple for you).Īll my problems were gone! no more broken execution plan anymore!. Seems I was very lucky until now to never meet such a problem, but looks like the issue is real, and may happen time to time in InnoDB databases during table statistics updates. So, for some reasons MySQL Optimizer is getting an information from InnoDB that my table containing only one single row instead of 200M rows. Then simply compare two traces, easy no? -)Īll details, the source of problem is shown by the following lines: SELECT query and obtain the optimizer trace when this query is executed SQL query in question, so it was very easy then to replay the same
Problematic cases! What is great that the trace is containing the whole And very quickly I was able to get trace files for my With Optimizer Trace enabled (currently the trace may be enabled onlyįrom the session itself, there is no any global setting available for So far, all my 32 concurrent users were now running SELECT TRACE INTO DUMPFILE '/tmp/trace.$(ID)-$(REF)' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE Īs it's much more simple to read this trace from a file rather SELECT More than 60sec, user then executed the following query to save the last SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000 Īnd then in case if in the user session the last executed query took SET OPTIMIZER_TRACE="enabled=on,end_marker=on" While regarding my stress code, I have toĪdd the following queries after CONNECT for my user sessions: Is helping a lot to understand the reason of choices made by optimizer.Īvailable on the Forge MySQL. Since MySQL 5.6 there was an Optimizer Tracing feature introduced which The time when the execution plan is wrong!. Which means that I have to catch the execution state exactly on So, seems that something is going wrong on the time when queriesĪre executed concurrently, and not when I'm trying to analyze themĪlone. And every time I'm executing EXPLAINįor any given "wrong" query - it's always giving me a right execution
#UNDERSTAND MYSQL OPTIMIZER TRACE FULL#
The read seems to be a kind of long read, involving many I/OĪll makes think that query is execution a full table scan (200M rows,Ĥ0GB table) and ignoring indexes, so instead of 1ms I have >5minīut the problem is completely "random" - it's not happening all of the Sessions are not really frozen, but waiting on read!. Sessions are "frozen" when executing a SELECT statement. Some of my user sessions become "frozen" periodically and finally my Last week I was faced to a strange problem while benchmarking MySQL 5.6: MySQL Performance: Overhead of Optimizer Tracing in MySQL 5.6
0 kommentar(er)
