The latest from the MySQL community

Understanding MySQL Internals

Understanding MySQL Internals. By Sasha Pachev, O’Reilly 2007. Page count: about 227 pages. (Here’s a link to the publisher’s site).

I should have read this book a long time ago, and it’s my loss that I didn’t. Although the title makes it sound like it should only benefit those who’ll be changing the MySQL server’s own code, that’s not true. To the contrary, at least parts of this book should be required reading for DBAs and developers who use MySQL, after they gain a moderate level of familiarity with how to use the server.

The book does indeed start off …

I explored two interesting topics today while learning more about Postgres.

Partial page writes

PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Trying to reduce the cost of full-page writes by increasing the checkpoint interval highlights a compromise. …

Many times I’ve heard people advise on “best practices” for a MySQL database. This often includes routine maintenance, such as “you should run OPTIMIZE TABLE on all of your InnoDB tables once a week to defragment them for better performance.”

But this advice is unsubstantiated and could even be detrimental. Here are some of the obvious problems that I can think of:

• The optimized table compacts the primary key (clustered index) to its default 15/16ths fill factor per page. But other indexes will be built in pseudo-random order and are likely to end up just as fragmented afterwards as before. Which indexes are more important for performance? Maybe the primary key is just a dummy value that’s not even used, and the secondary indexes are the ones that would benefit from compacting.
• Suppose the primary key is the important one, and SELECT queries will perform more quickly if it’s defragmented. Why does it …

InfiniDB has a sensible Enterprise/Community release process, which seems similar to what I suggested for MySQL. Its simplicity also stands in stark contrast to MySQL’s new release policy, which is hard to understand and has been confusing people.

Related posts:

1. Thank you for the MySQL 5.4 Community Release MySQL 5.4
2. …

Bernhard blogged over at http://ocklin.blogspot.com/2010/02/mysql-cluster-711-is-there.html that MySQL Cluster 7.1.1 Beta has been released. The big feature (from my point of view) is the SQL interface on top of NDB$INFO. This means there is now full infrastructure from the NDB data nodes right out to SQL in the MySQL Server for adding monitoring to any bit of the internals of the data nodes.

Yes, Maatkit’s name was inspired by Ma’at, which has an apostrophe. But there is NO apostrophe in the name of the popular toolkit for MySQL users.

I’m just sayin’.

Related posts:

1. Learn about Maatkit at the MySQL Conference I’m
2. Speaking about Maatkit at CPOSC I’m
3. Making Maatkit more Open Source one step at a time If you

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

I’m on the voting committee of the MySQL Conference this year. I was on the same committee in 2008. This year’s submissions are awesome. Much different from previous years, for whatever reason.

Some of the proposals are sales pitches, infomercials, or just generally BS, but there’s a gang of sharp-eyed people on the committee who are digging into who submits the proposals, what their company does, etc. Those sessions are getting shot down with bluntly honest reviews and low votes. I have a pretty good reason to believe those votes will carry a lot of weight. But even if they don’t, there simply aren’t many bad proposals! The overwhelming majority of them are very useful technical proposals from people who really know what they are talking about.

This year I truly agree that this is going to be a “deeply technical” event, as it has been …

I’d like an UNTIL clause, please. I’d use it sort of like LIMIT in MySQL and PostgreSQL, except that it would define when to stop returning looking for rows, instead of defining how many to return. Example:

SELECT * FROM users ORDER BY user_id UNTIL user_id >= 100;

That would select users up to and including user 99. Ideally the clause could accept any boolean predicate, including subqueries. I’ll hold my breath and wait for this wish to come true now.

No related posts.

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

Get it Done with MySQL 5&6

Get it Done with MySQL 5&6. By Peter Brawley and Arthur Fuller. Self-published, 2009. Page count: about 615 pages. I asked the authors for a print edition to review, but it is also available as an e-book.

The right word to describe this book falls somewhere between “tome” and “lunker.” You could beat back an unwelcome salesperson with it. You could also beat back any number of stubborn database problems. It’s kind of like a MySQL Manual plus a ton of practical how-to-use-MySQL information.

This is an unusual book in that it is useful for a very broad audience. I’d say you can get a lot out of it if you are a) new to databases b) new to MySQL c) in need of reference material d) looking for practical examples of installing or using MySQL e) trying to …

If you have recently downloaded or checked out a new version of Kontrollbase and find that you need to upgrade the schema from a previous version then you will probably find this post useful. If you don’t know if you need to upgrade then you probably do, so read along for a bit just to [...]