I don’t use many tools in my consulting practice but for the ones I do, I try to know them as best as I can. I’ve been using mk-query-digest for almost as long as it exists but it continues to surprise me in ways I couldn’t imagine it would. This time I’d like to share a quick tip on how mk-query-digest allows you to slice your data in a completely different way than it otherwise would by default.

Disclaimer: this only works when persistent connections or connection pools aren’t used and is only accurate when single mysql connection is used during execution of a request.

If you are seeking to reduce the load on the database server and [as a result] increase response time for some random user requests, you are usually interested in queries that are consuming most MySQL time and that’s how mk-query-digest groups and …

During the Collaborate 11 conference in Orlando Florida, on Tuesday April 12 from 10:15 to 13:00 I’ll be at the Oracle “DemoPod”. I’m told that this is in the Oracle booth, which is Booth#657 at the back of the exhibit hall.

On Wednesday April 13 from 13:00 to 14:00 my talk is “MySQL Oracle and ANSI Syntax”. Session ID = 431. Room = 310A, subject to change. Sorry, Trudy Pelzer won’t be a co-speaker, I’ll be doing this one alone.

I just realized that we haven’t blogged a list of our sessions at the O’Reilly MySQL Conference and Expo (#mysqlconf) yet. Here is a hopefully complete list.

• Diagnosing and Fixing MySQL Performance Problems (Monday Tutorial)
• Percona Server: secret features
• MySQL and SSD: Usage Patterns
• Achieving PCI Compliance with MySQL
• The …

Hot Column Addition and Deletion (HCAD)

In the previous HCAD post, I described HCAD and showed that it can reduce the downtime of column addition (or deletion) from 18 hours to 3 seconds. In fact, the downtime of InnoDB is proportional to the size of the database, whereas the downtime for TokuDB 5.0 depends on the time it takes for MySQL to close and reopen a table — a time that’s independent of database size. Go ahead and build bigger tables. The HCAD downtime for TokuDB won’t increase.

You may be wondering how we do HCAD. Here goes:

Under the hood

TokuDB is based on Fractal Tree indexing, one of the cools features of which is that they replace random I/O with sequential I/O. The way this happens has an impact on how HCAD happens, …

Want a free pass to the O’Reilly MySQL Conference next week in Santa Clara, California? It’s the MySQL conference, and the session lineup is awesome this year. We’ve got several free passes to give away. Post a comment here to claim your spot. First come, first served! (But make sure you are serious about committing to go — it would not be nice to claim a ticket and deny someone else the chance to go.)

Update Thanks everyone who commented! I think we have enough hats in the ring now… let me iron out the details, but I think all the tickets are claimed. Everyone who didn’t win — if we get another ticket, we’ll contact you, in order. And of course, remember that the expo hall passes are dirt cheap and you can come see us there, in booth #312! There’s a 20% discount code here: …

I’m pleased to announce the release of the Narada Search Engine Application, version 0.202, as well as a ready-to-use AMI, ami-140df07d.

The source release can be found at Launchpad – lp:narada and and a packaged version at http://patg.net/downloads/narada-0.202.tar.gz

What is Narada? Narada is a search engine application. It does have a web application component, but it also has a very novel approach on the back-end in how it implements various functionalities by using Gearman, memcached, Sphinx and either MySQL or Drizzle as a data store. It is somewhat of a proof-of-concept that was borne from an idea I had one late night while working on my first book, Developing Web …

Data Dictionary Language (DDL) operations have traditionally been slow in MySQL. Any change to the table definition would be implemented by creating a copy of the table and index layout with the requested changes, copying the table contents row by row, and finally renaming tables and dropping the original table.

The InnoDB Plugin for MySQL 5.1 implements a more efficient interface for creating and dropping indexes. Indexes can be created or dropped without rebuilding the entire table.

Speeding up Bulk INSERT, UPDATE and DELETE Operations

Normally, InnoDB would update all indexes of a table when rows are inserted or deleted. If you update an indexed column, InnoDB would have to delete the old value and insert the new value in the corresponding index. If you update a primary key column, the row would be deleted and inserted in every index of the table. This can be slow even despite the …

I saw that both Haidong Ji and Geert VanderKelen have proposed a Python monitor for MySQL replication, calling it the "poor man's version".
See Poor man’s MySQL replication monitoring and Geert's Refactored: Poor man’s MySQL replication monitoring.
Having Python in your server doesn't really qualify as "poor man". In many cases it's a luxury, and thus, here's my shot at the problem, using a Bash shell script.
Unlike its Python-based competition, this version also checks that the slave is replicating from the intended master, and that it is not lagging behind.

#!/bin/bash

USERNAME=msandbox
PASSWORD=msandbox
EXPECTED_MASTER_HOST=127.0.0.1
EXPECTED_MASTER_PORT=27371

SLAVE_HOST=127.0.0.1 …

This is a reply to the blog post Poor man’s MySQL replication monitoring. Haidong Ji had a few problems using MySQLdb (could use the ‘dict’ cursor) and apparently he doesn’t want to much dependencies. I agree that using the mysql client tool is a nice alternative if you don’t want to use any 3rd party Python modules. And the MySQL client tools are usually and should be installed with the server.

However, since MySQL Connector/Python only needs itself and Python, dependencies are reduced to a minimum. Here …

Using MySQL replication slave(s) for reporting (with potentially different storage engines) is a very popular way of scaling database read activities. As usual, you want to be on top of things when replication breaks so end users can be notified and issues addressed. When Nagios, Zabbix, or whatever monitoring tools are not available or otherwise not accessible, there got to be another way. I wrote the following Python script for that purpose. The script can then be scheduled via crontab to check replication status in an interval you define. When things break, you get a notification email.

Notes:
1. I toyed with MySQLdb Python module for this task, but I don’t like the fact that I cannot easily retrieve values via column names in a MySQLdb cursor. If there is an easier way that I am not aware of due to my ignorance, I’d appreciate it if you could let me know.

I then tried pyodbc, but got a Python 2.4 compiling issue. I …