Introduction

Muuttaa is a partitioned, persistent, self-regulating SQL queue that allows you to queue up alterations to your DB(s) and have them ran over time without crippling your DB(s) or the slaves. A common problem facing high volume websites is moving large pieces of data around, backfilling new denormalized columns or running large DELETE, INSERT, or UPDATE queries.

Muuttaa solves this problem by allowing you to queue up Muuttaa statements, which are comprised of one or more SQL statements and one or more host that they need to be ran against. Statements are queued up by your code and then ran over time by the Muuttaa daemon. Each Muuttaa statement is ran inside of a transaction against each host specified. If an error occurs the error and query is logged along with an error code and the statement's ID.

Muuttaa currently requires PHP and MySQL to work, though, theoretically, you should be able to queue up statements to be ran against other RDBMS systems.

Installation

You can install Muuttaa via PEAR's command line utility, pear. Run the following line on the server(s) you wish to run Muuttaa on. Replace x.y.z with the latest version number from our project's homepage.

$ pear install http://digg.googlecode.com/files/Muuttaa-x.y.z.tgz

Queue

Muuttaa uses MySQL to store the statements that must be ran. When you create a new queue you name it and Muuttaa will create two tables in one or more of the Muuttaa hosts you've specified. A queue named example would have the following tables created on the Muuttaa host(s).

• muuttaa_example_statements
• muuttaa_example_errors

The muuttaa_example_statements table houses all of the statements that have yet to be ran. Muuttaa is self-regulating, which means it will prune statements that have successfully ran. Muuttaa considers any statement which did not result in a failed query successful.

The muuttaa_example_errors table holds all of the errors Muuttaa comes across while processing Muuttaa statements. Muuttaa will prune errors that are older than 30 days automatically.

Processor

Muuttaa comes with a command line interface named muuttaa. This script can be ran in a number of different ways and handles the processing of statements in the Muuttaa queues. You can run muuttaa from the CLI or via the following options.

• Via cron.
• Via daemon.

Options

Examples

1. Running muuttaa through a single pass that processes 100 statements

$ muuttaa -q example -d mysql://muuttaa:secret@192.168.1.24/muuttaa -i 1 -s 100 -m 2

2. Running muuttaa via cron every minute with 5 iterations and waiting 5 seconds per iteration.

* * * * * muuttaa -q example -d mysql://muuttaa:secret@192.168.1.24/muuttaa -i 5 -s 100 -w 5 -m 2

3. Running muuttaa via daemon continuously processing 100 statements and waiting 5 seconds between each pass.
1. Starting muuttaa

$ sudo daemon -i -r -A 5 -L 10 -M 5 -n muuttaa -O /var/log/muuttaa.log -E /var/log/muuttaa.log \
> -P /var/run \ 
> -X "/usr/bin/muuttaa -q example -d mysql://muuttaa:secret@192.168.1.24/muuttaa -i 5 -s 100 -w 5"

2. Stopping muuttaa

$ sudo daemon -P /var/run --name muuttaa --stop

Muuttaa

The class Muuttaa is the class that manages committing Muuttaa_Statement's to the queue. All that's required to start adding statements to the queue is a queue name and one or more hosts to store the queues on.

Muuttaa::__construct($name, $queues)

Overview

Creates a new instance of a Muuttaa queue that you'll be able to insert statements into. When this function is called it will randomly select one of the queue hosts to insert the statements onto and create the necessary Muuttaa tables on that host.

Arguments

Muuttaa::addStatement(Muuttaa_Statement $stmt)

Overview

Add a Muuttaa_Statement to this queue. NOTE: Statements are not flushed to the queue until you run Muuttaa::commit().

Arguments

Muuttaa::commit()

Overview

Flushes all of the statements added via Muuttaa::addStatement() to the queue. When committing very large jobs to Muuttaa it's probably a good idea to flush things to the queue every 50 to 100 statements. This is just a general rule of thumb though.

Muuttaa_Statement

Muuttaa_Statement::addHost($host)

Overview

You must add one or more host to each statement. The host is the DB server that the statement is to be ran against and is not the host of the Muuttaa queue (though these could very well be one and the same).

Arguments

Muutta_Statement::addQuery($query)

Overview

Add a query to the statement. Each Muuttaa_Statement can contain one or more actual queries. All of the queries in a given Muuttaa_Statement are ran inside of a transaction. If one of the queries fails then the entire transaction is rolled back and logged.

Arguments

Muuttaa_Statement::setRetries($retries)

Overview

The number of times Muuttaa should attempt to retry the Muuttaa_Statement before it considers it a lost cause.

Arguments

Example

<?php

/**
 * Note: This is pseudo code in some aspects (e.g. querying the DB).
 */

require_once 'Muuttaa.php';

/**
 * Obviously it'd make sense to store your $host configuration
 * in a central location.
 */
$host = new stdClass;
$host->type = 'mysql'; // This should always be 'mysql';
$host->user = 'root';
$host->pass = 'secret'; 
$host->name = 'muuttaa'; // DB name where queue is to live
$host->port = 3306; // This shouldn't change 

$muuttaa = new Muuttaa('example', $host);

$stmt = new Muuttaa_Statement();
$stmt->addHost($host); // Muuttaa queues can live on the same DB as the other tables
$stmt->addQuery('UPDATE users SET deleted = 1 WHERE id = 59');

// Query the database and get results back
$sql = 'SELECT * FROM items WHERE userid = 59');

// Loop through results
foreach ($result as $row) {
    $stmt->addQuery('UPDATE items SET deleted = 1 WHERE id = ' . $row->id);
}

$muuttaa->addStatement($stmt);
$muuttaa->commit();

?>