Over the weekend we migrated one of our tables from MyISAM to InnoDB. Generally speaking we’re pretty happy with most of our InnoDB migrations.

InnoDB generally uses 2-3x more data on disk but the write ahead log (WAL) buys us so much more added performance that it’s generally worth the switch.

This migration however was a bit more problematic. Importing the data from mysqldump as MyISAM into the new box only took about 2 hours. Converting the data to InnoDB has so far taken 24 hours and using 21G vs the original 6.1G for MyISAM.

I should also note that it’s not complete yet and it’s 3.5x larger than our original MyISAM install.

Clearly the performance will be worse in our situation for InnoDB because I don’t think the buffer pool efficiency will make the write ahead logging fast enough to beat MyISAM on the same hardware.

We’re not the only people who have …

As I felt the itch to do some quick hacking yesterday, I decided to provide an RPM spec file for the MySQL proxy. The changes have been commited to the SVN trunk now and I added some hints to the INSTALL file on how to perform an RPM build.

Here is a quick summary of how to convert the current SVN code into an installable RPM. You build environment needs to fulfill a few additional prerequisites (a gcc compiler and the C library header files are taken for granted here), I added the versions I used on my openSUSE 10.2 system for reference:

• autoconf 2.56 or newer (autoconf-2.60)
• automake 1.9 or newer (automake-1.9.6)
…

For the curious, the slides for the eLiberatica 2007 presentations are now up at http://eliberatica.ro/2007/ - the slides include presentations from eZ CEO Aleksander Farstad, Brian Behlendorf, FSF Europe President Georg Greve, Ubuntist Kurt von Finck and MySQL co-founder Monty Widenius (and yours truly, but the slides are a bit weak - check out my …

Samsung has announced a new line of 64G SSDs for use in consumer laptops. Nice to see innovation but I REALLY want to see more SSDs in the server space.

I JUST want to use a CHEAP 2-5G SSD for write ahead and binary logs. I just need 1k write ops per second with a SATA interface. Then I could drop them in cheap commodity servers and get a significant performance boost. My disks would still use software RAID 0 (at least until I replace this partitioning) but only store data.

Give me one of these for $50 to $100 and I’ll buy one with ever server.

The fsync’s required for writing to disks on INNODB can really hurt performance. My bet …

Today I needed the ability to create a Oracle-style sequence generator outside of the normal MySQL auto-increment functionality.

I was performing a table format upgrade and one column needed a unique index and we’re not using auto-increment on this table.

This little bit of SQL should work fine:

SELECT @sequence:=IFNULL( @sequence + 1, 1 );

You’d have to use this construct with INSERT INTO ... SELECT constructs.

So for example:


CREATE TABLE TEST_SOURCE
(
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID));

INSERT INTO TEST_SOURCE (ID)
VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

… will give us a TEST_SOURCE table with 6 auto-increment values.


mysql> SELECT * FROM …

OK. First off. I broke the rules. There are actually seven here.

I figure Marten, Jay and Stewart can’t be wrong.

… so here it goes.

* Smarter InnoDB checkpointing. The fuzzy checkpointing seems less than ideal. I think you could just fill up memory with data pool modifications and then checkpoint every 3-5 minutes or so writing the entire DB out to disk in one head pass. You’d be able to fully saturate the disks in this manner. Granted faster is better but our 100MBps drives only see 15-30MBps in practice.

You’d need copy on write semantics though so if you’re seeing full …

I’ve noticed that in the last five years I’ve started to become very conservative about adopting new technologies.

We’re JUST now migrating to Opterons for example. I’m only now considering adopting Java 5.0.

My stack is essentially Java 1.4.2, MySQL 4.1.22, Debian 3.1 (migrating to Etch), and Athlon processors.

There just aren’t any surprises. The entire stack works and everything is a known entity. Since I have a pretty stable foundation I can look at technologies one at a time without fundamentally changing the stability of the entire system. Java 5.0 looks interesting but no real pressure here at the moment.

MySQL 5.1’s partitioning support will probably be the only feature that pushes us to upgrade. There are some InnoDB fixes I want to incorporate as well.

I really like this thread:

“OpenBSD is free as in air,” Theo de Raadt [interview] stated in a recent thread on the OpenBSD -misc mailing list. The discussion began with a note that the Open Sound System [story] had recently been “open sourced” under the GPLv2 and CDDL leading Theo to comment, “noone cares about being Open and Free anymore. They just care about being called Open and Free, and how convenient — a bunch of laywers generated an organization that will label then Open and Free when they are not in fact so.”

This is an interesting statement.

The GPL and BSD licensing camps have always had a core fundamental difference in theory. If GPL is copy-left and Microsoft is copyright then the BSD is copy-center.

What’s nice about the BSD license is that there’s NO complexity. You can turn around and sell the code if you want to and just …

The other day I criticized MySQL for using somewhat cheap marketing tactics on Planet MySQL.

Obviously I’m a MySQL fan so instead of just criticizing I figured I’d offer some constructive ideas (a patch basically) to fix the problem.

Instead of preaching to the community they should become a leader in the MySQL blogosphere.

MySQL does a great job here at the source code level but they’re using marketing 1.0 tactics which just won’t work with sophisticated customers anymore.

They just don’t really participate in the blogosphere much and I think it’s hurting them. For example, Marten Mickos doesn’t appear to have a blog. Nor is there an …

I sort of agree with Jeremy Cole. This new marketing push using Planet MySQL seems like a cheap advertising stunt.

First, what’s with the highlighted post? Why is it highlighted? Seems like and advertisment.

Which brings up a number of significant questions:

1. Where’s my cut? I write content that’s pushed to Planet MySQL which wouldn’t be attractive for running ads if it wasn’t a collection Plof intelligent authors writing about MySQL.

2. Can competitors run ads on Planet MySQL? Can Oracle run an ad on INNODB? Can Solid run an ad?

Planet MySQL is a community oriented site. Seems like we should keep it that way.

…