Thrid Part: Keep FusionPBX on Diet

After running successfully a medium-to-big cluster, one of your concerns is your database keeps growing. If you do not do anything, sooner than later there won't be a server that can host your FusionPBX database.

The good news about this is that it is very easy to know why the database grows so fast. It is logic, more customers, more calls, therefore, your database detailed record grows faster. In technical words, your v_xml_cdr table grows and grows.

Depending on the kind of customers you are hosting, CDR can be very valuable or depreciable. Depending on the country you are, by law, you may be required to store a CDR record somewhere. Somewhere not in your production database. So, your solution is to archive them. If someone asks for them, you will deal with that later.

Archive your FusionPBX Records

The first step is moving records to one database to another. Use the following script to archive. Modify it to meet your needs.

$mysqli = new mysqli('localhost', 'USER', 'PASSWORD', 'fusionpbx');
if ($mysqli->connect_errno) {
    print 'Error: '.$mysqli->connect_error.PHP_EOL;
    exit (1);
}
$mysqli_archive = new mysqli('ARCHIVE_SERVER', USER', 'PASSWORD', 'fusionpbx');
if ($mysqli_archive->connect_errno) {
    print 'Error 2: '.$mysqli_archive->connect_error.PHP_EOL;
    exit (1);
}
$sql = 'SELECT * FROM v_xml_cdr WHERE answer_stamp < DATE_SUB(NOW(), INTERVAL 30 DAY) limit 300';
if ($result = $mysqli->query($sql)){
    while ($row = $result->fetch_assoc()){
        $cols = implode(',', array_keys($row));
            foreach (array_values($row) as $value){
                isset($vals) ? $vals .= ',' : $vals = '';
                $vals .= '\''.$mysqli_archive->real_escape_string($value).'\'';
            }
        $sql_archive = 'INSERT INTO v_xml_cdr ('.$cols.') VALUES ('.$vals.')';
        // echo $sql_archive;
        $result_archive = $mysqli_archive->query($sql_archive);
        unset($cols, $vals);
        if ($result_archive === TRUE){
            $uuid = $row['uuid'];
            $sql_delete = 'DELETE FROM v_xml_cdr WHERE uuid="'.$uuid.'"';
            $result_delete = $mysqli->query($sql_delete);
            if ($result_delete === TRUE){
                echo $uuid .' moved'.PHP_EOL;
            }
        }
    }
}
$mysqli->close();
$mysqli_archive->close();

Enabling the CDR Archive in FusionPBX

At some point, FusionPBX 4.4+ got archive capabilities. They won't work out of the box. You will need my pull request #5009 to make it work properly. Patch your FusionPBX if it hasn't been accepted yet.

You will need to configure the following default settings:

• Category: CDR, Sub Category: archive_database, Type: boolean, Value: true
• Category: CDR, Sub Category: archive_database_driver, Type: text, Value: mysql,pgsql,odbc
• Category: CDR, Sub Category: archive_database_host, Type: text, Value: DATABASE HOST IP
• Category: CDR, Sub Category: archive_database_name, Type: text, Value: DATABASE NAME
• Category: CDR, Sub Category: archive_database_password, Type: text, Value: DATABASE USER_PASSWORD
• Category: CDR, Sub Category: archive_database_port, Type: text, Value: DATABASE HOST PORT
• Category: CDR, Sub Category: archive_database_type, Type: text, Value: mysql,pgsql,odbc
• Category: CDR, Sub Category: archive_database_username, Type: text, Value: DATABASE USER

Also, go to your group manager and enable the xml_cdr_archive permission. By default, it is off.

You will need also to figure out a way to sync some archive tables from the production database server to the archive database server. So far, I have found that only v_extensions and v_domains are required.

Good luck!