In my rare spare time work on the http://jan.kneschke.de/2009/5/4/binlog-storage-engine-mysql-proxy-edition and for a few days I was wondering why my index-based JOINs didn’t worked. With a index it works, with an index nothing is returned.

I narrowed it down to a simple test-case:

select * from test AS a JOIN test AS b USING (event_pos) WHERE a.event_pos IN (106);
...
1 row in set (0.03 sec)

EXPLAIN
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
|  1 | SIMPLE      | b     | const | PRIMARY       | …

In my rare spare time work on the Binlog Storage Engine: MySQL Proxy Edition and for a few days I was wondering why my index-based JOINs didn't worked. With a index it works, with an index nothing is returned.

I narrowed it down to a simple test-case:

select * from test AS a JOIN test AS b USING (event_pos) WHERE a.event_pos IN (106);
...
1 row in set (0.03 sec)

EXPLAIN
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
|  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | …

During my PGCon 2009 presentation there was a question on the saw tooth nature of the workload results on the high end side of benchmark runs. To which Matthew Wilcox (from Intel) commented it could be scheduler related. I did not give it much thought at that time till today when I was trying to do some iGen runs for the JDBC Binary Transfer patch (more on that in another blog post) and also Simon's read only scalability runs . Then I realized that I was not following one of my one tuning advice for running Postgres on OpenSolaris. The advice is to  use FX Class of scheduler instead of the default TS Class on OpenSolaris . More details on various scheduler classes can be found on docs.sun.com.

Now how many times I have forgotten to do …

During my PGCon 2009 presentation there was a question on the saw tooth nature of the workload results on the high end side of benchmark runs. To which Matthew Wilcox (from Intel) commented it could be scheduler related. I did not give it much thought at that time till today when I was trying to do some iGen runs for the JDBC Binary Transfer patch (more on that in another blog post) and also Simon's read only scalability runs . Then I realized that I was not following one of my one tuning advice for running Postgres on OpenSolaris. The advice is to  use FX Class of scheduler instead of the default TS Class on OpenSolaris . More details on various scheduler classes can be found on docs.sun.com.

Now how many times I have forgotten to do …

When I get a new Mac, I go through the same steps every time. Yes I use a Mac as a dev box.

Software

Xcode
Adium
Launchbar
iTerm – change defaults to black background. (edit bookmarks, default, background)
Apache HTTP Server (source)
MySQL (source)
PHP (source)
Eclipse PDT
Omnigraffle
…

In this posting I’ll describe TokuDB’s multiple clustering index feature.  (This posting is by Zardosht.)

In general (not just for TokuDB) a clustered index or a clustering index is an index that stores the all of the data for the rows.  Quoting the MySQL 5.1 reference manual:

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

Most storage engines allow at most one clustered index for each table. For example, MyISAM does not support clustered indexes at all, whereas InnoDB allows only the primary key be a clustered index.

…

It may sound odd offering more Zimbra installation advice since there is a lot on the subject in other blogs, our documents, wiki and Forums. In fact, some quick research surfaced over 1.4 million hits for Zimbra server install on the web and 36,000 on the Zimbra site alone.

But we are also fortunate to have more new Zimbra users than ever, and after helping some trial customers recently, it …

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 …