I started this post a month ago, but after Kaj’s discussion on the same topic at the MySQL Camp I figured it was time to post.

I had dinner with a friend recently (a very smart friend), and our conversation lead him to ask “What’s different with MySQL?”. One of the things I tried to describe was the “Pluggable Storage Engine Architecture” (PSE) potential for the future that I expect will set MySQL apart from all other Open Source and even commercial databases.

Here are some details of the example I tried to provide, given somebody who understands enough of the general principles of RDBMS’s.

Consider the ability that information (intelligent data) is available within a Relational Database via the appropriate tools and language (e.g. SQL) but it is not physically constrained to Tables, Columns …

Mike Kruckenberg posted recently a tip he found to create easy text bar charts using the REPEAT function, that he found at:

http://www.squarebits.com/blog/2006/11/generate_simple.html

Today I stumbled across this link:
http://www.webcheatsheet.com/php/dynamic_image_generation.php#diagram

Which, using PHP, creates a graphical bar chart using the data in a MySQL database and a small bit of math.

Both are very neat!

Spoke with Brian the other day on what was required to get NDB to be a pluggable engine - and started hacking.

The tricky bits invole dependencies of things like mysqldump and ndb_restore on some headers to determine what tables shouldn’t be dumped (hint: the cluster database used for replication).

Also, all those command line parameters and global variables - they’re fun too. It turns out InnoDB and PBXT are also waiting on this. In the meantime, I’ve done a hack that puts config options in a table.

Currently blocked on getting the embedded server (libmysqld) to build properly - but i now have a sql/mysqld binary with pluggable NDB. All libtool foo too.

Hopefully i’ll be able to post soon with a “it works” post

Inspired by PeterZ’s Opening Tables scalability post, I decided to try a little benchmark. This benchmark involved the following:

• Create 50,000 tables

• CREATE TABLE t{$i} (i int primary key)

I wanted to test file system impact on this benchmark. So, I created a new LVM volume, 10GB in size. I extracted a ‘make bin-dist’ of a recent MySQL 5.1 tree, did a “mysql-test-run.pl –start-and-exit” and ran my script, timing real time with time.

For a default ext3 file system creating MyISAM tables, the test took 15min 8sec.

For a default xfs file sytem creating MyISAM tables, the test took 7min 20sec.

For an XFS file system with a 100MB Version 2 log creating MyISAM tables, the test took 7min …

It seems, people are clammering for a more consolidated help guide for SQL Performance tips.

Jay Pipes at the MySQL Camp ran a session Interactive Top 10 SQL performance Tips. There was plenty of input and discussion, and at the time Sheeri simply typed them into a wiki page for later work.

Well it seems even that rough list is popular at Del.icio.us ranking near the top of the Hot List on the front page. I saw it earlier and it was second or third, but didn’t think of taking a screen shot until now, but it’s still high.

I’d say that we could easily …

At our MySQL Camp Jay and Brian pitted off in the Umbrella Joust. Not sure if there was a winner, or a looser, but in the end no blood was split (except Leslie, but that’s another story).

See these and more camp photos at Flickr.

Some early notes by Brian Aker on Falcon as discussed at the MySQL Camp.

Falcon is a transactional engine MySQL will be introducing. The first discussions were held about 3 years ago with Ann Harrison and about 1 1/2 years ago, MySQL started taking seriously the possibilities.

Falcon is not an InnoDB replacement. It’s a different way of looking at the problem of how it looks at and manages transactions, and how it’s designed. It flips around the way data is stored. Some points:

• It uses as much memory as possible, like Oracle SGA or InnoDB pool.
• It has a row cache not a page cache for more optimal memory use.
• No locking at all. Jim doesn’t believe in it for concurrency control. It has total versioning.
• Falcon has to keep all changes in memory, so not great for user transactions that may take longer
…

The final day of the MySQL Camp quotes. See also Day 1 and Day 2.

“Patches on Patches on Patches.” — Jeremy in his Query Profiler patch talk.

“I bagged Jeremy, no I mean I bashed Jeremy and all I got was this badge.” — Sheeri

“You have an enum of ‘Red’, ‘Blue’, and ‘Yellow’, and what is the result if you add 1 five times to the value of the enum.”
— Brian Aker Quiz question. The answer is Blue, and the winner was Taso. Why. You will need to do the certification example, or email me and I’ll tell you.

“High bandwidth opaque identifier” — Chuckie talking about his Unique Internet Id

“So you have until the end of lunch to work out how to start the Instance Manager” — Brian Aker on …

Monty gave us a quick overview of next generation of MyISAM. It is set to include:

• New data disk format
• Transaction support
• multi-versioning
• row level locking and escalation to table level locks. (interesting)
• bitmap indexes and new table scanning optimizing indexes with up to 1000x times performance.

No details of time frame were given for delivery, however development is well underway.

What the?

Well this is the inheritance diagram of the Item Class in the MySQL 5.1 Source tree, nicely documented using the Doxygen tool as mentioned by Jay in his presentation at MySQL Camp.

Jay started the Community Doxygen Project on the Forge to improve the level of commenting enabling a better platform for the community to contribute MySQL server code changes.

At this early stage David Shrewsbury is working on fine tuning initial documentation examples for QA and review. You can check out the Status Page of automated commenting conversion.

You can see the present documentation of MySQL 5.1 source …