The latest from the MySQL community

Here is an example of using protobuf on table definitions for Drizzle:

[brian@piggy serialize]$ ./table_reader example_table.frm
CREATE TABLE `example_table` (
        `sample0` VARCHAR(83),
        `sample1` VARCHAR(86) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL ,
        `sample2` VARCHAR(77) CHARACTER SET utf8 COLLATE utf8_swedish_ci,
        `number` INTEGER ,
        `colors` ENUM('red','blue','green') ,
        `some_btye_string` BLOB ,
        `important_number` DECIMAL(8,3) ,
        `important_string` VARCHAR(20),
         PRIMARY KEY (`number`),
         UNIQUE KEY `sample0` (`sample0`),
         UNIQUE KEY `sample1` (`sample1`)
)
  ENGINE = Innodb;

[brian@piggy serialize]$ du example_table.frm
4       example_table.frm

The size is very reasonable, and descriptive code is much, much smaller then before.

What else is sharp? …

I put this bad boy into production, and its working great.

So, I had an app that was sticking data into S3 when the data was marked or qualified as warehouse data. The problem with this method was when retrieving the data from S3 it was not chunked but appended to. So pulling 100M over the wire, parsing, sorting and adding to the db would fail since the process consumed more then its allowed share of resources. Plus grab data from S3 has a very high response time, plainly put its slow.

So, since the warehouse data is prone to error-the main reason why I needed a new solution, I decided to use ZLIB compression that is native to Innodb's Barracuda build.

To install innodb plugin here is a good write up I will not duplicate it.

Configuring the plugin you must set in your my.cnf
…

I recently added two new packages to my repository on the openSUSE Build Service:

• Maatkit is a collection of essential command-line utilities for MySQL. Each is completely stand-alone, without dependencies other than core Perl and the DBI drivers needed to connect to MySQL, and doesn't need to be "installed" - you can just execute the scripts. This makes the tools easy to use on systems where you can't install anything extra, such as customer sites or ISPs.
• protobuf - Protocol Buffers - Google's data interchange format. Protocol Buffers are a way of encoding structured data in an efficient yet extensible format. Google uses Protocol …

Are you a member of a local Linux User Group? Or a MySQL User Group? Or any other group that is related to open source software? Have you heard of the Software Freedom Day yet? This is a good opportunity to spread the word and showcase what OSS is all about to a wider audience. Some quotes from their Software Freedom Day website:

Software Freedom Day is a global, grassroots effort to educate the public about the importance of software freedom and the virtues and availability of Free and Open Source Software. Local teams from all over the world organise events on the third Saturday in September.
Software Freedom Day is a global celebration and education …

OmniSQL (a command line tool for DBAs needing to issue ad-hoc queries against sharded data) version 0.0.6 is officially released. Instead of logging in separately to multiple databases to issue the same query, groups of databases can be specified in a configuration file and queries will be automatically issued against all targeted MySQL instances. [...]

Let's assume that we have the following scenario happening in a database server (also assume that this is the only thing happening in it):

5 table scans started at the same time, hitting different databases but the table structure and number of rows are all identical (roughly 200 million rows).

When I do a mysqladmin extend -r -i 10 | grep Handler_read_rnd_next

It shows that this variable is being updated. The question is since there are 5 threads running, are all of them hitting the Handler* subroutine one after the other? If so, would this slow down the whole table scanning process?

I tried to test this by running 1 table scan and it took about 1 hour to finish. And if I try to run 2 table scans together, it takes about 4 1/2 hours to finish.

I just picked up on Matt Asay's post about some windows machine that crashed during the Beijing Olympics. At the pivotal moment when the torch carrier descended down from top of the stadium, someone caught a picture of the blue screen of death.
Here is the "perfect picture" of the event:





















What makes this picture even funnier for me is that I was in Beijing for a long time prior to the Olympics and noticed how frantically they …

Tomorrow, Thursday the 15th, I'm driving down to Atlanta to give a presentation on Join-fu: The Art of SQL at the Atlanta PHP User Group. If you're interested in how to squeeze the most performance out of your (My)SQL applications, some "join" me. We'll be covering a number of topics, including a brief overview of MySQL internals, optimizing schema and index organization, understanding the many MySQL storage engines, and most of all, how to think in SQL and not in your programming language of choice.

Why is it important to think in terms of SQL and not your programming language of choice when implementing database-centric applications? Well, the short answer is that SQL is a set-based …

Want to know the type of an arbitrary expression in MySQL? Someday in the far far future in version 7.1, you might be able to with the TYPEOF() function.

For now you can try this:

CREATE TEMPORARY TABLE typeof AS SELECT [expression] AS col;

For example, let’s see what the type of CRC32 is.

mysql> CREATE TEMPORARY TABLE typeof AS SELECT CRC32('hello world') AS col;
mysql> DESCRIBE typeof;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col   | int(10) unsigned | NO   |     | 0       |       | 
+-------+------------------+------+-----+---------+-------+

This is one possible way to programmatically determine the type of an expression — even an arbitrarily complex one.

Not beautiful, but …

Last weekend I updated the Debian package of MySQL to 5.0.67, the latest Community Edition release. It was not uploaded into the Debian archive, but is available from my home directory on gluck (amd64 only at the moment). The next Debian release lenny is frozen already, which means no newer upstream releases get accepted into testing before lenny is released, hence lenny definitely gets shipped with MySQL 5.0.51a. We will not see MySQL 5.0.67 or 5.1 in lenny, but we will continue providing backports of newer MySQL releases.

Our current primary goal regarding the MySQL packages in Debian is to get 5.0.51a in shape for lenny, so please do not send us mails asking about MySQL 5.1 …