Category Archives: Database

Production MySQL performance tuning

Posted on January 15, 2009 by Todd Huss

For the past 9 years I’ve been working almost exclusively with MySQL (with a little PostgreSQL thrown in) and while I don’t do nearly as much DBA work these days, I still find myself troubleshooting a query or tuning my.cnf. … Continue reading →

Posted in Database, FreeBSD, Linux, MySQL, Systems Administration | Comments Off

Standalone Migrations: Using Rails migrations in non Rails projects

Posted on December 22, 2008 by Todd Huss

Update 8/7/2010: Standalone migrations is now a gem (sudo gem install standalone_migrations) so disregard the outdated installation instructions below Update 7/8/2009: With the latest batch of contributed patches standalone migrations now works just like Rails migrations Update 12/26/2008: I switched … Continue reading →

Posted in Database, Java, Linux, MySQL, Ruby, Ruby on Rails | 5 Comments

Comparing Amazon EC2 with VPS and dedicated hosting

Posted on November 30, 2006 by Todd Huss

I’ve been reading all the great things about Amazon EC2 (or Elastic Compute Cloud) and lots of pricing comparisons with VPS and dedicated hosting. I finally got an EC2 account and tinkered a bit and there’s a big difference between … Continue reading →

Posted in Database, FreeBSD, Linux, MySQL, Systems Administration | 9 Comments

Upgrading to Spring 2.0 and Hibernate 3.2

Posted on October 19, 2006 by Todd Huss

Tonight I just completed the upgrade from Spring 1.2 and Hibernate 3.1 to Spring 2.0 and Hibernate 3.2 for work. I was expecting a rough upgrade but was pleasantly surprised how easily it went.

Posted in Database, Java, Software Engineering | 3 Comments

SQL statements mysteriously not replicating with MySQL replication

Posted on September 13, 2006 by Todd Huss

If you’re using MySQL replication there’s a feature/bug that you should be aware of. The following SQL statement would not replicate to the slave servers:

Posted in Database, MySQL | 2 Comments

Modeling enumerated types in the database

Posted on May 29, 2006 by Todd Huss

Let’s say you have an Employees table and you want a column to track the status of an employee such as [’employed’, ‘resigned’, ‘retired’, ‘terminated’, etc…]. You have a couple of options including: Using your database’s custom enumeration data-type if … Continue reading →

Posted in Database, Ruby, Software Engineering | 3 Comments

Creating database test fixtures and the rails export fixtures plugin

Posted on May 16, 2006 by Todd Huss

Being able to quickly and easily create test fixtures for your database is important yet it’s not always easy. There are basically 3 approaches I’ve seen used: 1. Use a MySQL or PostgreSQL dump that gets imported before the tests … Continue reading →

Posted in Database, Java, MySQL, Ruby, Software Engineering | 3 Comments

Moving MySQL tables live with zero downtime

Posted on May 9, 2006 by Todd Huss

The biggest challege to moving large amounts of data into production with (almost) zero downtime with MySQL is that the old table will be dropped and the new table locked while you’re loading the data. If you try that while … Continue reading →

Posted in Database, MySQL, Systems Administration | 2 Comments

Load balancing across MySQL servers using JDBC

Posted on February 1, 2006 by Todd Huss

In our production environment at GreatSchools we have 3 production MySQL database servers: 1 read-write master and 2 read-only slaves. In moving our site from Perl to Java we need to load balance read-only connections across the read-only slave servers … Continue reading →

Posted in Database, Java | 9 Comments

More powerful database constraints with regular expressions

Posted on January 26, 2006 by Todd Huss

Is it just me or do you agree that we should be able to easily apply regular expression constraints to a column in a table without writing a stored procedure? For example if I want to guarantee that a varchar … Continue reading →

Posted in Database, Software Engineering, Systems Administration | Comments Off