InnoDB’s checkpoint algorithm is not well documented. It is too complex to explain in even a long blog post, because to understand checkpoints, you need to understand a lot of other things that InnoDB does. I hope that explaining how InnoDB does checkpoints in high-level terms, with simplifications, will be helpful. A lot of the simplifications are because I do not want to explain the complexities of how the simple rules can be tweaked for optimization purposes, while not violating the ACID guarantees they enforce.

A bit of background: Gray and Reuter’s classic text on transaction processing introduced two types of checkpoints beginning on page 605. There is a sharp checkpoint, and there is a fuzzy checkpoint.

A sharp checkpoint is accomplished by flushing all modified pages for committed transactions to disk, and …

I’ll be presenting several sessions at the O’Reilly MySQL Conference & Expo 2011, which is April 11-14 in Santa Clara, California. I recommend this conference to anyone interested in open-source databases including MySQL, PostgreSQL, CouchDB, MongoDB, and others. There is very good coverage of a diverse list of open-source databases.

My sessions are as follows:

• Building on Strengths, Learning from Differences: a keynote address on the open-source database ecosystem, how we got here, and what we can do to make the most of the future.
• Forecasting MySQL Performance and Scalability: mathematical models for forecasting performance and …

WHAT TIME IS IT?

This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

• CURDATE() returns the current date.
• CURTIME() returns the current time.
• UTC_DATE() returns the current UTC date.
• UTC_TIME() returns the current UTC time.
• NOW() return the current date and time.
• UTC_TIMESTAMP() returns the current UTC date and time.
• SYSDATE() returns the date and …

I’ve released version 1.1.8 of the Better Cacti Templates project. This release includes a bunch of bug fixes and several important new graphs. There are graphs for the new response-time statistics exposed in Percona Server, and a new set of graphs for MongoDB.

There are upgrade instructions on the project wiki for this and all releases. There is also a comprehensive tutorial on …

I read a recent blog post about the coming extinction of MySQL bloggers with concern. The post plotted the blogging activity of Planet MySQL bloggers and fit a polynomial curve to it:

Blogging Activity

The curve isn’t extended out, but it’s a polynomial, and we know what happens: it’s going to intersect the X-axis. I didn’t do the math, but if you want to, you can predict, to the day, when blogging as we know it will cease.

I couldn’t help thinking: what else can we fit a curve to? The stock market? — no, that’s heavy stuff. How about the hours of daylight this year? There can’t be any harm in that, can there? So I went to an online hours-of-daylight calculator and got the sunrise and sunset times for the first 60 days of the year. I plotted the result in gnuplot and fit a polynomial to it. The X-axis is the …

I wish I could be at this: Percona Live San Francisco. It is on February 16th. Cost is minimal, and content is maximal. The topic is on what’s new in MySQL these days beyond 5.1: the new 5.5 release, HandlerSocket, MariaDB, and of course Percona Server.

This is a follow-up to the enormously successful Percona Performance Conference in 2009.

Related posts:

1. Sessions of interest at the Percona Performance Conference
2. Schedule for MySQL-and-beyond conference is live
3. …

This looks like fun: Ruby on Ales. It’s March 24-25, 2011 in Bend Oregon (USA), and the tag line is Ruby, snow, and beer. Does it get any better than this?

Related posts:

1. My sessions at the O’Reilly MySQL Conference 2011
2. O’Reilly MySQL 2011 conference CfP is open
3. Controlling Maatkit from Ruby
4. Postgres folks, consider the 2011 MySQL conference
…

This isn’t actually new — it has been out for a few releases. The mk-query-digest tool from Maatkit now outputs information about each class of queries’ variance-to-mean ratio. The new output goes in a couple of places, including perhaps most usefully the “profile” report. Here’s an example from a real MySQL system:

# Profile
# Rank Query ID           Response time    Calls R/Call Apdx V/M   Item
# ==== ================== ================ ===== ====== ==== ===== =======
#    1 0xBFCF8E3F293F6466 11256.3618 68.1% 78069 0.1442 1.00  0.21 SELECT [redacted]
#    2 0x620B8CAB2B1C76EC  2029.4730 12.3% 14415 0.1408 1.00  0.21 SELECT [redacted]
#    3 0xB90978440CC11CC7  1345.3445  8.1%  3520 0.3822 1.00  0.00 SHOW STATUS
#    4 0xCB73D6B5B031B4CF  1341.6432  8.1%  3509 0.3823 1.00  0.00 SHOW STATUS
# MISC 0xMISC               560.7556  3.4% 23930 0.0234   NS   0.0 <17 …

I just received a confirmation that my presentation proposal for the MySQL user conference 2011 was accepted! The title for my proposal is MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications, and it covers pretty much everything implied by the title.

As always, the Hyatt Regency Hotel in Santa Clara, California serves as the venue. The conference will be held from April 11-14. Except for the venue and period, I think this year's conference will bear few similarities to previous editions. Let me try and explain.

This year's theme is "MySQL, the ecosystem and …

One of the query optimization scenarios I’ve seen a lot over the years is finding something within some distance from a point. For example, finding people within some distance of yourself, apartments in a radius from a postal code, and so on.

These queries usually use the great-circle formula. That might be because Google finds lots of pages claiming that this is the right way to do a radius search. “The earth is not flat!”, they all say. That’s true, but it doesn’t mean that the great-circle formula is a good approach. It’s usually a really bad approach, in fact. It’s needlessly precise for most things, not precise enough for others, and it’s an expensive query to execute; all the trig functions tend to eat a bunch of CPU, and make it impossible to use ordinary indexes. This is true for all of the databases I’ve used — MySQL, Postgres, and SQL Server.

The great-circle formula is needlessly precise for a few …