We’ve been looking at high concurrency level issues with Drizzle and MySQL. Jay pointed me to this article on the concurrency issues due to shared cache lines and decided to run some of my own tests. The results were dramatic, and anyone who is writing multi-threaded code needs to be aware of current CPU cache line sizes and how to optimize around them.

I ran my tests on two 16-core Intel machines, one with a 64 byte cache line, and one with 128 byte cache line. First off, how did I find these values?

one:~$ cat /proc/cpuinfo | grep cache_alignment
cache_alignment : 64
...

two:~$ cat /proc/cpuinfo | grep cache_alignment
cache_alignment : 128
...

You will see one line for each CPU. If you are not familiar with /proc/cpuinfo, take a closer look at the full output. It’s a nice quick reference of other things like L2 …

As previously noted, I've been playing with XtraDB a bit at work. Over a week ago I decided to test compression on one of our larger tables and it took a bit longer than I expected.

(root@db_server) [db_name]> ALTER TABLE table_name \
    ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
Query OK, 825994826 rows affected (8 days 14 hours 23 min 47.08 sec)
Records: 825994826  Duplicates: 0  Warnings: 0

Zoiks!

It's too bad we couldn't use all the cores on the machine for the ALTER TABLE, huh?

On the plus side, the file sizes aren't too bad.

Before:

-rw-rw---- 1 mysql mysql 1638056067072 2009-05-24 09:23 table_name.ibd

After:

-rw-rw---- 1 mysql mysql  587617796096 2009-05-27 07:14 table_name.ibd

I'll have more to say about XtraDB …

As our data warehousing process grows and the workflows get more complex, we've revisited the question of what tools to use in this process. Out of curiosity, I had a look at basing such a process on Hadoop/Hive for scalability reasons, but the lack of mature tools and the sacrifices on efficiency that would entail meant we're better off using something else as long as a distributed processing platform is the only thing that can get the job done. I'm also curious about the transition to continuous integration, a model I noticed showing up a couple of years ago and now getting some air under its wings as CEP, IBM's …

The spirit and intent of the Free Software Foundation (FSF) and the GPL license are right on target. However, we must be careful to ensure that the GPL license is interpreted in a manner fulfills the spirit and intent behind its framing. Richard Stallman and associates set out to draft a license agreement that ensures that free software remains free. They didn’t want to see open source become corrupted with the insertion of proprietary code that would eat away at the freedoms they envisioned.

To protect the eternal purity of the open source software, they created constraints on how proprietary code can interact with the GPL code. Their one weapon in this battle is the automatic and forced expansion of their GPL license to any code that integrates with the base GPLed code. I often refer to this process as acting like a virus. I don’t use this term to infer nefarious intent any more than viral marketing infers nefarious intent. On the …

Writing small volume of data (Bytes-MBs) with sync (fsync()/fdatasync()/O_SYNC/O_DSYNC) is very common for RDBMS and is needed to guarantee durability. For transactional log files, sync happens per commit. For data files, sync happens at checkpoint etc. Typically RDBMS does syncing data very frequently. In this case, overwriting is much faster than appending for most filesystems/storages. Overwriting does not change file size, while appending does. Increasing file size requires a lot of overheads such as allocating space within the filesystem, updating & flushing metadata. This really matters when you writes data with fsync() very frequently. The following are simple benchmarking results on ext3 RHEL5.3.


1. creating an empty file, then writing 8KB 128*1024 times with fdatasync()
fdatasync per second: 3085.94321
(Emulating current binlog (sync-binlog=1) behavior)

2. creating a 1GB data file, then …

Connector/J has a feature where the driver can take prepared statements of the form "INSERT INTO foo VALUES (...)", and if configured with "rewriteBatchedStatements=true", can re-write batches of them to the form "INSERT INTO foo VALUES (...), (...), (...)". This is a performance win on a few fronts, because of reduction in latency (remember, MySQL in general doesn't have a "batch" form of prepared statement parameter bindings, so each parameter set usually needs to be sent as a separate INSERT), and because of optimizations of handling "multivalue" INSERT in the server itself.

Prior to code sitting at the the head of Connector/J 5.1 which fixes Bug#41532 and Bug#40440 "larger" batches (over 500 batched parameter sets or so) experienced extreme performance degradations due to a bunch of extra parsing that the driver did while …

I had this idea around 5.4. It is meant to scale better on multi-core machines compared to, say, standard 5.1. This has been proven also it seems. 5.4 is still in Beta, but testing has shown that scalability is definitively better.

What I wanted to know was how well 5.4 would work on a lower spec box compared to 5.1. One reason for me wanting to test this is that I am currently in my summer house, and I have no multi-core 64-bit machines around. All I have in terms of Linux boxes is an old Dell Laptop with Gentoo on it, all this will as similar setup as possible between 5.1 and 5.4.

So, I download 5.4 sources, as there are no 32-bit Linux binaries yet, and I do a simple configure and make. And.. I fail. Out of the box, some of the inline optimizations in innodb (the config I built with was max-no-ndb) will screw up gcc when using -O3 optimization (OK, I admit, I'm not 100% sure that this is the cause, but it sure looks …

If you’ve attended just one of my recent talks, either at the UC, LOSUG or MySQL University, you should know that MySQL 5.1.30 will be in the next official drop of OpenSolaris.

In fact, you can find MySQL 5.1 in the current pre-release builds – I just download build 111 of the future 2009.06 release.

Key things about the new MySQL 5.1 in OpenSolaris:

1. Contains the set of DTRACE probes that also exists in MySQL 5.4 (see DTrace Documentation)
2. Like the 5.0, we have SMF integration, so you can start, stop, monitor and change some of the core configuration through SMF
3. Directory layout is similar to 5.0, with a version specific directory (/usr/mysql/5.1), and the two can coexist if you want to handle a migration from 5.0 to 5.1

To install MySQL 5.1, use the pkg tool to …

For anybody interested in trying out our new MySQL Workbench 5.2 Alpha2 I have prepared a short Quick-Tour that will show you the most important steps to successfully use WB to query your databases.

Manage Your Connections

MySQL Workbench 5.2 introduces a new Home Page that makes it very easy to access all your Database Connections and EER Models. It features the Workbench Central Panel, the Database Connections Panel and the Model Files Panel.

In order to be able to connect to your  MySQL server you have to create a new Database Connection so MySQL Workbench knows about your server instance. Follow the steps shown below to create your first Database Connection.

Click on the screen shot to see it at full size.

Connecting to and Working with the MySQL Server

Once you have created your …

A couple of weeks ago I wrote a lua script to use with the MySQL Proxy that transforms the Proxy into a key=>value lookup dictionary.

But I couldn't just stop there. So I decided to add replication to it :).


The basic idea is that you have one proxy that acts like a master, it can handle all write/read operations. Once it receives a write query, it will send that query to the slave proxy instances, and after the last slave gets the query, the master will return a confirmation to the mysql client.
And of course, you send your read queries to the slave proxy instances.

Show me the code.
It is available on the …