Planet Drizzle

Brian Aker: Drizzle, Views And Triggers

Wed, 15 Jul 2009 20:15:43 +0000

I've been trading twitter responses back and forth with jswanhart of Kickfire recently on the state and planning of Views in Drizzle.

In Drizzle right now we do not have views. There are plans to add views which never "materialize", but that is still a couple of milestones off.

One of the problems when talking about views is that the word "materialize" has been over used.

To "materialize" a view, means that you take the view definition, turn it into a temporary table, and then join it against a query. In Drizzle we consider this a "no no".

Why? Because any query that does this is more then likely going to turn out to be to slow to be of any real use in our domain space. The same problem exists with subqueries though for the time being we are leaving them in. Subqueries at this point are only semi-useful. Many of the common methods of using them result in a materialized table, which just makes them too slow. Our plans involve eliminating these sorts of queries and only allowing ones which can be "merged".

This will be limiting, but experience shows that the current state of use is that most users don't want to be able to write a query that will under perform (unless I am wrong, Google has disabled the subquery system in their version of MySQL just to make sure no developer gets anywhere near this part of the system).

Frankly, I would like to take this one step further at some point, and eliminate cartesian joins unless they are explicitly requested. How often do you really want one? Pretty much never. Typically they are done by accident and you either wait out the mistake or start hitting "CTRL-C" hoping to kill the query before it eats up the machine.

So "materializing" is out, but what is not out is "materialized views". This is an SQL feature whereby a table is created and is updated based on entries from one or more tables, either syncronously or asycronously. This is something I do expect us to support at some point.

One of the strengths in the current design is that the replication system lives as a service to the micro-kernel.

What does this mean?

It means that we internally have triggers on any sort of DDL/DML event that occurs in the server. As soon as someone wants to write up some parser goodness we can have basic triggers in whatever language you want.

We also gain materialized views via the same method. The "tires" on this project have been kicked around but no one has started it yet.

Having everything built on the same entry point to the micro-kernel really simplifies the design of a lot of different components. We have someone doing the design on direct to memcached replication for instance (we will have UDF as well, but this will simplify cache coherency and not require you to modify any SQL to make it work).

Materialized Views are pretty cool, materializing a view though pretty much make you want to cry.

BTW It was just pointed out to me that Justin had some of his own thoughts about this.

Official Drizzle Blog: OSCON 2009 – Drizzle and Gearman

Wed, 15 Jul 2009 17:55:50 +0000

Lots of activity at OSCON next week where there will be several presentations, tutorials, BOF’s and a Drizzle and Gearman booth in the conference hall. Hope to see you there!

PgDay San Jose 2009: pgGearman: A distributed worker queue for PostgreSQL – Sunday 4:25 p.m.

Gearman – Build Your Own Distributed Platform in 3 Hours – Tuesday 8:30 a.m.

Gearman – Bringing the Power of Map/Reduce to Everyday Applications – Wed 11:35 a.m.

Gearman BOF – Wed 7:00 p.m.

Drizzle: Status, Principles, and Ecosystem Thursday 10:45 a.m.

Drizzle BOF – Thursday 7:00 p.m.

Marcus Eriksson: Drizzle JDBC 0.4 - Rewrite batch handler

noreply@blogger.com (Marcus Eriksson) — Tue, 14 Jul 2009 21:00:13 +0000

I just pushed up Drizzle JDBC 0.4, which, most importantly, contains a rewrite batch handler (I guess much like the one Mark Matthews announced for Connector/J a couple of months ago). It gives the expected 10x+ batch insert improvements, on my laptop I can insert 1M "simple" rows in under 20 seconds.

It is written as a plugin, so you need to do this to your Connection to enable it:

if(connection.isWrapperFor(DrizzleConnection.class)) {
  DrizzleConnection dc = connection.unwrap(DrizzleConnection.class);
  dc.setBatchQueryHandlerFactory(new RewriteParameterizedBatchHandlerFactory());
}

(Yes, it will be possible to configure plugins via the connection string and by setting properties). Please, try it out and report any bugs you find!

I also wrote a Scala version of the plugin, it is not bundled with the 0.4 release though - if you are interrested, it is on Launchpad. Scala is awesome, hoping to do some more Scala development in the near future.

This is the changelog for 0.4:

Findbug fixes

Internal refactorings

Make sure all tests pass against both MySQL and Drizzle

Batch handler API cleanups, details here.

Fix read of 16bit word (by Trond Norbye)

A query rewrite batch handler

Download 0.4 here.

Brian Aker: Drizzle in the News...

Fri, 10 Jul 2009 17:56:12 +0000

I've been too busy to blog lately. On our plate right now for Drizzle is pretty full. We have the new event system pushed for Drizzle, the OOP fixes that allow us to use C++ in the codebase, the refactoring on the Storage Engine interface... and on top of this all of the great work being done by Google Summer of Code Students!

Here are some blog posts by others :)

Want to know a little more about our build system?
http://blog.drizzle.org/2009/07/09/drizzle-build-system/

How about a write up from one of the engine writers on changes he is making for the new engine interface:
http://torum.net/2009/07/changes-to-the-drizzle-storage-subsystem/

We keep adding more tools to our regression system, here is a load emulator:
http://torum.net/2009/07/introducing-skyload/

A mention of Drizzle from RedMonk:
http://redmonk.com/sogrady/2009/07/02/lamp-of-the-clouds/

Finally, a blog on moving Wordpress to Drizzle:
http://bethesignal.org/blog/2009/07/08/wordpress-and-drizzle/

Getting Started with Drizzle and PHP:
http://devzone.zend.com/article/4793-Getting-Started-with-Drizzle-and-PHP

Monty Taylor: pandora-build - make your software compile as pedantically as ours!

Fri, 10 Jul 2009 16:57:14 +0000

I've been working on cleanups to the Drizzle autoconf and automake system. Actually, to be fair, I've been doing this work on Drizzle, Gearmand, libdrizzle and libmemcached.

In all of those projects, we have the same set of build ideas:

Warnings == Errors
As many warnings as we can enable
Lots of people will be building directly from bzr

As it turns out, I'm pretty lazy, and I got tired of remember to port the latest m4 macro fix that we made in one project across to the other three. O, hai - code reuse!

So I did some refactoring and split the several macros and large portions of the configure.ac files out into a set of m4 macro files. These now live in their own project, which you can grab from bzr via:

bzr branch lp:pandora-build

Although each of the macros is useful by itself, the fun part is that you can just put:

PANDORA_CANONICAL_TARGET

at the top of your configure.ac file right after AC_INIT and the normal AC_CONFIG macros. It will set up libtool, automake, run some standard tests, and turn on assloads of warnings, etc.. essentially setting your build env up to be just like we're running for all of our projects. There are a few parameters that you can pass to control some optional features (drizzle, for instance, is the only one of our projects that uses gnulib)

To add the macros to your project, you can do one of two things. First, you can just copy all of the m4 files in to your m4 dir, but that will make Robert Collins sad. The better way is that you can do:

autoreconf

./configure

make install

in the pandora-build tree, which will add all of these macros to /usr/share/aclocal.

I welcome folks to start using them in their projects and to give me feedback. I'm sure feedback #1 will be "better documentation"...

Official Drizzle Blog: Drizzle Build System

Fri, 10 Jul 2009 03:34:09 +0000

I wanted to follow up from Brian’s blog last month where he was talking about how we do our testing in the Drizzle project. Brian mentioned our build system and I thought I would expand on that.

Right now in Drizzle we use both Hudson and Buildbot for automated and continuous builds. Our dashboards can be seen here and here. We use both email and IRC for notifications when builds fail. While the dashboards are not as Green as we would like right now, we are only a few fixes away from having it generally back on track and our goal is to keep it on track as we move forward!

We have a wide variety of build clients as you can see and run our build process from both the trunk and staging code base. All changes first go through staging for verification which means making sure it still builds (warnings are always treated as errors), passes all unit tests (no disabling of tests) and has not regressed in our sysbench numbers. After code is promoted to trunk we also run updates for LCOV, SLOC and Doxygen. Trond

also set up regular runs for OpenGrok.

Jay

put together a great automation system to run sysbench, doxygen, lcov, sloc as well as the ability to run Valgrind tools as part of the sysbench run. Eric and Monty also extended this to use Gearman so we can submit jobs on demand to various machines if we want to run jobs out of cycle.

Thanks to Michael Shadle for putting together our fancy graphs on the wiki and also donating a few machines for buildbot clients.

Always looking for more clients so if you have a machine you can provide, let us know.

Any suggestions for improvement?

Toru Maesaka: Notes on changes made to the Drizzle Storage Subsystem

Thu, 09 Jul 2009 09:26:43 +0000

Yesterday I merged the BlitzDB tree with Drizzle’s trunk for the first time in a long time (yeah…) and discovered some interesting changes made to the storage subsystem while I was away.

Previously all functions that caused an action to the storage engine was a member of the handler class but various things like table creation and transaction related functions have now moved to the StorageEngine class. These changes are somewhat drastic but makes good sense for Drizzle to grow further since it makes the subsystem easier to understand and frees Drizzle from the interface design that was strongly affected by MyISAM. For those that are interested, the StorageEngine class is located in “drizzled/plugin/storage_engine.h”.

For me it was pretty easy to update BlitzDB to work with the new subsystem since I don’t have anything special in the engine that required me to use my brain. I only had to move bas_ext(), table creation and rename functions over to the StorageEngine class and adjust it to the new interface:

int createTableImpl(Session *session, const char *table_name, 
                    Table *table_arg, HA_CREATE_INFO *ha_create_info); 
 
int renameTableImpl(Session *session, const char *from, const char *to);

For a real example, I recommend comparing the old InnobaseEngine class declaration with the updated one. As for where this redesign is going, this is the answer I got on the Drizzle channel from Stewart who did the actual work for all this.

stewart: tmaesaka: the basic idea is that handler becomes a cursor. the StorageEngine is for actions on the engine.
stewart: tmaesaka: and handler is a cursor on a table.

Something to keep in mind if you’re thinking about creating or porting a storage engine to Drizzle

Toru Maesaka: Introducing skyload: a libdrizzle based load emulator

Tue, 07 Jul 2009 02:38:00 +0000

Today, I would like to introduce “skyload“, a small project that I’ve been working on for the last couple of weeks. In brief, skyload is a libdrizzle based load emulation tool that is capable of running concurrent load tests against database instances that can speak Drizzle (and/or) the MySQL protocol.

http://code.google.com/p/skyload/

Something I’d like to emphasize here is that, skyload is not a replacement for mysqlslap or drizzleslap since it only provides a subset of what they can do. As I’ve stated on the project description, skyload is designed to do a good job at this subset of tasks by giving you more control over how you emulate the load in an intuitive way. For instructions on installing skyload and quickly getting up to speed, take a look at the following URL:

http://code.google.com/p/skyload/wiki/Tutorial_en

As you will see, the first release only contains bare minimum specifications (only INSERT load emulation). The next step I want to take is to discuss features that other storage engine developers would actually find useful. This is because I started writing skyload for primarily myself and other storage engine developers (more on this next).

Original Intentions

I originally began writing skyload for BlitzDB development since I wanted to see the concurrent insertion performance of Tokyo Cabinet based row storage mechanism that I wrote. I first tried benchmarking the write performance with drizzleslap but it turned out that drizzleslap’s original code (inherited from MySQL 6.0) is rather buggy and segfaulted quite easily (I’m planning on contributing a fix for this).

So I gave up on drizzleslap for the time being and started looking at the sysbench port for Drizzle that Monty Taylor has been working on:

sysbench: now with Drizzle

Sysbench for Drizzle is a lovely piece of software but it couldn’t quite provide what I was looking for (concurrent insertion benchmark). After having a quick conversation with Monty about my requirements on the Drizzle IRC channel, I decided to write a libdrizzle based benchmark tool that can be used for both Drizzle and MySQL.

Future Plans

I don’t want to reinvent existing software that works (or those that can be fixed). The project positioning that I’m hoping for skyload is a good mix between (mysql|drizzle)slap and sysbench. Hopefully it will be useful to folks that works on Drizzle and MySQL related projects.

I’m totally open for ideas, patches, and contributors. If this project had caught your attention, please don’t hesitate to ping me or the Drizzle community

I haven’t setup a mailing list since I don’t see the need for it yet so if you’d like to share your thoughts I think either the Drizzle mailing list or IRC (#drizzle @ irc.freenode.net) is the quickest way for me to get back to you.

Happy Hacking!

Official Drizzle Blog: Drizzle source tarball 1088 has been released

Mon, 06 Jul 2009 18:01:15 +0000

Drizzle source tarball based on build 1088 has been released. The change log can be viewed here. While continuing to do a lot of great code clean up, this release also features work by Padraig who has got a great start working on extracting I_S out into plugins.

We also now support protobuf 2.1 except when using gcc 4.4 or if you are running on Solaris. Monty is working on getting a patch into protobuf 2.1.1 for the gcc 4.4 issue. He is also working closely with the SunStudio engineers to get that issue resolved as well.

-Lee

Ronald Bradford - 42SQL: An important Drizzle/MySQL difference

Sat, 04 Jul 2009 04:01:11 +0000

There are many features that are similar in MySQL and Drizzle. There are also many that are not.

I’ve previously discussed topics like Datatypes and tables, SQL_MODE and SHOW.

A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.

During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.

Here is the problem.

mysql> create table t1(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t2(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3(c1 int unsigned not null auto_increment primary key, c2 varchar(256) not null, unique key (c2)) engine=innodb default charset utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'charset latin1' at line 1
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Only a maximum of 191 is now possible.

drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(191) not null, unique key (c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(192) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Stephen O'Grady: Open Source and the Cloud: Where’s the LAMP?

Fri, 03 Jul 2009 00:16:34 +0000

“My challenge to everyone competing with Amazon, Google and Microsoft is to remember that you’re competing with Amazon, Google and Microsoft. These are strong technology companies, and if you’re going to compete with them, open source is the only way to do that. Otherwise, you have no leverage.” – Matt Mullenweg

Let’s accept up front that the next Amazon, Google or Microsoft is not going to be able to purchase hardware as cheaply as the last Amazon, Google and Microsoft. That’s strike one. Bandwidth is also going to be a bit more dear. Strike two. Consider the challenges of managing all of the above, and that’s strike three.

But before we call them – and count them – out, let’s consider for a moment the history of the software industry. Before the cloud, before software as a service there was this weird little trend called open source. This bizarre practice involved opening (read: giving away) your source code (read: your software) so that anyone, your competitors included, could use it. For free.

Odd as this might have seemed at the time, of course, open source allowed the small to compete with the big by leveraging rather than submitting to their weaknesses. It’s sometimes difficult to remember in this Google-obsessed age, but during Windows 95’s heyday, it was natural to conclude that Microsoft was the once and future provider of all the technology that one might reasonably require. Of course we’d once thought the same about IBM, but this was different. Microsoft was different.

My how things change. And stay the same, to be fair, as Microsoft hasn’t exactly gone the way of SGI. But anyone who’s watched the Microsoft business over the past decade or so will tell you that open source has been a disruptive influence on the firm, top to bottom. As if it wasn’t enough that monopolies like the browser and operating system markets were threatened by open source alternatives, its biggest and most terrifying competitors were building their own businesses on software they didn’t have to develop. Not that Microsoft’s been alone in feeling the corrosive disruption of free software, of course; it could and has been argued, in fact, that the biggest single reason that Sun is about to be subsumed into Oracle is the LAMP stack.

David versus Goliath, indeed.

To explore the specifics of how open source might impact the cloud, let’s indulge in a bit of Q&A.

Q: Before we begin, do you have anything to disclose?
A: Yes indeed. Folks with relevant technologies like Canonical, Cloudera, Convirture, Dell, IBM, Reductive Labs, Red Hat, Microsoft, Sun and so on are RedMonk customers, while we ourselves are customers of providers of Amazon and Google.

Q: To continue the above: could history repeat itself? Could David beat Goliath – again – in the cloud space, on the backs of free software?
A: Frankly, I doubt it.

Free software is not, by itself, enough to overcome the aforementioned economy of scale advantages enjoyed by the Amazon’s, Google’s, and Microsoft’s of the world, let alone the larger, enterprise focused systems players like HP, IBM, and Oracle (why not you too, Cisco?). But that, to me, is not the interesting question.

Q: What is the interesting question, then?
A: What we should be asking is not whether free software can replace Amazon et al, but whether or not it can power a viable cloud alternative. An alternative sufficiently viable to keep the big guys honest and prevent lockin. On the answer to that question, to me, hinges nothing less than the future of the cloud market.

Q: Why is that question so important?
A: First, there’s the aforementioned question of lockin. Neither customers nor the governments that tax them can be trusted to stave off damaging monopolies, in my opinion. History demonstrates conclusively that IT staffs, necessarily focused on the present, will happily sacrifice the future for the sake of Getting Things Done today. Equally clear is the fact that governments, when finally awakened to anticompetitive threats, generally do too little, too late. Meaning that the best hope for an open and vibrant playing field – i.e. a market of cloud providers not intent on locking you in at the first opportunity – in future is competition for the existing players.

Besides their monopoly-resistant properties, open source cloud software could play an important role in the rise of so-called private clouds – cloud infrastructures that are run on-premise. Whether one agrees or disagrees with the concept of private clouds or not, they’re coming. For compliance, privacy, uptime and a host of other reasons. Given that one can’t replicate the platforms of an Amazon, a Google or a Microsoft internally, it would seem to make public to private or vice versa transitions challenging in the extreme.

(Re)enter open source. Though some might point to interoperability and standards conversations as the most promising candidates for ensuring adequate competition in the cloud space, my experience in other standards arenas leads me to assign greater value to reference implementations of said standards. Open source implementations, more specifically, because at the end of the day the entire interoperability and standards discussion is about ensuring a level playing field. Throw in the fact that open source could potentially allow replication of the public cloud stack privately and you might yet see enterprises and governments pushing for open source.

Q: Are the benefits for open source cloud offerings strongest within the customer, then?
A: Not at all. Lost in discussion of cloud development has been the fact that the development platforms and targets are changing, and quickly. The level of interoperability that even unwieldy standards like J2EE offer is generally absent in the cloud. Platform as a service (PaaS) customers are writing applications, typically, to a completely proprietary abstraction layer, whether it’s offering by Google, Salesforce or someone else. And even Infrastructure as a Service (IaaS) customers deploying to enterprise standard platforms like RHEL will find their deployments regrettably unique, be that in the way that storage is accessed or the instances themselves are managed.

As Matt points out above, then, open source is going to be the primary mechanism with which startups compete, in my view. In the two primary styles of cloud implementations, IaaS and PaaS – what I’ve previously termed instance and fabric – we’ve seen dramatically different economic opportunities. With IaaS, the opportunities for developers and vendors has typically been to abstract the infrastructure via management, clustering and provisioning type applications. These opportunities are, frankly, likely to dwindle as Amazon increasingly offers these services itself. Within PaaS ecosystems such as Google or Salesforce, there is even less opportunity, in that the fabric is responsible for many of the tasks currently being serviced by vendors operating in the IaaS ecosystem. Most cloud vendors are building on Google or Salesforce rather than around them.

In other words, it’s a competitive market, and it’s only going to get more competitive as the bigger systems players rapidly pivot and reposition their wares for use in the cloud.

So how do you compete? Realistically, unless your company letterhead reads Google, IBM, Microsoft, Oracle or Salesforce, you’re probably going to have a hard time convincing even medium size cloud customers to write to something other than Amazon.

Unless, of course, you can develop a credible alternative that is popular enough to assuage concerns about longer term viability. Which pretty much means you’re going the open source route, in my view. Thus it is that the combination of open source and cloud is even more important for developers than it is for customers.

Q: Are any developers seeing things in those terms?
A: Sure. Take Cloudera, who’s offering a suite of commercial services around the open source Hadoop platform. Or the folks from Reasonably Smart – recently acquired by the folks from Joyent – who offer up the code from their Git and Javascript based PaaS layer with the following explanation: “we see [open source] as the only real way to make our platform truly attractive. Other Platform-as-a-Service providers may state a desire to be open, we’ve been that way from day one.”

Elsewhere, Red Hat is throwing a virtual conference strictly on the topic of open source cloud computing.

Q: Is the cloud a natural ally for open source?
A: Not at all. One of the godfathers of the free software movement, Richard Stallman, has called cloud computing “stupidity.” Others have argued that software deployed to the cloud obsoletes open source licenses, undermining the point of the software itself, with some even going so far as to call the loophole that permits this “a cancer.”

Q: Is there evidence to support these concerns?
A: Not much that I can see, candidly. Though the thinking is sound, in practice there are a great many healthy open source projects that are primarily deployed in network settings. From Hadoop to WordPress, well managed open source projects are succeeding without resorting to the more severe restrictions of the AGPL.

Q: Besides customers like enterprises and governments, who might most benefit from an open source cloud stack?
A: In a word: hosts. Given the stark economic reality that the major provider cloud providers’s economic advantages will expand with the growth they’re currently experiencing, what are smaller providers to do? Embracing open source seems to be the clearest response. Much as smaller and medium sized hosts worldwide today run Debian, Fedora, CentOS or Ubuntu as a means of minimizing their expense, so too are tomorrow’s would be cloud providers likely to embrace open cloud stacks in an effort to remain competitive in the burgeoning cloud market.

Besides, it’s not clear how big a cloud market will be left when the big guys are finished carving it up. If you assume (as you probably should) that IBM customers are more than likely to leverage an IBM cloud, HP customers an HP cloud and so on, you’ve already lost an important portion of the Global 100. Then consider the entrenched strength of the category’s market pioneer in Amazon and the relative strengths of communities that the likes of Google and Salesforce.com can sell into, and the addressable market is dwindling rapidly.

LAMP, with its flexibility, simplicity and perhaps most importantly – lack of upfront licensing costs – fueled an explosion in the hosting services market once upon a time. It’s entirely possible that a similarly open source cloud stack could do the same, particularly since far more software is delivered via the network than when the hosting industry first expanded.

Q: What is this cloud LAMP stack going to look like?
A: What we’re going to see, what we’re beginning to see, I think, is a loose coalition or confederation of projects and vendors that will together comprise an increasingly viable top to bottom alternative to some of the cloud providers today. We’re clearly not going to see an Amazon or a Google spring forth, complete, overnight, but the fact is from management to virtualization to operating systems to cloud provisioning the open source alternatives to the current proprietary cloud stacks are more credible by the day.

Q: Which projects and vendors will be part of this “coalition?”
A: Ultimately, there will have to be a variety of participants with varying aims and interests, but they’re probably going to look a lot like the recent Eucalyptus/Ubuntu partnership. Besides Linux (all flavors) and Eucalyptus, examples of projects I would expect to see considered for various roles in an open source cloud stack would be things like ConVirt, Drizzle, Hadoop, Puppet, Reasonably Smart and so on. Which is not to mention critical enabling technologies like KVM or potential API candidates like the one GoGrid made available under a CC license.

As you can tell, it’s far too early to begin casting for the new acronym, but it’s clear to me that there are going to be options for those that wish to pursue open source cloud computing. Which should be obvious, since most of the existing clouds are built on open source.

Q: What about timeframes: what are your expectations in terms of when the open source cloud will arrive?
A: It’s far too early to tell. What I would say instead is that the clock is ticking, and that the network effects favor the incumbents, so if I were an open source provider with cloud ambitions, I’d be ramping up the partnership and alliance conversations as quickly as possible.

If you happen to be one such developer or vendor, drop us a line and we’ll do what we can to help connect you to similarly interested parties.

Eric Day: libdrizzle and PHP Extension Released

Thu, 02 Jul 2009 07:14:24 +0000

Version 0.4 of libdrizzle has been released. This was mostly a maintenance release with build system changes and small bug fixes. This is the client and protocol library for Drizzle and MySQL that provides both client and server interfaces.

Version 0.4.1 of the Drizzle PHP Extension has also been released. James Luedke has moved development and releases of the extension into PECL, and has also fixed a number of bugs, extended the interface, and worked with the PHP/PECL developers to get the extension up to the proper PHP coding standards. Thanks James!

Ronald Bradford - 42SQL: Benchmarking Drizzle with MyBench(DBD::drizzle)

Wed, 01 Jul 2009 17:14:52 +0000

With thanks to Patrick Galbraith and his DBD::drizzle 0.200 I am now able to test client benchmarks side by side with MySQL and Drizzle.

For simple benchmarking with clients, generally when I have little time, I use a simple Perl framework mybench. I was able to change just the connection string and run tests.

The diff of my two scripts where:

---
> my $user      = $opt{u} || "appuser";
> my $pass      = $opt{p} || "password";
> my $port      = $opt{P} || 3306;
> my $dsn       = "DBI:mysql:$db:$host;port=$port";
---
 my $user      = $opt{u} || "root";
 my $pass      = $opt{p} || "";
 my $port      = $opt{P} || 4427;
 my $dsn       = "DBI:drizzle:$db:$host;port=$port";
---

It’s too early to tell what improvement Drizzle will make. Just running my first test with single and multi thread tests shows an improvement in all figures in Drizzle via MySQL, however I will need to run this on various different versions of MySQL including the latest 5.0 to confirm.

Brian Aker: Drizzle, Rethinking MySQL for the Web (Video from OSBridge)

Tue, 30 Jun 2009 16:21:31 +0000

Here is the link to the video of the talk I gave at OSBridge ->

http://blip.tv/file/2296093

It is the standard "this is Drizzle Talk".

Official Drizzle Blog: Drizzle and Gearman in Boston next week

Tue, 30 Jun 2009 16:21:23 +0000

Eric Day will be heading back to his home state (Maine) next week for a visit, and while there both Eric and Patrick Galbraith will be talking at the Boston MySQL Meetup Group on Monday night about Drizzle, Gearman, and how to combine the two with projects like Narada. If you are in the Boston area, be sure to check it out!

Eric Day: Drizzle and Gearman in Boston Next Week

Tue, 30 Jun 2009 08:46:09 +0000

I’ll be heading back to my home state (Maine) this week for a visit, and while I’m back there Patrick Galbraith and I will be talking at the Boston MySQL Meetup Group on Monday night about Drizzle, Gearman, and how to combine the two with projects like Narada. If you are in the Boston area, be sure to check it out!

Eric Day: Why You Won’t See a Drizzle Proxy

Mon, 29 Jun 2009 20:21:52 +0000

I’ve been following the excellent work that Jan, Kay, and others have been doing with MySQL Proxy, it has really matured into a great piece of software. I talked to Jan at the MySQL UC and toyed with the idea of integrating libdrizzle into MySQL Proxy. I’ve also been asked by a number of folks when a Drizzle Proxy project will be started and if it will be as feature rich as MySQL Proxy. For a while I just said “Someday, I just don’t have the time.” Lately though I am hoping we never have a Drizzle Proxy project.

Let me explain.

One of the fundamental ideas in software engineering is code reuse through libraries or modules. Rather than create a Drizzle Proxy project, why not add a proxy module into the Drizzle server? This way, at any point during the query execution path, you could toss the query to the proxy module to deal with, and the main execution engine would be done. You could of course run the Drizzle server in a “proxy only” mode where new queries may only be parsed and then a post-parsing module determines where and how that query is proxied. Post proxy hooks will be needed as well for result processing. Functionally, it’s the same thing as the proxy, but without having to reinvent the components needed in the proxy. (Just as a side note, I understand this may not have been an option for the MySQL proxy folks).

So, to be clear, I still want to have proxy functionality, just not as an independent project.

Even with a proxy module inside of the server, I’d like to address some of the reasons proxies are created and used. These are not necessarily specific to a database proxies, many of these reasons apply to other server types as well. In the case of a database proxy, especially with Drizzle, I would like to address the list of reasons below in a different way. Why? In most architectures, I see a proxy server as a fix for a shortcoming with another component, possibly in the client, server, or maybe even in the application data model. It also introduces latency and another failure point that may not be necessary. The less code and machines your application has to run through, the better. Don’t get me wrong, there are reasons to use proxies, but sometimes they are used as a hack.

Query processing and rewriting - In Drizzle we plan to add query rewrite plugin hooks, both pre-parser and post-parser. At some point we want to add pluggable parser support and clean up the abstract syntax tree. These plugins would enable rewriting of queries at a few different levels, both with the raw strings or with rearranging the syntax tree before the optimizer takes over.
Query multi-cast, data partitioning, result merging - In my opinion, this may could probably be done at the client library layer or through another system such as Gearman. If pushing that logic into the client is not an option, you could still accomplish this through the proxy module I mentioned above, possibly running the server in a mixed-mode (some queries answered locally, some proxied).
Connection Pooling/Concentration - People often confuse these two terms. Pooling is the re-use of connection on a client side. This should be pushed to client APIs whenever possible. When this is not possible, you need to use a generic TCP proxy or database proxy, but these should only be run locally (not on a separate machine). Concentration is a piece of software that acts as a connection multiplexer. It takes multiple client side connections and allows them to map onto a single connection to the server. This is usually because the server does not have an efficient threading or file descriptor handling model to withstand thousands of connections. It’s not always an option to re-architect a server to handle this, but it should be preferred over creating another layer to do the concentration for you. In Drizzle, this is one thing I have a particular interest in. It involves improving or re-writing the pool-of-threads scheduler and making the execution engine more stateful so it can yield a thread when it knows it will block.
Sharding, HA/failover - Again, something I think belongs in the client library, and is part of the new Drizzle protocol. I’ll be adding support into libdrizzle to manage sharding and connection failover shortly.
Debugging layer - At some point we should be adding probes into the server where output can be piped to a module of your choice. For example, you can register for a set of events and have a module send those out into a Gearman network for processing and debugging. This will give you the flexibility to process probe output however you want and does not introduce another layer just for debugging.

These are things I plan to work on at some point or would like to help someone else work on inside Drizzle. Also, these are my own thoughts and may not be shared by fellow Drizzle developers. Treat this as an invitation for discussion. :)

Official Drizzle Blog: Drizzle source tarball 1063 has been released

Mon, 22 Jun 2009 17:17:33 +0000

Drizzle source tarball based on build 1063 has now been released. The change log can be viewed at https://launchpad.net/drizzle/trunk/aloha.

Baron Schwartz: Recap of Southeast Linux Fest 2009

Sun, 21 Jun 2009 13:48:49 +0000

Last weekend, my brother and I attended SELF 2009. A few thoughts on it:

The mixture of sessions was interesting. There were some really good ones. I think the best session I attended was an OpenSolaris/NetBeans/Glassfish/Virtualbox/ZFS session, given by a Sun employee. He was an excellent presenter, and really showed off the strengths of the technologies in a nice way. He started up enough VMs to make his OpenSolaris laptop chew into swap, and I thought it was fun to see how it dealt with that. I’ve heard Solaris and OpenSolaris do a lot better at avoiding and managing swapping than GNU/Linux, but I couldn’t make any opinion from watching. I did think it was odd to have this session at a “Linux” (yes, they left off the GNU) conference. But I thought the session was a good addition to the conference. In other sessions, and in the hallways and expo, there was a lot more slant towards open-source software and gadgetry in general than there was towards GNU/Linux. The sessions that were about Linux or GNU/Linux were top-heavy towards topics like educational initiatives.

The Free Software Foundation had a booth in the expo hall. It was funny that they didn’t boycott the event, because I know RMS won’t speak at so-called “Linux User Groups” and insists they be called “GNU/Linux User Groups.” I guess the FSF is not unified behind that banner. Regardless, I used the opportunity to renew my membership perpetually. I’m so lazy that I need something like this to stay involved!

The expo hall was dominated by Red Hat, Fedora, and SUSE; PostgreSQL was there, but not MySQL. There was a good variety and number of vendors. It was great to see the healthy support of the event, which was free, by the way.

Clemson, SC is not easy to get to, and while the Clemson campus was attractive and functioned fine, it’s nothing you can’t find elsewhere. I ended up driving over 9 hours to get to it. I’d have preferred the technology triangle, which if nothing else is close to major airports, bus and train stops, and Red Hat.

Richard Hipp talked about the great fsync() bug, a similar talk to the one he gave at the first OpenSQL Camp. Someone asked about Tokyo Cabinet and he responded that he hasn’t found any fsync() calls in its source code. *cough* Something worth thinking about for on-disk usage (I haven’t looked at its source much myself). TC can also be used in-memory-only, and a while back I suggested that usage of it for Drizzle to replace the Memory engine; I don’t know what became of that.

Sessions of interest at MySQL Conference and Expo 2009 I haven
PostgreSQL Conference East 2009, Day Three As I said
Sessions of interest at MySQL Camp 2009 I wrote pr

Related posts brought to you by Yet Another Related Posts Plugin.

Michael Shadle: WordPress on Drizzle - Beaten To the Punch

Wed, 17 Jun 2009 23:52:28 +0000

Looks like Jeff Waugh actually beat me to it.

Haven't seen the code... but he's done it and sounds like he's done a somewhat thorough job.

Sadly I learned this from Brian's presentation on Drizzle at OS Bridge. Doh.

Official Drizzle Blog: Article on Drizzle in Linux Magazine

Tue, 16 Jun 2009 16:37:03 +0000

Great article by Jeremy Zawodny in Linux Magazine. Thanks Jeremy!

Pythian Group: Eric Day Speaks About Gearman and Drizzle July 6, 2009 in Boston

Tue, 16 Jun 2009 13:40:18 +0000

The July meeting of the Boston MySQL User Group will feature Eric Day, a prominent Drizzle developer, talking about Drizzle and Gearman:

In this talk we will discuss two growing technologies: Drizzle and Gearman.

We will explain what the Drizzle project is, what we aim to accomplish, and an overview of where we are at. We will also be introducing the fundamentals of how to leverage Gearman, an open-source, distributed job queuing system. Gearman’s generic design allows it to be used as a building block for almost any use - from speeding up your website to building your own Map/Reduce cluster. We will tie Drizzle and Gearman together and demonstrate how they work in a custom Search Engine application.

————————

Here is the URL for MIT’s Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL’s for the parking lots (free and open to the public after 3 pm):
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go

Free pizza and soda will be served, so please RSVP accurately.

To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.

For more information, see: http://mysql.meetup.com/137/

Toru Maesaka: Storage Engine Dev Journal #3 : Supporting variable width tables

Tue, 16 Jun 2009 12:14:52 +0000

Something I’ve added to BlitzDB recently that was pretty high on my todo list is support for variable width tables. So what is a variable width table? it is a table that contains columns that can vary in size, namely BLOB and TEXT types.

Going back to the basics, when a new row is to be written, a storage engine is given a pointer to the row data in MySQL format that it must somehow store for later lookup/retrieval. By meaning “somehow”, the storage engine is given the freedom to do whatever it likes with the row.

Writing a row for a fixed length table (a table with columns that are always the same size) is deadly easy. A storage engine can choose to not temper with the row and simply write or copy the data to it’s storage mechanism. This is because the storage engine is given a row that contains all the data. Rows for variable width tables however, are treated differently since things aren’t as simple (it’s variable!).

The difference is that columns for BLOB and TEXT types are represented by two parts inside a MySQL/Drizzle row:

length of the data
pointer to the actual data

This is simple to understand since we need to know the size of the data to copy it.

Minor Complication

The minor complication as you would expect here is that you can’t directly write the provided row to your engine like you can with fixed length tables. The data that you want to copy/write exists elsewhere (hence the pointer) so directly writing the row has no meaning (the data would have disappeared by your next access to that row). You need to make sure that the actual data for BLOB/TEXT column(s) are arranged appropriately on your engine’s row buffer and written out to it’s storage mechanism.

This process is commonly referred to as row packing (converting to your engine format) and unpacking (convert back to MySQL format). So how is this done? it’s actually pretty simple!

The solution is actually simple

As much as it sounds like a bother to support variable length rows, it’s actually not that bad. First you need to understand what a MySQL row looks like internally.

A MySQL row begins with a bitset that represents which fields are NULL. The length of this data obviously depends on the number of NULLable columns you have but this is easy to handle with Drizzle since we’re given all the relevant information by the TableShare object (same goes for MySQL from a different object).

After this data comes the actual column data in the order that appears in your CREATE TABLE statement. What you need to do to get packing working with this row is the not-so-obvious part that you really need an example to look at. Fortunately Tweeting about this attracted Brian’s attention which helped me move forward.

Loop the fields!

So, let’s take row insertion to a variable width table as an example. Imagine this table:

CREATE TABLE t1 (
  id int PRIMARY KEY NOT NULL,
  description text,
  arbitrary_data blob
) engine=your_engine;

and let’s imagine that we need to process this query:

INSERT INTO t1 VALUES (1, "hello world", "blobbbbb");

Now, the storage engine needs to “pack” the data for each column into it’s buffer in the write_row() function. Conveniently, Drizzle/MySQL provides a pack() function for it’s column types (fields) that will do the data packing for you. That is, you do not have to inspect the provided row for pointers to the actual data and do the packing/copying yourself.

How? well, the table object (which is visible from your engine) conveniently holds a list of fields in the appropriate order. The actual pack() function is a member of these fields so you just need to call it as you loop over the list:

/* make sure row_buffer has enough memory */
unsigned char *pos = row_buffer;
 
/* copy NULL bits, "table->s" is the TableShare object */
memcpy(pos, row, table->s->null_bytes);
pos += table->s->null_bytes;
 
/* "row" is the MySQL formatted row given by the core */
for (Field **field = table->field; *field; field++) {
  if (!((*field)->is_null()))
    pos = (*field)->pack(pos, row + (*field)->offset(row));
}

The above code snippet will populate “row_buffer” with the actual data that you want to write to your storage mechanism. You do not have to forward the “pos” pointer because pack() returns a pointer at the end of where it had worked in the buffer (think Pascal Strings). This is precisely why we created the pos pointer, to avoid row_buffer from being forwarded.

For the opposite situation (when retrieving a row), an unpack() function is provided for each field so you just need to take advantage of it like we did with the pack() snippet above.

Little bit more on fields

The actual pack() function that gets called depends on the type of column since the Field class is an abstract base class for the sub classes that actually represents column types inside Drizzle/MySQL. If you want to know what a pack() function looks like for a BLOB type, grep for “Field_blob” in the source tree and there will be a pack() member function for it.

The code layout for field subsystem in MySQL is rather difficult to comprehend since everything is crammed in “sql/field.c” and “sql/field.h” files (at least as of 5.4). So, if you want to get a good grasp of how things are architectured, you should take a look at Drizzle. Field subclasses are located individually in the “drizzled/field/” directory and the base class is located in “drizzled/field.h”.

So, that’s about it! Hopefully this information will help other engine developers when they come across a need to support variable width tables

Eric Day: Gearman Pluggable Protocol

Fri, 12 Jun 2009 22:30:08 +0000

I just finished adding pluggable protocol support to the Gearman job server, this will enable even more methods of submitting jobs into Gearman. If all the various Gearman APIs, MySQL UDFs, and Drizzle UDFs are not enough, it’s now fairly easy to write a module that takes over the socket I/O and parsing hooks to map any protocol into the job server. As an example module, I added basic HTTP protocol support:

> gearmand -r http &
[1] 29911
> ./examples/reverse_worker > /dev/null &
[2] 29928
> nc localhost 8080
POST /reverse HTTP/1.1
Content-Length: 12

Hello World!

HTTP/1.0 200 OK
X-Gearman-Job-Handle: H:lap:1
Content-Length: 12
Server: Gearman/0.8

!dlroW olleH

I’ve added a few headers for setting things like background, priority, and unique key. For example, if you want to run the above job in the background:

POST /reverse HTTP/1.1
Content-Length: 12
X-Gearman-Background: true

Hello World!

HTTP/1.0 200 OK
X-Gearman-Job-Handle: H:lap:2
Content-Length: 0
Server: Gearman/0.8

So what protocols are we looking at? HTTP and memcached were on the top of the list, but I’m guessing other folks may have better ideas or perhaps could use it for custom integration with their existing infrastructure. This is now tested in my development branch and will be pushed to trunk in the next couple days. If anyone is interested in working on the HTTP module, please hack away, patches are welcome! :) It may be interesting to map a worker interface in as well depending on headers, along with better support for client requests and HTTP error codes.

Here is another quick example that shows how this can be useful. With the job server we started above still running, use the gearman command line client/worker to start up a worker that can do the function ‘proto’ and responds with dumping the file PROTOCOL (use any other file you have around):

> gearman -w -f proto cat PROTOCOL

If you’ve note used this command line tool before, -w makes the process act like a worker, -f function specifies which function the worker should register as, and everything after is executed every time a job is run (it fork()s, remaps stdin/out to pass the payload/read result, and then exec()s).

Now point your browser to http://localhost:8080/proto and you will see the contents of the file (assuming you are running all this on your local machine). This may not seem too useful, but now imagine more complex workers running on a distributed cluster. We now have a simple web server with distributed CGI scripts! :)

Pythian Group: Log Buffer #150

Fri, 12 Jun 2009 16:11:49 +0000

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Giuseppe Maxia explains some details of MySQL’s New Release Model. Andrew Morgan announces a New MySQL Cluster Maintenance Release. Aleksandr Kuzminsky of the MySQL Performance Blog releases build16 of the Percona MySQL binaries (versions 5.0.77 and 5.0.82), which adds some 5.4 features and fixes some bugs.

Darran Cassar, the MySQL Preacher, has created a package for Security Roles and Password Expiry on MySQL

. And for a future MySQL release, look for Two New Status Variable Patches, for query invalidation count and a last received datetime for replication heartbeat. These patches were contributed by MySQL Support Team member Andrew Hutchins.

Dave Beulke points out a new feature in DB2 9.7 — DB2 Compatible with Oracle.

To prepare for a future SQL Server release where CREATE DEFAULT, sp_bindefault and sp_unbindefault will be deprecated, Martin Bell advocates Changing Bound Defaults to Default Constraints. If you are going to upgrade SQL Server, definitely look at the notes from SQL Master of SQL Server QA’s presentation on SQL Server Upgrade Issues and How To Evaluate Potential Issues.

Stewart Smith lets is know that Drizzle Tarballs for the Next Milestone - Aloha are being released weekly. Meanwhile, Jay Pipes and the rest of the Drizzle team find, fix and explain the cause of a performance regression in Drizzle Performance Regression Solved - TCMalloc vs. No TCMalloc.

Lenz Grimmer has started organizing OpenSQLCamp 2009, Aug 22-23 in Germany; he posts details and links in Speaking at FrOSCon and Organizing the OpenSQLCamp 2009, European Edition. If you want to speak, Lenz also lets you know that the Call for Papers for the OpenSQLCAmp 2009 is Now Open! In other conference news, the Call for Papers for the OpenSQLCAmp 2009 is Now Open! In other conference news, the Call for Papers for the OpenSQLCAmp 2009 is Now Open! In other conference news, the Call for Papers for the OpenSQLCAmp 2009 is Now Open! In other conference news, Ronald Bradford gives out a discount code and reminds us that we can still attend OSCon 2009 at a Discounted Rate (until June 23rd). OSCon 2009 will be held July 20-24 in San Jose, California. And in Iowa, Michelle Ufford sends out the East Iowa SQL Saturday Call for Speakers to be held on Saturday, October 10, 2009.

Getting back to basics, Richard Foote explains Oracle’s cost-based optimizer in CBO and Indexes, an Introduction for Absolute Beginners. Speaking of optimizations, Valcora has Another Way To Do Performance Tuning — make sure you actually need the queries that are running against your system!

Tanel Poder points to a blog post on Using Perfsheet and TPT Scripts for Solving Real Life Performance Problems in an Oracle RAC environment. And Jonathan Lewis provides a script you can run if you are concerned about the potantial of Oracle PGA leaks. Over at Oraclue, Miladin Modrakovic shows how to discover memory “leaks and other problems with allocations of memory” in Memory Annotations and Oradebug.

If you are migrating a database from Oracle to MySQL, you may be interested in George Trujillo’s process of Converting an Oracle Schema to MySQL.

Kimberly L. Tripp reveals a lot of information about how SQL Server optimizes queries and common myths when she reveals The Tipping Point Query Answers. David Fetter shares Materialized Views Performance Tips in Postgres, and Leo Hsu and Regina Obe talk about Planner Statistics

in the Postgres optimizer.

In the land of DB2, Henrik Loeser shares a PureXML Performance Tip: A Sequence of Good Indexes.

Coskan explains
How to Use Sysman Schema Without Oracle Enterprise Manager. John Hallas notes that using Oracle’s EM to migrate a database to ASM is easy, but seems slow, in ASM Metadata and Migrating a Database to ASM. He then goes on to share a coworker’s Script to Backup ASM Metadata. J. Arneil shows how to go about Fixing up ASM Disk Header Corruption, should you find yourself in a rough spot.

Aaron Alton has a great article telling us that in Defensive Programming, Assumptions Must be Guaranteed or Tested, and another one on handling tags efficiently in Full Text Search vs. Denormalized Tables Remus Rusanu provides a Transact-SQL stored procedure template for Exception Handling and Nested Transactions.

I’ll end with a link to another survey on What’s the hardest part of becoming an involuntary DBA? It’s one simple question, so go fill it out! You have the time, especially since Craig Mullins points out that on average, we got a 4.6% salary increase in 2008 in Salaries for Data Professionals Inching Upward. To learn more and become even better in your field, get a 15-day free trial to Safari Books Online from O’Reilly, with a 15% discount if you continue past the free trial, courtesy of Susan Visser.

Ronald Bradford - 42SQL: Problems compiling MySQL 5.4

Thu, 11 Jun 2009 19:05:44 +0000

Seem’s the year Sun had for improving MySQL, and with an entire new 5.4 branch the development team could not fix the autoconf and compile dependencies that has been in MySQL for all the years I’ve been compiling MySQL. Drizzle has got it right, thanks to the great work of Monty Taylor.

I’m working on the Wafflegrid AWS EC2 AMI’s for Matt Yonkovit and while compiling 5.1 was straight forward under Ubuntu 8.10 Intrepid, compiling 5.4 was more complicated.

For MySQL 5.1 I needed only to do the following:

apt-get install -y build-essential
apt-get install libncurses5-dev
./configure
make
make install

For MySQL 5.4, I elected to use the BUILD scripts (based on Wafflegrid recommendations). That didn’t go far before I needed.

apt-get install -y automake libtool

You then have to go compiling MySQL 5.4 for 10+ minutes to get an abstract error, then you need to consider what dependencies may be missing.
I don’t like to do a blanket apt-get of a long list of proposed packages unless I know they are actually needed.

The error was:

make[1]: Entering directory `/src/mysql-5.4.0-beta/sql'
make[1]: warning: -jN forced in submake: disabling jobserver mode.
/bin/bash ../ylwrap sql_yacc.yy y.tab.c sql_yacc.cc y.tab.h sql_yacc.h y.output sql_yacc.output -- -d --verbose
make -j 6 gen_lex_hash
make[2]: Entering directory `/src/mysql-5.4.0-beta/sql'
rm -f mini_client_errors.c
/bin/ln -s ../libmysql/errmsg.c mini_client_errors.c
make[2]: warning: -jN forced in submake: disabling jobserver mode.
rm -f pack.c
../ylwrap: line 111: -d: command not found
/bin/ln -s ../sql-common/pack.c pack.c
....
make[1]: Leaving directory `/src/mysql-5.4.0-beta/sql'
make: *** [all-recursive] Error 1

What a lovely error ../ylwrap: line 111: -d: command not found

ylwrap is part of yacc, and by default in this instance it’s not even an installed package. I’ve compiled MySQL long enough that it requires yacc, and actually bison but to you think it would hurt if the configure told the user this.

It’s also been some time since I’ve compiled MySQL source, rather focusing on Drizzle. I had forgotten just how many compile warnings MySQL throws. Granted a warning is not an error, but you should not just ignore them in building a quality product.

Official Drizzle Blog: Drizzle’s Regression Issue Discovered (a.k.a. Eric [Saves The] Day!)

Thu, 11 Jun 2009 17:48:18 +0000

I won’t re-post what has already been said on the other blogs, but this seemed like a great central place to share the news.

After a couple months of trying to hunt down the mysterious little gnome causing headache to the team, it seems Eric stumbled upon the solution, probably in the shower, where all good ideas come to us. Take a look at his blog post for all the sexy statistics (last link) – I assume he did all the work after the shower, of course. Safety first, Eric!

Links:

Official Drizzle Blog: Drizzle lines of code

Thu, 11 Jun 2009 15:50:30 +0000

Jay along with help from many of you has done a great job automating a lot of our tools to provide Drizzle metrics to get a handle on how we are doing. You’ve seen the regular emails that now generate sysbench numbers for each build, we also have regular automated builds to generate data for valgrind, lcov, doxygen and sloc. Oh and lets not forget OpenGrok which was setup by Trond.

If you look at lcov we still have a few rough edges to work out and actually could use some help if any of you are familiar with the inner workings of lcov.

The sloc graph below is showing data going back to March. Nice steady progress downwards which in this case is great! The big drop around build 960 was due to plugin clean up work by Monty and the slight increase around build 984 was from protocol work from Eric (all necessary and important!). Now if we could get one of you to help generate a nice dynamic web page to display this information……any takers?

See here for a pdf of the graph

Trond Norbye: Compiling Drizzle on OpenSolaris 2009.06

Thu, 11 Jun 2009 10:58:27 +0000

I thought it would be appropriate with a new and updated blog post on how to compile Drizzle with the release of OpenSolaris 2009.06. To make the blog more copy'n'paste friendly I have removed the prompt from all of the command's I am displaying :-)

The first thing we need to do is to install a complier, and all of the common tools used to build opensource projects. Drizzle also require libevent and gperf, and there exists precompiled packages for them. So let's go ahead and install the software with the following command:

   pfexec pkg install ss-dev SUNWlibevent SUNWgnu-gperf

I like to put the software I compile in separate ZFS filesystems, so let's go ahead and create:

/opt/dscm - To hold the scm systems
/opt/drizzle - This is where we want our Drizzle installation
/opt/gearman - This is where we want our Gearman installation

"Why not just put everything in /usr/local?" you may ask. Well, I don't like that because then I have a hard time figuring what files to remove when I want to uninstall a package. "This must turn into a long and complex path?" would probably be your next question. The answer is no. Just create the appropriate symbolic links and you are good to go :-)

So let's go ahead and create the ZFS filesystems:

for f in dscm drizzle gearman google
do
   pfexec zfs create -o mountpoint=/opt/$f rpool/$f
   pfexec chown `/usr/bin/id -u`:`/usr/bin/id -g` /opt/$f
done

Drizzle, Gearman and libmemcached all use Bazaar for development, and there isn't a package available for OpenSolaris so we need to install this ourself. The Bazaar team is really active and using the "release early, release often" model, and I want a easy way to keep up with the versions. Instead of having zombie files / versions laying around, I ended up with a model where I install each version into its own directory, and I have a symbolic link to the version I want to use. Because we install in a "nonstandard" location, we need to create a startup-script so that Python can find the modules. So let's go ahead and install Bazaar (1.15 is the latest stable version right now) :

wget --no-check-certificate http://launchpad.net/bzr/1.15/1.15final/+download/bzr-1.15.tar.gz
gtar xfz bzr-1.15.tar.gz
cd bzr-1.15
python setup.py install --prefix=/opt/dscm/bazaar-1.15
mkdir /opt/dscm/bin
cat > /opt/dscm/bin/bzr <<EOF
#! /bin/ksh
export PYTHONPATH=/opt/dscm/bazaar/lib/python2.4/site-packages
exec /opt/dscm/bazaar/bin/bzr "\$@"
EOF
chmod a+x /opt/dscm/bin/bzr
ln -s bazaar-1.15 /opt/dscm/bazaar
cd ..
rm -rf bzr-1.15.tar.gz bzr-1.15

The next time you want to upgrade Bazaar, all you need to do is to move the symbolic link /opt/dscm/bazaar to point to the new version. You can now either put /opt/dscm/bin into your path, or you can create something like /opt/local/bin and create a symbolic link to /opt/dscm/bin/bzr from there (and then put /opt/local/bin in your path. To avoid path problems, I'll keep on referring to bzr with absolute path throughout the example.

For some reason OpenSolaris doesn't contain a prebuilt 64-bit version of GNU readline, so that we need to compile that ourself (It is scheduled for an upcoming build AFAIK). To keep the example simple, I'll just install the readline library into /opt/drizzle. So just execute the following commands to download, build and install:

wget http://ftp.gnu.org/gnu/readline/readline-6.0.tar.gz
gtar xfz readline-6.0.tar.gz
cd readline-6.0
./configure --disable-static --prefix=/opt/drizzle 
gmake all install
gmake clean
./configure --disable-static --prefix=/opt/drizzle --libdir=/opt/drizzle/lib/`isainfo -k` CFLAGS="-m64"
gmake all install
ln -s `isainfo -k` /opt/drizzle/lib/64
ln -s . /opt/drizzle/lib/32
cd ..
rm -rf readline-6.0.tar.gz readline-6.0

Stop! why do you build it two times?" If you look at the options there I compile one version with "-m64", and that option will create a 64bit binary. Most people would probably not care for the 32bit binary, but I like to build both versions when I build a library (so that I don't have problems later on if I want to build a 32 (or 64 bit) binary using the library. The reason for the two symbolic links I create at the end is explained in chapter 32-bit and 64-bit Libraries.

Drizzle use Google Protocol buffers in the communication protocol, so let's go ahead and compile them. I don't use the latest version, because there is a compilation error in that version (and I haven't had the time to look at that yet):

wget http://protobuf.googlecode.com/files/protobuf-2.0.3.tar.gz
gtar xfz protobuf-2.0.3.tar.gz
cd protobuf-2.0.3
./configure --disable-static --with-zlib --prefix=/opt/google CPPFLAGS="-fast -m32" LDFLAGS="-fast" \
            --bindir=/opt/google/bin/i86
gmake all install
gmake clean
./configure --disable-static --with-zlib --prefix=/opt/google CPPFLAGS="-fast -m64" LDFLAGS="-fast -m64" \
            --libdir=/opt/google/lib/`isainfo -k` --bindir=/opt/google/bin/`isainfo -k`
gmake all install
cd ..
ln -s `isainfo -k` /opt/google/lib/64
ln -s . /opt/google/lib/32
cp /usr/lib/isaexec /opt/google/bin/protoc
rm -rf protobuf-2.0.3.tar.gz protobuf-2.0.3

With all the dependencies installed, we can go ahead and grab the source for libmemcached, libdrizzle, Gearman and Drizzle:

for f in libdrizzle gearmand libmemcached drizzle 
do
   /opt/dscm/bin/bzr branch lp:$f
done

So let's go ahead and start building them. libdrizzle is first up:

cd libdrizzle
./config/autorun.sh
./configure --disable-static --prefix=/opt/drizzle CFLAGS="-fast -m32" LDFAGS="-fast"
gmake all install
./configure --disable-static --prefix=/opt/drizzle --libdir=/opt/drizzle/lib/`isainfo -k` CFLAGS="-fast -m64" LDFAGS="-fast"
gmake clean
gmake all install
cd ..

The next one on the list is libmemcached:

cd libmemcached
./config/bootstrap
PATH=$PATH:/usr/perl5/bin ./configure --disable-static --prefix=/opt/drizzle CFLAGS="-fast -m32" LDFAGS="-fast" \
    --without-memcached --bindir=/opt/drizzle/bin/i86
gmake all install
PATH=$PATH:/usr/perl5/bin ./configure --enable-64bit --disable-static --prefix=/opt/drizzle \
    --libdir=/opt/drizzle/lib/`isainfo -k` CFLAGS="-fast" LDFAGS="-fast" --without-memcached --bindir=/opt/drizzle/bin/`isainfo -k`
gmake clean
gmake all install
for f in memcat memrm memcp memerror memflush memslap memstat
do
cp /usr/lib/isaexec /opt/drizzle/bin/$f
done
cd ..

There is a problem with the configure script for Gearman, so it is not able to create a 32 bit binary on a machine capable of running in 64 bit mode, so from now on we will only create 64 bit binaries (I will work on a patch for this):

cd gearmand
./config/bootstrap
./configure --prefix=/opt/gearman --disable-static --sbindir=/opt/gearman/sbin/`isainfo -k` --libdir=/opt/gearman/lib/`isainfo -k` \
            --bindir=/opt/gearman/bin/`isainfo -k` CFLAGS="-fast -I/opt/drizzle/include -m64" \
            LDFLAGS="-L/opt/drizzle/lib/64 -R/opt/drizzle/lib/64"
gmake clean
gmake all install
cd ..
cp /usr/lib/isaexec /opt/gearman/sbin/gearmand
cp /usr/lib/isaexec /opt/gearman/bin/gearman

Before we can start compiling Drizzle we need to make sure that Drizzle can detect our PCRE installation. OpenSolaris ships with a version that is too new for the Drizzle configure script, so that we need to create a symbolic link to make sure it detects it properly:

pfexec ln -s pcre/pcre.h /usr/include/pcre.h

Now all is set for compiling Drizzle:

cd drizzle
PATH=$PATH:/opt/dscm/bin ./config/autorun.sh
PATH=$PATH:/opt/google/bin ./configure CPPFLAGS="-I/opt/google/include -I/opt/gearman/include -I/opt/drizzle/include" \
   LDFLAGS="-L/opt/google/lib/64 -L/opt/gearman/lib/64 -L/opt/drizzle/lib/64 -R/opt/drizzle/lib/64:/opt/gearman/lib/64:/opt/google/lib/64" \
   --prefix=/opt/drizzle --libdir=/opt/drizzle/lib/`isainfo -k` 
PATH=$PATH:/opt/google/bin gmake all install

Now you should have Drizzle installed in /opt/drizzle. If you look in some of my previous blog posts you should be able to find out how to install it as an SMF service :-)

Cheers

Brian Aker: Regression, The Bummer

Wed, 10 Jun 2009 19:14:26 +0000

Wahoo!

We finally found the regression problem in Drizzle that we have been looking for over the last couple of months.

In the processes of doing this was have walked every line of code. I sat the other night doing a single step through the entire sysbench run looking for anything out of place. Nothing came up at all.

Eric was the person who finally asked the question "could it be tcmalloc"? No one had assumed it because it typically is a good solution (and we will be looking into why it turned out to be at fault, we will probably push now to more aggressively remove the MEMROOT system we inherited since we suspect it/it doesn't play well with C++).

We have not been able to push any patches in the last couple of months that really fixed other performance issues that we know exist.

Why?

Because we feared complicating the problem of finding the original problem. We have all spent time looking through our ancestors to see if there was something we missed.

1) Could it be C++?
2) Could reducing the number of locks, creating a traffic jam around a single lock?
3) Was UTF-8 at fault?

In the end it was none of these :)

So for us?

We have patches coming soon to optimize the UTF-8 system, to minimize LOCK_open, optimize/simplify the THR lock system simpler, and to partition caches internally.

Brian Aker: Stored Procedures or Server Side Scripting?

Wed, 10 Jun 2009 18:24:34 +0000

Here is a bit of code I worked up for us a recently for Drizzle:

drizzle> DELIMITER |
Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

drizzle> CREATE PROCEDURE perl_hello (param1 string)
-> return "Hello " . $_[0] . "!"
-> |
Query OK, 0 rows affected (0.05 sec)

drizzle> CALL perl_hello('Brian');
-> |
Query OK, 1 row affected (0.00 sec)

drizzle> DELIMITER ;
drizzle> SELECT @perl\G
*************************** 1. row ***************************
@perl: Hello Brian!
1 row in set (0.00 sec)

Stored Procedures!?!

In an actual language!?!

About a week ago I was talking to a CTO for a company who is looking at adoption of Drizzle. One of things he came back with was "I don't need stored procedures, but I do need server side scripting".

Back at the very first MySQL User's Conference we had a debate over the future of stored procedures in MySQL. I and some others really wanted the first stored procedure language to be external, David really wanted it to be PHP. I didn't see the value in implementing a single language. I thought people would be more interested in writing code in whatever language they wanted. Also, I figured that an external system would allow for different groups to develop languages more rapidly.

Fast forward to when we began Drizzle. Parsers are where you spend a lot of your time. The smaller the parser the better off you are. So I went to task removing all of the signs of the SP language from Drizzle. We have been free of them now for over a year now (yes, long before we went public). Things are finally shaping up so that when we begin on Bell, our next milestone, stored procedures, or something like them, are now on our list.

Though are they stored procedures, or is this server-side scripting?

A few premises of the design:

Any language should be pluggable. We won't have a native language.

We only support re-entrant engines. This solves all of the pre-locking problems that exist currently. We haven't removed 40% of the locks in Drizzle, only to have to come up with a bunch of new ones to support engines which were never built to handle this stuff.

While it won't be required, we will focus first on enabling scripting languages that are not in-process. Why is that? We don't want anyone to crash the database. Informix had this problem early on and got a bad rep for it. We want to avoid this.

We will enable driver writers to be able to communicate in a native way. AKA if you are writing something in Java, you will be able to use a JDBC interface inside of the database. For Perl DBI, etc. I want to be able to test my SP's in any environment. The difference between running in of the database, and out of should be trivial or non-existent.

I am a little bit torn about using the SP call/creation SQL commands in Drizzle. You won't be doing the typical SP language (well... unless someone wants to write a plugin for them!). I would also like to encourage people to think differently about what writing server side code should look like. Personally I don't feel that stored procedures are the right solution for a lot of the cases, keep your business logic in your application layer(!), but we also know that users expect to be able to be able to run code locally. Triggering/Callback mechanisms can be very useful though, and enabling them is a part of this. Doing Triggers today in C is simple, but that is not something that everyone should/would/could want to do.

Putting this in the plugin structure means no overhead to the parser or the rest of the database. Keeping them out of process means no drain or memory expansion of the Database. SMP boxes will benefit because you can confine the language VM to a particular set of processors/amount of memory.

We don't want the database to ever blow up because of bugs in the execution language!

And if you never want them? You never load the plugin in the first place.

Why Perl? I've embedded Perl for years and know how to make it work. I've only done Java once, so I will leave that to other experts.

I suspect I can find a Java person somewhere inside of Sun :)

Jay Pipes: Drizzle Performance Regression Solved - TCMalloc vs. No TCMalloc

nospam@example.com (Jay Pipes) — Wed, 10 Jun 2009 17:03:12 +0000

As many of you who follow the drizzle-discuss mailing list know, for the last three months, Drizzle developers have been hot (and many times cold) on the tail of a performance problem that we were seeing when comparing Drizzle with MySQL (any version of MySQL, not just 5.4).

Briefly, on certain machines, we were seeing Drizzle performing at approximately 50% of MySQL, with throughput measured in transactions per second on both a readonly and readwrite workload with Sysbench. The frustrating part of the results was that on other machines, even other machines with virtually identical architectures, compilers, and operating system, we were seeing Drizzle outperforming MySQL by around 20-30%.

So, the Sun Drizzle team, friends in the Sun PAE (performance applications engineering?) team and various contributors set out to iteratively analyze exactly what was going on during sysbench runs in both MySQL and Drizzle. We analyzed the call stacks of both servers using callgrind, cachegrind, vmstat, and other tools, looking for differences which could explain the differences in performance.

These efforts ultimately showed either red herrings, or showed results which did not indicate where such a dramatic performance difference was coming from. The efforts did, however, lead us to a much better understanding of the calling patterns, lock contention, and other parts of the servers, and this work will, I am sure, prove very valuable in the coming months as we continue to refine the execution of the server and remove more global contention points.

So, what was the eventual culprit? Turns out that Drizzle was, by default, linking the TCMalloc library from Google (libgoogle-perftools-dev package on Debian systems) when it was found installed on the machine. This makes sense. We thought that the TCMalloc library would provide a benefit to our Session-mem_root-based allocation strategy adopted from the MySQL core kernel. Unfortunately, it turns out that TCMalloc dramatically degrades the throughput of the server. When we disabled tcmalloc in our build and re-ran the benchmarks, our numbers went through the roof.

The machine these results are from has the following specifications:

16-core Quad Xeon quad-core Intel processors
4M L2 cache size
32GB RAM - though this is irrelevant as the benchmark was fully in-memory for a 1M row table and 256M innodb buffer pool...
Ubuntu 8.10 OS - Linux kernel: 2.6.27-11-server x86_64
GCC 4.3.2
Disk system software RAID 5 across 3 146GB 15K RPM disks - though this is irrelevant as the benchmark was fully in-memory for a 1M row table and 256M innodb buffer pool...

The benchmarks were run for a minimum of 10 iterations of 60 seconds each on a readonly and readwrite workload with 1M rows in the table. The configuration was:

--innodb_buffer_pool_size=256M
--innodb_log_file_size=128M
--innodb_log_buffer_size=8M
--innodb_additional_mem_pool_size=16M
--innodb_thread_concurrency=0

The Drizzle Automation Suite was used for benchmarking.

Below, you'll see the before and afters of disabling TCMalloc on both a readonly and readwrite workload. We think you'll agree the results are, well, dramatic.

READONLY workload

With TCMalloc linked:

+------+-------------+
| c    | tps         |
+------+-------------+
|    2 | 1067.444444 | 
|    4 | 1435.190000 | 
|    8 | 1937.624444 | 
|   16 | 2601.817778 | 
|   32 | 3367.795556 | 
|   64 | 3930.240000 | 
|  128 | 3940.764444 | 
|  256 | 3071.503333 | 
|  512 | 2003.308889 | 
| 1024 | 1224.704444 | 
| 2048 |  530.994444 | 
+------+-------------+

Without TCMalloc linked:

+------+-------------+
| c    | tps         |
+------+-------------+
|    2 | 1511.681000 | 
|    4 | 2714.570000 | 
|    8 | 4408.986000 | 
|   16 | 5795.430000 | 
|   32 | 5619.712000 | 
|   64 | 4988.760000 | 
|  128 | 4483.512000 | 
|  256 | 3914.125000 | 
|  512 | 2541.946000 | 
| 1024 | 1325.511000 | 
| 2048 |  643.446000 | 
+------+-------------+

And for you pretty graph people:

READWRITE workload

With TCMalloc linked:

+------+-------------+
| c    | tps         |
+------+-------------+
|    2 |  589.006667 | 
|    4 |  842.576667 | 
|    8 | 1142.627778 | 
|   16 | 1561.532222 | 
|   32 | 2160.194444 | 
|   64 | 2169.077778 | 
|  128 | 1793.243333 | 
|  256 | 1241.846667 | 
|  512 |  860.450000 | 
| 1024 |  491.360000 | 
+------+-------------+

Without TCMalloc linked:

+------+-------------+
| c    | tps         |
+------+-------------+
|    2 |  676.238000 | 
|    4 | 1096.708000 | 
|    8 | 1661.204000 | 
|   16 | 2210.335000 | 
|   32 | 2353.749000 | 
|   64 | 2202.926000 | 
|  128 | 2087.273000 | 
|  256 | 1717.978000 | 
|  512 | 1361.468000 | 
| 1024 | 1000.169000 | 
| 2048 |  288.299000 | 
+------+-------------+

Again, pretty graph:

Note: With TCMalloc linked, the readwrite workload would not complete at 2048 connections. Without TCMalloc linked, it did complete, although with a significant reduction in throughput. This is likely because of the known InnoDB issue regarding 1024 active transactions limit...

Open Invitation to Benchmark Drizzle, MySQL, XtraDB and PostgreSQL

The above results compare only Drizzle to itself with and without TCMalloc. We benchmarked also against MySQL 5.4, but as we've previously stated, we don't think comparison numbers should be published unless by a third or objective party. This serves as an open invitation to benchmark Drizzle against MySQL 5.4, XTraDB, PostgreSQL 8.4 and anything else. We'd love to see a validation that our principles of smaller, cleaner code with fewer global contention points, using standard libraries and having features live in a module ecosystem truly does enable a faster, leaner query-running machine. By the same token, if published benchmarks identify cases where Drizzle underperforms compared to another RDBMS, we'd love to tackle the performance problems the benchmarks show. The more data, the better

Eric Day: Drizzle Regression Hunting

Tue, 09 Jun 2009 22:32:03 +0000

We’ve been looking for a Drizzle regression for some time now, and today I decided I would take a step back and make another attempt to find it. The first step in doing this was to reproduce this consistently and find a baseline. We’ve noticed it most dramatically with a 16 concurrent connection test from sysbench in read-only mode. I used two 16-core Intel machines running Linux we have for development. We’ve noticed the regression on certain machines but not all, and these two machines provided one of each. I also setup a MySQL 5.1.35 server to use as a baseline to give some comparisons outside of Drizzle. So first, a few more details on the machines:

Machine 1: 16 core, 16GB RAM, cache sizes from dmesg:
[    0.010000] CPU: L1 I cache: 32K, L1 D cache: 32K
[    0.010000] CPU: L2 cache: 4096K
From /proc/cpuinfo:
cache_alignment : 64

Machine 2: 16 core, 40GB RAM, cache sizes from dmesg:
[    0.010000] CPU: Trace cache: 12K uops, L1 D cache: 16K
[    0.010000] CPU: L2 cache: 1024K
[    0.010000] CPU: L3 cache: 16384K
From /proc/cpuinfo
cache_alignment : 128

For Drizzle I used the latest trunk in Launchpad (r1058), and for MySQL I downloaded mysql-5.1.35-linux-x86_64-glibc23.tar.gz from mysql.com.

For sysbench, I grabbed the Drizzle branch of it at lp:~drizzle-developers/sysbench/trunk since this has the libdrizzle driver. The libdrizzle driver also supports the MySQL so I use it to test against both. The sysbench commands I used were:

Drizzle: sysbench –test=oltp –oltp-read-only=on –max-time=15 –max-requests=0 –oltp-table-size=1000000 –num-threads=16 –db-ps-mode=disable –db-driver=drizzle –drizzle-host=127.0.0.1 –drizzle-port=4427 –drizzle-db=test –drizzle-user=root –drizzle-table-engine=innodb run

MySQL: sysbench –test=oltp –oltp-read-only=on –max-time=15 –max-requests=0 –oltp-table-size=1000000 –num-threads=16 –db-ps-mode=disable –db-driver=drizzle –drizzle-host=127.0.0.1 –drizzle-port=3306 –drizzle-db=test –drizzle-user=root –drizzle-table-engine=innodb –drizzle-mysql=on run

I started Drizzle and MySQL with the following options. These are not meant to be finely tuned options, but just enough to get the servers running with some sane comparable defaults and able to reproduce the regression.

bin/mysqld –no-defaults –server-id=1 –port=3306 –socket=/home/eday/other/mysql.data/sock.master –basedir=/home/eday/other/mysql –datadir=/home/eday/other/mysql.data/db.master –log-error=/home/eday/other/mysql.data/db.master/error –innodb-buffer-pool-size=128M –innodb-log-file-size=64M –innodb-log-buffer-size=8M –innodb-thread-concurrency=0 –innodb-additional-mem-pool-size=16M –character-set-server=utf8 –table-open-cache=4096 –open-files-limit=4096 –pid-file=/home/eday/other/mysql.data/db.master/pid

drizzled –datadir=/home/eday/other/drizzle.data –innodb-buffer-pool-size=128M –innodb-log-file-size=64M –innodb-log-buffer-size=8M –innodb-thread-concurrency=0 –innodb-additional-mem-pool-size=16M –table-open-cache=4096 –table-definition-cache=4096

Now with everything up and running, I gathered some data. Headings are: -

	1-drizzle	1-mysql	2-drizzle	2-mysql
TPS	1335	2434	1559	1239
vmstat
in	6k	110k	60k	50k
cs	100k	210k	120k	100k
us	22	75	72	78
sy	6	20	25	18
id	72	5	3	4
wa	0	0	0	0
valgrind with cachegrind tool
TPS	5.21	3.15	3.55	1.96
iref	858M	1011M	668M	789M

As you can see, we hit the major regression in column one. Our interrupts and context switches are way out of line, and the CPU is mostly idle. Note though that when run under cachegrind (valgrind –tool=callgrind ), we see the normal pattern and don’t notice the regression. This means to reproduce we can’t have any intrusive debugging tools. I also tried counting system calls as a sanity check and found (using strace -fc ):

1-drizzle: 402 TPS
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.47  106.807940        2867     37252     11127 futex
 13.97   26.907878      220556       122           select
  5.67   10.913999    10913999         1           rt_sigtimedwait
  5.29   10.181982      565666        18           poll
  2.16    3.1154314          11    362463           read
  1.63    2.1146381        1479      2128           pread
  1.55    2.981563          16    181220           write
  0.67    0.1297864         122     10598           sched_yield
  0.67    0.1288412        1394       924           nanosleep

1-mysql: 245 TPS
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 60.03  108.1509367        4594     23836      5835 futex
 15.42   27.1119721      100788       279         1 select
  5.25    8.1579991     1064443         9           rt_sigtimedwait
  1.65    2.1005637         637      4716           pread
  1.30    1.1367183          21    110986           write
  1.07    1.950471           9    221889    221889 sched_setscheduler
  1.06    1.929137           9    223097      1044 read

2-drizzle: 276 TPS
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 65.93  115.556211        3704     31198      8101 futex
 15.30   26.810000      203106       132           select
  5.89   10.330000    10330000         1           rt_sigtimedwait
  5.80   10.170000      565000        18           poll
  2.65    4.647123          37    125060           write
  2.35    4.116470          16    250158           read
  1.72    3.021068        1483      2037           pread
  0.27    0.477662       20768        23           fsync
  0.04    0.072760           4     18218           madvise
  0.02    0.043264          18      2357           sched_yield

2-mysql: 168 TPS
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 68.25  100.851565        6704     15044      3328 futex
 21.22   31.350000      116111       270         1 select
  5.17    7.642591      347391        22           rt_sigtimedwait
  1.93    2.853778         619      4612           pread
  1.15    1.694888          11    153040       346 read
  0.90    1.327743           9    152529    152529 sched_setscheduler
  0.78    1.145964          15     76305           write
  0.32    0.468542          37     12705           madvise

Again, when tracing the process to count system calls, we see the regression disappear, which leaves us with a smaller set of tools to use.

So what next? One theory we were tossing around is a cache alignment issues. This seems like a pretty dramatic drop in performance to be caused by this, but I ran a test to see what the behavior of a process is when you are being throttled by a shared cache line. The results showed idle CPU, but the interrupts and context switches did not drop off. This does not follow the same pattern we saw in Drizzle (interrupts and context switches did drop off). Our cache line size is also smaller on the machine showing the regression, so that did not help support this theory.

While stabbing at a few other ideas, I ran ldd to see which libraries were being use in the two drizzled binaries on each machine. Suppressing some common libs:

1-drizzle: ldd drizzled/drizzled
        ...
        libpcre.so.3 => /lib/libpcre.so.3 (0x00007f9a88532000)
        libtbb.so.2 => /usr/lib/libtbb.so.2 (0x00007f9a88320000)
        libtcmalloc.so.0 => /usr/lib/libtcmalloc.so.0 (0x00007fcced5bc000)
        ...

2-drizzle: ldd drizzled/drizzled
        ...
        libpcre.so.3 => /lib/libpcre.so.3 (0x00007fc0af803000)
        libtbb.so.2 => /usr/lib/libtbb.so.2 (0x00007fc0af5e9000)
        ...

The machine showing the regression is linking with tcmalloc. Looking at the drizzle configure.ac, we use libtcmalloc by default if it is found (machine 2 does not have tcmalloc installed). I relinked drizzled without tcmalloc and received these results:

	1-drizzle	1-mysql	2-drizzle	2-mysql
TPS	2751	2434	1559	1239

There it is! For some reason tcmalloc was giving us a 51% performance drop. Perhaps this is due to the tcmalloc version or settings we need to tweak for performance (something to look into later), but for now disabling this by default is the solution. We’re verifying the fix now and should be in the Drizzle trunk shortly.

Stewart Smith: Drizzle pluggable MetadataStore (or: no table definition file on disk)

Tue, 09 Jun 2009 03:25:17 +0000

Stewart Smith: Drizzle Tarballs for next milestone: aloha

Tue, 09 Jun 2009 02:02:45 +0000

Official Drizzle Blog: Drizzle source tarball 1055 has been released

Mon, 08 Jun 2009 20:49:46 +0000

Drizzle source tarball based on build 1055 has now been released. The change log can be viewed at https://launchpad.net/drizzle/trunk/aloha.

For this release we continue to focus on code clean up, build improvements, increased test coverage and performance improvements. We also removed LOCK TABLES, BIT_COUNT and BIT_LENGTH, made several logging improvements, started the first several phases of refactoring JOIN and provided initial support for gcc 4.4 although for now you still need Monty’s patch for protobuf to use gcc 4.4.

-Lee

Ronald Bradford - 42SQL: OSCON 2009 at a discounted rate

Fri, 05 Jun 2009 21:33:31 +0000

OSCON moves this year from Portland to San Jose.

As one the community panel for Drizzle: Status, Principles, and Ecosystem I also have a speaker discount which you can combine with O’Reilly having also extended early bird registration until June 23.

Be sure to add the os09fos code for an additional 20% off, and be sure to shout me a drink there.

Marcus Eriksson: Pluggable Batch Update Handlers

noreply@blogger.com (Marcus Eriksson) — Thu, 04 Jun 2009 20:07:24 +0000

Reading about the awesome batch insert performance blog post by Mark Matthews last week (http://www.jroller.com/mmatthews/entry/speeding_up_batch_inserts_for) got me thinking, why has this not been done before? Connector/J must be the most deployed JDBC driver in the world and batch inserts are a common use case, why hasn't the community stepped up and implemented the query rewrite feature before? Most likely because it is a complex issue that requires deep knowledge of the rest of the driver. I have been a happy Connector/J user myself for several years and never considered doing something like this.

To handle this complexity in drizzle-jdbc the batch query functionality is pluggable, i.e. you can implement a small interface and tell the connection to use that implementation. So, if anyone out there has some crazy ideas about how to improve performance of batch inserts/updates, it should be fairly easy.

First you need to implement the ParameterizedBatchHandler interface, it has two methods:


void addToBatch(ParameterizedQuery query);
int [] executeBatch(Protocol protocol) throws QueryException;

addToBatch is called when addBatch() is called on the PreparedStatement. I.e. when someone wants to add the current set of parameters in a prepared statement to the current batch - the query parameter contains all the information you need to make something smart.

executeBatch is called when executeBatch() is called on the PreparedStatement. The protocol sent to this method should be used to send the query to the server (though, you could make new connections to the server, fork up a few threads and send queries to the server in parallel).

Then, to make the connection use your handler:


Connection connection = DriverManager.getConnection("jdbc:drizzle://localhost:4427/test_units_jdbc");
if(connection.isWrapperFor(DrizzleConnection.class)) {
   DrizzleConnection dc = connection.unwrap(DrizzleConnection.class);
   dc.setBatchQueryHandler(VerrrryFastBatchHandler.class);
}
PreparedStatement ps = connection.prepareStatement("insert into asdf (somecol) values (?)");
ps.setString(1,"aa");
ps.addBatch();
ps.executeBatch();

The current implementation in drizzle-jdbc simply stores all queries in a list and when doing executeBatch, the queries are sent, one-by-one, to the server. I'm planning on doing a rewrite handler in the near future.

Look at these files for more information:

ParameterizedBatchHandler.java

Brian Aker: Drizzle, State of Testing

Thu, 04 Jun 2009 17:05:56 +0000

Testing, Testing, Testing...

I've gotten a number of questions about how we are doing testing, and even how our methodology for accepting code works :)

A lot of this comes from running open source projects for almost a couple of decades (hell, if I toss in uploading public domain to software to BBS'es for the Commodore 64 it is a bit longer!).

One of the most important rules I have learned over the years is that anything that is not automated and not required, will get skipped.

Today Drizzle runs 213 tests, the entire MySQL test suite minus tests that are for features we don't have. We don't allow for any regression, meaning that no one is allowed to disable a test in order to get their code pushed. Our test suite was also modified so that we can run all of the tests against a particular engine. Today we do this with both Innodb and PBXT. So instead of having "engine specific" tests, we can test everything. Feedback we are getting from storage engine vendors is that this is golden. Even if they never release for Drizzle, they can use it to vastly increase the testing they do today.

We also do not allow any code to be pushed that causes a compiler to toss a warning. We do this for a wide set of versions of gcc, and also for Sun Studio. We treat warnings as errors :)

We are also enormously proud of this fact. This took a lot of effort :)

Our most recent change is that we now include a regression test for performance for sysbench. For each push into our "staging" tree we run a full test at different steps of "connections". We test both read-only and read-write workloads. My only real complaint right now about this system is that we look at absolute numbers, and being a math geek, I would really like some more information on standard deviation :)

The development process we use maximizes our use of tools. For Drizzle we accept no patches via email. Your patch must come through Launchpad, where you must have an account. We do this so that we can always track down "who did what". This is done almost entirely without exception, and the exception being that I am sure someone at some time as either pointed out a bug in code to me or has made a comment on how something should be written while reading over my shoulder.

We don't require anyone to sign a "all your bases belong to us" sort of contract. Speaking as myself, I personally find them unpalatable and frequently suggests developers think about them before ever signing. Code flowing is what I find important. With Drizzle we have had a 100+ contributors, and I suspect that number would be much smaller if we had taken a different point of view on this. One of the most interesting conversations I ever had about this topic was with Rasmus, who has continued to refuse to sign the Apache Foundation's agreement. If someone was to ever put code in from their employer, we can strip the code back out in seconds. We can also hand over all of their information to the original copyright holder for them to prosecute. The ones of us who do final review of code are very nitpicky about this. One of the nice things about "source code" is that it has finger prints. Anything that doesn't follow our coding style, which is very specific, stands out.

Code which was not original tends to always show it roots. I know from talking to Theodore Tso that IBM gives its developers who take in code to its open source projects, a class in how to identify copyright violations. IBM's general handling of open source always tends to impress me.

Having a modular approach in our design also means that any "large" sort of change can be reduced down to a plugin. Our average patches are bug fixes or refactoring bits. If you find that you enjoy making code readable, fast, and standard looking, you will probably enjoy working on Drizzle.

If you want to write fun and new code, then you should look at writing plugins!

On a personal note, if I was to write a database from scratch, I would go after a completely different problem domain. I still happen to need a relational database though, which is why I work on Drizzle.

That and the people who are working on the project are both awesome and fun :)

Code flow in Drizzle is pretty simple. You write a patch, you submit a tree to Laundpad, and one of the captains reviews it and puts it into their tree. I pull from one of their trees and merge into a local tree. Before I push the code it has to pass all tests/etc on 64bit Intel Fedora, Open Solaris Sparc, Solaris Sparc, and OSX. When I get around to buying myself a new desktop Mac I am going to spin up a few more platforms in virtual boxes, so that I can test more platforms. There is a simple Gearman system I use to populate and run the code on all of these systems.

Once the code passes on all of the above it goes to our staging tree on launchpad. From there the automated system gives me feedback on regression. If we pass then the code goes to trunk. Our turn around time for code is frequently about 24 hours. Since all of the above testing is done, we drop tarballs anytime we want too.

It is pretty much clockwork for us. If a human wasn't involved I suspect we could just set a cronjob to handle it every two weeks (and who knows... if Lee gets bored with this, maybe he will do exactly that!).

What is the future?

Today we generate automated reports for cachegrind, callgrind, and valgrind. We run pahole by hand. We are told that there is a new tool for generating random queries for MySQL for crash testing, we need to look into this.

I've also got a set of tests written around drizzleslap (aka mysqlslap) that we need to toss in soon.

All of these need to go into the process. We should never regress on L2 misses or branch predictions. We should never see holes in our structures/classes.

We don't have enough tests for failure cases. Our test coverage is public:
http://drizzle.org/lcov/index.html

I want to see that increased. The problem is that there has never been a test system for "this is how to fail at X". A few hacks have been written, but we need to come up with a complete methodology for this. The one open source database that has an awesome test suite for this is SQLite, and we need to spend some time learning from them all of what they do.

Over dinner recently with Josh Berkus, he mentioned the work they are doing on pgbench. I am hoping that we can get a patch in it so that it will support libdrizzle. That way we could use one of the Postgres tools as well.

These are our next big steps :)

Have any suggestions? Want to contribute? All of our tools are open source. We welcome extensions and they are general enough that almost any other database could use them as well!

Marcus Eriksson: Drizzle-JDBC 0.3

noreply@blogger.com (Marcus Eriksson) — Mon, 01 Jun 2009 09:54:55 +0000

I just pushed up 0.3 of drizzle-jdbc to the maven repository, go here to download. It will soon be synced to the official maven repository.

Changes from 0.2 to 0.3:

Add the services file to make the driver autoload.

Throw proper JDBC4 SQLExceptions.

Make blobs + getObject() work against new versions of drizzled.

Fix bug with generated keys and prepared statements.

Fix bug with prepared statements and adding parameters several times (fix by Trond Norbye).

Name the packet fetcher thread.

Fix bug with getSchemas() - returned columns in wrong order.

Rework of the packet fetching by Trond.

Pluggable batch query handlers (I'll blog about this soon)

Official Drizzle Blog: JavaOne/Community One next week

Sun, 31 May 2009 04:13:07 +0000

We will be showing a great demo next week at Community One and Java One which will highlight a scalable open source search engine using Drizzle, Memcached, Gearman and Sphinx. See Eric’s write up for more details.

If you are going, please drop by the Drizzle booth and check out the demo and say hello. Hope to see you there.

Eric Day: libdrizzle 0.3 Released

Fri, 29 May 2009 23:11:16 +0000

I’m pleased to announce a new version of libdrizzle! This is mostly a bug fixing and maintenance release before I start in on more significant development. One of the new features I added was a hook to be able to use your own I/O event mechanism rather than the default poll(). This will allow you to use libraries like libevent, which can be useful when dealing with a large number of file descriptors, or to mix with other file descriptors in your application (for example, you could listen on other fd’s alongside the non-blocking Drizzle/MySQL socket connections). There is not much for examples or documentation yet with this feature, but for now you can email or find me in #drizzle on irc.freenode.net if you would like to know more.

One of the next steps with libdrizzle is a better protocol abstraction, since the Drizzle protocol is diverging quite a bit from how the MySQL protocol works. With these abstractions, it will also be possible to easily add other database-like protocols (where column/row/fields make sense). I’m also going to start looking into more memory optimizations and performance tuning.

Marcus Eriksson: JavaOne sessions

noreply@blogger.com (Marcus Eriksson) — Fri, 29 May 2009 15:15:18 +0000

I'm attending JavaOne this year, it starts on Tuesday. These are the sessions i look forward the most to (in no particular order):

Return of the Puzzlers: Schlock and Awe (TS-5186)
Josh Bloch and Neal Gafter present a couple of interresting programming puzzles, will be fun.

Effective Java: Still effective after all these years (TS-5217)
Again, Joshua Bloch. His book "Effective Java" is one of the best programming books i've read.

Java Platform Concurrency Gotchas (TS-4863)
Concurrency is a difficult issue, this session presents issues with code examples.

Ghost in the virtual machine: A reference to references (TS-5245)
Bob is the author of google guice (see below) and from what I've heard, he is also a great speaker.

Defective Java Code: Mistakes that matter (TS-5335)
About bugs in java code, static analysis etc.

Drizzle: A new database for the cloud (TS-5410)
Drizzle is a great project, positive, helpful people.

JDBC? We don't need no stinkin' JDBC: How LinkedIn scaled with memcached, SOA and a bit of SQL. (TS-4696)
Always interesting to hear how big companies handle scalability issues really job related for me.

Introduction to Google Guice: The Java Programming language is fun again (TS-5434)
Google Guice really opened my eyes to what you can do with java, the subject of the talk is so true, it shows what java is capable of and that java can be fun. This is how modern java development should be done.

Eric Day: Narada - A Scalable Open Source Search Engine

Thu, 28 May 2009 00:55:58 +0000

I’ve been working with Patrick Galbraith for the past couple weeks on a new project that started as an example in his upcoming book. It is a search engine built using Gearman, Sphinx, Drizzle or MySQL, and memcached. Patrick wrote the first implementation in Perl to tie all these pieces together, but there is also a Java version underway bring written by Trond Norbye and Eric Lambert that will be shown at the CommunityOne and JavaOne conferences next week. I’ve been helping get the system setup on a new cluster and with the port to Drizzle.

Narada provides interfaces that allow you to submit URLs to be indexed and crawled, and then to search those indexes and get a result set back. This allows you to index and search your own set of URLs, possibly for a single website or just for your own personal archive. The crawler in the back-end will be able to stop after some recursion limit from the original URL and also be able to apply URL filters (for example, only index pages under the domain “oddments.org”). Other filters and extensions should be easy to add. Narada is interesting because it is:

Open Source - You can modify it to fit your own needs, hopefully in a modular way so that changes can be contributed back to the project.
Easy to Scale - The system is built on a number of asynchronous queues, and the processes to perform that work can run on any number of machines. Increasing your capacity is now trivial, simply start up more machines and with new workers.
Language Agnostic - While the first versions are in Perl and Java, it is easy to mix in other languages. For example, if a certain component was slow, we could rewrite it in C for better performance. The APIs to index and search can also be wrapped for any language since it will mostly just involved wrapping the Gearman client API. I’m thinking of hacking up a PHP API.

So, how does Narada work under the hood?

Click here for the full-size image

The blue boxes represent your front-end application that use Narada, using the Gearman client API. The yellow boxes represent Gearman workers that perform one of the tasks in the chain. The orange boxes represent the storage mechanisms such as Drizzle, MySQL, Sphinx index, or memcached.

When a URL is submitted, it will first be queued in a Drizzle table for later processing. A Gearman job is started during the table INSERT to notify a Fetch Worker that a new URL is ready. Once a free Fetch Worker is available, it downloads the page and looks for more URLs to index. This is where recursion limits and filters are implemented. Next, it takes the resulting document and pushes it into memcached and notifies the Document Worker a new document is ready to be stored and indexed. The Document Worker then stores this inside of another Drizzle table and will start the Sphinx indexer if it hasn’t been run in a while. We don’t want to index on every URL since this would be wasteful and expensive. At this point the document is stored, indexed, and memcached is primed with the content.

When a search request comes in, the client will dispatch a search job to the Search Worker. This worker is responsible for performing the Sphinx search and gathering the necessary information from memcached or Drizzle so the client can return some meaningful results. In the future we will most likely be sharding the data and indexes, so the Search Worker will also be responsible for aggregating multiple shard searches into one set for the caller.

The code is still rough around the edges, but we’ve set it up on a couple clusters so far and it is working quite well. We’ll be actively working on it and refining the install process so it is easier to get it up and running.

Eric Day: Cache Line Sizes and Concurrency

Wed, 27 May 2009 19:20:35 +0000

We’ve been looking at high concurrency level issues with Drizzle and MySQL. Jay pointed me to this article on the concurrency issues due to shared cache lines and decided to run some of my own tests. The results were dramatic, and anyone who is writing multi-threaded code needs to be aware of current CPU cache line sizes and how to optimize around them.

I ran my tests on two 16-core Intel machines, one with a 64 byte cache line, and one with 128 byte cache line. First off, how did I find these values?

one:~$ cat /proc/cpuinfo | grep cache_alignment
cache_alignment : 64
...

two:~$ cat /proc/cpuinfo | grep cache_alignment
cache_alignment : 128
...

You will see one line for each CPU. If you are not familiar with /proc/cpuinfo, take a closer look at the full output. It’s a nice quick reference of other things like L2 cache sizes and CPU speed. As you can see, machine one has a 64 byte cache size, and machine two has a 128 byte cache size. Next, I wrote the following C program to test concurrency:

cache_line.c

This program creates a global array of counter variables and runs a variable number of threads, where each thread increments it’s own 4-byte counter in the array. It does so at a number of array spacing levels to see the performance when counters fall on the same cache lines. With a spacing of 1 the memory is directly adjacent, and for each spacing level it skips that many counter variables in the global array. For example, if spacing is 4, the threads would use counter[0], counter[4], counter[8], and so on, which uses a chunk of memory every 16 bytes. The cache_line.c program outputs a CSV formatted table that you can use to generate some graphs. The seconds CSV output is the same set of tests without using the global array counters, and instead a local counter on the stack. This is meant to provide a baseline (since those will always be on their own cache line). The results were:

So what does this tell us? When spacing is one and all counter memory (16 threads * 4 bytes == 64 bytes) is entirely on one cache line, concurrency is poor. As we add more space between each counter variable, we start to see performance improve (faster runtime). This is because all thread counters are no longer on one cache line. On the 64 byte cache line machine, we see things really level off when spacing is 16. This is because each counter is now on it’s own cache line. On the 128 byte cache line machine, you can see it takes one more iteration of spacing because the cache line is twice as big.

So what can we take from this? If you have any arrays or data structures that are accessed and updated independently from different threads, make sure they are on a different cache line. This may mean wasting a little space, but as you can see, the concurrency performance is well worth it.

Official Drizzle Blog: Gsoc kick off

Tue, 26 May 2009 19:39:41 +0000

Last month drizzle community was excited to be joined by 4 students as part of Gsoc. As the summer of code has already kicked off , its time to better know the students and take a look on what they will be working on this summer, with their mentors and drizzle community at large.

Padraig O'sullivan

Padraig O’Sullivan (posulliv) is a graduate student in Electrical and Computer Engineering department at university of maryland. Padraig would be working on re-implementing Information_Schema for drizzle, his plans to tackle the problem can be found at the project page on drizzle wiki. Jay pipes would be mentoring padraig during his time as SOC student with drizzle. Padraig is from ireland which explains his love for beer , before coming to US for post grad he was an intern at oracle dublin. Padraig is intrigued by scalable and distributed systems. Other than computers and code padraig likes to play guitar and drink beer. Padraig goes by the nick “posulliv” on twitter, IRC and launchpad.

biping meng (updated)

Biping Meng (ABi) is a final year computer science student at nanjing university, china. Biping will work on refactoring and streamlining the Pool of threads scheduler with Eric day and Jay Pipes as mentors. Abi is interested in search engine and natural language processing and to that effect he did his internship at Baidu which is a popular search engine in china. Biping likes to play table tennis and badminton but mostly likes to code, his team was among top 20 in microsoft’s imagine cup for developing a RTS game called Isles. He goes by the screenname llml on freenode and can also be found at launchpad.

jasekidd

Jiangfeng Peng (Jasekidd) is a computer science student at south china university of technology. Jasekidd would be researching and identifying points of parallelization in drizzle runtime. Jay pipes and Brian Aker would be mentoring Jasekidd during his work as Gsoc student. His major is high performance computing and multi threaded applications, in his spare time jasekidd likes to play badminton and basketball. He goes by the handle of jasepeng in IRC and launchpad. His project plans can be found at drizzle wiki.

No recent pictures of nathan

Nathan Williams (nlw) is a final year student at DigiPen institute of technology, his major is real time interactive simulation. Nathan will be working on refactoring and custom List<> implementation with STL’s list, vector and queue for drizzle under mentorship of Monty taylor. Nathan comes from computer network and security background, his passtimes are no different than his work time as he whiles away time programming and gazing at maths. He can be found by the screen name nlw both on IRC and freenode. His project progress would be logged at his wiki page. Sadly couldn’t grab his picture as he doesn’t have any recent clicks.

Being the first year in SOC, its pretty exciting that students have taken a liking to work on challenging problems; with a small community and friendly mailing list, I am sure this is going to be one of the most productive SOC year for even times to come. Drizzle being some 1000 revisions old and its problem solving plans would benefit a lot from SOC and students who have joined in. All the best to students and thanks to all mentors

Eric Day: Gearman and Drizzle at OSCON

Tue, 26 May 2009 18:08:46 +0000

If you missed Gearman or Drizzle at the MySQL Conference, have no fear, a number of folks will be at OSCON too! There will be a many opportunities to learn more or get involved with the two projects:

3-Hour Gearman Tutorial - Learn about the latest developments and participate in hands-on demos to help build your own Gearman-powered applications.
45-Minute Gearman Session - Get a more concise glimpse about what Gearman is and how to use it.
45-Minute Drizzle Panel - Get an update and ask questions about the project and community.

Gearman and Drizzle Booths - We’ll have a booth for each project in the expo hall with various developers helping out. Come visit to either help out or learn some new things! The expo hall is free.

Gearman and Drizzle BoFs - Not official yet, but there will be at least one BoF for each project throughout the week. This is free as well! Right now there are no specific topics, just general discussion and hacking.

If your are looking for deals on OSCON passes, there are some discount codes on their twitter feed. Also, it’s still early registration until June 2nd, so now would be a good time to register. :)

Hope to see you there!

Eric Day: Drizzle patch for Sphinx 0.9.9-rc2

Tue, 26 May 2009 04:38:43 +0000

Last week I blogged about a patch for Sphinx 0.9.8.1 that allowed Drizzle to be used as a data source (this works much like the MySQL and PostgreSQL options). This past weekend I made a new patch that would work with the latest version, 0.9.9-rc2. It can be found here:

sphinx-0.9.9-rc2-drizzle.patch

The instructions are pretty much the same:

Download sphinx-0.9.9-rc2.tar.gz and sphinx-0.9.9-rc2-drizzle.patch
tar xzf sphinx-0.9.9-rc2.tar.gz
cd sphinx-0.9.9-rc2
patch -p1  ../sphinx-0.9.9-rc2-drizzle.patch
./buildconf.sh
./configure --with-drizzle
make
make install

See my previous blog post for configuration changes to make Sphinx index from Drizzle.

Official Drizzle Blog: Drizzle source tarball 1033 released

Tue, 26 May 2009 00:13:13 +0000

Drizzle source tarball based on build 1033 has now been released. We are also now including a change log so you can get a sense of what has gone into each release. For this release we continue to focus on code clean up, build improvements, increased test coverage and performance improvements. This release also now provides support for protobuf 2.1

Trond Norbye: Manage Gearmand and Drizzle with SMF

Mon, 25 May 2009 16:07:06 +0000

If you are running Gearman or Drizzle on Solaris, you may want to let SMF start and monitor the services. I just pushed service definitions and and scripts to install them a couple of days ago.

If you look in the scripts directory in Gearman, you will see a script named smf_install.sh. If you run this script it will define a user and group named gearmand, create the Gearman authorizations and profile before a service named gearman is defined. To start the Gearman service all you need to do is to run:

trond@storm ~> svcadm enable gearman

For Drizzle you need to look in the support-files/smf directory for a script named install.sh. To start Drizzle all you need to do is to run:

trond@storm ~> svcadm enable drizzle

Official Drizzle Blog: Contributor Code Walk Through

Mon, 25 May 2009 15:00:09 +0000

Our esteemed Jay Pipes will conduct the first Drizzle contributor code walk-through and build/install guide on June 4th at 4:00 p.m. PST (7:00 p.m. EST). He will go over the basics of using Bazaar and Launchpad for the contribution process, this will include:

How Drizzle’s source code is organized
The build, debugging and testing toolset
Quick How-To for using Launchpad and Bazaar effectively
Overview of how the source code is structured (different modules, etc)
Details about some of the larger structures and classes used in Drizzle and how they work together
Explanation of how to create a skeleton plugin and test cases for your plugin
Q&A

Look for the slides up on DimDim.com soon. The presentation will also be recorded for others to view at a later time. Jay will post instructions for DimDim and the presentation as we get closer to the date next week.

Toru Maesaka: Storage Engine Dev Journal #2 : Command Line Options

Fri, 22 May 2009 09:27:10 +0000

If you’re working on developing a Drizzle plugin, you may come across situations where you want to accept user options for it at server startup. For example, if you design your plugin to create files for activity logging, you may want to allow the DBA to specify where to write those files out.

In my case, I decided to provide a command line option to BlitzDB for row based query caching. This option is intended for special use-cases where the read/write ratio is 9:1. For those that are interested, row caching is disabled by default because it creates overhead in the engine for read-through logic and cache invalidation _unless_ read requests are significantly higher than update requests.

There are situations where BlitzDB’s row cache can be helpful but this is beyond the scope of this entry so I will save it for another day

Adding startup options to your plugin

Drizzle allows you to add command line options to your plugin without editing the server code. But before you start hacking away, there are few not-so-obvious things that you need to understand.

So, let us first look at the data types that your plugin can accept:

DRIZZLE_SYSVAR_BOOL
DRIZZLE_SYSVAR_STR
DRIZZLE_SYSVAR_INT
DRIZZLE_SYSVAR_UINT
DRIZZLE_SYSVAR_LONG
DRIZZLE_SYSVAR_ULONG
DRIZZLE_SYSVAR_LONGLONG
DRIZZLE_SYSVAR_ULONGLONG
DRIZZLE_SYSVAR_ENUM

As you can see, there is a wide range of types that you can choose from. What you should choose depends on what you want to use the value for.

Pick your data type

So lets take my row cache option as an example. Caching over 4 billion rows in one physical server is very unlikely and since we’re not interested in negative numbers, we’re going to pick:

DRIZZLE_SYSVAR_UINT

which we can store the value as uint32_t in the plugin.

Declare that your plugin accepts options

Every plugin must declare itself as a plugin which looks like this for BlitzDB:

drizzle_declare_plugin(blitz) {
  "BLITZ",
  "0.3",
  "Toru Maesaka",
  "Non-transactional General Purpose Engine",
  PLUGIN_LICENSE_GPL,
  blitz_init,             /*  Plugin Init      */
  blitz_deinit,           /*  Plugin Deinit    */
  NULL,                   /*  status variables */
  blitz_system_variables, /*  system variables */
  NULL                    /*  config options   */
}
drizzle_declare_plugin_end;

Here, we’re interested in the second last argument which is called blitz_system_variables in the above example. Feel free to call this whatever you like for your plugin.

So what exactly is blitz_system_variables? Its a null-terminated array of system variables that your plugin accepts. This is what it looks like for BlitzDB:

static struct st_mysql_sys_var *blitz_system_variables[] = { 
  DRIZZLE_SYSVAR(row_cache),
  NULL
};

As you can see, BlitzDB only supports one option at the moment so there is only one entry called row_cache.

Define your options

You must define every option that you’ve added to the system variable array. We decided to use DRIZZLE_SYSVAR_UINT earlier and called it row_cache so it is defined like this:

static DRIZZLE_SYSVAR_UINT (
  row_cache, /* option name */
  blitz_row_cache_size, /* variable to set the value to */
  PLUGIN_VAR_READONLY, /* mode */
  N_("Enable row caching for BlitzDB tables."),
  NULL,       /*  check func    */
  NULL,       /*  update func   */
  0,          /*  default value */
  0,          /*  minimum value */
  UINT32_MAX, /*  maximum value */
  0           /*  block size    */
);

The comments pretty much explains what the arguments are but for more details, you should take a look at the macros in drizzled/plugin.h. You could also look at what other plugins do by grepping for the system variable type that you’re interested in.

Test your new startup option

If all goes well you should be able to compile Drizzle and check whether command line options are visible from the plugin. An option takes the following form:

--<name_of_plugin>-<option_name>

So, in the row cache example, row cache can be enabled like this:

/usr/local/sbin/drizzled --blitz-row_cache=10000

Also note that you can replace the underscore with a hyphen:

/usr/local/sbin/drizzled --blitz-row-cache=10000

That’s it! it should be relatively easy to add more options once you successfully get your first one done.

MySQL Performance Blog: Open Development vs Making a Big Splash

Fri, 22 May 2009 05:39:33 +0000

I find it very interesting how Sun does not get the very basic principle of true community Open Source development - you've got to give up on making a big splash.

Traditional close source company often develop product in the secret and when it comes out as a surprise for computers and making a big splash for the users. Does it remind you something ? Yes! this is exactly how Innodb Plugin was released last year or MySQL 5.4 performance improvements this year.

Community did not know about them and did not participate early in this efforts.

Another big splash which seems to be planned later this year is "Performance Schema" - which is in development for years as this post claims but to date there is no code for community to play with

I believe if you want to learn true community respect, you've got to respect (and so involve) community. You should show your early and buggy code and let community play with it and complain. This is true Open Source development and it takes guts.

I should praise Drizzle for having code available from very early days. I should praise Monty for having Maria storage engine tree available from its early and buggy days. I should praise Falcon and PBXT teams which were not shy to show us multiple redesigns they had to go through. This earns my respect and this is how I think Open Source development should be done.

At Percona I think we're doing relatively well - our trees are public and lauchpad and I do not think we had ever a project which was cooking internally for more than a month. As soon as we got something which was interesting enough to start playing, such as Xtrabackup we got it out, just stating this is very early code.

Google does not get very many points on this one - I think they are very obsessed with "everything Google does must be great" policies which prevents them from releasing code before it is complete and properly tested at least internally. Though I think Google do not position themselves as doing Open Source development but rather being kind enough to publicly share the development which is done internally. This is fair and I appreciate it.

So why Sun and Oracle are after big splashes ? Well I think this is the only way tradition companies know how to market the stuff. If project starts from single line of code and growths day by day to completion it is very hard to build excitement around it. There is no big news just a slow process.

I would argue however releasing certain code as Stable/GA should be enough of event for masses - it is not however enough for community which see it just as minor incremental development from Initial Alpha/Beta releases.

Entry posted by peter | 28 comments

Add to: | | | |

Brian Aker: Libmemcached, BZR, Launchpad

Wed, 20 May 2009 18:43:37 +0000

Today I moved from using Mercurial to using bzr on Launchpad for libmemcached.

Why BZR?

I use Launchpad for pretty much all of my projects at this point. I have been really happy with it, and I have found that bzr works well for Linux/Windows/Mac. Drizzle, Gearman and others are already there so this just simplifies my daily workflow.

This should help with me being able to take patches a bit more quickly (and for that matter do reviews). Having contributors push their patches to their own trees, allows me to easily pull patches and do reviews. With Drizzle we keep a "staging" tree just so that we can regression test any code before it goes to trunk. Since code in Drizzle goes through several people before I see it, we each get to make revisions on it and look for issues like copyright violations/etc. By tracking code by Launchpad account, we always know who code came from.

For libmemcached I will also be enabling the bug system on Launchpad. While tracking via email has worked, I would like to come up with something a bit more formal.

I've been using Laundpad for over a year now and I am really happy with it. Career wise I have used several proprietary systems, of which Bitkeeper was the best, and several open source ones, of which Subversion was the worst (it was the only system that ever lost code).

All of the distributed revision control systems are pretty good now. I've used all of the major ones and have not really had many issues. The reason I go with bzr really has to do with workflow with Launchpad.

Mercurial is great, and I will continue to use it for a lot of personal projects. It is the only open source distributed revision control system that I find to be easy to host. Its ability to do push/pull from HTTPS is a major asset for me. So for work I do where I need to continue to host the data I will be sticking with it. I completely understand why Google went with it, and I really wish BZR would add this feature.

Looking back over the last few years it is amazing to see how far the revision source control system have gone. Just a few years ago all of the tools were garbage and it was a nightmare to pick one.

Thankfully we are well past that.

Eric Day: Gearman, Now With Persistent Queues

Wed, 20 May 2009 08:52:33 +0000

I’m pleased to announce version 0.6 of the Gearman C server and library. The major new feature of this release is a pluggable persistent queue for the job server. It comes bundled with a libdrizzle module (so your queue can live in Drizzle or MySQL), but Brian has already written a libmemcached module and there is a flat-file module in the works as well. The persistent queue allows background jobs to be stored via the pluggable module, so if the job server crashes or is shutdown, the queue module can repopulate the job server with any jobs that were not yet complete. This is just the first version of the queue support, so expect more modules and features in the future!

On a related note, James Luedke has also released version 0.3 of the Gearman PHP Extension. This project now lives in the PHP PECL repository (where it should), so be sure to look there for downloads and the latest source.

Brian Aker: Libmemcached Version 0.29 Released

Tue, 19 May 2009 15:54:49 +0000

We have released version 0.29 of libmemcached.

The highlights:
* Fixed malloc usage to calloc for spots where we need zero filled memory.
* All code warnings now treated as errors.
* Fixes for debian packaging.
* Added new pooling mechanism.
* MEMCACHED_BEHAVIOR_NO_BLOCK no longer also sets MEMCACHED_BEHAVIOR_BUFFER_REQUESTS.
* Updated generic rpm.

The new pooling mechanism is thread safe so if you have been looking for a way to limit your number of memcachd_st structures and you use threads you are now set!
This work was done to improve the connection handling in Drizzle for Memcachd (and it is going into the UDF for MySQL as well).

You can find out more information here:
http://tangent.org/552/libmemcached.html

Eric Day: Gearman UDF now in Drizzle

Fri, 15 May 2009 19:17:00 +0000

If you’ve pulled the latest Drizzle code from lp:drizzle, you may have noticed a new plugin/gearman_udf directory in there. This is a new UDF that acts as a Gearman client. This is mostly a port of the Gearman MySQL UDF I wrote, but I did it the proper C++ way to fit in better with Drizzle. It also uses the new plugin system Monty Taylor has been working in, which makes it much easier. :)

To use it, just make sure you have the Gearman C library installed and Drizzle will pick it up and build it for you. No extra configuration required!

The following example assumes you have a Gearman job server and a reverse worker running (see examples/reverse_worker in the C library package).

drizzle> SELECT gman_servers_set("127.0.0.1");
+-------------------------------+
| gman_servers_set("127.0.0.1") |
+-------------------------------+
| 127.0.0.1                     |
+-------------------------------+
1 row in set (0 sec)

drizzle> SELECT gman_do("reverse", table_name) FROM information_schema.tables LIMIT 3;
+---------------------------------------+
| gman_do("reverse", table_name)        |
+---------------------------------------+
| STES_RETCARAHC                        |
| SNOITALLOC                            |
| YTILIBACILPPA_TES_RETCARAHC_NOITALLOC |
+---------------------------------------+
3 rows in set (0 sec)

Stewart Smith: Size of Storage Engines

Fri, 15 May 2009 05:33:00 +0000

For whatever reason, let’s look at “Total Physical Source Lines of Code” from a recent mysql-6.0 tree (and PBXT from PBXT source repo):

See the spreadsheet here.

Raw data:

Blackhole        336
CSV             1143
Archive         2960
MyISAM         34019
PBXT           41732
Maria          69019
InnoDB         82557
Falcon         91158
NDB           365272

NDB has a 100,000 line test suite.

PBXT supports MySQL and Drizzle.

Conclusions to draw? Err… none really.

Eric Day: Sphinx Now Indexing from Drizzle

Thu, 14 May 2009 22:28:46 +0000

In preparation for an upcoming demo, we decided to use Sphinx for the full text search. We want to do this with Drizzle but hit an obvious problem - Sphinx does not know how to speak to Drizzle to grab documents for indexing. At least that was the case until last night when I created a patch to do so using the new libdrizzle library.

You can download the patch here: sphinx-0.9.8.1-drizzle.patch

I’m releasing this patch BSD, so do with it what you want. I’m hoping Andrew (the Sphinx author) will include this in the main package in an upcoming release. :)

So for now, to get things working, we first need to patch the sphinx-0.9.8.1 source and build/install:

Download sphinx-0.9.8.1.tar.gz and sphinx-0.9.8.1-drizzle.patch
tar xzf sphinx-0.9.8.1.tar.gz
cd sphinx-0.9.8.1
patch -p1  ../sphinx-0.9.8.1-drizzle.patch
sh bootstrap
./configure --with-drizzle
make
make install

To show this in action, start up drizzled (see the Drizzle wiki for details), and create a database named ‘test’. Then load the sample data included with Sphinx (assuming /usr/local for install):

drizzle  /usr/local/etc/example.sql

Next we need to configure Sphinx to pull documents from Drizzle in the sphinx.conf. To do this, copy the sphinx.conf.dist to sphinx.conf and modify it:

cp /usr/local/etc/sphinx.conf.dist /usr/local/etc/sphinx.conf
vi /usr/local/etc/sphinx.conf
diff /usr/local/etc/sphinx.conf.dist /usr/local/etc/sphinx.conf
17c17
       type                                    = mysql
---
>       type                                    = drizzle
28c28
       sql_port                                = 3306  # optional, default is 3306
---
> #     sql_port                                = 3306  # optional, default is 3306

We switch the source type to drizzle and comment out the port so it uses the default Drizzle port (4427). Now with the DB loaded with sample documents and Sphinx configured, let’s test it:

> indexer test1
Sphinx 0.9.8.1-release (r1533)
Copyright (c) 2001-2008, Andrew Aksyonoff

using config file '/usr/local/etc/sphinx.conf'...
indexing index 'test1'...
collected 4 docs, 0.0 MB
sorted 0.0 Mhits, 100.0% done
total 4 docs, 193 bytes
total 0.010 sec, 19300.00 bytes/sec, 400.00 docs/sec

> search -i test1 document
Sphinx 0.9.8.1-release (r1533)
Copyright (c) 2001-2008, Andrew Aksyonoff

using config file '/usr/local/etc/sphinx.conf'...
index 'test1': query 'document ': returned 2 matches of 2 total in 0.000 sec

displaying matches:
1. document=1, weight=1, group_id=1, date_added=Thu May 14 13:20:35 2009
2. document=2, weight=1, group_id=1, date_added=Thu May 14 13:20:35 2009

words:
1. 'document': 2 documents, 2 hits

We still need to get the Sphinx search engine ported to Drizzle, but now we can at least index from Drizzle and use one of the Sphinx APIs to search from our applications. With the returned document IDs we can then ask Drizzle (or memcached if we cache them there) for the URLs, documents, or any other data. Let me know if you have any problems getting it running, the fastest way to reach me is ‘eday’ on irc.freenode.net (/msg or /join #drizzle).

Michael Shadle: Porting WordPress to Drizzle - Will I Succeed?

Thu, 14 May 2009 08:38:41 +0000

Since I can't write C to save my life, I'm attempting to help out in other ways. This has been in the form of donating machine time, helping with the wiki, blog and being a general smartass on #drizzle.

However, where I can provide some help is trying to showcase and see how apps may need to change to work with Drizzle as opposed to MySQL.

I will admit, this may go nowhere as I am consistently swamped with work ~~and a busy social life~~, but it might be something fun to mess around with to get used to working with Drizzle and possibly come up with something to give back to the community.

I don't expect a huge amount of changes. I think some column types might need to change and definately the DB class (I will be using the native drizzle-php-ext module, even though I believe the mysql/mysqli/etc. modules may work transparently already) - so we shall see.

So wish me luck!