Much has been made of certain limitations of MySQL’s query optimizer (“planner”). Subqueries, lack of sort-merge joins, and so on. This is not MySQL-bashing and no one should be offended. Some people have worked to make things better, and that code is in branches that were intended for future releases. But if that work were available right now, would you trust it?

This question is important because the optimizer is complex and full of compromises and black magic. Even minor changes occasionally have weird edge cases that cause a regression in some workload. Are major changes trustworthy?

I’ll give a specific example. In version 5.0, MySQL gained the ability to use more than one index for a query. This is called index_merge in EXPLAIN, and sometimes people think it’s the best thing ever. In practice, I can say two general things about queries that use an index_merge plan:

1. If the optimizer chooses an …

(and before you ask, yes “rotating blades” comes from “become a fan”)

I’m forming the ideas here first and then we can go and implement it. Feedback is much appreciated.

Two tables.

Table one looks like this:

CREATE TABLE fan_of (
user_id BIGINT,
item_id BIGINT,
PRIMARY KEY (user_id, item_id),
INDEX (item_id)
);

That is, two columns, both 64bit integers. The primary key covers both columns (a user cannot be a fan of something more than once) and can be used to look up all things the user is a fan of. There is also an index over item_id so that you can find out which users are a fan of an item.

The second table looks like this:

CREATE TABLE fan_count (
item_id BIGINT PRIMARY KEY,
fans BIGINT
);

Both tables start empty.

You will have 1000, 2000,4000 and 8000 concurrent clients attempting to run the …

The video of my 5-minute Ignite talk on XtraDB is up on YouTube. Ignite talks have exactly 20 slides on auto-advance every 15 seconds. “Enlighten us, and make it fast!” It’s better than a lightning talk. I explained the difference between Percona XtraDB and other technologies such as standard MySQL, OurDelta, and MariaDB.

Related posts:

1. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug
2. Recap of CPOSC 2009, plus slides Yesterday
3. Learn about mk-query-digest …

I created the Bluegecko MySQL training AMI as a MySQL sandbox that folks could use to learn things about MySQL. I wanted the AMI to have MySQL 5.0, and a large collection of tools — for both tinkering and visualizing what is happening inside MySQL and on the system in general.

I chose to host the AMI on a small instance since they’re cheap to operate, which means you can fire up 3-4 of them to try out a replication scheme without feeling guilty. It runs CentOS 5.4 and MySQL 5.0.77 from the CentOS repository.

One of the goals I had for the training AMI was for it to be easy to visually inspect what is going on inside of MySQL and the system at large. To that end I’ve installed Cacti, a graphing and visualization tool, and equipped it with templates that allow you to visualize …

(Re-posted from Florian’s blog.)

MySQL replication support for the Pacemaker cluster manager (the stuff that we explained in this webinar) has made it into the Linux-HA resource agents default branch. If you are interested in testing — and you should! — please read the extended announcement. Feedback is extremely welcome on the linux-ha-dev mailing …

MySQL-5.5.4 ® is the great release with performance improvements, let's see how it performs in
tpcc-like workload.

The full details are on Wiki page
http://www.percona.com/docs/wiki/benchmark:mysql:554-tpcc:start

I took MySQL-5.5.4 with InnoDB-1.1, tpcc-mysql benchmark with 200W ( about 18GB worth of data),
InnoDB log files are 3.8GB size, and run with different buffer pools from 20GB to 6GB. The storage is FusionIO 320GB MLC card with XFS-nobarrier. .

While the raw results are available on Wiki, there are graphical results.

I intentionally put all line on the same graph to show trends.

…

This blog post is just to list links and tips on getting home to Finland.

First leg: getting to anywhere in Europe

Being stuck in San Fransisco, my first problem has been to get just anywhere closer to home. Taking a boat from America to Europe takes from 6-15 days and is not significantly cheaper than flying. So the best bet is just to fly to Europe.

read more

Some of the utilities we were adding to Maatkit really did not belong there. Yes, this included some of the functionality in the now-retired mk-audit tool. We really learned a lesson about what it’s possible to support, design, spec, code, and test in a single tool.

I’ve moved those tools to a new project, Aspersa. Some folks are revolting and calling it Asparagus, because apparently that’s easier to say. Aspersa is the name of the common garden snail, which turns out to be a fascinating creature. It is also slow. Draw your own conclusions.

This project is more of a home for simple scripts and snippets — a simple place I can grab all the little utilities I use to make my life easy. There is a “summary” tool that largely replaces mk-audit’s functionality outside the database, and I plan to add a …

The DECIMAL datatype is a datatype in MySQL that provides additional precision over FLOATs or DOUBLEs at the expense of arithmetic. You specify the precision by using
datatype-name DECIMAL
or
datatype-name DECIMAL(8)
or
datatype-name DECIMAL(10,4)

MySQL uses roughly 4 bytes for every 9 digits either side of the decimal.

The english definition (http://www.thefreedictionary.com/decimal) would assume that when you specify the DECIMAL datatype you would want to insert numbers with fractions.

This however is not the case. Lets show this:

mysql> create database decimal_madness;
Query OK, 1 row affected (0.00 sec)

mysql> use decimal_madness;
Database changed
mysql> create table test1 ( tmp decimal);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test1 values(1.1);
Query OK, 1 …

Working on a production/test servers would always need you to be ready with your rescue tools, be it third party tools or commands and scripts to help you troubleshoot and monitor the database servers. Usage of the third party tools will be pretty easy to manage or monitor your databases through a GUI. However how many of us DBA’s would monitor the databases from command line very often?

Here are few scripts that I found online which I would like to post for your quick reference. Please find attached the scripts and follow the usage instructions.

allschemas.sql

or copy the following to a notepad and save it as allschemas.sql

**************************************

Purpose: List a summary of all Instance Schemas including disk size

**************************************

SELECT NOW(), VERSION();