During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write buffer is, it is a disk buffer were pages are written before being written to the actual data file. Upon restart, pages in the double write buffer are rewritten to their data files if complete. This is to avoid data file corruption with half written pages. I knew it has an impact on performance, on ZFS since it is transactional I always disable it, but I never realized how important the performance impact could be. Back from PLMCE, a friend had dropped home a Dell R320 server, asking …

On Wednesday I gave a presentation on “How to Avoid Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does a JOIN operation between two tables always produce an “access table” on the rows of the first table of the join, or it is possible to define an index (COVERING INDEX) to avoid this access to the first table?

Yes, if your …

The MySQL February Newsletter is available! Find out the latest news on MySQL products and MySQL Connect 2014, and read the technical tips written by MySQL experts at Oracle and in the community. Below are the highlights in this edition:

• Start Preparing for MySQL Connect 2014 Call for Papers
• New GA Release: MySQL for Visual Studio
• Blog: State of the UNION
• Blog: New MySQL Web Installer -- Have You Tried It Yet?
• Blog: MySQL Workbench: Frequent Crashes on Mac OS X? This Might Be The Fix
• Event: MySQL Seminars Are Coming to a City Near You

You can read it online or subscribe to it to receive the latest updates right in your inbox. Watch this …

Sequel Pro

I often use Sequel Pro when I'm getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It's a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in …

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from INFORMATION_SCHEMA slowness, I think it is good to bring innodb_stats_on_metadata back on the table.

The problem

Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting in memory but not the whole dataset, around 4000 InnoDB tables.

The I/O load is very light as …

Time zones are a tricky feature. You live in a given time zone, and most of the time you won’t think about that at all. You may live in a place where you are conscious of time zones, such as the United States, if your business spans across the country, where you know that New York is three hours ahead of San Francisco or Chicago and Dallas share the same time zone. Time Zone support in MySQL is a complicate business in itself. Once you have updated your time zone tables, you can set your time zone in an human readable format:

set global time_zone="America/Los_Angeles";

This is nice and well. It tells you which time zone your server is working with. However, things get a bit hairy when you need to do practical …

Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:

mysql> select replace(@@optimizer_switch, ',','\n')\G …

Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:

mysql> select replace(@@optimizer_switch, ',','\n')\G …

Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”

Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query efficiency.  The discussion will cover optimizer reports, query profiling, and session status to measure performance.

The query patterns will …

A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.

Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
 

INTO clause in nested SELECT statements

 
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
 

Table aliases in DELETE statements

 
In MySQL 5.5.3, several changes …