Step-by-Step Guide: Offloading high-frequency vendor commission records metadata writes to a Redis KV store
Architectural Rationale: Why Redis for Commission Metadata?
WordPress, particularly when dealing with e-commerce plugins that generate high volumes of vendor commission records, can experience significant I/O bottlenecks. Traditional relational databases (like MySQL) are optimized for transactional integrity and complex queries, but they can struggle with the sheer velocity of small, frequent writes required for logging commission events. Each commission record, even if just metadata (e.g., transaction ID, vendor ID, commission amount, timestamp, status), represents a distinct write operation. When thousands of these occur per minute, the database’s write-ahead log (WAL), buffer pool contention, and index updates become a performance bottleneck. Redis, as an in-memory data structure store, excels at high-throughput, low-latency key-value operations. By offloading the *initial* logging of commission metadata to Redis, we decouple the high-frequency write path from the primary transactional database, allowing the latter to focus on core e-commerce operations and reporting. This strategy significantly improves the responsiveness of commission generation and reduces the load on MySQL.
Redis Setup and Configuration for High Throughput
For this use case, we’ll leverage Redis’s simple key-value capabilities. The primary goal is speed and durability for the metadata itself, not complex querying within Redis. We’ll configure Redis for persistence, but with a strategy that prioritizes write performance.
`redis.conf` Tuning for Writes
The following `redis.conf` settings are crucial for optimizing write performance while maintaining a reasonable level of data safety. We’ll prioritize AOF (Append Only File) over RDB snapshots for better durability on frequent writes, but tune AOF settings to minimize write amplification.
# General Settings daemonize yes pidfile /var/run/redis_6379.pid logfile /var/log/redis/redis-server.log port 6379 bind 127.0.0.1 # Or your specific network interface # Persistence - Prioritize AOF for frequent writes appendonly yes appendfilename "appendonly.aof" # Appendfsync: 'everysec' is a good balance between durability and performance. # 'always' is too slow, 'no' is too risky for this use case. appendfsync everysec # AOF Rewrite - Essential to prevent AOF file from growing indefinitely auto-aof-rewrite-percentage 100 # Rewrite when the AOF file grows by 100% auto-aof-rewrite-min-size 64mb # Minimum size before a rewrite is triggered # Memory Management - Adjust based on your available RAM and expected data size # maxmemory <bytes> # Uncomment and set if you need to limit memory usage # maxmemory-policy volatile-lru # Or another policy if maxmemory is set # Client connection limits maxclients 10000 # Adjust as needed # Other performance-related settings tcp-backlog 511 # Default, but good to be aware of # disable-command rename-command CONFIG "" # Consider disabling if not needed for security
After modifying `redis.conf`, restart the Redis server:
sudo systemctl restart redis-server
PHP Implementation: Writing Commission Metadata to Redis
We’ll use the popular phpredis extension for efficient interaction with Redis. The strategy is to use a unique key for each commission record, perhaps incorporating a timestamp and a unique identifier, and storing a serialized representation of the metadata. Alternatively, for simpler metadata, Redis hashes can be very efficient.
Using Redis Strings (Serialization)
This approach is flexible and can store any PHP data structure. We’ll use json_encode for serialization.
// Ensure you have the phpredis extension installed:
// sudo apt-get install php-redis or pecl install redis
$redis = new Redis();
try {
// Connect to Redis
$redis->connect('127.0.0.1', 6379);
// $redis->auth('your_redis_password'); // If authentication is enabled
$redis->select(0); // Select database 0
} catch (RedisException $e) {
// Handle connection error - log it, fallback, etc.
error_log("Redis connection failed: " . $e->getMessage());
// Potentially fall back to direct DB write or queueing
return false;
}
/**
* Logs commission metadata to Redis.
*
* @param int $transaction_id The ID of the transaction.
* @param int $vendor_id The ID of the vendor.
* @param float $commission_amount The calculated commission amount.
* @param string $status The status of the commission (e.g., 'pending', 'processed').
* @param array $extra_data Optional additional metadata.
* @return bool True on success, false on failure.
*/
function log_commission_metadata_to_redis(int $transaction_id, int $vendor_id, float $commission_amount, string $status = 'pending', array $extra_data = []): bool
{
global $redis; // Assuming $redis is globally available or passed in
$timestamp = time();
// Create a unique key. Consider adding a prefix for better organization.
// Example: 'comm_meta:tx_12345:vend_678:ts_1678886400'
$key = sprintf('comm_meta:tx_%d:vend_%d:ts_%d', $transaction_id, $vendor_id, $timestamp);
$metadata = [
'transaction_id' => $transaction_id,
'vendor_id' => $vendor_id,
'amount' => $commission_amount,
'status' => $status,
'created_at' => date('Y-m-d H:i:s', $timestamp),
'extra' => $extra_data,
];
// Serialize the metadata. JSON is generally efficient and human-readable.
$serialized_metadata = json_encode($metadata);
if ($serialized_metadata === false) {
error_log("Failed to JSON encode commission metadata for transaction {$transaction_id}.");
return false;
}
// Use SET command for simplicity. EX (expire) can be useful if you only need
// metadata for a short period, but for logging, we might omit it.
// If you need to ensure uniqueness and avoid overwrites, use SETNX.
// For high-frequency writes, SET is generally preferred.
$result = $redis->set($key, $serialized_metadata);
if ($result) {
// Optionally, set an expiration if metadata is temporary.
// $redis->expire($key, 86400); // e.g., expire after 24 hours
return true;
} else {
error_log("Failed to set commission metadata in Redis for key: {$key}");
return false;
}
}
// Example Usage:
// Assuming you have transaction_id, vendor_id, commission_amount available
// $success = log_commission_metadata_to_redis(12345, 678, 55.75, 'pending', ['order_item_id' => 9876]);
// if (!$success) {
// // Handle the error - perhaps attempt to write directly to MySQL as a fallback
// }
Using Redis Hashes (Structured Data)
For structured metadata where fields are known and consistent, Redis Hashes are more memory-efficient and allow for partial updates. This is often a better choice for commission records.
/**
* Logs commission metadata to Redis using a Hash.
*
* @param int $transaction_id The ID of the transaction.
* @param int $vendor_id The ID of the vendor.
* @param float $commission_amount The calculated commission amount.
* @param string $status The status of the commission (e.g., 'pending', 'processed').
* @param array $extra_data Optional additional metadata.
* @return bool True on success, false on failure.
*/
function log_commission_metadata_hash_to_redis(int $transaction_id, int $vendor_id, float $commission_amount, string $status = 'pending', array $extra_data = []): bool
{
global $redis; // Assuming $redis is globally available or passed in
$timestamp = time();
// Use a base key for the hash, e.g., 'comm_meta_hash:tx_12345:vend_678'
// We'll store multiple commission entries under a list associated with this key,
// or use a unique key per entry if individual retrieval is needed.
// For high-frequency writes, a single key per record is simpler.
$key = sprintf('comm_meta:tx_%d:vend_%d:ts_%d', $transaction_id, $vendor_id, $timestamp);
$fields = [
'transaction_id' => (string)$transaction_id, // Redis stores values as strings
'vendor_id' => (string)$vendor_id,
'amount' => (string)$commission_amount,
'status' => $status,
'created_at' => date('Y-m-d H:i:s', $timestamp),
];
// Merge extra data, ensuring keys don't conflict and are stringified.
foreach ($extra_data as $ekey => $evalue) {
$fields['extra_' . $ekey] = (string)$evalue; // Prefix extra fields to avoid collisions
}
// HMSET is deprecated in newer Redis versions, use HSET with multiple fields.
// The phpredis client handles this gracefully.
// $result = $redis->hMSet($key, $fields); // Older way
// Use HSET with multiple field-value pairs.
// Convert array to arguments for HSET: $redis->hSet($key, 'field1', 'value1', 'field2', 'value2', ...)
$args = [$key];
foreach ($fields as $field => $value) {
$args[] = $field;
$args[] = $value;
}
$result = $redis->executeCommand('HSET', $args);
if ($result !== false) { // HSET returns the number of fields added, or 0 if all fields already existed and were updated.
// Optionally, set an expiration if metadata is temporary.
// $redis->expire($key, 86400); // e.g., expire after 24 hours
return true;
} else {
error_log("Failed to set commission metadata hash in Redis for key: {$key}");
return false;
}
}
// Example Usage:
// $success = log_commission_metadata_hash_to_redis(12346, 679, 70.20, 'pending', ['order_item_id' => 9877, 'notes' => 'Special case']);
// if (!$success) {
// // Handle error
// }
Asynchronous Processing and Database Synchronization
Writing directly to Redis within the request lifecycle is fast, but it doesn’t solve the problem of getting this data into your primary MySQL database for reporting and reconciliation. This is where asynchronous processing becomes critical. We need a mechanism to periodically read data from Redis and insert it into MySQL.
Option 1: Cron Job with Redis Scan and MySQL Batch Inserts
A common and robust approach is to use a scheduled cron job (or a WordPress cron equivalent, though a system cron is generally more reliable for heavy tasks) that scans Redis for new commission metadata, processes it, and inserts it into MySQL in batches.
// cron_sync_commissions.php
connect('127.0.0.1', 6379);
$redis->select(0);
} catch (RedisException $e) {
error_log("Redis connection failed in cron job: " . $e->getMessage());
exit(1); // Exit with error code
}
// --- MySQL Connection ---
// Using PDO for robust database interaction
$dbHost = 'localhost';
$dbName = 'your_wordpress_db';
$dbUser = 'your_db_user';
$dbPass = 'your_db_password';
$dbCharset = 'utf8mb4';
$dsn = "mysql:host=$dbHost;dbname=$dbName;charset=$dbCharset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $dbUser, $dbPass, $options);
} catch (\PDOException $e) {
error_log("MySQL connection failed in cron job: " . $e->getMessage());
exit(1); // Exit with error code
}
// --- Synchronization Logic ---
$cursor = '0'; // Initial cursor for SCAN
$batchSize = 1000; // Number of keys to process per SCAN iteration
$mysqlBatchSize = 500; // Number of records to insert into MySQL per transaction
$processedCount = 0;
$insertedCount = 0;
// Define the Redis key pattern to scan
$keyPattern = 'comm_meta:*'; // Adjust if you used a more specific prefix
// Use SCAN to iterate through keys without blocking the Redis server
do {
// SCAN returns an array: [next_cursor, [key1, key2, ...]]
$result = $redis->scan($cursor, $keyPattern, $batchSize);
$cursor = $result[0];
$keys = $result[1];
if (empty($keys)) {
continue;
}
$recordsToInsert = [];
$keysToDeleteFromRedis = []; // To remove processed keys
foreach ($keys as $key) {
$processedCount++;
$serialized_metadata = $redis->get($key);
if ($serialized_metadata === false) {
// Key might have been deleted or expired between SCAN and GET
continue;
}
$metadata = json_decode($serialized_metadata, true);
if (json_last_error() !== JSON_ERROR_NONE || !is_array($metadata)) {
error_log("Failed to decode JSON for Redis key: {$key}");
// Decide whether to delete this malformed key or leave it
$keysToDeleteFromRedis[] = $key; // Remove malformed data
continue;
}
// Prepare data for MySQL insertion
// Ensure all required columns exist and data types are correct
$record = [
'transaction_id' => $metadata['transaction_id'] ?? null,
'vendor_id' => $metadata['vendor_id'] ?? null,
'amount' => $metadata['amount'] ?? null,
'status' => $metadata['status'] ?? 'pending',
'created_at' => $metadata['created_at'] ?? date('Y-m-d H:i:s'),
// Map extra data, assuming a flexible 'commission_details' JSON column in MySQL
'commission_details' => isset($metadata['extra']) && is_array($metadata['extra']) ? json_encode($metadata['extra']) : '{}',
];
// Basic validation before adding to batch
if ($record['transaction_id'] === null || $record['vendor_id'] === null || $record['amount'] === null) {
error_log("Missing critical data in Redis record for key: {$key}");
$keysToDeleteFromRedis[] = $key; // Remove incomplete data
continue;
}
$recordsToInsert[] = $record;
// Mark key for deletion *after* successful processing and adding to batch
$keysToDeleteFromRedis[] = $key;
// Batch insertion into MySQL
if (count($recordsToInsert) >= $mysqlBatchSize) {
if (insert_batch_to_mysql($pdo, $recordsToInsert)) {
$insertedCount += count($recordsToInsert);
$recordsToInsert = []; // Clear batch
} else {
// Log error, decide on retry strategy or manual intervention
error_log("Batch insert to MySQL failed. Data not committed.");
// Do NOT delete keys from Redis if batch insert failed, they need reprocessing.
$keysToDeleteFromRedis = []; // Clear keys to delete for this failed batch
break; // Stop processing this SCAN iteration to avoid further failures
}
}
}
// Insert any remaining records in the last batch
if (!empty($recordsToInsert)) {
if (insert_batch_to_mysql($pdo, $recordsToInsert)) {
$insertedCount += count($recordsToInsert);
} else {
error_log("Final batch insert to MySQL failed.");
$keysToDeleteFromRedis = []; // Clear keys to delete for this failed batch
}
}
// Delete successfully processed keys from Redis
if (!empty($keysToDeleteFromRedis)) {
// Use DEL command. It accepts multiple keys.
// Note: If a key was processed and added to $recordsToInsert, but the batch insert failed,
// we *don't* delete it from Redis. This is handled by clearing $keysToDeleteFromRedis on failure.
$redis->del($keysToDeleteFromRedis);
}
} while ($cursor !== '0'); // Continue until SCAN returns cursor '0'
echo "Commission sync complete. Processed {$processedCount} Redis keys. Inserted {$insertedCount} records into MySQL.\n";
/**
* Inserts a batch of commission records into the MySQL database.
* Assumes a table named 'wp_commission_records' with appropriate columns.
*
* @param PDO $pdo The PDO database connection.
* @param array $records An array of associative arrays, each representing a record.
* @return bool True on success, false on failure.
*/
function insert_batch_to_mysql(PDO $pdo, array $records): bool
{
if (empty($records)) {
return true;
}
// Define your table structure. Example:
// CREATE TABLE wp_commission_records (
// id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
// transaction_id BIGINT UNSIGNED NOT NULL,
// vendor_id INT UNSIGNED NOT NULL,
// amount DECIMAL(10, 2) NOT NULL,
// status VARCHAR(50) NOT NULL DEFAULT 'pending',
// created_at DATETIME NOT NULL,
// commission_details JSON NULL, -- Store extra data as JSON
// processed_at TIMESTAMP NULL DEFAULT NULL, -- Track when it was synced
// INDEX idx_tx_vend (transaction_id, vendor_id),
// INDEX idx_status (status)
// );
$sql = "INSERT INTO wp_commission_records (transaction_id, vendor_id, amount, status, created_at, commission_details, processed_at) VALUES (:transaction_id, :vendor_id, :amount, :status, :created_at, :commission_details, NOW())";
$stmt = $pdo->prepare($sql);
// Use a transaction for atomicity
$pdo->beginTransaction();
try {
foreach ($records as $record) {
$stmt->execute([
':transaction_id' => $record['transaction_id'],
':vendor_id' => $record['vendor_id'],
':amount' => $record['amount'],
':status' => $record['status'],
':created_at' => $record['created_at'],
':commission_details' => $record['commission_details'],
]);
}
$pdo->commit();
return true;
} catch (\PDOException $e) {
$pdo->rollBack();
error_log("MySQL batch insert error: " . $e->getMessage());
return false;
}
}
// --- Scheduling the Cron Job ---
// Add the following line to your crontab (e.g., by running `crontab -e`):
// */5 * * * * /usr/bin/php /path/to/your/cron_sync_commissions.php >> /var/log/commission_sync.log 2>&1
// This runs the script every 5 minutes. Adjust frequency based on your write volume.
Option 2: Redis Streams for Event Sourcing
For a more modern and event-driven architecture, Redis Streams can be used. Each commission metadata write becomes an entry in a stream. A separate consumer application (e.g., a PHP daemon, Node.js service, or even a Python script) can then read from the stream and write to MySQL. This decouples the producer (WordPress) and consumer (sync process) more effectively and provides better fault tolerance and replayability.
// Producer side (within WordPress) using phpredis
function log_commission_to_redis_stream(int $transaction_id, int $vendor_id, float $commission_amount, string $status = 'pending', array $extra_data = []): bool
{
global $redis; // Assuming $redis is connected
$timestamp = time();
$message_id = '*'; // Let Redis generate the ID
$data = [
'transaction_id' => (string)$transaction_id,
'vendor_id' => (string)$vendor_id,
'amount' => (string)$commission_amount,
'status' => $status,
'created_at' => date('Y-m-d H:i:s', $timestamp),
'extra' => json_encode($extra_data), // Encode complex data
];
try {
// XADD stream_name ID field1 value1 field2 value2 ...
$result = $redis->xAdd('commission_stream', $message_id, $data);
if ($result) {
return true;
} else {
error_log("Failed to add commission data to Redis stream 'commission_stream'.");
return false;
}
} catch (RedisException $e) {
error_log("Redis stream error: " . $e->getMessage());
return false;
}
}
// Consumer side (separate PHP script, e.g., a daemon)
// This script would run continuously.
/*
// Example consumer logic (simplified)
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->select(0);
$streamName = 'commission_stream';
$consumerGroup = 'commission_processor_group';
$consumerName = 'processor_'.uniqid(); // Unique name for this consumer instance
// Create consumer group if it doesn't exist
try {
$redis->xGroupCreate($streamName, $consumerGroup, '0', true); // '0' means create from the beginning if not exists
} catch (RedisException $e) {
// Ignore error if group already exists
if (strpos($e->getMessage(), 'BUSYGROUP') === false) {
error_log("Error creating consumer group: " . $e->getMessage());
// Handle other critical errors
}
}
echo "Starting consumer for stream '{$streamName}'...\n";
while (true) {
// Read from the stream, using XREADGROUP
// block: milliseconds to wait if no data (0 means no block)
// count: max messages to return
// streams: array of [stream_name => '>', ...] where '>' means new messages for this consumer group
$read = $redis->xReadGroup($consumerGroup, $consumerName, [$streamName => '>'], 1000); // Block for 1 second
if ($read) {
foreach ($read as $streamData) { // $streamData is like ['stream_name' => [['id' => '...', 'data' => {...}]]]
$messages = $streamData; // Assuming only one stream is read
foreach ($messages as $message) {
$messageId = $message['id'];
$data = $message['data'];
// Process the message data and insert into MySQL
$success = process_commission_message_for_db($data); // Implement this function
if ($success) {
// Acknowledge the message so it's removed from pending entries
$redis->xAck($streamName, $consumerGroup, [$messageId]);
echo "Processed and acknowledged message ID: {$messageId}\n";
} else {
error_log("Failed to process message ID: {$messageId}. Will retry.");
// Do not acknowledge, message will remain pending for retry.
// Implement retry limits or dead-letter queueing for persistent failures.
}
}
}
}
// Small sleep to prevent tight loop if no data and no block
// usleep(100000); // 100ms
}
function process_commission_message_for_db(array $data): bool {
// Use PDO to connect to MySQL and insert $data
// Similar logic to insert_batch_to_mysql but for a single record.
// Return true on success, false on failure.
// Example:
// $pdo = get_db_connection(); // Your PDO connection function
// $sql = "INSERT INTO wp_commission_records (transaction_id, vendor_id, amount, status, created_at, commission_details, processed_at) VALUES (:tx_id, :v_id, :amt, :stat, :created, :details, NOW())";
// $stmt = $pdo->prepare($sql);
// try {
// $stmt->execute([
// ':tx_id' => $data['transaction_id'],
// ':v_id' => $data['vendor_id'],
// ':amt' => $data['amount'],
// ':stat' => $data['status'],
// ':created' => $data['created_at'],
// ':details' => $data['extra'] ?? '{}',
// ]);
// return true;
// } catch (\PDOException $e) {
// error_log("MySQL insert error from stream: " . $e->getMessage());
// return false;
// }
return true; // Placeholder
}
*/
Monitoring and Maintenance
Consistent monitoring is key to ensuring this offloading strategy remains effective. Key metrics to watch include:
- Redis Memory Usage: Ensure Redis is not exceeding its `maxmemory` limit. Monitor `redis-cli INFO memory`.
- Redis Latency: Use `redis-cli –latency -c -n 100` to check for high latency spikes.
- AOF Rewrite Progress: Monitor Redis logs for AOF rewrite operations. Frequent rewrites might indicate inefficient data structures or a need to adjust `auto-aof-rewrite-percentage`.
- Cron Job Execution: Ensure the synchronization cron job runs on schedule and completes within an acceptable timeframe. Check its log file for errors.
- MySQL Write Load: Monitor your primary database’s write I/O. The goal is to see a reduction in load from commission-related writes.
- Redis Consumer Lag (for Streams): If using Redis Streams, monitor consumer lag using `XINFO CONSUMERS stream_name group_name`.
Regularly review the Redis persistence files (`appendonly.aof`) and consider periodic RDB snapshots if you need a point-in-time backup, though for high-frequency writes, AOF is the primary durability mechanism.
Conclusion
Offloading high-frequency metadata writes for vendor commissions to Redis is a powerful technique for optimizing WordPress e-commerce performance. By carefully configuring Redis, implementing efficient PHP code for writes, and establishing a robust asynchronous synchronization process to your primary database, you can significantly reduce I/O bottlenecks and improve the overall responsiveness and scalability of your platform.