Saturday, September 17, 2016

MySQL Optimizer Sessions at Oracle OpenWorld

Oracle OpenWorld starts soon and there will be a few sessions on the MySQL Optimizer.   On Monday, I will have my tutorial on how to analyze and tune MySQL queries.  Later in the week Manyi Lu, the MySQL optimizer team manager, will present what was new in the MySQL optimizer 5.7, and also give a sneak peek into the MySQL 8.0 release.  Both of us will together have a presentation  on Common Table Expressions; a new SQL feature in MySQL 8.0.  The details are as follows: 

Monday, Sep 19, 1:45 p.m. 
How to Analyze and Tune MySQL Queries for Better Performance [TUT3750]
Oystein Grovlen, Senior Principal Software Engineer, Oracle

Wednesday, Sep 21, 3:00 p.m.
MySQL Optimizer: What’s New in 5.7 and Sneak Peek at 8.0 [CON6112]
Manyi Lu, Director Software Engineering, Oracle

Thursday, Sep 22, 12:00 p.m.
MySQL 8.0: Common Table Expressions [CON7928]
Manyi Lu, Director Software Engineering, Oracle
Oystein Grovlen, Senior Principal Software Engineer, Oracle


All MySQL tutorials and conference sessions are this year at at The Park Central hotel.  For more information on MySQL sessions, see Focus on MySQL @ OpenWorld.  OpenWorld participants can build their own OpenWorld agenda and reserve seats for sessions by going to the OpenWorld home page and select Tools and Resources → My Schedule.

We will also like to invite people to the MySQL community reception at Jillian's on Tuesday, September 20 @ 7 pm.  This reception is not limited to OpenWorld registrants.  So if you are in the SF area, please, feel free to come and meet MySQL developers and users!

Friday, March 4, 2016

Oracle Virtual Technology Summit

In the coming weeks, Oracle Technology Network welcomes you to join the Virtual Technology Summit which is a half-day online conference with multiple tracks.  This time there is a MySQL track, and I will do a presentation on Analyze & Tune MySQL Queries for Better Performance.  The MySQL track also contains a presentation on MySQL 5.7, and a presentation on how to use Oracle Enterprise Manager to manage MySQL databases.

There will be three events with the same program, but at different times to best suit different parts of the world.   The first opportunity is the Americas event on March 8.  Later, there will be events for Asia/Pacific (March 15) and Europe/Middle East/Africa (April 5).  

To register, go to the pages for the Americas, Asia/Pacific, and Europe/Middle East/Africa events, respectively.  It will also be possible to listen to the webcast after the events, but then you will miss the opportunity to ask questions as we go along.

 

Wednesday, January 27, 2016

How to Get Optimizer Trace for a Query

While EXPLAIN shows the selected query plan for a query, optimizer trace will show you WHY the particular plan was selected. From the trace you will be able to see what alternative plans was considered, the estimated costs of different plans, and what decisions was made during query optimization.

To turn on recording of optimizer trace for the current session:
SET optimizer_trace='enabled=on';

When optimizer trace is enabled, the information schema table optimizer_trace will contain the trace for the latest query that was explained or executed. I usually dump the trace to a file using this SQL statement:
SELECT trace FROM information_schema.optimizer_trace 
    INTO OUTFILE '<filename>' LINES TERMINATED BY '';

One important thing to note is that there is a configurable maximum size for the memory buffer used to record the trace. The default is pretty low, and you will often have to increase the size to capture the entire trace. The missing_bytes_beyond_max_mem_size column of the optimizer_trace table shows how many bytes are missing from the trace. If this column is non-zero, you should increase the setting of the variable optimizer_trace_max_mem_size:
SET optimizer_trace_max_mem_size=1000000;

The above statement increases the trace buffer to about 1 MB. Unless your query joins very many tables, this should be sufficient.

To get more information about optimizer trace, check:

Optimizer tracing: Query Execution Plan descriptions beyond EXPLAIN
Optimizer tracing: how to configure it
MySQL Internals Manual on Optimizer Tracing

Tuesday, January 26, 2016

Improved Performance of Queries with Derived Tables

Last month/year I published a blog post on mysqlserverteam.com with an example of how MySQL 5.7 gives you improved performance for queries with derived tables.

The query example was part of my tutorial “How to Analyze and Tune MySQL Queries for Better Performance” at Oracle OpenWorld 2015. Slides for the entire presentation can be found here.

Tuesday, April 21, 2015

Slides from Percona Live and airbnb Tech Talks

Last week I presented my talk, "How to Analyze and Tune SQL Queries for Better Performance" both at Percona Live in Santa Clara and at airbnb Tech Talks in San Francisco.  The slides are available on slideshare. A video recording from the airbnb talk should eventually be available the airbnb Tech Talks page.

Wednesday, February 25, 2015

MySQL Webinar: Analyze & Tune Queries for Better Performance

Thanks to everyone who attended my webinar today, and thanks for all the positive feedback in the Q&A session.  Unfortunately, I was not able to respond to everyone during the 15 minutes available for Q&A.  If your question did not get answered, feel free to use the comments section of this blog post to ask your question.  You can also ask questions on the MySQL Optimizer Forum.

The slides from the presentation are available here.  In a few days, I expect it to be possible to access it as an MySQL On-Demand Webinar.

Wednesday, February 18, 2015

MySQL Webinar: Analyze & Tune Queries for Better Performance

On Wednesday, February 25 at 18:00 CET (9 am Pacific Time), I will do webinar on how to analyze and tune MySQL queries for better performance.

The webinar covers how the MySQL optimizer chooses a specific plan to execute SQL queries. I will also show you how to use tools such as EXPLAIN (including the new JSON-based output) and Optimizer Trace to analyze query plans. We will also review how the Visual Explain functionality available in MySQL Workbench helps us visualize these plans. The webinar will also contain several examples of how to take advantage of the query analysis to improve performance of MySQL queries.

The presentation will be approximately 60 minutes long followed by Q&A.

For details on how to register for the webinar visit http://www.mysql.com/news-and-events/web-seminars/analyze-tune-queries-for-better-performance/