Xaprb

I wish I could go to PGWest this year. A lot of great work has been done on PostgreSQL in the last year and a half. There’s a new release with built-in replication, and there are in-place upgrades. That solves two of what I think are its three biggest shortcomings in many large-scale database deployments. (Lack of index-only queries is the third shortcoming.)

PGWest is the first major conference about Postgres since the 9.0 release, so all the cool stuff is happening there. I encourage MySQL users to go to it as well — there is a lot to learn from PostgreSQL. Just remember to be polite and don’t start, encourage, or tolerate any sniping between MySQL and Postgres fans.

MySQL 5.1 Plugin Development

MySQL 5.1 Plugin Development, by Sergei Golubchik and Andrew Hutchings, Packt 2010. About 250 pages. (Here’s a link to the publisher’s site.)

This book is well worth reading for anyone interested in MySQL internals. I learned a lot from it. It is well-written and understandable. I cannot say that I’m planning to write storage engines or more advanced plugins, but I have a great many ideas how to improve MySQL, and I now understand more clearly which of those are suitable to write as plugins, and of what type of plugin is appropriate. I also think I have a better idea how much work these various ideas might involve.

The book begins with an orientation to building plugins on various platforms. Next it covers user-defined functions (UDFs). I have written UDFs, but that’s as far as I have gone with MySQL plugins. The rest of the book covers Daemon plugins, INFORMATION_SCHEMA plugins, full-text parser plugins, and storage engine plugins from basic to advanced. The last example is a nearly complete storage engine built on Tokyo Cabinet, with some pretty advanced functionality. It finishes with a quick overview of the types of plugins available in development and future versions of MySQL, and what’s possible in MariaDB.

The examples are full code listings, with paragraphs of text alternating with a few lines of code. It’s like reading a really well-commented C program, like reading InnoDB source, but with even more explanations. You can download everything you need to build and run the examples yourself — even the sample images used for demonstrating full-text search of EXIF data.

I enjoyed reading about what’s possible in MariaDB. I had not kept up-to-date with the work that’s being done there. If I were a storage engine developer, I’m sure I would appreciate what MariaDB has done. I would speculate that many of the people who’ve written in-house custom storage engines for their own businesses might find MariaDB interesting.

I think that anyone who is planning to modify the MySQL source code should read this book. It could save a lot of work and show easier ways to do things. I learned a lot about the MySQL source code that I have not gotten from other places. This one will go onto my list of essential books for MySQL users.

I’ve written a tool to profile a process’s IO usage. It works by gathering lsof and strace from a process, and then figuring out how the file descriptors, function calls, and filenames are all related to each other. The manual page has examples. I’m curious to see how it works for you. Note that it might be a good idea to run this on your development or staging system before you go running it against your production MySQL server — there are rumors of strace misbehaving on some kernels.