When I was doing data loading tests, I realized that usually low checksum calculation CPU percentage is actually the blocking factor. See, usually when background writers do the flushing, it gets parallelized, but if active query is forcing a checkpoint, it all happens in ‘foreground’ thread, checksum computation included. This is where more Sun-ish wisdom (these people tune kernel with debugger all the time) comes in:

gdb -p $(pidof mysqld) -ex "set srv_use_checksums=0" --batch

Puff. Everything becomes much faster. Of course, one would be able to restart the server with –skip-innodb-checksums, but that would interrupt the whole process, etc. Of course, proper people would implement tunable parameter (5 lines of code, or so), but anyone with Solaris experience knows how to tune stuff with debuggers, hahaha.

Odd though, I …

A great post from Jonathan Ellis on "Why you won't be building your killer app on a distributed hash table"

Scroll down to see my comment.  I agree a simple DHT is not a suitable solution for mainstream data management issues, but also I think that there is a gap between RDBMS and DHT.

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 that …

I’ve been working with Patrick Galbraith for the past couple weeks on a new project that started as an example in his upcoming book. It is a search engine built using Gearman, Sphinx, Drizzle or MySQL, and memcached. Patrick wrote the first implementation in Perl to tie all these pieces together, but there is also a Java version underway bring written by Trond Norbye and Eric Lambert that will be shown at the CommunityOne and JavaOne conferences next week. I’ve been helping get the system setup on a new cluster and with the port to Drizzle.

Narada …

Some of you may already notice, that in Build Service, there are already available rpms for new MySQL 5.1. Currently I think that in openSUSE 11.2 will be some 5.1 version of MySQL. So it may be interesting to tell our users what will change and how. Most of these changes are reflected in README.SuSE, but frankly - who reads READMEs?

Changes Plugins

One of the new features in MySQL 5.1 is support for plugins. So some of the storage engines will be now shipped like that. Following plugins will be available:

• archive
• blackhole
• federated
• example

As you can see, InnoDB will be still compiled as internal part of MySQL. If you are upgrading from version 5.0.X or if this is your first installation of MySQL 5.1, all plugins will be enabled by default and you can disable them manually later (see …

Some of you may already notice, that in Build Service, there are already available rpms for new MySQL 5.1. Currently I think that in openSUSE 11.2 will be some 5.1 version of MySQL. So it may be interesting to tell our users what will change and how. Most of these changes are reflected in README.SuSE, but frankly – who reads READMEs?

Changes Plugins

One of the new features in MySQL 5.1 is support for plugins. So some of the storage engines will be now shipped like that. Following plugins will be available:

• archive
• blackhole
• federated
• example

As you can see, InnoDB will be still compiled as internal part of MySQL. If you are upgrading from version 5.0.X or if this is your first installation of MySQL 5.1, all plugins will be enabled by default and you can disable them manually later (see …

I thought I was clear in earlier blog postings about Performance Schema, but I’ll have to write more firmly.

1. Performance Schema source code is available:
https://code.launchpad.net/~marc.alff/mysql-server/mysql-6.0-perfschema

2. Performance Schema has been in development for less than one year.

3. All Performance Schema worklog task descriptions are public on forge.mysql.com.

The PBMS BLOB repositories can now be backed up with mysqldump.

A new system table ‘pbms_dump’ contains 1 row for each repository record. Each row consists of 1 column which is a BLOB. The content of this table has no purpose other than for the use in repository backup. By inserting the data back into this table the repository is recovered. Before doing the backup the pbms_dump system table needs to be discovered, to do this execute the command “select * from pbms_dump where false”. After doing this then mysqldump will include it in backups. Be sure to use the “—hex-blob” option. For example:

Mysqldump –u root –hex-blob mydatabase > mydatabase.sql

Before recovering the database you need to notify PBMS that a recovery operation is in progress so that the recovery of the tables containing the BLOB references do not increment the BLOB reference count. This notification is done by setting …

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 |       | …