I am working on an SMS marketing project based on Yii2 Framework (PHP 7.3 & MariaDb (innodb engine)) where we have to move the logs from different tables to the archive db, which is total a different DB server from the live one.

The log tables keep growing and we had to setup an automated job which runs 3 times a week at midnight and will keep filtering out the logs that are older than 45 days and move them to another database.

I decided to use the Range partitioning and then use EXCHANGE PARTITION to move all the related data to a separate new table so that i keep the main/live table locked for partition process only and keep the copying/moving process that involves Select operation on a different table.

So I divided the whole process into 2 different processes.

• Partitioning
• Archiving

The partition process is further divided into

• Drop any Previously created Backup tables
• Create a new backup table like live table
• Create Partition on live table
• Exchange the partition with the backup table
• Remove the partition from the live table.

My main focus is on the partition process if it can be improved to work more efficiently and less time, currently i have the following stats for the partition process, i am only adding for one of the large tables

Transaction_history

Rows : total 172,899,990 rows approx

Time to Partition : 1472.429115057 secs(24.54048525095 Mins) with total rows in the partition (12,937,902)

Exchange partition : 0.062991857528687 secs

Removed Partition : 1293.8012390137 secs.(21.56335398356167 Mins)

The code is for the Yii2 console app i will be pasting the relevant partition code only and not the whole controller unless required.

/**
 * Creates the backup for the specified table and the range column
 * by creating a partition and then exchanging the old data
 * partition with the backup table and then move the data to the
 * archive database
 *
 * @param string $tableName   the name of the table to backup data from live DB
 * @param string $rangeColumn the name of the column used for the range partition
 *
 * @return null
 */
protected function partitionNow($tableName, $rangeColumn = 'created_at')
{
    $rangeOldPartition = $this->rangeOldPartition();
    $backupTableName = $this->generateBackupTableName($tableName);
    $dbLive = self::_getDsnAttribute('dbname');

    //drop backup table if exists
    $this->dropBackupTables($tableName);

    $this->stdout("Started Partitioning {$tableName}\n");
    $startTime = $this->start();

    try {
        $sql = <<<SQL
            -- create the backup table and remove partitioning from the backup table
            CREATE TABLE `{$dbLive}`.{{%{$backupTableName}}} LIKE `{$dbLive}`.{{%{$tableName}}};
            -- start partitioning the source table
            ALTER TABLE `{$dbLive}`.{{%{$tableName}}}
            PARTITION BY RANGE(UNIX_TIMESTAMP({$rangeColumn}))
            (
                PARTITION oldPt VALUES LESS THAN (UNIX_TIMESTAMP("{$rangeOldPartition}")),
                PARTITION activePt VALUES LESS THAN (MAXVALUE)
            );
        SQL;

        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        //necessary to catch exceptions or errors when
        // using multiple SQL statements with createcommand
        while ($command->pdoStatement->nextRowSet()) {
            //leave blank do nothing, this is for the sake 
            //of multiple sql statements that are run in one go 
        }

        $this->stdout("Partitioned table in {$this->end($startTime)} secs.\n", Console::FG_GREEN);
        $startTime = $this->start();

        $sql = <<<SQL
            -- exchange the partition with the backup table
            ALTER TABLE `{$dbLive}`.{{%{$tableName}}}
            EXCHANGE PARTITION oldPt WITH TABLE `{$dbLive}`.{{%{$backupTableName}}};
        SQL;

        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        $this->stdout("Completed Exchange partition {$this->end($startTime)} secs\n");

        $startTime = $this->start();
        $sql = <<<SQL
        -- remove partition from the source table once data moved to separate table
        ALTER TABLE `{$dbLive}`.{{%{$tableName}}} REMOVE PARTITIONING;
        SQL;
        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        $this->stdout("Removed Partition in {$this->end($startTime)} secs.\n");
        $this->stdout("Filterd out data from live table.\n");

    } catch (Exception $e) {
        throw $e;
    }
}

 /**
 * Returns the date for the specified interval
 * to backup default interval is 45 days.
 *
 * @return mixed
 */
protected function rangeOldPartition()
{
    $date = new DateTime();
    $date->sub(new DateInterval('P0M45D'));
    return $date->format("Y-m-d");
}

/**
 * Returns the database name after extracting the
 * specific string from the Dsn property
 *
 * @param string $name   the name of the property in dsn string.
 * @param string $target the target connection of the database live|archive, default "live"
 *
 * @return mixed
 */
private static function _getDsnAttribute($name, $target = 'live')
{
    if ($target === 'live') {
        if (preg_match("/{$name}=([^;]*)/", Yii::$app->getDb()->dsn, $match)) {
            return $match[1];
        }
    } else {
        if (preg_match("/{$name}=([^;]*)/", Yii::$app->db_backup->dsn, $match)) {
            return $match[1];
        }
    }
    throw new Exception("Unable to extract the db Name");
}

/**
 * Takes the source table name ad
 *
 * @param $tableName
 */
protected function dropBackupTables($tableName)
{
    $backupTableName = $this->generateBackupTableName($tableName);

    $sql = <<<SQL
        DROP TABLE IF EXISTS {{%{$backupTableName}}};
    SQL;
    Yii::$app->db->createCommand($sql)->execute();
}

/**
 * Generates the backup table name from the source table name
 *
 * @param string $tableName the source table to mock the backup table from
 *
 * @return string $backupTableName the name of the backup table
 */
protected function generateBackupTableName($tableName)
{
    $backupTableAlias = 'bkp_' . date('Ymd') . '_';
    return "{$backupTableAlias}{$tableName}";
}