Introduction
High click and conversion volumes quickly strain relational databases: growing tables, longer queries and delayed commission calculations. For operators of Affilentra who prefer self‑hosted control and perpetual licenses, scalable MySQL strategies are essential. This article describes pragmatic patterns — partitioning, time sharding, hot tables, archive pipelines and per‑merchant sharding — with a focus on data integrity and auditable commission calculations.
Design principles before implementation
- Separate roles: Software operators, merchants and partners retain clear responsibilities; data access and ownership of merchant data must be represented.
- Preserve tracking integrity: Tracking events must remain idempotent and tamper‑proof; archives must not affect billing processes.
- Hot vs. cold: Keep current data performant, archive older data space‑efficiently.
- Consistent queries: Queries for commission calculation should be deterministic and reproducible — even after archiving.
Partitioning: Time‑based & Merchant‑based
Partitioning reduces I/O and improves partition pruning when queries use the partition key. Two proven variants:
Time‑based (Range) Partitioning
A good choice for click and conversion logs: partitions by month or week. Advantage: simple archiving of entire time slices.
Practical example (conceptual):
CREATE TABLE clicks (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
merchant_id INT NOT NULL,
partner_id INT NULL,
click_uuid CHAR(36) NOT NULL,
created_at DATETIME NOT NULL,
payload JSON,
UNIQUE KEY ux_click_uuid (click_uuid)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
Important: Queries must include WHERE created_at BETWEEN ... or WHERE created_at >= ... so that MySQL can use partition pruning.
Merchant or LIST Partitioning
For a few very large merchants, LIST partitioning by merchant_id can make sense, or a combination (subpartitioning). Advantage: isolated hot I/O per merchant.
Sharding: Horizontal distribution
Partitioning helps within a single database. Under extreme load, horizontal sharding becomes necessary: multiple MySQL instances managed by a routing layer in Affilentra (or a proxy).
- Choice of key: merchant_id as shard key allows isolation by merchant; alternatively Hash(partner_id) for load distribution.
- Topology: Small merchants in a shared shard, high‑volume merchants in their own shards (single tenant DB or schema).
- Central metadata: Keep partner profiles, program rules and billing logs central so calculations remain consistent.
Affilentra concept: separation of roles enables per‑merchant isolation without changing business logic — ideal for a gradual sharding migration.
Hot Tables & Nearline Architecture
Practical rule: only the last N days (e.g. 7–30 days, depending on the business) remain in the hot tables; older data is moved into a compact, query‑optimized archive.
- Hot table: same structure as the live table, optimized for fast inserts and frequent reads.
- Cold/archive table: compressed, possibly a different storage engine or hardware with higher latency.
- Partition exchange: In MySQL you can "exchange" partitions (EXCHANGE PARTITION) or move quickly with RENAME/ALTER — minimizes downtime.
Archive Pipelines: move safely and reproducibly
Archiving workflow in steps:
- Set markers: Mark clicks/conversions that are still relevant for billing (e.g. status = 'pending_settlement').
- Snapshot before move: Before moving data, write a settlement snapshot into a separate table so calculations remain reproducible.
- Move by batch: Move complete partitions or in batches with consistent IDs; use atomic steps (e.g. RENAME + INSERT INTO ... SELECT) within the same DB where possible.
- Verification & reconciliation: Compare counters (row counts, checksums) before and after the move; errors should have a rollback path.
Tools: pt‑archiver (Percona) or custom ETL jobs. What matters is deterministic behavior and logging every change so billing can be audited.
Integrity Rules for Commission Calculation
- Idempotent events: Prevent duplicate processing of clicks and conversions by using a unique click_uuid.
- Settlement status: Use status fields (pending, validated, paid). Only archive records with settled = true or create an immutable settlement snapshot.
- Transactions: Perform validations and commission calculations within controlled transactions or distributed workflows.
- Audit log: Each payout should reference the used click IDs and conversion snapshots; this simplifies reconciliation after archiving.
Per‑Merchant Strategies
For large merchants, isolation is recommended:
- Dedicated schema/DB: Run high-volume merchants in their own databases; this reduces replication and backup conflicts.
- Differentiated partitioning: Within the merchant schema further partition by time; this enables monthly merchant-specific archiving.
- Quota and SLA per merchant: Monitor I/O, locks and query times per merchant; scaling decisions should be cost- and state-driven.
Affilentra supports role-specific permissions and makes per-merchant isolation practical: merchants retain control over products and creatives, operators retain infrastructure control.
Operations & Monitoring
- Query profiling: Identify slow queries and adjust index/partition strategy.
- Partition hygiene: Automate adding/removing time partitions to avoid too many partitions.
- Backups and restore tests: Test restore scenarios for hot and archive data separately.
- Metrics: Monitor insert rate, read latency, lock waits, partition pruning rate and archival throughput.
Summary: Incremental Implementation Plan
- Analyze: Identify top queries and hotspots (by merchant, partner, time).
- Partition: Start time-based, audit WHERE clauses so pruning takes effect.
- Hot/tiered storage: Hot table for current data, archive for cold data; use partition exchange.
- Per-merchant isolation: Set up separate DBs or shards for high-volume merchants.
- Archive pipeline & snapshots: Create settlement snapshots, perform batch moves with reconciliation.
- Monitoring & iterate: Observe metrics and adjust the strategy.
Practical Benefit for Affilentra Operators
These patterns let operators retain data sovereignty in a self‑hosted environment and prevent tracking loss or incorrect commission calculation. Affilentra‑architecture with separated roles facilitates per‑merchant isolation and allows controlled migrations to partitioned or sharded topologies — without sacrificing business logic.
Act now: For a scalable installation and consulting on partitioning and sharding strategies choose your entry: Choose entry.
Next step
Try Affilentra yourself
A unified platform that transparently brings together product provisioning, tracking, conversion validation, commission calculation and payout across clearly separated roles; option for a self‑hosted installation with a perpetual license without revenue sharing; merchants keep control over products, terms and creatives; partners receive personal tracking links and a traceable payout history.