Return to Question

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

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

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

The code is for the Yii2 console app.

Transaction_history

/**
 * 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}";
}

Transaction History table stats

Transaction History Schema

CREATE TABLE `transaction_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(19,6) NOT NULL,
  `description` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `transaction_type` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'blast',
  `remaining_balance` decimal(19,6) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`,`created_at`),
  KEY `transaction_type` (`transaction_type`),
  KEY `user_id_transaction_type` (`user_id`,`transaction_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

<?php
namespace console\controllers;

use Yii;
use DateTime;
use Exception;
use DateInterval;
use yii\helpers\Console;
use yii\console\Controller;
use yii\helpers\ArrayHelper;
use console\controllers\traits\ArchiveTraits;

class PartitionController extends Controller
{
    use ArchiveTraits;
    /**
     * Contains array of tables and their range column to be used for partition
     * in the key=>value format like [table_name=>range_column_name]
     *
     * Note: before you add any new table to the below list make sure you have
     * added the range column to unique index or part of the primary key
     */
    const BACKUP_TABLES = [
        'message_inbound' => 'created_at',
        'transaction_history' => 'created_at',
        'sms_api_log' => 'created_at',
        'balance_history' => 'created_on',
        'buy_number_logs' => 'created_at',
        'destination_delivery' => 'created_at',
        'message_delivery' => 'created_at',
        'email_delivery_logs' => 'created_at',
        'responder_keywords_logs' => 'created_at',
        'sms_alert_logs' => 'created_at',
        'suppression_message_logs' => 'created_at',
    ];
    
    private $_date = null;

    /**
     * @var batch size for the
     * inserts in the database
     */
    const BATCH_SIZE = 10000;

    /**
     * @var limit for the rows to be migrated to the
     * database in one iteration
     */
    const MIGRATE_LIMIT = 50000;

    public function actionIndex($date = null)
    {
        $this->_date = $date;
        $this->startNotification("Partition Process started", 'partition-start');
        ini_set("memory_limit", 0);
        $this->stdout("Starting Partition Process.\n");
        $this->startPartition();
        $this->stdout("Completed Partition Process.\n");
        $date = date("Y-m-d");
        $this->sendSummaryReport("Partitioning Process Complete for {$date}", "partition-complete", "partition");
    }

    /**
     * @param int $start the start timestamp
     */
    public function end($start)
    {
        return microtime(true) - $start;
    }

    public function start()
    {
        return microtime(true);
    }

    /**
     * Starts the partitioning process for the live DB log tables
     *
     * @return null
     * @throws Exception
     */ 
    protected function startPartition()
    {
        foreach (self::BACKUP_TABLES as $tableName => $rangeColumn) {
            try {
                $this->partitionNow($tableName, $rangeColumn);
                $this->stdout("\n");
            } catch (Exception $e) {
                $this->sendExceptionEmail($tableName, $e, "Exception on Partitioning table", "partition-exception");
                $this->stdout("There was an error while trying to archive the {$tableName} .\n");
                $this->stdout($e->getMessage() . "\n===============\n");
                $this->stdout("Continuing to archive the next table.\n");
            }
        }
    }

    /**
     * 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->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;
        }
    }

    /**
     * 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}";
    }

    /**
     * Returns the create table command for the given table
     * @param $tableName
     */
    protected function getCreateTable($tableName)
    {
        $data = Yii::$app->db->createCommand("show create table {{%{$tableName}}}")->queryOne();
        return str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $data['Create Table']);
    }

    /**
     * @param $tableName
     */
    protected function getPageData($tableName, $offset = 0)
    {
        $limit = self::MIGRATE_LIMIT;

        $sql = <<<SQL
            SELECT * FROM {{%{$tableName}}}
            order by id
            LIMIT {$offset},{$limit}
        SQL;
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * @param $tableName
     */
    protected function getRowCount($tableName)
    {
        $sql = <<<SQL
            SELECT COUNT(*) as total FROM {{%{$tableName}}}
        SQL;
        $data = Yii::$app->db->createCommand($sql)->queryOne();
        $this->stdout("Found {$data['total']} records.");
        return $data['total'];
    }

    /**
     * Returns the columns names for a table
     *
     * @param string $db        the database name
     * @param string $tableName the table name
     *
     * @return mixed
     */
    protected function getTableColumns($db, $tableName)
    {
        $sql = <<<SQL
        select
            COLUMN_NAME
        from
            information_schema.columns
        where
            table_schema = "{$db}" and table_name="{$tableName}"
        order by
            table_name,ordinal_position;
        SQL;

        return ArrayHelper::getColumn(
            Yii::$app->db->createCommand($sql)->queryAll(),
            'COLUMN_NAME'
        );

    }

    /**
     * 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");
    }
}