The latest from the MySQL community

A week or so ago I mentioned that I'd be running an "Open Source @" series of posts, and try to capture the work that various large enterprises are doing with open source. Being large enterprises, it has taken a bit longer to collect these than I would have liked, but we now have a critical mass and can move forward.

Today we're profiling Oracle. I have been harshly critical of Oracle in the past, and yet I continue to hold the company in high esteem. Oracle is one of the few winners in the proprietary "battle of the ecosystems." I do business with a wide range of Global 2000 businesses, and I see Oracle all over. I can't say the same of several of Oracle's competitors.

I asked Mike …

Brian postulates the idea of a "Queue" engine and why it is impractical. I agree that a queue storage engine doesn't really work too well. What would work better would be either a special 'queue' object type which may use any user accessible storage engine or perhaps a non-standard extension to the SQL syntax:

DEQUEUE message FROM queue_table LIMIT 1;

This can be done in (perhaps) the MySQL Proxy, where it is translated into a SELECT/DELETE combination or it can be added to the server, and should not cause much parser complications.


Then any storage engine may be used for a queue, purely dependent upon the user's requirements as some people will be satisfied with a non-persistent MEMORY based queue, MYISAM queues will be adequate for many and for a few who need …

A few months ago, I had a meeting with a small local startup. Their tech and their pitch is pretty neat. They had an angel kick them a megabuck of seed to get started. It's the classic geek startup: two main guys, one wearing the CEO/sales/biz/money hat, and the other wearing the CTO/it/tech/arch/geek hat. And they've hired a couple of coders.

But they've also rented some office space: two cubes, a meeting room, a front desk, and a lockable office for the locking file cabinet. Now, it was cheap office space, but still, why?

It's a waste of the angel's money, is increasing their burn. For what it's costing them, they could hire another coder. Dev speed is their current bottleneck, and going from 2 to 3 causes only minimal invocation of Brook's Law, especially if it's early. If they start to grow, they'll have to hire #3 and #4 soon enough anyway, at which point they've outgrown their space, and now also have the cost, …

krow just posted about the the difficulties of implementing a Queue Engine for MySQL.

I don't think it's really that impossible. Yes, there are some kind of SELECTs that either don't make sense, or are almost certainly not what the user wants, or are impossible to do.

But trying to use a very specialized engine for general purpose queries is not really something to worry about.

I would take the same approach that I do with my S3 storage engine, e.g., implement whatever makes sense, and then for the cases were it doesn't make sense, tell the user "So, don't do that, then!".

For a queue engine, I would separate the …

After mentioning Blaine Cook's use of MySQL as a queue engine for Twitter I've been pinged about why couldn't one be written for MySQL.

Useful? Hell yes.

Possible? Not Really.

Lets look at the simple case.

CREATE TABLE queue (id serial, message text);

INSERT INTO queue VALUES (message) "This is the first message";
INSERT INTO queue VALUES (message) "This is the second message";
INSERT INTO queue VALUES (message) "This is the third message";

In an application that would use this, the sort of query you would see would be:

SELECT message FROM queue LIMIT 1;

For a queue engine to work, it would always need to respond with the first record in, being the first record out. So you would get:

"This is the …

MySQL uses disk. Ok, so everybody knew that. MySQL uses disk in two primary ways.

• Random I/O (Reading & Writing Data/Index blocks)
• Sequential I/O (Binary Log, InnoDB Redo Log)

Historically it’s been best practice to separate these onto different spindles, and also separating the OS and tmp space onto a third spindle. With commodity H/W that can be easily done, but today a lot of people use SAN. Is this a good thing for a MySQL Database?
That’s a topic of much discussion at a later time, however I’ll add two points. A lot of SAN configurations are RAID 5, and RAID 10 is a better choice due to removing the requirement to calculate the parity. Second, last week I observed a RAID disk failure and it took an incredible long time for the disk to be re-built. Just how many SAN uses our there have actually timed a disk rebuild on a loaded system and seen the impact on the system in general.

…

Okay... I had originally stated that I would not publish my wish-list but since Jay Pipes has said that he was collating these ideas, I'll play along hoping that there is a positive influence on the future of MySQL. Here are my five, plus a few extra.... (out of list of many others)

1. Modular Architecture
   
   I know that many people have put this on their wish list and I am sure that many people would probably blame me for some of the current architecture woes. Honestly, I have an excuse: I am trying to evolve the plug-in system into something more usable. As Eric Herman can testify, we actually had an alternative plug-in architecture under development as a small skunk works project, which in my opinion was superior than what we have today but we permanently shelved it after the current plug-in system appeared in the tree.
   
   A good modular architecture would allow parts to be …

My opinion on MySql best practices, the new new 'chain letter' started by Ronald Bradford. As always, this is a developers point of view.

0. Make a database design. Databases generated automatically might work very well in development, but when you have large data volumes and lots of concurrent users they do not.

1. Be consistent in your choice of datatypes. I prefer to use just a subset in a system. Try to stick with SMALLINT UNSIGNED or INTEGER UNSIGNED for keys. Always use the same datatype for the same term in all tables, no joins with datatype conversion! Do not store numbers as CHAR/VARCHAR fields.

2. Make sure there are indexes supporting all searches that are part of routine system usage. If you have tables that are very small it might be better not to index them, but this should be an active decision.

3. Verify your …

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

The submissions have been flowing in nicely, but the usual speaker slackness (and waiting for the deadline to make that pretty whizzing sound as it zaps past) appears to also be alive and well!
So, prod prod... http://osdc.com.au/papers/cfp.html
Keep em coming.

This year's Open Source Developers Conference (OSDC) 2007 will be held in sunny Brisbane Australia, 26-29 November. It's by developers, for developers, with of course some modern business mixed in. About applications and languages, tools and strategy... you want to be part of this for sure.