To some, this will be a repost as I originally wrote it for an internal MySQL mailing list but I have had much positive replies to the posting, I thought I should share it with a wider audience.

One of these years I will finally get a talk accepted for OScon and actually be able to attend. I always hear good things about the conference and it seems like my company almost has its own track this year.

For those of you who are attending, be sure to attend some of the great talks being delivered by my friends and colleagues:

Improving Performance by Profiling PHP Applications
Next-Gen Interaction with Ajax, Comet, and PHP
Advanced Production Troubleshooting
Scalable Internet Architectures …

In a talk with Raph Levien today he said:

[basically] “… the metric for the success of an open source software project is whether the author now works at Google.”

… nearly everything I’ve ever written fails to pass this test.

Hopefully someone from Google’s M&A department can help me remedy this situation. (hint hint)

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 …

Computerworld has Marten Mickos on the hot seat about the growth of MySQL and what happens next. As always, Marten is insightful and rational. I am jealous that I am not as clear-headed. I am very passionate about open source. And I do believe that it is a superior method. But at the same time, I must be pragmatic. So when they say being dogmatic is very important for the Free Software Foundation ? well, they should be. That?s what we respect them for. But running a business is not about dogma. We are not judgmental about our customers or... READ MORE

BusinessWeek asks: How much is MySQL worth? MySQL, a fast-growing maker of database software used by some of the Internet's most recognized brands, is preparing to file for an initial public offering, perhaps as soon as late 2007. The offering could value the company at between $600 million and $1 billion, according to sources, and inject some pep into a tech IPO market that's seen only a handful of successful offerings in the past year.... READ MORE

Version 1.2 of Zmanda Recovery Manager (ZRM) for MySQL, a robust and intelligent solution for backup and recovery of MySQL databases is available at Zmanda downloads page.

Changes in this release:

• Code restructured into modules
• Snapshot plugin interface. LVM snapshots implemented as a plugin.
• ZRM should be run as user belonging to mysql group.
• Numerous bug fixes

Documentation is available at ZRM for MySQL wiki. Ask questions and provide feedback in Zmanda forums.

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 …