I just uploaded the pdf of the summer issue of Open Source Database Magazine. Included in this issue:

• A great article on the new features of Postgresql 8.4 by Robert Treat
• Part one of a two part article on Percona’s new XtraBackup backup program

Also the news, the book shelf and Peter Brawley’s Coding Corner. Downloads are available at http://www.osdbzine.net.

Sunday morning, in the wee hours of the morning, while packing, I discovered that I didn't know where either my DL or my Passport is. After spending an hour searching, I gave up, and decided to throw myself on the mercy of the TSA. Which I did at the airport. The TSA supervisor checked my credit cards, FlyClear card, corporate ID card, and CostCo card, and then stamped my boarding pass. I only need to do this 3 more times on this trip, and then do a very deep search of my room when I get back.

After I landed at SJC, I discovered that Tim Lord has shared the flight with me. If I had known that ahead of time, I would have let him share my taxi ride from Capitol Hill neighborhood to the SEA airport. As it was, I let him share my ride to San Jose Convention Center. I was actually too early to check into my hotel room. So instead I checked in my luggage, and then went off to find the Postgres Day.

Meeting up with open source …

In the spirit of the Good Practice/Bad Practice series, this post is – apart from my first as contributing engineer at Open Query – a basic level application tip. More advanced application developers will already be aware of the issue.

It is easy to overlook certain “non-deterministic” functions in your queries which will prevent the query cache from caching its results. Common examples include NOW(), CURDATE(), and RAND() (here is the complete list).

Obviously when RAND() is used, you usually don’t expect the same result if the query is repeated, so this isn’t a concern. But other cases, particularly date/time related queries, can actually benefit from the cache, and there is a simple workaround. When the query cache is able to satisfy a query, the overhead (in latency and CPU) of parsing and planning the query is …

There were rumors …

It’s been a while since I’ve posted my first post about the way we do document pages caching in Scribd and this approach has definitely proven to be really effective since then. In the second post of this series I’d like to explain how we handle our complex document URLs and logged in users in the caching architecture.

First of all, let’s take a look at a typical Scribd’s document URL: http://www.scribd.com/doc/1/Improved-Statistical-Test.

As we can see, it consists of a document-specific part (/doc/1) and a non-unique human-readable slug part (/Improved-Statistical-Test). When a user comes to the site with a wrong slug in the document URL, we need to make sure we send the user to the correct …

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

• Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root …

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

• We have 2 Servers in a Master-Slave topology.
• The database size is 100 GB (same tables on each).
• The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
• The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary logs  it would take an additional 20 hours ((12 * …

The PBMS engine now has built in support for transaction so that if you reference or dereference BLOBs during a transaction the changes you made will be committed or rolled back with the transaction. Up until now PBMS had depended on the host engine to handle transactions and inform it what needed to be done in the event of a rollback.

I have implemented transaction support by adding a circular transaction log and transaction cache. The circular transaction log is dynamic and can grow and shrink as required. The transaction records are all small (30 byte) fixed length records so the log doesn’t need to be that large to be able to handle a lot of transactions. A head and tail pointer are maintained to indicate where the head and tail of the circular log is. If the circular log becomes full then the records just overflow and are written to the end of the log. Once the transaction reader has caught up after an overflow has occurred it …

Microsoft contributes to Linux. Acquia raises $8m. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

Microsoft contributes to Linux
Microsoft announced that it is to contribute device driver code to the Linux kernel under the GPLv2. Prompting us to publish a CAOS Theory Q&A. Answering one questioning we failed to ask, ZDnet reported that Microsoft’s Linux contributions should find their way into the 2.6.32 release.

Acquia raises $8m
Mass High Tech …

If you're going to OSCON 2009, we're going to be there too!  Looking forward to catching up with friends and meeting new folks -- we'll be hanging out in the hallways, bouncing around different sessions and (just like everyone else) on the hunt for power outlets.  Just come up and say hi to any of us.

A couple of our remote NorthScalers have flown in for the first time, as OSCON's a longtime annual ritual. Especially, welcome aboard, Patrick Galbraith!  Congratulations of your new book, Developing Web Applications with Apache, MySQL, memcached, and Perl!  I have my signed copy already.  Patrick's jumping in right away to work on NorthScale's management and monitoring software for memcached.

One indelible …