from __future__ import *

Kailash and Friends Kailash Kher Kaipa

The OSCON 2005 PostgreSQL Presentations are up, but a bunch of them are in OpenOffice format. I went through the pain of installing OpenOffice to convert them to PDF. For completeness, I have also included the presentations that were already available as PDF.

Chris Browne:

• Replicating PostgreSQL Databases Using Slony-I
• Event Propagation in Slony-I
• Sample Replication RT/3
• Upcoming Slony-I Enhancements

Joe Conway:

• Terrabytes of Business Intelligence

Lance Obermeyer:

• Running PostgreSQL On Windows

Bruce Momjian:

• State of PostgreSQL 8.1
• Porting PostgreSQL To Windows

Aaron Thul:

• PostgreSQL Built Your Car

Robert Treat:

• Explaining Explain

We're currently experimenting with various optimization techniques for our databases using PostgreSQL's features, and we've become quite interested in the Table Partitioning in PostgreSQL 8.1. Bizgres has this feature now (amongst others that will likely benefit our app) in its 0.7 release, so I decided to give it a whirl.

Installing Bizgres 0.7 on Mac OS X is easy enough, but it loses a few points (no big deal, it's beta):

• The installer doesn't use Apple's installer, it uses some InstallAnywhere crap that I don't really trust
• The installer is probably one of the ugliest things I have ever seen on Mac OS X outside of perversiontracker
• It throws a readline library in a Fink-owned location if it's not already there (install_name_tool, anyone?!)
• It doesn't install any sort of preference pane, StartupItem, or GUI tools at all
• The demos are there, but they're sitting in tar files... wtf?

Installing on FreeBSD er.. loses even more points. It's not a supported platform yet, so that's to be expected. The build process depends on a suite of bash scripts to set some environment variables, and ant to do the build. Autoconf happens somewhere too, but that's not invoked directly. The problems here are:

• Bash isn't the default shell in FreeBSD, and isn't even normally installed (unless you did it yourself)

• The bash scripts assume /bin/bash (where it's /usr/local/bin/bash on FreeBSD)

• The Ant build script, common.xml:

  • Needs OS-specific incantations
  • Assumes make is GNU make
  • Assumes bash is at /bin/bash (even though it checks for its presence anywhere in $PATH!)

• Oh, and it needs Java for Ant:

  Java Is A Royal Pain In The Ass On FreeBSD (and installing it is beyond the scope of this post).

So anyway, I fixed those things and made a patch. Here's how you could build and install with it:

• Download the 0.7 source tarball

• Do this:

  cd bizgres
  curl -s http://redivi.com/~bob/bizgres-0_7_4-FreeBSD-1.patch | patch -u -p0
  su
  bash
  source source-before-build.sh
  ant -Ddebug=false -DinstallRoot=/usr/local/bizgres -DdevoBuild=true install
  exit
  exit
  

This will install bizgres to /usr/local/bizgres-0_7_4. It's not going to make any rc scripts for you, but they can be trivially adapted from the PostgreSQL port.

Note that:

• This is the first time I've ever attempted to port something to FreeBSD (though I do have extensive Mac OS X experience)
• Bizgres 0.7 is beta software
• This is largely untested
• FreeBSD is not a supported platform (by them, or me)

If this causes you to lose all of your data, first, make sure that you weren't using MySQL. If you were indeed using Bizgres on FreeBSD, then blame yourself for not listening to the above.

UPDATE:

Changed the URL of the patch to one that compiles with OpenSSL support by default. To add OpenSSL support to an already patched source (or to JUST add OpenSSL support to unpatched source), use the following:

http://redivi.com/~bob/bizgres-0_7_4-OpenSSL.patch

/usr/ports/databases/postgresql-contrib comes with the very useful (until 8.1, anyway) pg_autovacuum daemon, but no rc script. Here's one I whipped up:

/usr/local/etc/rc.d/019.pg_autovacuum.sh

#!/bin/sh
#
# PROVIDE: pg_autovacuum
# REQUIRE: postgresql
# KEYWORD: FreeBSD shutdown

# Add the following line to /etc/rc.conf to enable `pg_autovacuum':
#
# pg_autovacuum_enable="YES"
#
#

. /etc/rc.subr


pg_autovacuum_enable=${pg_autovacuum_enable:-"NO"}
pg_autovacuum_flags=${pg_autovacuum_flags:-"-v 300 -V 0.5"}

name=pg_autovacuum
rcvar=`set_rcvar`
command=/usr/local/bin/pg_autovacuum
load_rc_config ${name}
command_args="-D ${pg_autovacuum_flags}"
pg_autovacuum_user=pgsql

run_rc_command "$1"

I did submit a PR with this by way of send-pr, but it doesn't seem to have ended up in the PR repository. I'm not sure what to do about that, as it's the recommended way to submit PRs!

It seems that most of my gripes about MySQL boil down to three things:

1. It sucks if your recent previous experience is with more capable SQL databases. I actually learned SQL using MySQL back in 1997 or whatever, but it was using hardcopy literature written for the ANSI standard... so I'd write SQL with views, subselects, and whatnot, and curse MySQL for not having the features I thought I wanted. Once I moved on to the PostgreSQL/MSSQL/Oracle stuff I knew that I actually did want those features.

   I think the last project I used MySQL on was in 1998 or 1999. Everything since has been other databases, but I decided to try MySQL this time around. I decided to give it another shot because I know some people running high volume stuff on MySQL, I had met a couple respectable people working for MySQL over the years, I thought it would be fast (it's not -- what we really need is materialized views, and I had to write a daemon to poll the database to implement them), and I knew I wouldn't have to spend much time setting it up. I think this was a premature optimization, and I'm pretty sure that something else would've been faster anyway now that I have a feel for what MySQL is and isn't good at (read: our stuff).

   I know that a lot of this is being fixed in MySQL 5, but damned if I'm going to use a beta database that has hundreds of new features that haven't ever been in the wild. I also have a feeling that it's probably full of legacy brain damage like PHP 5, even though it tries to suck significantly less.

2. It broke in two different ways in one week, after having used it for only about two months. Granted, we're putting a lot of stress on it, but you expect a database that's been around this long to at least gracefully degrade into getting slow, rather than throwing data away. The first two months were nice, but I have a feeling that other people are experiencing this crap later on in the game, and are simply just screwed because they have too much of an investment in their MySQL-based code. It's painful to migrate a couple million rows no matter how you slice it, but I have better things to do than babysit a database.

   We have our own flat file transaction log now, so I'm not so worried about MySQL dying again during the transition :)

3. The MySQL GUI tools are really weird and buggy and/or out of date on OS X. They're almost good enough, but tend to crash or have weird UI quirks. Who the hell decided to remove the select all option from the edit menus? I put them back in with Interface Builder, but I feel sorry for people who don't have Cocoa experience :) This is only a somewhat valid complaint, because most of the other RDBMS don't really have GUI tools for OS X. Also, the performance/health monitoring graphs in the administrator are just totally worthless, because they adjust the scale once a second, and most of the time it's not even done correctly so the peaks get clipped. It looks cool, but man is that graph useless.

So long, and thanks for all the marketing buzz.

We've been using MySQL for MochiBot for a while. It worked OK, until this week.

MySQL replication is unreliable. We had two MySQL servers running, one as a master, and one as a slave; the simplest of all replication scenearios. The slave was used only for redundancy and load balancing. It receives only SELECT queries and never mutates data (none of the connections connecting to it even have permission to mutate!). What happened? Replication stopped -- but only on one table, with no errors. The binlogs are all synchronized, it just decided not to update that table anymore. The workaround for this was easy, we stopped using replication. Granted, I consulted a few people who are running really high profile and high traffic sites on MySQL and they've never seen this before, but I can no longer trust MySQL to replicate our data.

The final blow was MySQL's MyISAM tables are fast, but dangerous. What happens when you try and put a lot of data in them? They explode. There's an arcane MAX_TABLE_ROWS limit to a MyISAM table. If you hit this limit, it won't let you insert any more rows. It doesn't grow for you. Why not? Beats me. I should've known this, but frankly, I've never trusted MySQL to store an 8GB table before, so I never ran up against it. Never again!

We're through, and are in the process of migrating to PostgreSQL ASAP. The bonus is that we can use all the nice features of SQL that you lose when making the MySQL choice: subselects, views, triggers, stored procedures, etc. I really should've listened to myself and avoided MySQL in the first place.