Debugging a Production War Story

This is a presentation i made for the 2013 Flipkart BootCampers.

Its a production war story and how we went about debugging the root cause.

Valeriy has mentioned that MySQL manual has no documentation about EXPLAIN FORMAT=JSON feature. There was a bug filed for this since September, 2012. I had spent some time looking at EXPLAIN FORMAT=JSON, so I thought I would share my findings.

The first blog post about EXPLAIN FORMAT=JSON was made by Sheeri. She listed these features of FORMAT=JSON:

• attached_condition and implicit casts
• used_key_parts field

In addition to those, I was able to find

• Pushed index condition information
• More information about sorting/grouping
• Subquery …

Be sure to check out my other posts on mysqldump:
– Scripting Backups of MySQL with Perl via mysqldump
– Splitting a MySQL Dump File Into Smaller Files Via Perl

Part 1 of 2: (part two)
If you have used MySQL for a while, you have probably used mysqldump to backup your database. In part one of this blog, I am going to show you how to create a simple full and partial backup using …

One of the new features in MySQL 5.6 is persistent table statistics. The word “persistent” makes you think that table statistics will be very stable from now on.
This is generally true, but there are some exceptions. There is a particular case where InnoDB statistics may change with no warning, even when there is no user activity on the server. Consider the following pattern (which nearly all bug or feature testcases follow):

CREATE TABLE t1 (...) ENGINE=INNODB;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
EXPLAIN SELECT * FROM t1;
EXPLAIN SELECT * FROM t1;

With MySQL 5.6, the following is possible

• the first EXPLAIN will show that the optimizer expects table t1 to have 6 rows
• the second EXPLAIN will show that the optimizer expects table t1 to have 5 rows

For me, this was a big surprise. After all, table t1 never had 6 rows. If there was some rounding which rounded up 5 to 6, why …

When analyzing response time, or latency, you need much more information than an average provides. The average, commonly the arithmetic mean, shows the index of central tendency. But, as I found in earlier posts, the tendency is often not central, but may be skewed by outliers, or split by multiple modes. How often these factors occur was determined quantitatively, using tests and a survey of hundreds of production servers and different types of latency: over 95% had six-sigma outliers, and at least 20% had multiple modes. While these numerical results are useful, nothing beats a visualization, such as a histogram, …

“We went down the path of building Oracle NoSQL database because of explicit request from some of our largest Oracle Berkeley DB installations that wanted to move away from maintaining home grown sharding implementations and very much wanted an out of box technology that can replicate the robustness of what they had built “out of [...]

Due to a bug in our release packaging scripts, the wrong version of the man pages, with the wrong license text, were copied into the MySQL Server GPL packages. The MySQL Man Pages continue to be available under GPL. The MySQL Server GPL packages will be corrected ASAP. You can read and follow the public bug here.

We apologize for the confusion this has caused. As always, please feel free to contact us, to ask about changes that you are wondering about.  Reporting a bug is always a good way to communicate with us.

Have a Happy GPL Midsummer, Tomas

Hat tip to Colin Charles at MariaDB for this: http://blog.mariadb.org/mysql-man-pages-silently-relicensed-away-from-gpl Why is this important?  The MySQL documentation already has a restrictive license.  This means that it’s illegal to redistribute the MySQL documentation.  As a counterexample, the PHP documentation is licensed under a Creative Commons Attribution license, and there are many mirrors of it.  If one [...]

Replication : Be careful while executing DCL with replicate-ignore-db=mysql. Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql. But if we are executing DCL statements from different database schema (other than mysql schema) in master, the event will replicate to slave. slave configuration mysql> show slave status\G [...]

Recently I had some time to do some clenaups/changes/updates in server:database repo regarding MySQL (and MariaDB). Nothing too big. Well actually, there are few little things that I want to talk about and that is the reason for this blog post, but still, nothing really important…

MySQL 5.5, 5.6 and 5.7

MySQL 5.6 is stable for some time already, so it’s time to put it in the action. So I sent the request to include it in Factory and therefore in openSUSE 13.1. There is off course a list of interesting stuff you might want to take a look at before you update. If you don’t want to update, you can install mysql-community-server_55 from …