As a MySQL  consultant, I spend a lot of time talking with people about their backups. More specifically, we talk a lot about recovery. I had an interesting incident myself, which illustrates some of the things that are bound to happen as time passes.

First, let me explain how I do my personal backups. I have a series of external hard drives, which are fully encrypted, as is my computer’s hard drive. I maintain a full mirror of my computer’s hard drive on these external hard drives. I occasionally switch the hard drives out, and carry one or more of them to a bank’s safe deposit box. I try to do this once a week, but sometimes it isn’t quite that often.

As a result, I have one hard drive located physically near my computer, which contains a very recent backup of all my work. I have at least one, usually 3 or more, other copies of my data in a slightly less fresh format, but durably stored in a bank.

While …

I’ve created a Central Virginia MySQL Meetup group for those of you in the area. Our first event will be in a couple of weeks at Meddius’s headquarters. I’d like to meet and get to know more people in this area who use MySQL, so come out and we’ll have a good time together! Oh, and beer. And pizza. Of course.

Further Reading:

• Central Virginia MySQL Meetup has reached cruising altitude
• Speaking at MySQL Meetup in Northern Virginia
• …

As we can see in the solution:

Expression are evaluated in the column order in the select clause.

This is the same for the where clause.  An advice is to test first the expression that get more chance to return false at the head of the where conditions :   

1. SET @BRAND=0;
2. SET @ct=0;
3. SELECT
4.  *
5. FROM
6.  (SELECT
7.    IF(@BRAND<>id_BRAND,@ct:=0 ,@ct:=@ct ) ,
8.    @ct:=@ct+1 AS ct,   
9.    @BRAND:=id_BRAND,
10.    t2.*
11.   FROM
12.    (SELECT
13.      st.ID_BRAND,
14.      st.ID_MODEL,
15.      SUM(COMPTE) AS total,
16.      AVG(totalm)
17.   …

Here are some of the fake O'Reilly book covers I mentioned in a prior post.  These have been optimized for use as black & white Kindle screensaver wallpaper images.  If you haven't done so already, you can install a Kindle screensaver hack with a couple of downloads. 

Update: I've embedded a slideshow from PicasaWeb, but it requires Flash.  If you don't see it you can click on the links below to go directly to PicasaWeb.

• Kindle hacks:  …

I’m researching algorithms for automatic fault detection in MySQL (see my previous post for context). I need real-world data samples to test the algorithm. Can you help by sending me a bit of data from your production server?

The end goal is an open-source tool that will be a standard part of a typical MySQL installation. The problem I’m trying to solve for all MySQL users is this: something went wrong, what was it? Most of the time there’s no way to answer that; you have to set up a set of tools and hope you capture enough information to diagnose the problem next time. We need a tool that just runs all the time even when you don’t think anything is going to go wrong.

You can help build this tool. I need samples from a wide variety of healthy and sick servers, both heavily and lightly loaded. I need samples …

I was curious what information MariaDB’s “phone home” user feedback plugin sends. (It works on more than just MariaDB, by the way.)

It’s easy enough to find out: just load the plugin, then select from the INFORMATION_SCHEMA.FEEDBACK table. This returns a lot of rows that are obviously the status counters and variables, as well as the plugins loaded in the server. A quick exclusion join will eliminate those, and the result on my laptop is this:

select f.* from feedback as f
   left outer join global_variables as v on f.variable_name = v.variable_name
   left outer join global_status    as s on f.variable_name = s.variable_name
   left outer join plugins          as p on f.variable_name = p.plugin_name
where s.variable_name is null and v.variable_name is null and p.plugin_name is null;
+--------------------+--------------------------------------+
| …

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 …

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:

• 
  
• …

At ideeli, we use a variety of techniques to eliminate single points of failure and contention. These involve load balancing multiple application servers, caching layers, and calls to asynchronous services. However, at the end of the day we have a single master MySQL database. It doesn’t matter how many dozens of application servers are handling requests if the database is stalled. Without the right tools, it can be difficult to determine exactly what the database is doing at any given time. In this two part post, I will discuss some techniques for finding those problematic queries.

FINDING THE SLOW STUFF

The MySQL slow query log should be the first stop when looking for queries to optimize. Even with tools like mysqldumpslow, the format of the …

The pt-query-digest tool has a number of special cases that affect how it “fingerprints” queries when it groups similar queries together to produce an aggregated report over the group. One of these is a special rule for queries that appear to come from mysqldump, of the following form:

SELECT /*!40001 SQL_NO_CACHE */ * FROM `users`

All such queries will be fingerprinted together and presented in a single class of queries. I remember many instances where mysqldump queries crowded the report of the “most important” queries and just caused other queries to be excluded. Grouping them together made it obvious that mysqldump’s load on the server was a problem, but didn’t obliterate other interesting things we wanted to see in the report.

Further Reading:

• …