![]() ![]() | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | In my case, it would be "please do not trace greedy search".Īs an example, let's consider twenty tables, t1.t20, all similar to this one: It would be nice if I could tell the system: "please do not trace this and that". If I set to a high value, to see what I want, greedy search will be traced which will possibly exceed the amount of memory I can afford on this machine. What if I want to see the trace of what happens in the Optimizer after greedy search is complete? If I set to a low value, it will trim greedy search and what follows. Greedy search ends up being the greatest part of the trace. When there are many tables to join, as I said above the trace can grow a lot, because the Optimizer evaluates many possible join orders (using an algorithm known as " greedy search"): each evaluated join order is mentioned in the trace. One last benefit is that when I have finished reading, quitting " less" makes the trace go away from the terminal, it does not linger on my screen forever, does not occupy the terminal's display history. I have all " less" commands under hand: can save to a file, can search for a regular expression match, search forward or backward. I often raise it in my session, to 1 megabyte, which is enough for most queries in my daily work: Which has a default of 16 kilobytes yes, it's a low value, to protect the innocent. Up to gigabytes in some pathological cases! To avoid hogging memory then, a trace will never grow beyond the value of the session variable. ![]() An SQL statement which gives the optimizer a lot of work (for example, by joining many tables) will generate a large trace. Today I will be giving some must-have tips related to handling big traces.įirst thing to know, a trace lives in main memory (internally it is allocated on the heap or free store of the MySQL Server). It's good to see it mature now I remember that Sergey Petrunia did the first prototype back in March 2009! The Optimizer Tracing feature can help understanding what the Optimizer is doing it is available since milestone 5.6.3, announced October 3rd at Oracle Open World (here is the changelog). I recommend reading his article, as it presents this new feature in a simple, easy-to-read manner. Query to OPTIMIZER_TRACE table from information_schema: mysql> select query, trace from INFORMATION_SCHEMA.In this blog post, my colleague Jørgen Løland described a new feature of MySQL 5.6: Optimizer Tracing. SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000 Īfter run first query: mysql> select count(*) from sales So before running query you should enable optimizer trace: SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on See related BUG: Now let’s examine problem with Optimizer Tracer. From first view, there now difference between queries, but optimizer estimates half of rows when attaching sales_id > 0 condition. First query will use, sales_cust_idx in customer_id column, second query will use primary key in sales_id column. Mysql> explain select count(*) from sales where sales_id > 0GĮxtra: Using where Using index 1 row in set (0.00 sec)įrom Explain plan it is obvious that, first query will use “Index Scan”, second will use “Range Scan + Index”. ![]() Mysql> explain select count(*) from salesG Mysql> explain format=json select count(*) from sales | For understanding goal of article please read previous one about related verified optimizer BUG: ** We have 2 queries: **select count() from sales select count(*) from sales where sales_id > 0 Firstly let’s get explain plan for query with JSON format and as regular: - JSON You enable the optimizer trace, then you run the actual query. It is similar to EXPLAIN, with a few notable differences: It doesn’t just show the intended execution plan, it shows the alternative choices. From : Optimizer trace is a new diagnostic tool introduced in MySQL 5.6 to show how the optimizer is working internally. What is Optimizer Trace? After reading topic about Optimizer Tracer by decided to test it. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |