I’m sure you’ve heard of Box Anemometer from the Box (MySQL) team, an excellent UI tool based on pt-query-digest. Now the guys from the box team offer us another killer UI tool based on pt-stalk, and I’m sure you will really appreciate to use it !

What is it ?

 
“RainGauge” consists of three parts :

• A set of scripts that collect data about the health of your system and your databases (based on pt-stalk)
• A process that push these data on a centralised place
• A very simple interface to navigate in the collected data

But the collection begins only when specific conditions are triggered, and you choose what these conditions are. Find below the general flowsheet :

…

In relation to these two posts from Justin Swanhart and Anders Karlsson about transaction isolation levels, I thought it was interesting to do a little survey to get an idea of the most commonly used isolation levels.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll. Related Posts :

• [UPDATED] Why you should go to Percona Live (London) ?
• Fatal timeout !
• …

There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.

As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.

What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.

Gotcha #1 : too much noise

I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error log may have way more information than you'd like to get. …

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2 …

Recently I had to turn a few EC2 instances into MySQL database servers. The third time I had to do it, I grabbed the list of steps from my previous sessions and just replayed it. Later I thought maybe polishing information a little bit and publishing a step-by-step walkthrough on the blog may help a few people. So here it is.

Before you begin.

For my MySQL instances I used the following:

• Extra Large, High-Memory, and High-CPU instances. Although the instruction should work on any type of instance.
• RedHat Enterprise Linux 6.2 64-bit AMI
• For MySQL data storage, multiple identical EBS devices attached to each instance

The configuration template provided in this post assumes the new MySQL instance only needs InnoDB storage engine.

Grab the packages.

Download the appropriate packages from MySQL web page. You …

Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.

Threads and connections

MySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a table-like view where each connection becomes a separate row:

 …

Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.

But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :

--exclude-pattern "pattern" 
All databases or tables that match the pattern are not backed up. 
If --all-databases or --databases are specified, the exclude pattern applies
to …

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :

1 – Catch the query (if you can) !

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

•  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

…

I just want to share about a strange behavior of one of our MySQL server yesterday.
This server is a 5.1.50 MySQL server on debian 4.0 (Yes, I know…)

When a “mysqld got signal 6” error occurred yesterday, the MySQL server crashed and didn’t want to restart.
Then, I found these informations in the error log file :

/usr/local/mysql/bin/mysqld: File '*** glibc detected ***
malloc():memory corruption: 0x00002aac2d5ab460 ***' not found (Errcode: 2)
120306 17:19:47 [ERROR] Failed to open log (file '*** glibc detected ***
malloc():memory corruption: 0x00002aac2d5ab460 ***', errno 2)
120306 17:19:47 [ERROR] Could not open log file
120306 17:19:47 [ERROR] Can't init tc log
120306 17:19:47 [ERROR] Aborting

120306 17:19:47 InnoDB: Starting shutdown...
120306 17:19:53 InnoDB: Shutdown completed; log sequence number 55 1061584593
120306 17:19:53 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Great, let see …

Today I’m working on integrate the Percona toolkit (instead of maatkit) in my own tools and I’m playing with pt-query-digest and pt-query-advisor.
These tools can be very interesting to identify some queries from established rules.
The  –review option is available for two of them and helps me to store a sample of each class of query and match them with an advice.

The rules (or advices) are available in the pt-query-advisor documentation and let you identify various problems such as queries with an argument with leading wildcard or with a table joined twice, for example.
There are 3 types of rules : note, …