A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL’s GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.
This post was prompted by a message to the MySQL general emailing list some time ago, but I’m finally getting around to actually testing the theoretical solution I mentioned then (I can never just think my way through anything that involves locking and waiting… I have to test it).
Here’s the set-up:
create table test.messages (
id int not null auto_increment primary key,
message varchar(50) not null
);
The producer
The producer’s job is to insert rows into the table. In pseudo-code,
while (true ) {
get_lock();
// time passes...
query("insert into messages(message) values ('hi')");
release_lock();
}
Releasing the lock immediately after inserting will “wake up” the consumer, which must be blocked, waiting for the lock. Locking again as soon as possible will make the producer wait until the consumer is done processing, then the consumer will wait again.
The consumer
Since the consumer is waiting for the lock, that means it has tried to exclusively lock the same resource the producer has locked. Once the producer releases it, the consumer can go ahead and process the rows just inserted. In pseudo-code:
$last_row = 0;
while ( true ) {
get_lock();
$rows = query("SELECT * FROM messages WHERE id > $last_row");
for each $row ( $rows ) {
// Process
$last_row = $row[id];
}
release_lock();
}
Locking
The actual locking implementation always makes the details more complicated.
Both the producer and the consumer will have to get an exclusive lock on the queue table, or something that represents the queue table. The immediately obvious solution is LOCK TABLES. This doesn’t work well for most situations.
Why not? Since the producer and/or the consumer might need to access data in more than one table, they’ll have to lock all the tables they need. This will block other parts of the system from functioning, assuming there’s more than just a queue in the database. Other queries might then need to use LOCK TABLES too, and this just has a way of spreading out of control until the entire database becomes serial, mutual-exclusive access. This is terrible for any serious application.
Fortunately, MySQL has application locks, implemented with GET_LOCK() and RELEASE_LOCK(). They’re advisory, so you can ignore them if you want, but they are handy for things like this, where the producer and consumer just need to lock the same thing. They’re also relatively cheap. You’re really just locking a string, which you can pick. I’ll use the name of the table.
Here’s the code:
// Producer:
$timeout = 1000000;
while (true) {
query("SELECT GET_LOCK('messages', $timeout)");
// time passes...
query("insert into messages(message) values ('hi')");
query("SELECT RELEASE_LOCK('messages')");
}
// Consumer:
$last_row = 0;
while ( true ) {
query("SELECT GET_LOCK('messages', $timeout)");
$rows = query("SELECT * FROM messages WHERE id > $last_row");
for each $row ( $rows ) {
// Process
$last_row = $row[id];
}
query("SELECT RELEASE_LOCK('messages')");
}
This works because the producer and consumer are really notifying each other — it’s not one-way, it’s symmetric. Inside MySQL, there’s a queue of threads waiting for locks. As soon as one releases the lock, the other gets it, and immediately goes back onto the queue waiting for it again.
Complications
There’s more to it than this. GET_LOCK() has a timeout, which can’t be infinite. If the timeout expires, the function returns, but doesn’t grant the lock. Some other errors could also cause this to happen. The producer and consumer have to be prepared to recognize when the lock isn’t granted, and retry. The return value of GET_LOCK() signifies whether the lock was really granted. Also, either the producer or consumer could die, and then there’d be no wait for the lock at all. The consumer can tell that this happened by noticing there’s no work to do. The producer can’t really tell unless it queries the database. But the producer is likely waiting for something (another lock, user input,…) where the code says “time passes.” So this shouldn’t really be a problem.
Another limitation is the possibility of the consumer starting first and locking out the producer. If it doesn’t release the lock and try to re-lock periodically, the producer will never be able to get a lock. If it does, there’s still another problem. The consumer should sleep so as not to spin-wait for the presence of a producer. If the producer produces a row while the consumer is sleeping, and then doesn’t produce and release again for a very long time, the consumer will not find out about the row the producer inserted. It will have to wait for the next message the producer inserts. The solution is to make sure the consumer keeps the lock while it sleeps.
All of these issues are solvable with special-case startup code, but I’m sure you can work out something that meets your needs. I don’t want to make this article more complicated, because this will all be application-dependent.
Sample application
Here is a Perl script that implements a producer and consumer on a MySQL table called test.messages. To run it, give a --mode argument of ‘p’ or ‘c’. Be sure you create the table (see above) first:
Start two instances, one in producer mode, one in consumer mode, and watch the consumer print out messages as you enter them into the producer. Fun!
More options
If you do need to poll, there are still some steps you can take to make it more efficient. I wrote about efficient polling with exponential or Fibonacci wait a while ago. This technique has worked well for me in many applications.
You can also poll on something small and efficient, instead of polling a potentially big messages table. Make another table in which the producer inserts a single row, or flips a single row from zero to one, and the consumer resets it. Polling on a small resource is much more efficient than a big resource. You can use this technique together with transactions to coordinate the work of many producers and consumers, even when you don’t have explicit methods of locking (for example, if your database server doesn’t support it).
Finally, if you need a fixed-size FIFO queue or “round-robin table,” try the suggestions in my article on how to create a queue in SQL.
Technorati Tags:consumer, events, listener, locking, mysql, notification, polling, producer, queue, sql
You might also like:
- How to coordinate distributed work with MySQL’s GET_LOCK
- How to monitor InnoDB lock waits
- How to find out who is locking a table in MySQL
- How to give locking hints in MySQL
What would make me buy MySQL Enterprise?
MySQL AB’s recent changes to the Community/Enterprise split have made people go as far as calling the split a failure. I don’t think it’s working well either, but it could be fixed. Here’s what I think would make Enterprise a compelling offer.
I’d recommend Enterprise if I could
If the MySQL Enterprise Server were a good thing, I’d recommend it to my consulting clients. I’d suggest we start using it at my employer, too. I believe in supporting people and companies whose work benefits me. Here’s the thing, though: I think it would be detrimental, even dangerous.
Why on earth would I think that?
Because nobody’s testing the Enterprise source code before it’s released.
It’s getting bug fixes that haven’t been stress-tested in the real world. Some of them are even being rolled back, many months later, because they were broken.
Reasons I’d buy MySQL Enterprise
The reasons I’d buy a MySQL Enterprise subscription would be as follows, in order of importance:
But… that’s what Enterprise is, right?
The official list of benefits in an Enterprise subscription looks like it matches my list, doesn’t it?
The thing is, those first two bullets are blatantly untrue. Want proof? Look at the change list for MySQL 5.0.48, which will be the next Monthly Rapid Update. Here are just a few of the changes near the top of the list, with my comments:
These bug fixes address minor problems, but seem to have the potential to cause major damage if there’s a problem with the fix itself. None of these should be included in a hot-fix release. In fact, after looking through the whole list, I don’t see anything I would want to go to my production servers before six months of community testing. There’s simply too much at stake. The upside of including these changes is so small, and the potential downside so large, that it doesn’t make sense to include them.
What would I not want in Enterprise?
Here are some things that would not attract me to Enterprise:
Take a look at bullet points number three and four in the list of Enterprise benefits:
These are exactly the things I don’t want in my Enterprise source code. These two “benefits” directly conflict with the first two benefits. They cannot coexist, period.
MySQL’s marketing information says new experimental features are unstable, but hot bug fixes are stable and reliable. In reality, there’s no difference between new features and new bug fixes; they are both unstable and untested and don’t belong in a conservative, reliable product.
Until this changes, the Enterprise source code will continue to be less trustworthy than the Community source code, in my opinion. Even if the Community source doesn’t get the bug fixes, at least you know what you’re dealing with.
How would I change the current release policy?
I think this is easiest to explain with diagrams. Here’s the current release policy, as I understand it (I know this is over-simplified, but I’m trying to simplify this enough to show how I’d change it):
As I understand it now, the Community source gets (or is intended to get — it’s not really working, but that’s off-topic) frequent contributions from the community, and occasional bug fixes that are applied to the Enterprise source. The Community source is built and released infrequently.
On the other hand, the Enterprise source gets frequent hot fixes and releases, and infrequently gets features merged from the Community source after they’re deemed stable.
I’m not sure who designed this scheme, but I think a lot of people tried to say it was a bad idea and they went ahead anyway. Perhaps the symmetry in the diagram appealed to someone.
Here’s how that would have to change before I’d buy Enterprise:
In this model, the Community gets all source code changes first, and after they are stable, they’re merged into the Enterprise source. The Community code is built and released frequently, and Enterprise is extremely conservative.
This I’d pay for. This is a compelling offer that gives Enterprise customers substantial return for their money.
In this model, I’d be paying MySQL to do the painstaking work of looking at all the changes that happened in the Community source tree during the last release cycle, carefully selecting the good stuff, merging that into the Enterprise source tree, and testing the result. This is a proven model for creating high-quality software from a rapidly changing codebase. I don’t know why MySQL invented their own method instead, but it was a mistake.
Notice something else about this: unless the MySQL developers know something about revision control and merging I don’t (entirely possible, since I’ve never used the product they use), this is a lot simpler to manage. There are no cross-currents between the two source trees. It’s not just the aesthetics of having all the arrows going the same direction; I’d be a lot more confident that the merges went smoothly in this model. I think there’s a much lower chance of a mistake.
I also think the engineers would have a lot less work to do, and could concentrate more on making software and less on maintaining two complicated source trees. In fact, I believe the Community branch has actually been getting bug fixes too, contrary to my first diagram. This isn’t what MySQL initially announced they’d do, but if I had to guess, I’d say the engineering team said it would be too much work to keep the bug fixes out of the Enterprise branch.
Notice what I’m not saying about Community
I am explicitly avoiding saying something in particular about the Community source. I want quick release cycles and all patches applied there first, for one and only one reason: so the Enterprise source is trustworthy and stable. I’m not saying I want it so I can get the most bleeding-edge new fun stuff for free in Community. That is not a factor for me in the mindset I’m using to write this article — I am imagining myself as a customer who is very risk-averse (which is true).
This model would probably make some Community users happy too, though.
What if I needed an immediate fix?
What if I found a serious bug in the software and needed it fixed right away for my business? Shouldn’t MySQL release a hot-fix into the Enterprise tree for that?
No. I found a bug, who cares? If I found it, it means the community didn’t find it first. If the community didn’t find it, it probably only affects me. Therefore, the bug fix should go into the Community server.
If I couldn’t work around the problem (unlikely), I should be able to pay MySQL’s support engineers to make me a custom patch and build just to fix this problem. I’d assume all the risk of that, of course. This unstable, experimental patch should not go into the Enterprise source, but other customers should hear about it.
Right now you might be considering the similarity to Red Hat Enterprise Linux, and thinking “but RHEL does get hot fixes, so why shouldn’t MySQL Enterprise?” The reason is MySQL Enterprise isn’t an entire operating system distribution of software, with third parties fixing defects in upstream source. The Community process I’m advocating should take care of the vast majority of such bugs. Someone might find a critical security flaw that would warrant a hot-fix to the Enterprise product without waiting six months. But seriously, look at the bugs people find in MySQL — look at that changelog I linked to. There are no critical security flaws or kernel buffer overflows — and those are the kinds of things RHEL gets hot fixes for.
Some people might be drawn to MySQL’s current monthly hot-fix policy because they come from a Microsoft background, where Microsoft releasing service packs and hot-fixes is seen as a good thing. All I can say to those people is, you’ve become like a frog in a pot of boiling water. Microsoft’s fixes and service packs are a broken way of fixing their broken software, and are not a good way to manage quality software, so you shouldn’t measure the value of a release policy by whether it looks like Microsoft’s.
What would my ideal Enterprise version look like?
I’d really like to see MySQL AB stop adding new features and make the existing ones work better. The bugs I keep finding are usually quite simple, and I think that’s a sign of a low-quality codebase. For example, try creating a view that already exists. It breaks replication. How did this bug go unnoticed for so long? In my opinion, it’s because the server hasn’t been stable since 5.0 was released, and nobody’s using the bleeding-edge features as much as the core of the server, which is where I’d like MySQL AB to concentrate for the Enterprise version.
The Enterprise version I’d like to see doesn’t have views. That’s right, it doesn’t have views, because nobody’s used and tested them thoroughly yet (if they had, there wouldn’t be so many bugs in them). It doesn’t have triggers, stored procedures, the FEDERATED storage engine, stored functions — in terms of features, it’s somewhere in version 4.1. That’s what I’d call MySQL Enterprise. I don’t want these features because I don’t use them right now anyway, because they have the potential to cause such massive pain. I want them to go back to the community incubator so the bugs can get worked out. I’m managing just fine without using them, but I’m not managing fine with the pain they’re causing just by being there even though I don’t use them.
But at the same time the existing features, especially those needed for scaling and high availability, would be given a lot more attention. Replication would have much stronger assurances of accuracy and reliability. InnoDB would scale to more processors. The query optimizer would get a lot of love. In terms of improvements to existing features, my ideal Enterprise version is somewhere around 5.0.32. I chose that version because it was released about six or eight months ago, which means the big changes in that version would have been out in the Community for six or eight months and I’d be satisfied having them in the Enterprise version.
Right now, if you want to upgrade because of a bug that’s fixed in a newer version, you upgrade into some other bugs. I’m seriously tired of upgrading into the newest, latest, greatest bugs, like infinite loops in relay logs that fill hard drives with gigabytes of duplicate logs in a matter of minutes. These bugs have cost a significant amount of money, time, and frustration. I would definitely recommend people buy and use Enterprise if it fixed bugs without introducing new ones, but I see no signs of that happening.
MySQL’s sales pitch doesn’t convince me
There’s one more thing I think MySQL would have to do to get me to buy Enterprise, and that’s develop a better sales pitch. I’ll explain that — keep reading.
I think the way the Community/Enterprise split is designed smacks of marketing people making decisions. I don’t think this is ultimately going to be as successful a strategy for MySQL as it could be, because they won’t be able to sell it as well. Why not? Because unlike many other products, the people who make decisions about their company’s MySQL installations are engineers, by and large. The current marketing message sounds pretty condescending to an engineer.
I’ve even joined a MySQL webinar just to see. It was supposedly about scaling with MySQL, but in fact there was very little content. They spent a lot of time trying to say you should buy Enterprise. This was very strange, since the webinar was only open to current Enterprise customers. But the reasons they gave for choosing one or the other had me shaking my head in disbelief. It went something like this:
These aren’t direct quotes, but they probably aren’t far off — they certainly capture the spirit, if not the letter, of the webinar. Their strongest reason for using Enterprise was “because you should use Enterprise,” and they said it several times. And when they said Enterprise users should run Community on their test systems, I thought “you’re kidding. I’m going to test with a different version of the product than I run in production? Enough already.” I signed off with about five minutes left in the webinar.
The bottom line is, I don’t trust a company that assumes I won’t have a problem with such nonsense. I know there are smart engineers working on the MySQL server, but the marketing message is the face the world sees. In my experience, that ends up giving the marketing people the right to make decisions, even when the engineers disapprove. Therefore, I have no confidence the people making the decisions about how MySQL is developed and released are competent to do so.
If MySQL’s marketing materials were written and presented by people with serious tech savvy, I’d be a lot more comfortable about the invisible parts of the company. I assume most other engineers are going to extrapolate backwards from the façade, just like me, and conclude the decision-making process is untrustworthy.
Incidentally, this is exactly why my current employer (an advertising agency) rocks: because the sales folks and execs have decades of experience running companies in the industries we serve, and the people who answer when you call to discuss your account are analysts, not customer service reps. Whoever picks up the phone is an Excel wizard and has a SQL window (not a reporting system, a SQL prompt) open directly to an analysis server — our analysts and sales people are smart and capable and generally have business or engineering degrees from top universities; they’re not just friendly voices.
Contrary to popular wisdom, you can tell a lot about the book by looking at the cover. That’s why MySQL needs a sales pitch that’s convincing and respectable to an engineer.
Conclusion
MySQL AB says it needs to offer its paying customers something of value, and rightly so. Unfortunately, someone who doesn’t seem to understand software engineering at all has decided on a truly backwards way to do that. The result is a release policy that seriously degrades the quality of both product versions. MySQL AB’s marketing folks keep trying to say the Emperor’s new clothes are beautiful, but proof by repeated assertion just doesn’t work on people who know software engineering.
Put another way, MySQL AB is trying to sell Enterprise on the so-called benefit of including bug fixes so the product is “more stable.” This is an oxymoron. They should be selling the service of excluding untrusted code instead.
The current Enterprise offering not only isn’t compelling, but is designed to actually be lower quality than the Community source because there are fewer people testing it. Not using the Enterprise source is a no-brainer for me. However, if they’ll correct this mistake and start producing a source tree that’s conservative, high-quality, and stable, I’ll recommend people buy it. I wish MySQL well in their efforts to commercialize the product, but I don’t want what they’re trying to sell right now.
Technorati Tags:enterprise, mysql, sqlYou might also like: