Tech Blog

Running an online service like Floorplanner is not without risks. There are a lot of things that can go wrong. One of them is downtime of your service due to a crashing system. It doesn’t have to happen of course, but when it does, it can have some nasty consequences. A good way to reduce the risk of a crashing system is to set up a redundant system.

In short, a redundant system is an exact copy of your main system. When your main system goes down (for whatever reason), the redundant system can take over. One of the most important parts of any online service is the database. A way to maintain an exact copy of your database is setting up database replication. This post gives you the basics on how to set up database replication using a MySQL database.

Database replication isn’t probably something you’d setup right from the start. This means that you have to work with a running system when you decide to do so. From past experience we know that it’s not a good idea to replicate your database to the same machine, let alone to the same disk. To make this work, you need a separate machine with MySQL installed. This is your slave machine and database.

Let’s get down to business. These are the steps for setting up database replication:

1. Enable binary logging on master database
2. Create a backup of master database
3. Transfer backup to slave machine
4. Import backup to slave database
5. Start slave database

1. Enable binary logging on master database

The binary log contains all statements that update data [..]. Statements are stored in the form of “events” that describe the modifications. The binary log also contains information about how long each statement took that updated data.

Every write action that’s performed on your database, like an insert or an update, is stored in these binary logs (bin logs). The master dumps all actions in these logs and the slave database can read them and perform the same actions. This way your master and slave databases will always have the same data.

To enable binary logging you have to create a config file, for example master.cnf. An important thing here is the server-id, which is needed for replication to work. Then there is log-bin which specifies base name of binary logs, and finally binlog-do-db, which specifies which databases should be bin logged.

[mysqld]
datadir=/home/yourname/mysql/myisam/data
basedir=/usr/local/mysql
port=3306
server-id=1
log-bin=mysql-bin
binlog-do-db=my_fat_db1
binlog-do-db=ma_other_fat_db

Then start (or restart) the master database using the config file:

~ $ mysqld --defaults-file=master.cnf&

The slave database needs a userid/password in order to access the master machine. It’s best practice create a dedicated user with just the required privileges. This can be done with the following SQL statement.

GRANT replication slave ON *.* TO 'repl'@'slave-ip-here'

2. Create a backup of master database

The next step it to create a backup of your master database. The type of your database is very important for this action. If you use MyISAM, you need to schedule some downtime to dump your database. Otherwise the dumped data isn’t consistent and therefor useless. With InnoDB it’s possible to dump a consistent backup of your database while keeping it up and running. We stumbled on this feature by accident, but we haven’t been able to in the official documentation.

MyISAM

~ $ mysqldump --lock-all-tables

InnoDB

~ $ mysqldump --single-transaction

3. Transfer backup to slave machine

Now that the backup is created on the master machine, it needs to be transferred to the slave machine.

4. Import backup to slave database

Once the backup is present on the slave machine, it can be imported into the slave database:

~ $ mysql < filename

5. Start slave database

The last step is to tell the database that it’s a slave database. First we have to give it an server id. We use a config file for this, slave.cnf:

[mysqld]
datadir=/home/yourname/mysql/myisam/data
basedir=/usr/local/mysql
port=3306
server-id=2
replicate-do-db=my_fat_db1
replicate-do-db=my_other_fat_db

Then we start the database with this config file:

~ $ mysqld --defaults-file=slave.cnf&

and we issue the following SQL statement:

CHANGE MASTER TO
-> MASTER_HOST='master-ip-here',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='bin-log-filename-here',
-> MASTER_LOG_POS=4;

It’s hard to predict the MASTER_LOG_POS. You can find this number by issuing the following SQL statement on the master database:

SHOW MASTER STATUS\G;

Once the slave is configured correctly it will get the bin logs from the master machine, parse them and execute each action. It takes some time to have an exact copy of the master database, depending on the size and number of bin logs. To get an idea about the delay, issue the following query:

SHOW SLAVE STATUS\G;

With this post we hope to contribute to a less riskier world and more peaceful state of mind for the sys admins out there

ParseTree is a very useful to gem that can translate Ruby code into a syntax tree. I recently needed to evaluate a static part of this tree to return the original hash it represented. I wrote a simple method called ParseTree.eval_static_tree for this purpose.

The method can only evaluate trees that have a static value composed of hashes, arrays, strings, symbols and numerics. You can however pass a block to the function that will be called for every dynamic part the method encounters (function calls, etc.)

A quick sample on how to use it:

code = '{ :static_array => ["str", 123, 4.5], :dynamic => method_call }'
tree = ParseTree.translate(code)
# => [:hash, [:lit, :static_array], [:array, [:str, "str"], 
#       [:lit, 123], [:lit, 4.5]], [:lit, :dynamic], [:vcall, :method_call]]
 
ParseTree.eval_static_tree(tree)
# => RuntimeError: tree is not static: :vcall ...
 
# Pass a block to simply return nil for every dynamic item in the tree.
ParseTree.eval_static_tree(tree) { |dynamic_subtree| nil }
# => {:dynamic=>nil, :static_array=>["str", 123, 4.5]}

Bart and I have been working a lot on request-log-analyzer lately, our tool to produce performance reports for web applications based on their log files. Today, we released version 1.4.0, which boasts many new features since I last blogged about a release. The changelog contains all changes we have implemented recently with some additional information, but these are the highlights:

• New and improved log formats: r-l-a can now handle Apache access logs, Rack CommonLogger logs and Amazon S3 access logs. Moreover, the Rails format has been restructured to offer more flexibility.
• Improved database support: the database supports other databases than SQLite3 as well, and r-l-a can append information to an existing database instead of overwriting it. Moreover, a console tool similar to Rails’s script/console is bundled to inspect the database and run queries on it easily.
• Added standard deviation to reports: the standard deviation measure has been added to duration and traffic reports to get some feel of the variation in values besides the mean.
• E-mailing reports: r-l-a can email the performance report to a given e-mail address. This can be useful when running r-l-a in a cron job.
• Compressed log support: r-l-a will decompress compressed logs automatically.
• Speed improvements: we have profiled request-log-analyzer itself and significantly improved its performance.
• API: we created a basic API so it is possible to use the r-l-a engine as a library as well.
• Monitoring integration: integrate performance information into your Munin dashboard or your Scout account.

As always, use sudo gem install request-log-analyzer to install or upgrade.

Ruby en Rails 2009 conference

Bart and I will be presenting request-log-analyzer and performance tuning of Rails applications in general at the Ruby en Rails conference in Amsterdam, October 30-31 2009. We hope to see you there!