Think API

Codeigniter: Separating reads and writes for scaling MySQL

Posted on the August 6th, 2009 under Codeigniter,Programming by Sukumar

Generally websites average a ratio of 9:1 or more for reads:writes for their applications which makes MySQL replication as one of the ways to scale you web application. The simplest configuration is to separate reads and writes with all the reads coming from the slave servers.

We can implement this in codeigniter using database groups.
Below is a sample execution.

Create two active groups – one for read and one for write.

File: system/application/config/database.php

$active_group = "write";
$active_record = TRUE;

$db['read']['hostname'] = "localhost";
$db['read']['username'] = "root";
$db['read']['password'] = "";
$db['read']['database'] = "read_database_name";
$db['read']['dbdriver'] = "mysql";
$db['read']['dbprefix'] = "";
$db['read']['pconnect'] = TRUE;
$db['read']['db_debug'] = FALSE;
$db['read']['cache_on'] = FALSE;
$db['read']['cachedir'] = "";
$db['read']['char_set'] = "utf8";
$db['read']['dbcollat'] = "utf8_general_ci";

$db['write']['hostname'] = "localhost";
$db['write']['username'] = "root";
$db['write']['password'] = "";
$db['write']['database'] = "write_database_name";
$db['write']['dbdriver'] = "mysql";
$db['write']['dbprefix'] = "";
$db['write']['pconnect'] = TRUE;
$db['write']['db_debug'] = FALSE;
$db['write']['cache_on'] = FALSE;
$db['write']['cachedir'] = "";
$db['write']['char_set'] = "utf8";
$db['write']['dbcollat'] = "utf8_general_ci";

Create separate database connections to access read and write databases separately where needed (constructor is generally a good place for this).

$read_db = $this->load->database('read', TRUE);
$write_db = $this->load->database('write', TRUE);

You can now go about running queries in the usual way.

/* For reads */
$query = $read_db->get('table_name');

foreach ($query->result() as $row)
{
    echo $row->title;
}

/* For writes */
$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

$write_db->insert('mytable', $data);

In case you want to try this on an application you are already running, you can leave the “default” connection group intact and create only the “read” connection group. Use it where you think you are running read heavy queries.

Related posts:

1. Making Codeigniter clean URLs cleaner Usually when working with codeigniter you come across the use...
2. How to skip MySQL replication counter There are such times when MySQL replication stops when you...
3. Upgrading to MySQL 5.1 GA? Better Wait MySQL 5.1 General Availability has been released to the public....

Tags: Codeigniter, database replication, mysql, mysql replication, replication