imavast.blogg.se - Understand mysql optimizer trace

#UNDERSTAND MYSQL OPTIMIZER TRACE UPDATE#
#UNDERSTAND MYSQL OPTIMIZER TRACE FULL#
#UNDERSTAND MYSQL OPTIMIZER TRACE CODE#

On top of that, tuning is usually very time-consuming.

#UNDERSTAND MYSQL OPTIMIZER TRACE UPDATE#

For one thing, it requires a significant amount of expertise in order to understand execution plans, and often update or re-write good SQL. Let’s face it, tuning is difficult for a number of reasons. However, performance tuning is often easier said than done. You should -)Īny comments and feedbacks are very welcome!.With the added complexity of growing data volumes and ever-changing workloads, database performance tuning is now necessary to maximize resource utilization and system performance. Just needs to have a solution for the most straight forwardīTW, did you try already these features?. You may even not see, as the network latency sometimes may giveĪnd seems that Persistent Statistics in InnoDB is the must! It Than 1% -) while 20% difference on a web application (for ex.) User session, the global performance degradation will be less

#UNDERSTAND MYSQL OPTIMIZER TRACE CODE#

Today to add ahead in my code of all perf tools an option toĮnable Optimizer Tracing on demand! - such a feature added in toĪny application using MySQL may save you days of debugging! Then,Įven 20% of performance degradation is nothing comparing toĭifference in bad response time. So, keeping in mind these degradation levels, I'm pretty ready

with enabled optimizer tracing: 41100 TPS => 16%.

with enabled optimizer tracing: 28500 TPS => 20%.

optimizer tracing is enabled within all user sessionsĪll tests were executed with 32 concurrent users on 12 cores.

persistent statistics are enabled on InnoDB.

Let's get a look on the following graphs representing 3 test Overhead in the "most worse" case when the workload is moreĬPU-bound and queries response time is less than 1ms?.

and finally I was able quickly find the source of my problem!īut my "problematic" workload was more I/O-centric ratherĬPU-bound (even CPU was used near 100%).

near no degradation at all when InnoDB persistent statistics.

Tracing was enabled on all(!) 32 concurrent sessions

less than 10% overall performance degradation once Optimizer.

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. Skiping all details, the source of problem is shown by the Trace when this query is executed with a right plan. Then to replay the same SELECT query and obtain the optimizer And very quickly I was able to get traceįiles for my problematic cases! What is great that the trace isĬontaining the whole SQL query in question, so it was very easy Optimizer Trace enabled (currently the trace may be enabled onlyįrom the session itself, there is no any global setting availableįor security reasons). So far, all my 32 concurrent users were now running with 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 Save the last query optimizer trace into a file: Took more than 60sec, user then executed the following query to SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000 Īnd then in case if in the user session the last executed query SET OPTIMIZER_TRACE="enabled=on,end_marker=on"

Regarding my stress code, I have to add the following queries since MySQL 5.6 there was an Optimizer Tracingįeature introduced which is helping a lot to understand the Have to catch the execution state exactly on the time when the Going wrong on the time when queries are executed concurrently,Īnd not when I'm trying to analyze them alone. And every time I'mĮxecuting EXPLAIN for any given "wrong" query - it's always Of the time and not in all users sessions. (200M rows, 40GB table) and ignoring indexes, so instead of 1ms Iīut the problem is completely "random" - it's not happening all

#UNDERSTAND MYSQL OPTIMIZER TRACE FULL#

all makes think that query is execution a full table scan.

the read seems to be a kind of long read, involving many I/O.

sessions are not really frozen, but waiting on read!.sessions are "frozen" when executing a SELECT statement.MySQL 5.6: some of my user sessions become "frozen" periodicallyĪnd finally my whole test was not valid. Last week I was faced to a strange problem while benchmarking