The InnoDB labs release includes a snapshot of the InnoDB Native Partitioning feature.

To better understand why we implemented this, we need to start with some background on tables, storage engines, and handlers. In MySQL an open instance of a table has a handler object as an interface to the table’s storage engine. For a partitioned table there is a main table handler that implements the partitioning feature, but for storage, each partition has its own handler. This worked fairly well, but the more partitions you had the more overhead from the per partition handlers. So to remove this overhead for partitioned InnoDB tables we’re introducing Native Partitioning support! This means a new InnoDB partitioning aware handler, so that we have a single handler object for a partitioned table and not one handler object per partition.

Let us create a simple table with 8k partitions:

[This post refers to innodb_ruby version 0.9.11 as of October 2, 2014.]

Many DBAs know that building indexes in “random” order (or really any order that greatly differs from ordered by key) can be much less efficient. However, it’s often hard to really understand why that is. With the “-illustrate” visualization modes available in innodb_ruby, it’s possible to quite easily visualize the structure of indexes. The space-lsn-age-illustrate mode to innodb_space allows visualization of all pages in a space file by “LSN age”, generating something like a heatmap of the space file based on how recently each page was modified.

(Note that a small Ruby script generate_data_simple.rb was used to generate the test tables used below. …

Have you donwloaded our latest eBook, Configuring MySQL for Performance? It offers simple advice on configuring the most important settings to maximize your MySQL performance.

Below are a couple tidbits:

The Default Storage Engine

The default_storage_engine setting is the engine used for tables that don’t specify an engine explicitly. This used to be MyISAM, but it’s changed to InnoDB more recently. This is a good thing; it’s the engine we’ve suggested as default for most use cases for years. Just check to ensure this is set as you desire. If you don’t know what engine to use, the right answer is probably InnoDB.

The Query Cache

The query cache (variables whose names begin query_cache ) should generally be disabled, unless you’re quite familiar with MySQL and its inner workings and know that it’ll help on your workload. You can find a lot of discussion online about the potential impacts …

The HTTP Plugin for MySQL offers three APIs: REST-like CRUD, REST-like JSON DOCUMENT and SQL. The SQL API lets you run any SQL you want. Including, for example, DROP mysql.users if you mess up your setup. Insecure? It depends on your viewpoint.

It’s more than just another protocol…

On the first look HTTP is just another network protocol for sending requests to MySQL. HTTP is the protocol of the web. Whether you need to integrate MySQL in a larger setup and use web services for data exchange or you want to access MySQL from a JavaScript client that is restricted to HTTP/Websocket. HTTP is the natural choice. CouchDB convinced many when it introduced the idea.

Wed, 2014-10-01 23:00gerrynarvaja

In my last article, I explored how to enable Global Transaction IDs in MariaDB 10.0. I used a very common topology of 3 servers that is used for simple failover in case of a failure. In this article, I'm going to use that topology as a base to set up multi source replication and show how it is possible to simplify the high availability setup compared to regular MySQL replication.

The Basics

The topology uses 3 servers: A is the active master, B a stand-by master set up to replicate from A and C a multi purpose slave replicating from A. I also set up A to replicate from B. This way, if A fails, the system would start writing to B and once A comes back up, it will recover all the transactions from B through replication. As I mentioned in my previous article, typically this set up is used in …

The full schedule for Percona Live London 2014 was revealed earlier this month.
Congratulation to all selected speakers and a big thanks to those who submitted a talk.

I have many reasons to believe that this conference will be a very good year.
Let me explain why

A community event made by community members

If you didn’t know who selected the talks for the conference, have a look to the conference committee page.
This committee is a perfect mixed between community members and evangelists.
As chairman of this conference, I can say that these guys made an amazing job to rate and comment this huge amount of  submitted talks.

I particularly want to point out that despite the commitment of some …

Time for another quiz to impress your friends, if you have that kind of friends.

The manual page in question

mysql --skip-enable-disable-column-names=0 -e "select 1"
mysql --skip-disable-enable-column-names=0 -e "select 1"
mysql --loose-disable-enable-skip-column-names=0 -e "select 1"

What's the result? An error about unknown options? A warning? Column headers enabled? Disabled?

Answer ▼

mysql --skip-enable-disable-column-names=0 -e "select 1"
+---+
| 1 |
+---+
| 1 |
+---+

mysql --skip-disable-enable-column-names=0 -e "select 1"
+---+
| 1 |
+---+

mysql --loose-disable-enable-skip-column-names=0 -e "select 1"
mysql: option '--skip-column-names' cannot take an argument

Why Query Rewrites?

Now that the cost model project is progressing, most of you are going to notice execution plan changes. In the vast majority of the cases, the changes will be for the better, and some bugs with a long history will finally be closed. In some cases, however, you will notice that your queries run slower. This is inevitable: even if the MySQL optimizer is doing a much better job with the information it has, it may still be the case that the information was incomplete and that the best plan was, in fact, found by not trusting that information! Normally, we would just say “add an optimizer hint” and be over with it. But sometimes you can’t do that. For instance your query could be auto-generated from an application that you have no control over. This is why you want to intervene right before the …

Now it's time to get serious about replicating to MariaDB from Oracle, and we are real close now, right? What I needed was a means of keeping track of what happens in a transaction, such as a LOG table of some kind, and then an idea of applying this log to MariaDB when there is a COMMIT in Oracle. And thing is, these two don't have to be related. So I can have a table which I write to and also have a Materialized View that is refreshed on COMMIT on, and I need a log table or something. And when the Materialized View is refreshed, as there is a COMMIT, then the log can be applied. From a schematic point-of-view, it looks something like this:
This looks more complex than it is, actually, all that is needed is some smart PL/SQL and this will work. I have not done much of any kind of testing, except checking that the basics …

In time for Oracle OpenWorld 2014, we released Connector/Python 2.0. We also released a labs release Connector/Python 2.1 and we have a new feature: a C Extension which uses Connector/C.

This C Extension is an optional, an alternative to the pure Python MySQL Client protocol implementation. One of the reasons to implement it was to improve performance in some situations, for example, when huge result sets are returned. Pure Python is still default, if C Extension is not available.

The following post will get your through downloading and installing the MySQL Connector/Python 2.1.0 labs release.

Requirements

• Windows users out of luck; the labs release only compiles on Linux, OSX …