Partitioning for ASTPP CDRs Table

General questions DB
1

Following worked for me so that sharing for others use. Please do remember to take a full backup of the db using root

mysqldump -u root -p --single-transaction --routines --triggers astpp > /opt/astpp_backup_$(date +%Y%m%d_%H%M%S).sql

-- Pure Partitioning for ASTPP CDRs Table
-- Uses only callstart column - ignores reseller_id and other constraints

-- ============================================================================
-- PHASE 1: VERIFY CURRENT STRUCTURE
-- ============================================================================

-- Check current index (we'll ignore reseller_id for partitioning)
SHOW INDEX FROM cdrs;

-- Verify callstart data distribution
SELECT 
    YEAR(callstart) as year,
    MONTH(callstart) as month,
    COUNT(*) as record_count
FROM cdrs
GROUP BY YEAR(callstart), MONTH(callstart)
ORDER BY year DESC, month DESC;

-- ============================================================================
-- PHASE 2: PURE PARTITIONING (ONLY CALLSTART)
-- ============================================================================

-- Partition using only callstart column
-- This ignores reseller_id and other constraints completely

ALTER TABLE cdrs PARTITION BY RANGE (YEAR(callstart) * 100 + MONTH(callstart)) (
    -- 2024 partitions (historical data)
    PARTITION p2024_07 VALUES LESS THAN (202407),
    PARTITION p2024_08 VALUES LESS THAN (202408),
    PARTITION p2024_09 VALUES LESS THAN (202409),
    PARTITION p2024_10 VALUES LESS THAN (202410),
    PARTITION p2024_11 VALUES LESS THAN (202411),
    PARTITION p2024_12 VALUES LESS THAN (202412),
    
    -- 2025 partitions (current data)
    PARTITION p2025_01 VALUES LESS THAN (202501),
    PARTITION p2025_02 VALUES LESS THAN (202502),
    PARTITION p2025_03 VALUES LESS THAN (202503),
    PARTITION p2025_04 VALUES LESS THAN (202504),
    PARTITION p2025_05 VALUES LESS THAN (202505),
    PARTITION p2025_06 VALUES LESS THAN (202506),
    PARTITION p2025_07 VALUES LESS THAN (202507),
    PARTITION p2025_08 VALUES LESS THAN (202508),
    PARTITION p2025_09 VALUES LESS THAN (202509),
    PARTITION p2025_10 VALUES LESS THAN (202510),
    PARTITION p2025_11 VALUES LESS THAN (202511),
    PARTITION p2025_12 VALUES LESS THAN (202512),
    
    -- Future partition for data beyond 2025
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ============================================================================
-- PHASE 3: VERIFY PARTITIONING
-- ============================================================================

-- Check partition distribution
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) as data_size_mb
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA = 'astpp' 
AND TABLE_NAME = 'cdrs'
ORDER BY PARTITION_ORDINAL_POSITION;

-- Verify data distribution across partitions
SELECT 
    PARTITION_NAME,
    COUNT(*) as record_count
FROM cdrs
GROUP BY PARTITION_NAME
ORDER BY PARTITION_NAME;

-- ============================================================================
-- PHASE 4: TEST PARTITION PRUNING
-- ============================================================================

-- Test partition pruning for July 2025 (should only scan p2025_07)
EXPLAIN SELECT COUNT(*) FROM cdrs 
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';

-- Test partition pruning for August 2025 (should only scan p2025_08)
EXPLAIN SELECT COUNT(*) FROM cdrs 
WHERE callstart >= '2025-08-01' AND callstart < '2025-09-01';

-- ============================================================================
-- PHASE 5: PERFORMANCE TESTING
-- ============================================================================

-- Test July 2025 query performance
SELECT 
    'July 2025' as period,
    COUNT(*) as total_calls,
    SUM(billseconds) as total_duration,
    SUM(debit) as total_revenue
FROM cdrs 
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';

-- Test August 2025 query performance
SELECT 
    'August 2025' as period,
    COUNT(*) as total_calls,
    SUM(billseconds) as total_duration,
    SUM(debit) as total_revenue
FROM cdrs 
WHERE callstart >= '2025-08-01' AND callstart < '2025-09-01';

-- ============================================================================
-- PHASE 6: MONITORING
-- ============================================================================

-- Monitor partition sizes
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) as data_size_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) as index_size_mb
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA = 'astpp' 
AND TABLE_NAME = 'cdrs'
ORDER BY PARTITION_ORDINAL_POSITION;

-- Check partition pruning effectiveness
EXPLAIN SELECT COUNT(*) FROM cdrs 
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';

-- ============================================================================
-- PHASE 7: ROLLBACK (IF NEEDED)
-- ============================================================================

-- If partitioning causes issues, remove it (keeps data intact)
-- ALTER TABLE cdrs REMOVE PARTITIONING;

-- ============================================================================
-- USAGE EXAMPLES
-- ============================================================================

/*
-- Query specific date range (automatic partition pruning)
SELECT COUNT(*) FROM cdrs 
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01';

-- Daily call counts for July 2025
SELECT 
    DATE(callstart) as call_date,
    COUNT(*) as daily_calls,
    SUM(billseconds) as total_bill_seconds,
    SUM(debit) as total_debit
FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01'
GROUP BY DATE(callstart)
ORDER BY call_date;

-- Account analysis for July 2025
SELECT 
    accountid,
    COUNT(*) as call_count,
    SUM(billseconds) as total_bill_seconds,
    SUM(debit) as total_debit
FROM cdrs
WHERE callstart >= '2025-07-01' AND callstart < '2025-08-01'
GROUP BY accountid
ORDER BY call_count DESC
LIMIT 10;
*/
1 Like