Database Sizing for Magento 2

Current Database Assessment

Measure Current Size

-- Total database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'magento'
GROUP BY table_schema;

-- Size by table
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    table_rows AS 'Rows'
FROM information_schema.tables
WHERE table_schema = 'magento'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Magento-specific tables
SELECT 
    'catalog_product_entity' AS 'Table',
    COUNT(*) AS 'Rows',
    ROUND(AVG(LENGTH(ROW())), 0) AS 'Avg Row Size'
FROM catalog_product_entity;

Data Composition

Typical Magento Database Composition:

Table Group              | % of Total | Growth Rate
-------------------------|------------|------------
catalog_product_*        | 35%        | High
catalog_category_*       | 10%        | Low
sales_order_*            | 25%        | High
customer_*               | 15%        | Medium
cms_*                    | 5%         | Low
log_*                    | 10%        | High (archive regularly)
other                    | 5%         | Low

Entity Count Estimation

// Estimate based on business metrics
$estimates = [
    'products' => 50000,           // SKUs
    'categories' => 500,           // Categories
    'customers' => 200000,         // Registered users
    'orders' => 500000,            // Total orders
    'order_items' => 1500000,      // 3 items per order avg
    'reviews' => 100000,           // Product reviews
    'wishlist' => 50000,           // Wishlist items
    'quotes' => 300000,            // Shopping carts
];

// Calculate storage per entity
$storagePerEntity = [
    'product' => 2,       // KB per product
    'order' => 5,         // KB per order
    'customer' => 1,      // KB per customer
    'order_item' => 0.5,  // KB per item
];

// Total estimated size
$totalKB = 0;
foreach ($estimates as $entity => $count) {
    $kb = $storagePerEntity[$entity] ?? 0.5;
    $totalKB += $count * $kb;
}
// Total: ~8.5 MB (actual will be larger due to indexes)

Growth Rate Modeling

Business Growth Assumptions

$growthRates = [
    'products' => 0.10,        // 10% annually
    'customers' => 0.25,       // 25% annually
    'orders' => 0.30,          // 30% annually
    'reviews' => 0.40,         // 40% annually
];

// Project 3 years
$currentOrders = 500000;
$annualGrowth = 0.30;

$year1 = $currentOrders * (1 + $annualGrowth);
$year2 = $year1 * (1 + $annualGrowth);
$year3 = $year2 * (1 + $annualGrowth);

// Year 1: 650,000
// Year 2: 845,000
// Year 3: 1,098,500

Storage Growth Projection

// Database size projection
function projectDatabaseSize(
    $currentSizeGB,
    $annualGrowthRate,
    $years
): array {
    $projections = [];
    $size = $currentSizeGB;
    
    for ($i = 1; $i <= $years; $i++) {
        $size *= (1 + $annualGrowthRate);
        $projections["year_{$i}"] = round($size, 2);
    }
    
    return $projections;
}

// Example: 50GB current, 30% annual growth
$projections = projectDatabaseSize(50, 0.30, 3);
// year_1: 65 GB
// year_2: 84.5 GB
// year_3: 109.85 GB

Log Table Growth

Log tables grow fastest:

Table                | Daily Growth | Annual Growth
---------------------|-------------|---------------
catalogsearch_*      | 100K rows   | 36M rows
report_*             | 50K rows    | 18M rows
log_url              | 20K rows    | 7M rows
log_visitor          | 50K rows    | 18M rows

Solution: Archive old logs regularly

Archive Strategy

-- Archive orders older than 2 years
CREATE TABLE sales_order_archive AS
SELECT * FROM sales_order
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

DELETE FROM sales_order
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Partition by date
ALTER TABLE sales_order
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Growth Planning

Capacity Thresholds

Database Size    | Action Required
-----------------|----------------------------------
< 50 GB         | Standard configuration
50-100 GB       | Add read replica
100-200 GB      | Consider partitioning
200-500 GB      | Implement archiving
> 500 GB        | Evaluate sharding

Monitoring

-- Monitor growth
CREATE TABLE db_growth_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    measured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_size_mb DECIMAL(10,2),
    data_size_mb DECIMAL(10,2),
    index_size_mb DECIMAL(10,2)
);

-- Weekly measurement
INSERT INTO db_growth_log (total_size_mb, data_size_mb, index_size_mb)
SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2),
    ROUND(SUM(data_length) / 1024 / 1024, 2),
    ROUND(SUM(index_length) / 1024 / 1024, 2)
FROM information_schema.tables
WHERE table_schema = 'magento';

Storage Requirements

Raw Storage Calculation

Database Size: 100 GB
Replication: 3 copies (primary + 2 replicas)
Backup: 2 copies (full + incremental)
Logs: 20% of database size

Raw Storage Needed:
- Database: 100 GB × 3 = 300 GB
- Backups: 100 GB × 2 = 200 GB
- Logs: 100 GB × 0.2 = 20 GB
- Total: 520 GB

Recommended: 1 TB (with headroom)

IOPS Requirements

Workload          | IOPS Needed | Storage Type
------------------|-------------|-------------
Light (< 50K PV) | 1,000       | Standard SSD
Medium (50-200K)  | 3,000       | Provisioned SSD
Heavy (200K-500K) | 10,000      | Provisioned SSD
Peak (500K+)     | 20,000+     | Provisioned SSD

Storage Type Selection

Type       | Use Case          | Cost/GB/Month
-----------|-------------------|---------------
HDD        | Archives, logs    | $0.02
Standard   | Development       | $0.10
SSD        | Production DB     | $0.20
Provisioned| High-performance  | $0.30+

For Magento:
- Database: Provisioned SSD
- Media: Standard SSD
- Backups: HDD
- Logs: HDD (archive to S3)

Cost Estimation

$storageCosts = [
    'database' => [
        'size_gb' => 100,
        'type' => 'provisioned_ssd',
        'cost_per_gb' => 0.30,
        'monthly' => 100 * 0.30
    ],
    'media' => [
        'size_gb' => 500,
        'type' => 'standard_ssd',
        'cost_per_gb' => 0.20,
        'monthly' => 500 * 0.20
    ],
    'backups' => [
        'size_gb' => 200,
        'type' => 'hdd',
        'cost_per_gb' => 0.02,
        'monthly' => 200 * 0.02
    ]
];

$totalMonthly = array_sum(array_column($storageCosts, 'monthly'));
// = 30 + 100 + 4 = $134

Storage Optimization

Table Optimization

-- Analyze table bloat
SELECT 
    table_name,
    data_free / 1024 / 1024 AS 'Free Space (MB)',
    ROUND(data_free / (data_length + index_length) * 100, 2) AS 'Fragmentation %'
FROM information_schema.tables
WHERE table_schema = 'magento'
AND data_free > 10485760  -- > 10MB free
ORDER BY data_free DESC;

-- Optimize fragmented tables
OPTIMIZE TABLE sales_order;
OPTIMIZE TABLE catalog_product_entity;

-- Or scheduled optimization
-- Run weekly during low-traffic period

Data Type Optimization

-- Review column types
SELECT 
    table_name,
    column_name,
    data_type,
    column_type
FROM information_schema.columns
WHERE table_schema = 'magento'
AND data_type IN ('text', 'blob')
AND column_type NOT LIKE '%tiny%';

-- Consider:
-- VARCHAR(255) → VARCHAR(100) if max length is 100
-- TEXT → VARCHAR if content is small
-- INT → SMALLINT for small ranges

Index Optimization

-- Find unused indexes
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'magento';

-- Remove unused indexes
DROP INDEX index_name ON table_name;

-- Benefit: less storage, faster writes

Compression

-- Enable InnoDB compression
ALTER TABLE large_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- Check compression ratio
SELECT 
    table_name,
    round(data_length/1024/1024, 2) as 'Data MB',
    round(index_length/1024/1024, 2) as 'Index MB'
FROM information_schema.tables
WHERE table_schema = 'magento'
ORDER BY data_length DESC;

Log Rotation

// Automate log cleanup
$logTables = [
    'catalogsearch_fulltext',
    'report_viewed_product',
    'log_url',
    'log_visitor',
    'log_customer'
];

foreach ($logTables as $table) {
    // Delete rows older than 90 days
    $sql = "DELETE FROM {$table} WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)";
    $connection->query($sql);
    
    // Optimize after cleanup
    $connection->query("OPTIMIZE TABLE {$table}");
}

Monitoring Dashboard

$dashboardMetrics = [
    'current_size' => $this->getDatabaseSize(),
    'growth_rate' => $this->getGrowthRate(),
    'top_tables' => $this->getLargestTables(),
    'fragmentation' => $this->getFragmentationLevels(),
    'days_until_full' => $this->calculateDaysUntilFull(),
];