Occasionally, you find yourself in the need to kill problematic connections to the database.
Usually if it's only one or two connections, you can use the combination of "SHOW PROCESSLIST" command to identify the problematic connection ID, and run a "KILL ID" command.

What do you do if you need to kill 10 connections? Or 56? I wouldn't want to type in all those kill commands, it's just dirty work. What we need is a more neat manner to perform those kills. Mass kill, if you wish.

Alternative way: use the INFORMATION_SCHEMA's PROCESSLIST table, to construct the kill statements semi-automatically.

SELECT CONCAT('kill ',id,';') AS kill_list
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command='Sleep';


This select will return something like this (when using the command line client):

 …

Perception and reality don't always converge with users when it comes to MySQL licensing, even with Sun/MySQL focusing on selling its MySQL Enterprise support subscriptions. Just today I was talking with a client who thought that to get a MySQL Enterprise subscription, it was required to purchase licenses.

Not true. Simple. They hadn't talked with a MySQL sales person, so this wasn't the (usual ;-) warping of reality that I tend to see, but somehow perusing the information on the net they'd come to the above conclusion. Easy to dispell once understood, but much more complicated (if not impossible) to figure out how they came to that conclusion in the first place....

They also didn't realise that the MySQL Enterprise binaries are also GPL. Ohwell.

But really, nobody wants to care about licensing, it's this necessary evil that you have to deal with. Best to keep it simple, or even better, make it a …

As I indicated in my previous post on MySQL performance, we have been doing some performance work using an internally developed web2.0 application. Akara and I will be presenting this app publicly to a large audience for the first time at the upcoming Velocity Conference in Burlingame, CA on June 23, 24. Check out our abstract.  Most of our work uses Cool Stack so a lot of the results we will be presenting will be based on that. If you're struggling with performance issues, this conference may be worth checking out.
If you will be attending the conference, please stop by and say hello. It's always good to see people whom we only know through blogs and forums.

As I indicated in my previous post on MySQL performance, we have been doing some performance work using an internally developed web2.0 application. Akara and I will be presenting this app publicly to a large audience for the first time at the upcoming Velocity Conference in Burlingame, CA on June 23, 24. Check out our abstract.  Most of our work uses Cool Stack so a lot of the results we will be presenting will be based on that. If you're struggling with performance issues, this conference may be worth checking out.
If you will be attending the conference, please stop by and say hello. It's always good to see people whom we only know through blogs and forums.

First, let's look at the numbers. The table below lists the speed of building a timeline like Twitter does, all of them using pull model.

As I explained in my previous post (Implementing Timeline in Web Services - Paradigms and Techniques, it is difficult (if not impossible) to write an optimal SQL query to build timelines on the fly. Yesterday I asked on the MySQL Internals mailing …

MySQL Quickpolls might be insightful for people who develop products and services for MySQL. Recently I was looking again at “How do you backup your production database” poll. To interpret the results, I wanted to know who are the people answering that and other Quickpolls. Are they the DBAs responsible for running MySQL in production or the developers writing applications that use MySQL? For a backup guy like me knowing that makes a difference.

Every Quickpoll gets a time stamp when opened and tells how many people answered the poll. It occurred to me that the normalized number of people (MySQL polls run for different periods of time) answering each poll could give me some insight. The graph below shows the daily number of people answering each poll in the last 24 months.

Of course, I understand there could be self-selection …

I spent this past weekend writing a Paper for a project I’ve been playing with. It is a simplified distributed processing system loosely based on Google’s MapReduce, except rather than focusing on larger batch jobs, it prototypes out some common database application uses. The model is currently very basic, but I plan on exploring this further (possibly with a performance-enhanced implementation in C). I’ve also been reading up on other interesting projects like Hadoop, HyperTable, Amazon’s SimpleDB, and of course the DB interface for Google’s AppEngine. I’m wondering how these …

Somedays you just cannot win :)

First some of the things that you need to use and understand

Explain Syntax

Order by Optimization

Group by Optimization

Update: Updated errors.

Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, and tight index scan.

Loose index scan, scans the entire table index, while tight index scan uses some sort of constraint. For large datasets that are accessed often and require some sort of group by, tight index scans are better.


So how to pick columns to create …

Fun, and all I wanted was some news - because I have no idea what everyone else's blog urls are.