Over the course of years, I have observed that the three most sensitive indicators of MySQL having a server lockup are the queries per second, number of connections, and number of queries running. Here is a chart of those three on a production system. Find the bad spot:

I am currently working on developing an automated system that detects abnormal behavior in these three metrics, but doesn’t require any a priori inputs or thresholds, e.g. you don’t have to tell it “more than X is bad.” (It could be that during a low period of the day, X is different than during the peak load.)

It turns out that this is hard to do reliably, without a lot of false positives and without false negatives (not triggering during an incident). If there is existing literature on the mathematical techniques to do this, I’d be …

New performance schema features in 5.6.4
As development of the 5.6 serie continues, a new milestone (5.6.4) is now available.

The source code for mysql-trunk is on launchpad, and the MySQL 5.6 documentation is also public.

In this milestone, a few new performance schema features have been implemented, as can be seen in the changelog.

MY.CNF, or easier configuration
Until now, the performance schema used a very simple, or shall I say, crude, way to decide what needs to be instrumented or not: flip the master switch on (performance_schema), and you get absolutely everything available, with as much details as possible, by …

Are you a MySQL DBA? Checkout the common_schema project by Oracle Ace Shlomi Noach.

The common_schema is an open source MySQL schema that packs a number of utility views, functions and stored procedures. You can use these utilities to simplify MySQL database administration and development. Shlomi just released revision 178, and I'm happy and proud to be working together with Shlomi on this project.

Among the many cool features created by Shlomi, such as foreach, repeat_exec and exec_file, there are a few %_dependencies procedures I contributed:

• 
  
• …

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful

MySQL 5.6:

Until now, all my work around MySQL Performance was just a part of fun rather my main job... But things are changing since today ;-) I'm moving to MySQL Team, and will work on MySQL Performance full time from now on! ;-) Which is meaning yet more fun (I hope ;-)), more blog posts (I'm sure ;-)), more benchmarks (of course), and more brain work (even more than sure ;-))..

And as I've mentioned benchmarks - many of you complained over a time that we're not really testing what we should.. Well, everything is relative and depending directly on what you're trying to fix or analyze.. ;-) So, I'll not stop running dbSTRESS or Sysbench workloads (or TPCC-like, DBT-2, etc.).. But, I'm also looking for other various test scenarios to reproduce different MySQL performance problems you're observing in your production, or your customer's sites, etc. Please, leave comments on this post, or contact me directly if you prefer. I'll try then to group all such …

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:
Unfortunately, there is no way to remove this from generated syntax with this command.(Emphasis mine).
Here's how it goes:

mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE …

For those who saw my previous post about the crashing (regression) bug with SELECT COUNT(DISTINCT) on InnoDB with Primary Key (PK), you’ll be interested to know my test case does not crash in 5.5.18 (which was just released).

I’ve only tested my test case thus far, but it seems fine.

Unfortunately, the fix is not mentioned in the 5.5.18 changelogs though.

And there is no mention (yet, anyway) of a fix in the bug report I filed (though it was designated a ‘duplicate’, so it wouldn’t necessarily be updated).

I’m trying to get confirmation from the MySQL Dev Team on this (via the bug report), and will update this post if/when I hear anything.

I’ll also perform some of the …

Over the course of years, I have observed that the three most sensitive indicators of MySQL having a server lockup are the queries per second, number of connections, and number of queries running. Here is a chart of those three on a production system. Find the bad spot:

I am currently working on developing an automated system that detects abnormal behavior in these three metrics, but doesn’t require any a priori inputs or thresholds, e.

We announced a while back that we were going to continue the traditional MySQL conference in Santa Clara, because O’Reilly wasn’t doing it anymore. But we haven’t given an update in a while. Here’s the current status:

• We created a conference committee.
• We created a conference website that allows people to create an account and submit proposals. We’re currently looking for session proposals; more on that later.
• We have been working hard on the logistics of the venue, food and beverage, audio/visual, power, networking, exhibit hall, registration, hotel rooms, and a number of other things. …

For business intelligence developers, knowing the schema of a source system is not the same thing as knowing the data. Quite often the table/column view of a system does not adequately present all that is needed to get the right answer from a query. Joins and keys are certainly important to understand how tables relate to one another, but many questions still remain: Are all tables still used? Are all columns still used? Are there columns that have been “re-purposed” to contain data they weren’t originally designed to house? Data Profiling is the process by which we can understand how data is represented in a given database.

Basic data profiling allows us to collect statistics on columns, identify dependencies between tables and also dependencies between columns within tables. This type of data profiling delivers information such as:

• Number of distinct, NULL and empty values in a column
• Highest/Lowest …