ss-prechecks: SingleStore database metrics report for migration evaluation (like PlanetScale ps-prechecks)

ss-prechecks-readme.md

ss-prechecks - SingleStore Pre-Checks for Migration Evaluation

A Laravel artisan command that generates SingleStore database metrics in a format similar to PlanetScale's ps-prechecks tool.

Purpose

When evaluating database migrations (e.g., to PlanetScale, TiDB, or other platforms), you need accurate metrics about your current SingleStore setup. This command generates a comprehensive report including:

• Total rows, memory usage, and disk usage
• Per-database breakdown
• Top tables by size
• Storage type distribution (columnstore vs rowstore)
• Configuration variables
• Technology usage (full-text indexes, JSON columns, triggers, etc.)
• Active connections and status counters

Installation

Copy SsPrechecks.php to your Laravel project:

cp SsPrechecks.php app/Console/Commands/SsPrechecks.php

Update the namespace if needed.

Usage

# Run against default connection
php artisan ss-prechecks

# Run against specific connection
php artisan ss-prechecks --connection=production_readonly

# Save to file
php artisan ss-prechecks --connection=production > ss-prechecks-report.txt

Sample Output

# SingleStore Pre-Checks Report
# Generated: 2026-01-14 13:43:14 UTC

# Summary ################################################
  Total Rows:              1,250,000,000
  Memory Usage:            8.00 GB
  Columnstore Compressed:  750.00 GB
  Columnstore Uncompressed:1.70 TB
  Compression Ratio:       2.27x

# Database Breakdown #####################################
  Database                        Rows          Memory Disk (Compressed)
  analytics                900,000,000       150.00 MB       180.00 GB
  main_app                 250,000,000         6.00 GB        80.00 GB
  ...

Key Differences from MySQL ps-prechecks

MySQL (ps-prechecks)	SingleStore (ss-prechecks)
information_schema.TABLES	TABLE_STATISTICS + columnar_segments
Binary log info	N/A (Helios managed)
Replication status	N/A (Helios managed)
my.cnf parsing	N/A (cloud managed)

SingleStore-Specific Metrics

• Columnstore vs Rowstore breakdown
• Compression ratio (uncompressed/compressed)
• Memory usage (rowstore tables)
• Partition distribution awareness

Requirements

• Laravel 8+
• PHP 8.0+
• SingleStore database connection

License

MIT

SsPrechecks.php

<?php

namespace App\Console\Commands;

use Exception;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

/**
 * SingleStore pre-checks command for migration evaluation.
 * Adapted from PlanetScale's ps-prechecks tool for SingleStore compatibility.
 *
 * @see https://github.com/planetscale/ps-prechecks
 */
class SsPrechecks extends Command
{
    protected $signature = 'ss-prechecks
                            {--connection= : The database connection to use}';

    protected $description = 'Generate SingleStore database metrics report for migration evaluation';

    private array $excludedDatabases = ['information_schema', 'memsql', 'cluster'];

    public function handle(): int
    {
        $connectionName = $this->option('connection');
        $connection = $connectionName ? DB::connection($connectionName) : DB::connection();

        try {
            $this->printHeader();
            $this->printSummary($connection);
            $this->printDatabaseBreakdown($connection);
            $this->printTableInformation($connection);
            $this->printStorageAnalysis($connection);
            $this->printNoteworthyVariables($connection);
            $this->printNoteworthyTechnologies($connection);
            $this->printProcesslist($connection);
            $this->printStatusCounters($connection);
            $this->printSchemaSummary($connection);

            return 0;
        } catch (Exception $e) {
            $this->error('Error generating report: ' . $e->getMessage());

            return 1;
        }
    }

    private function printHeader(): void
    {
        $this->line('');
        $this->line('# ########################################################');
        $this->line('# SingleStore Pre-Checks Report');
        $this->line('# Generated: ' . now()->toDateTimeString() . ' ' . now()->tzName);
        $this->line('# ########################################################');
        $this->line('');
    }

    private function printSummary($connection): void
    {
        $this->line('# Summary ################################################');
        $this->line('');

        $stats = $connection->select("
            SELECT
                SUM(rows) as total_rows,
                SUM(memory_use) as total_memory
            FROM information_schema.TABLE_STATISTICS
            WHERE database_name NOT IN ('" . implode("','", $this->excludedDatabases) . "')
              AND partition_type = 'master'
        ");

        $totalRows = $stats[0]->total_rows ?? 0;
        $totalMemory = $stats[0]->total_memory ?? 0;

        $columnstoreStats = $connection->select('
            SELECT
                SUM(compressed_size) as compressed,
                SUM(uncompressed_size) as uncompressed
            FROM information_schema.columnar_segments
        ');

        $compressedSize = $columnstoreStats[0]->compressed ?? 0;
        $uncompressedSize = $columnstoreStats[0]->uncompressed ?? 0;

        $this->line(sprintf('  Total Rows:              %s', number_format($totalRows)));
        $this->line(sprintf('  Memory Usage:            %s', $this->formatBytes($totalMemory)));
        $this->line(sprintf('  Columnstore Compressed:  %s', $this->formatBytes($compressedSize)));
        $this->line(sprintf('  Columnstore Uncompressed:%s', $this->formatBytes($uncompressedSize)));
        $this->line(sprintf('  Compression Ratio:       %.2fx', $uncompressedSize > 0 ? $uncompressedSize / max($compressedSize, 1) : 0));
        $this->line('');
    }

    private function printDatabaseBreakdown($connection): void
    {
        $this->line('# Database Breakdown #####################################');
        $this->line('');

        $databases = $connection->select("
            SELECT
                ts.database_name,
                COUNT(DISTINCT ts.table_name) as tables,
                SUM(ts.rows) as total_rows,
                SUM(ts.memory_use) as memory_use
            FROM information_schema.TABLE_STATISTICS ts
            WHERE ts.database_name NOT IN ('" . implode("','", $this->excludedDatabases) . "')
              AND ts.partition_type = 'master'
            GROUP BY ts.database_name
            ORDER BY SUM(ts.rows) DESC
        ");

        $columnstoreSizes = $connection->select('
            SELECT
                database_name,
                SUM(compressed_size) as compressed_size
            FROM information_schema.columnar_segments
            GROUP BY database_name
        ');

        $columnstoreByDb = [];
        foreach ($columnstoreSizes as $row) {
            $columnstoreByDb[$row->database_name] = $row->compressed_size;
        }

        $this->line(sprintf('  %-30s %15s %15s %15s', 'Database', 'Rows', 'Memory', 'Disk (Compressed)'));
        $this->line(sprintf('  %-30s %15s %15s %15s', str_repeat('-', 30), str_repeat('-', 15), str_repeat('-', 15), str_repeat('-', 15)));

        foreach ($databases as $db) {
            $diskSize = $columnstoreByDb[$db->database_name] ?? 0;
            $this->line(sprintf(
                '  %-30s %15s %15s %15s',
                $db->database_name,
                number_format($db->total_rows),
                $this->formatBytes($db->memory_use),
                $this->formatBytes($diskSize)
            ));
        }
        $this->line('');
    }

    private function printTableInformation($connection): void
    {
        $this->line('# Table Information (Top 20 by Size) #####################');
        $this->line('');

        $tables = $connection->select('
            SELECT
                database_name,
                table_name,
                SUM(compressed_size) as compressed_size,
                SUM(uncompressed_size) as uncompressed_size
            FROM information_schema.columnar_segments
            GROUP BY database_name, table_name
            ORDER BY SUM(compressed_size) DESC
            LIMIT 20
        ');

        $rowCounts = $connection->select("
            SELECT
                database_name,
                table_name,
                SUM(rows) as row_count
            FROM information_schema.TABLE_STATISTICS
            WHERE partition_type = 'master'
            GROUP BY database_name, table_name
        ");

        $rowsByTable = [];
        foreach ($rowCounts as $row) {
            $key = $row->database_name . '.' . $row->table_name;
            $rowsByTable[$key] = $row->row_count;
        }

        $this->line(sprintf('  %-50s %15s %15s', 'Table', 'Rows', 'Size (Compressed)'));
        $this->line(sprintf('  %-50s %15s %15s', str_repeat('-', 50), str_repeat('-', 15), str_repeat('-', 15)));

        foreach ($tables as $table) {
            $fullName = $table->database_name . '.' . $table->table_name;
            $rows = $rowsByTable[$fullName] ?? 0;
            $this->line(sprintf(
                '  %-50s %15s %15s',
                $this->truncate($fullName, 50),
                number_format($rows),
                $this->formatBytes($table->compressed_size)
            ));
        }
        $this->line('');
    }

    private function printStorageAnalysis($connection): void
    {
        $this->line('# Storage Analysis #######################################');
        $this->line('');

        $storageTypes = $connection->select("
            SELECT
                CASE
                    WHEN storage_type LIKE '%columnstore%' THEN 'Columnstore'
                    WHEN storage_type LIKE '%rowstore%' THEN 'Rowstore'
                    ELSE storage_type
                END as storage_type,
                COUNT(DISTINCT CONCAT(database_name, '.', table_name)) as table_count
            FROM information_schema.TABLE_STATISTICS
            WHERE database_name NOT IN ('" . implode("','", $this->excludedDatabases) . "')
            GROUP BY 1
        ");

        $this->line('  Storage Type Distribution:');
        foreach ($storageTypes as $type) {
            $this->line(sprintf('    %-20s %d tables', $type->storage_type ?? 'Unknown', $type->table_count));
        }
        $this->line('');
    }

    private function printNoteworthyVariables($connection): void
    {
        $this->line('# Noteworthy Variables ###################################');
        $this->line('');

        $variables = [
            'version',
            'version_comment',
            'max_connections',
            'wait_timeout',
            'default_partitions_per_leaf',
            'leaf_failure_detection',
            'aggregator_failure_detection',
            'sync_replication',
            'redundancy_level',
            'snapshot_trigger_size',
            'columnstore_segment_rows',
        ];

        foreach ($variables as $var) {
            try {
                $result = $connection->select("SHOW VARIABLES LIKE '{$var}'");
                if (! empty($result)) {
                    $this->line(sprintf('  %-35s %s', $result[0]->Variable_name, $result[0]->Value));
                }
            } catch (Exception $e) {
                // Skip variables that don't exist
            }
        }
        $this->line('');
    }

    private function printNoteworthyTechnologies($connection): void
    {
        $this->line('# Noteworthy Technologies ################################');
        $this->line('');

        try {
            $fulltextCount = $connection->select("
                SELECT COUNT(*) as cnt
                FROM information_schema.STATISTICS
                WHERE index_type = 'FULLTEXT'
            ");
            $this->line(sprintf('  Full-Text Indexes:       %d', $fulltextCount[0]->cnt ?? 0));
        } catch (Exception $e) {
            $this->line('  Full-Text Indexes:       N/A');
        }

        try {
            $jsonColumns = $connection->select("
                SELECT COUNT(*) as cnt
                FROM information_schema.COLUMNS
                WHERE data_type = 'json'
                  AND table_schema NOT IN ('" . implode("','", $this->excludedDatabases) . "')
            ");
            $this->line(sprintf('  JSON Columns:            %d', $jsonColumns[0]->cnt ?? 0));
        } catch (Exception $e) {
            $this->line('  JSON Columns:            N/A');
        }

        try {
            $triggers = $connection->select('SELECT COUNT(*) as cnt FROM information_schema.TRIGGERS');
            $this->line(sprintf('  Triggers:                %d', $triggers[0]->cnt ?? 0));
        } catch (Exception $e) {
            $this->line('  Triggers:                N/A');
        }

        try {
            $procedures = $connection->select("
                SELECT COUNT(*) as cnt
                FROM information_schema.ROUTINES
                WHERE routine_type = 'PROCEDURE'
            ");
            $this->line(sprintf('  Stored Procedures:       %d', $procedures[0]->cnt ?? 0));
        } catch (Exception $e) {
            $this->line('  Stored Procedures:       N/A');
        }

        $this->line('  Foreign Keys:            N/A (SingleStore does not enforce FK constraints)');
        $this->line('');
    }

    private function printProcesslist($connection): void
    {
        $this->line('# Processlist Summary ####################################');
        $this->line('');

        $processes = $connection->select('SHOW PROCESSLIST');

        $byCommand = [];
        foreach ($processes as $proc) {
            $cmd = $proc->Command ?? 'Unknown';
            $byCommand[$cmd] = ($byCommand[$cmd] ?? 0) + 1;
        }

        $this->line('  By Command:');
        arsort($byCommand);
        foreach ($byCommand as $cmd => $count) {
            $this->line(sprintf('    %-20s %d', $cmd, $count));
        }

        $this->line('');
        $this->line(sprintf('  Total Connections: %d', count($processes)));
        $this->line('');
    }

    private function printStatusCounters($connection): void
    {
        $this->line('# Status Counters ########################################');
        $this->line('');

        $counters = [
            'Uptime',
            'Threads_connected',
            'Threads_running',
            'Questions',
            'Slow_queries',
            'Bytes_received',
            'Bytes_sent',
            'Rows_read',
            'Rows_affected_by_writes',
            'Successful_write_queries',
            'Failed_read_queries',
            'Failed_write_queries',
        ];

        foreach ($counters as $counter) {
            try {
                $result = $connection->select("SHOW GLOBAL STATUS LIKE '{$counter}'");
                if (! empty($result)) {
                    $value = $result[0]->Value;
                    if (is_numeric($value) && $value > 1000000) {
                        $value = number_format($value);
                    }
                    $this->line(sprintf('  %-30s %s', $result[0]->Variable_name, $value));
                }
            } catch (Exception $e) {
                // Skip counters that don't exist
            }
        }
        $this->line('');
    }

    private function printSchemaSummary($connection): void
    {
        $this->line('# Schema Summary #########################################');
        $this->line('');

        $databases = $connection->select("
            SELECT
                table_schema as db,
                COUNT(*) as table_count,
                SUM(CASE WHEN table_type = 'VIEW' THEN 1 ELSE 0 END) as view_count
            FROM information_schema.TABLES
            WHERE table_schema NOT IN ('" . implode("','", $this->excludedDatabases) . "')
            GROUP BY table_schema
            ORDER BY table_count DESC
        ");

        $this->line(sprintf('  %-30s %10s %10s', 'Database', 'Tables', 'Views'));
        $this->line(sprintf('  %-30s %10s %10s', str_repeat('-', 30), str_repeat('-', 10), str_repeat('-', 10)));

        foreach ($databases as $db) {
            $this->line(sprintf(
                '  %-30s %10d %10d',
                $db->db,
                $db->table_count - $db->view_count,
                $db->view_count
            ));
        }
        $this->line('');
        $this->line('# End of Report ##########################################');
        $this->line('');
    }

    private function formatBytes(int $bytes): string
    {
        if ($bytes >= 1099511627776) {
            return sprintf('%.2f TB', $bytes / 1099511627776);
        }
        if ($bytes >= 1073741824) {
            return sprintf('%.2f GB', $bytes / 1073741824);
        }
        if ($bytes >= 1048576) {
            return sprintf('%.2f MB', $bytes / 1048576);
        }
        if ($bytes >= 1024) {
            return sprintf('%.2f KB', $bytes / 1024);
        }

        return $bytes . ' B';
    }

    private function truncate(string $str, int $length): string
    {
        if (strlen($str) <= $length) {
            return $str;
        }

        return substr($str, 0, $length - 3) . '...';
    }
}