This year at the O'Reilly MySQL User's Conference we will be doing the first ever Ignite talk series!

What is Ignite?

It's a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it. All talks are exactly five minutes long, and Ignite presenters will be sharing their personal and professional passions, using 20 slides that auto-advance every 15 seconds.

Sound interesting?

So far we have Robin Schumacher talking about when you should be using InfiniDB instead of general MySQL server. Monty Widenius will be presenting the "The Full Monty, State of MariaDB".

…

I’ve recently been migrating my wiki/documentation for Kontrollbase to Trac. For those that are not aware, Trac is a web-based documentation/wiki/Subversion tool that is used by countless number of software projects. Subversion, of course, is a software collaboration and code management repository that manages branches/tags/trunk files with revision control. It’s one of the most heavily used open-source code repositories available. Given that I use SVN (subversion) for all of my software applications and am now using Trac, the book “Managing Software Development with Trac and Subversion” by David J Murphy comes as a useful and great resource for integrating these two useful tools. …

Recently I needed the query stream hitting a very busy master. Normally I would have been using the MySQL Proxy to collect queries, but on a very busy machine the Proxy is as much of a problem as it is part of the solution, so I chose a different approach.

I had a SPAN port configured for the master, which is Ciscospeak for a port on a switch which mirrors all traffic of one or more other ports. I had an otherwise idle machine listening to the SPAN port on a spare network card. That way it is possible to collect traffic to and from the master without any interference with the master.

On the listener box, I had tcpflow collecting data to my master (and only traffic to, not from the master): CODE:tcpflow -i eth1 dst master and port 3306 These tcpflow files now need to be processed into a …

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT CODE:

1. SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0

This column in the table is looks like this:

PLAIN TEXT CODE:

1. `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL

The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

PLAIN TEXT CODE:

1. …

When setting up a query using a temporary lookup table, I got this error:

ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;
Query OK, 3228659 rows affected (2.01 sec)
Records: 3228659  Duplicates: 0  Warnings: 0 

Then the query is easy:

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals
GROUP BY journal_date 
HAVING cnt > 10000</pre>

UNION

SELECT COUNT(1) cnt, journal_invoice_ref
FROM tmp_journals_2
GROUP BY journal_invoice_ref
HAVING cnt < 10

Recently Alexandr announced new Percona-XtraDB-9.1 release, and now it is good time to summarize features we have and what is expected soon.

This release contains long waited features from 5.0:

• extended slow.log
• USER/TABLE/INDEX/CLIENT_STATISTICS + THREAD_STATISTICS ( coming in release-10)

Extended slow.log is now even more extended, there is additional information for each query:

PLAIN TEXT CODE:

1. # Bytes_sent: 4973  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 7808

That is you can see how many bytes was returned by query, was temporary table used,
was it disk table or in-memory, and how big was temporary table.

Also you can profile each individual …

Here's a very interesting article for those of us who work with big data: Data, data everywhere in the Economist.  One interesting quote predicts the emergence of a new database professional: the data scientist: "Chief information officers (CIOs) have become somewhat more prominent in the executive suite, and a new kind of professional has emerged, the data scientist, who combines the skills of software programmer, statistician and storyteller/artist to extract the nuggets of gold hiddenRead More...

It is now one year since a few colleagues and I left Sun to start our own company, Monty Program Ab (after which more have joined). A lot has changed during the year. For instance we ended up producing a full fork of MySQL rather than focusing on the Maria engine as I planned a year ago.

This February we released the first stable version of MariaDB, version 5.1.42, which is our enhanced and backwards compatible MySQL branch/fork. You can download it from the askmonty.org website. Please test it out and comment upon it here or on Launchpad, the code host for the …

Today I pushed up the initial patch which adds XA support to Drizzle’s transaction log. So, to give myself a bit of a rest from coding, I’m going to blog a bit about the transaction log and show off some of its features.

WARNING: Please keep in mind that the transaction log module in Drizzle is under heavy development and should not be used in production environments. That said, I’d love to get as much feedback as possible on it, and if you feel like throwing some heavy data at it, that would be awesome What is the Transaction Log?

Simply put, the transaction log is a record of every modification to the state of the server’s data. It is similar to MySQL’s binlog, with some substantial differences:

• The transaction log is composed of …

..kind of cool.

Here is a good use case for compressed MyISAM:
We have a rather large database that doesn't get updated very frequently.
We have several slave servers that have this database, but because the database is big, they have a bit of a hard time querying it.

Disk I/O is a big issue for us and compressing the data, offloads some of that onto the CPU for decompression. It also allows the system cache to keep more of the tables in memory.

This database only gets updated every few weeks and it's pretty much a manual process.
To update the slave servers, we simply copy the MyISAM tables over to them (yes, you can do that) and because they are compressed, they are easier to send over the network.

Unlike Archive tables, compressed MyISAM can have indexes (Archive tables now allows 1 index) so you can use them for fast querying.

Compressed MyISAM …