What if MySQL had an HTTP interface and would reply JSON? What if MySQL had server-side JavaScript? What if you could store JSON documents in MySQL? And, what if there was Map and Reduce built-in to MySQL? Make it happen, today. A proof of concept MySQL 5.6 daemon plugin presented at IPC Spring 2012.

HTTP, JSON, JavaScript, Map&Reduce built-in to MySQL
View more presentations from Ulf Wendel

Eye-opening community contributions

Mycached (2009, Cybozu) was a ground breaking achievement. A …

As described in the first article of this series, Tungsten Replicator can replicate data from MySQL to Vertica in real-time.  We use a new batch loading feature that applies transactions to data warehouses in very large blocks using COPY or LOAD DATA INFILE commands.  This second and concluding article walks through the details of setting up and testing MySQL to Vertica replication.

To keep the article reasonably short, I assume that readers are conversant with MySQL, Tungsten, and Vertica.  Basic replication setup is not hard if you follow all the steps described here, but of course there are variations in every setup.  For more information on Tungsten check out the Tungsten Replicator project at code.google.com site well as …

Real-time analytics allow companies to react rapidly to changing business conditions.   Online ad services process click-through data to maximize ad impressions.  Retailers analyze sales patterns to identify micro-trends and move inventory to meet them.  The common theme is speed: moving lots of information without delay from operational systems to fast data warehouses that can feed reports back to users as quickly as possible.

Real-time data publishing is a classic example of a big data replication problem.  In this two-part article I will describe recent work on Tungsten Replicator to move data out of MySQL into Vertica at high speed with minimal load on DBMS servers.  This feature …

Last week one of my collagues came to me with the following problem. He was not able to create an innodb table. MySQL returned ERROR 1005: Can’t create table (errno: -1). Most of the times this problem is a foreign key related issue but now this wasn’t the case (Foreign key error will return errno: 150). What actually happened is that ibdata already contains the registration of the new table but the frm file wasn’t there. This way when he wanted to create the table the server responded with error. Although he wasn’t able to remove the table either. It was caused the file-by-file cloning of the database.

For better understanding you have to be familiar with the inner operations of MySQL. With the most simplistic explaination I could say that MySQL is just working on a high SQL abstraction level which can use different storage engine for storing datas. Viewing from this aspect MySQL doesn’t care how the files …

On Thursday I went to the Harmony conference arranged by Oracle User Group Finland to speak about Galera clustering. (They chose the topic based on my suggestions.) The slides are now available on SlideShare. I'm pretty satisfied with this talk myself, the slides contain the most important steps you need to know to get started, but also the internal architecture of Galera, how it works, and what kind of replication topologies and load balancing you would want to use with it. And benchmarks of course.

read more

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:

mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+--------+
| id | name   |
+----+--------+
|  3 | demo01 |
+----+--------+
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec) …

Don't forget to join us Tuesday for the MySQL Community Reception.
While the webcast users will have to miss you those who attend will have a great afternoon packed with great sessions , BOFs, a keynote by Jeremy Cole, DBA Team Manager, Twitter and of course the MySQL Community Reception.

You can still register to attend the event in-person !

One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the original developers) did also. But I didn’t know the …

This article describes a single MySql function to sanitize strings. The provided options are:

• replace multiple spaces to a single space
• remove Unix and Windows linebreaks and replace them with a space
• remove tabs and replace them for a single space
• remove multiple backslashes
• completely remove htmlentities (&)
• replace htmlentities with the corresponding character
• remove all HTML tags

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2 …