The latest from the MySQL community

Last week I wrote about my efforts to measure MySQL’s replication speed precisely. The most important ingredient in that recipe was the user-defined function to get the system time with microsecond precision. This post is about that function, which turned out to be surprisingly easy to write. The manual section on user-defined functions provides very good instructions on how they work and how to build them. But just for the record, on Ubuntu 7.

In the spirit of the MySQL Pluggable Storage Engines the proxy has a plugin infrastructure now.

The main goal was to cleanup the code so what the admin part and the proxy itself can be loaded at runtime.

The new code-layout looks like

• mysql-proxy.c (codename: the cauldron)
  • takes care of the command line options
  • signal handling
  • win32 service handling (not done yet)
• libmysql-proxy.so
  • provides the mainloop and everything for the socket and protocol handling
• libadmin.so
  • the old admin interface
• libproxy.so
  • the well known proxy itself

Now you can plugin your own modules if you like and only load those modules that you really want.

The next step is making the …

These days everyone is looking for a MySQL DBA or MySQL Architect. I am regularly contacted by recruiters, Proven Scaling customers, and other contacts, and they all have the same question: “Where do we find MySQL people to hire?” Most of them have had requisitions open for 6+ months (I know of a few in the 12+ month range), they haven’t found anyone, and they’re feeling desperate now. Since I get this question so often, I thought I’d consolidate my advice on the subject and post it.

They don’t exist on the market today.

Currently there are many more job openings for MySQL people than there are qualified people to fill them. Many of you reading this and trying to hire someone are working for startups and are probably relatively “unknown”, perhaps you don’t have a lot to offer. This makes it even harder for you, as you must compete …

There was an article this week in InfoWeek entitled How Linux is Testing the Limits of Open Source Development which really hit home. In many ways, it's reflecting some of the issues that our much smaller opentaps Open Source ERP + CRM project is going through as well, and it made me realize that there may be a natural, self-correcting mechanism which limits the growth of open source projects.

Let me give you an example of how I think this works: Imagine a really smart developer sat down and started an open source project. She starts writing code, and pretty soon the software is useful. More and more people download it, and soon some other contributors/developers show up. The rate of development increases, and more features show up, leading (hopefully) to more developers. …

It’s been a long time since we’ve started this project and it is time to make a checkpoint. So, I’ve decided to release final 1.0 version and make 1.X branch stable while all serious development with deep architectural changes will be done 2.X branch (trunk at this moment).

Changes from previous release:

• Perl semaphores implementation caused huge memory leaks (mmmd_mod).
• Now we do not send any commands to hard offline hosts with dead TCP/IP stack to prevent mointoring problems for other hosts.
• Removed legacy StartSlave method from agent code which caused problems on some Perl versions
• Added a few fixes to prevent non-exclusive roles from moving. This caused internal status structures to …

I’m noticing some trends of late, as I go through the proposals for the MySQL Conference & Expo 2008.

Rails: has it lost its steam? I’d like to see some talk submissions for Ruby and Ruby on Rails users, clearly. This stuff is still hot on the web (look at Twitter, and Dopplr, for instance) and there’s lots more out there.

I’m recommending a lot of people work on talks together. I don’t know if this will happen, but we have mashups in this web 2.0 world, I don’t see why we don’t have talk mashups with 2 presenters. Having a co-presenter not only keeps your talk real, but keeps the momentum going (especially when your talk is scheduled early in the morning or after …

How come performance degrade with more data nodes?
Some of you might have noticed that performance might actually drop slightly when you move from a two node cluster to a four node cluster. To understand this we need to understand how information is and tables are organized in MySQL Cluster. Moreover, I will also mention a bit on distribution awareness, a feature that can be used to minimze this performance drop in bigger configurations.

In MySQL Cluster a table divided into fragments and spread on the data nodes.
You have as many fragments (Fx) as you have data nodes (Nx).
If you have four data nodes, then a table will have four fragments:

Table T1= {F1, F2, F3, F4}

These fragments will be laid out on the data nodes as follows:

N1=F1P
N2=F2P
N3=F3P
N4=F4P

I have written FxP, where P indicates the Primary fragment and potential …

Typically a backup or dump of a MySQL server includes all of the databases available, using the -A or –all-databases options. But what of restoring, and recovering that dump?

One can simply go to the target machine, and delete everything in the data directory, right? Oops, you didn’t delete the initial MySQL database did you? How about the special “information_schema”, MySQL’s data dictionary? The other option of course is to use mysqladmin:

SQL> mysqladmin -f -u root -p drop mydatabase

But still I’ve had cases where I’ve dropped parts or all of these initial MySQL databases. So what to do if you do?

Luckily MySQL comes with a shell script to save you in just such cases. It’s called mysql_install_db and can typically be found in /usr/bin. For Oracle folks you can almost think of this like the catalog.sql which in turn runs the sql.bsq file. It is illustrative to take a look at this shell …

A recent thread on SQLServerCentral.com had an individual what it took to be a successful DBA. Couple that with a RunAs Radio podcast with SQL Server MVP Brad McGehee on Being a Better DBA and that raises the question, "What does it take to become a DBA?"

I came at being a DBA from a round about route. I was first a developer, became a system administrator, went back to being a developer, and then finally became a DBA. While I primarily work directory services and architecture now, I've worked to keep my DBA skill set, because being a DBA is something that I loved doing and still love when I get a chance to dive back in full throttle. Therefore, I thought I would offer some thoughts on what I think it …

Suppose you want to remove auto_increment from 100G table. No matter if it's InnoDB or MyISAM, you'd usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to complete. If you're unlucky i.e. you have a lot of indexes and not too much RAM - you could end up waiting days. If you want to make this happen quick - there's another way. Not documented, but works well with both - InnoDB and MyISAM.

Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes more of an issue. One of the steps here is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild. …