Skip to main content

Performance Tuning

Optimize inventory sync for large catalogs and high-volume operations.

Overview​

The inventory sync system is designed to handle stores with 20,000+ SKUs efficiently. This guide covers performance optimization techniques and best practices.

Performance Benchmarks​

Expected Sync Times​

SKU CountSync DurationNotes
1,0001-2 minutesSmall store
5,0003-5 minutesMedium store
10,0005-10 minutesLarge store
20,00010-20 minutesVery large store
50,000+30-60 minutesEnterprise store

Factors Affecting Performance​

  • Product complexity: Variants increase processing time
  • API rate limits: Platform-specific throttling
  • Network latency: Distance to CMS and Business Central
  • Database performance: Query optimization and indexing
  • Server resources: CPU, memory, and I/O capacity

Optimization Strategies​

1. Batch Processing​

All database operations use batch processing for optimal performance.

Current Settings:

  • Database writes: 500 records per batch
  • API calls: 250 products per request (BigCommerce)
  • API calls: 100 products per request (WooCommerce)

Tuning Batch Sizes:

Edit src/Sync/Inventory_Sync.php:

protected int $batch_size = 500; // Increase for faster DB writes
Recommendation
  • Fast database: Increase to 1000
  • Slow database: Decrease to 250
  • High latency: Keep at 500

2. Channel Filtering (BigCommerce)​

Only sync products assigned to specific channels.

Benefits:

  • Reduces product count by 50-80%
  • Faster API calls
  • Lower memory usage

Configuration:

{
  "channel_id": 1,
  "name": "Main Store"
}

Set in site settings to enable channel filtering.

3. Database Indexing​

Ensure proper indexes exist for fast queries.

Required Indexes:

-- rm_items table
CREATE INDEX idx_sku_site ON rm_items(sku, site);
CREATE INDEX idx_prod_id_site ON rm_items(prod_id, site, type);
CREATE INDEX idx_site_active ON rm_items(site, active);

-- rm_queue table
CREATE INDEX idx_status_priority ON rm_queue(status, priority, created_at);
CREATE INDEX idx_job_type ON rm_queue(job_type);
CREATE INDEX idx_site_id ON rm_queue(site_id);

-- rm_webhook_log table
CREATE INDEX idx_site_event ON rm_webhook_log(site_id, event_type);
CREATE INDEX idx_payload_hash ON rm_webhook_log(payload_hash);
CREATE INDEX idx_received_at ON rm_webhook_log(received_at);

Check Existing Indexes:

SHOW INDEX FROM rm_items;
SHOW INDEX FROM rm_queue;
SHOW INDEX FROM rm_webhook_log;

4. Memory Optimization​

PHP Memory Settings:

; php.ini or wp-config.php
memory_limit = 512M
max_execution_time = 600

WordPress Constants:

// wp-config.php
define('WP_MEMORY_LIMIT', '512M');
define('WP_MAX_MEMORY_LIMIT', '512M');

Monitor Memory Usage:

echo 'Peak memory: ' . round(memory_get_peak_usage() / 1024 / 1024, 2) . ' MB';

5. API Rate Limit Handling​

Respect platform rate limits to avoid throttling.

BigCommerce:

  • Rate limit: 450 requests per 30 seconds
  • Recommended: 250-300 requests per 30 seconds
  • Built-in: Automatic retry with backoff

WooCommerce:

  • No strict rate limit
  • Recommended: 100-200 requests per minute
  • Server-dependent

Configuration:

// In platform-specific sync classes
private int $api_delay_ms = 200; // Delay between API calls
private int $max_requests_per_minute = 200;

6. Concurrent Processing​

Process multiple sites simultaneously.

Current Setup:

  • Queue processes jobs sequentially
  • One site at a time
  • Safe but slower

Enable Concurrent Processing:

Warning

Requires separate cron jobs or worker processes. Advanced users only.

# Multiple queue processors
*/5 * * * * wp cron event run gsm_process_queue &
*/5 * * * * wp cron event run gsm_process_queue &
*/5 * * * * wp cron event run gsm_process_queue &

7. Webhook-Based Updates​

Maximize webhook usage to reduce full sync requirements.

Benefits:

  • Real-time product tracking
  • Reduced API calls during sync
  • Faster sync completion

Setup Checklist:

  • Webhooks configured for all sites
  • Signature verification working
  • Queue processor running
  • Logs show 100% success rate

Monitoring Performance​

Sync Duration Tracking​

SELECT 
  site_id,
  AVG(TIMESTAMPDIFF(SECOND, started_at, completed_at)) as avg_duration_sec,
  MIN(TIMESTAMPDIFF(SECOND, started_at, completed_at)) as min_duration_sec,
  MAX(TIMESTAMPDIFF(SECOND, started_at, completed_at)) as max_duration_sec,
  COUNT(*) as total_syncs
FROM rm_queue
WHERE job_type = 'inventory_sync'
AND status = 'completed'
AND completed_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY site_id;

API Call Tracking​

Add logging to track API calls:

// In sync classes
private int $api_call_count = 0;

protected function track_api_call(): void {
    $this->api_call_count++;
    if ($this->api_call_count % 100 === 0) {
        $this->log("API calls: {$this->api_call_count}");
    }
}

Database Query Performance​

-- Slow queries
SELECT * FROM mysql.slow_log
WHERE sql_text LIKE '%rm_items%'
OR sql_text LIKE '%rm_queue%'
ORDER BY query_time DESC
LIMIT 20;

Troubleshooting Performance Issues​

Slow Syncs​

Symptoms:

  • Sync takes > 30 minutes
  • Frequent timeouts
  • High CPU usage

Solutions:

  1. Check database performance

    SHOW PROCESSLIST;

  2. Optimize tables

    OPTIMIZE TABLE rm_items, rm_queue, rm_webhook_log;

  3. Review slow queries

    SET global slow_query_log = 'ON';
    SET global long_query_time = 2;

  4. Increase PHP limits

    max_execution_time = 900
    memory_limit = 1024M

High Memory Usage​

Symptoms:

  • Out of memory errors
  • Sync crashes
  • Server becomes unresponsive

Solutions:

  1. Reduce batch size

    protected int $batch_size = 250; // Lower value

  2. Clear objects between batches

    gc_collect_cycles(); // Force garbage collection

  3. Process in smaller chunks

    • Split large syncs into multiple jobs
    • Process products in batches of 1000

API Rate Limiting​

Symptoms:

  • 429 errors in logs
  • Sync failures
  • Slow API responses

Solutions:

  1. Increase delays

    usleep(500000); // 0.5 second delay

  2. Implement exponential backoff

    $delay = min(pow(2, $attempt) * 1000, 30000);
    usleep($delay * 1000);

  3. Reduce concurrent requests

    • Process fewer products per API call
    • Increase time between calls

Database Optimization​

Table Optimization​

Regular Maintenance:

# Weekly optimization script
mysql -e "OPTIMIZE TABLE rm_items, rm_queue, rm_webhook_log;"

Automated:

-- Create event for weekly optimization
CREATE EVENT optimize_gsm_tables
ON SCHEDULE EVERY 1 WEEK
STARTS '2026-01-01 03:00:00'
DO
BEGIN
    OPTIMIZE TABLE rm_items;
    OPTIMIZE TABLE rm_queue;
    OPTIMIZE TABLE rm_webhook_log;
END;

Query Optimization​

Use EXPLAIN to analyze queries:

EXPLAIN SELECT * 
FROM rm_items 
WHERE site = 5 
AND active = 1;

Look for:

  • Full table scans
  • Missing indexes
  • Large row counts

Partitioning Large Tables​

For very large deployments (millions of records):

-- Partition rm_webhook_log by date
ALTER TABLE rm_webhook_log
PARTITION BY RANGE (TO_DAYS(received_at)) (
    PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p2026_03 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Caching Strategies​

Product Cache​

Cache product data to reduce API calls:

// Store in transient
set_transient("gsm_products_{$site_id}", $products, HOUR_IN_SECONDS);

// Retrieve from cache
$products = get_transient("gsm_products_{$site_id}");

Inventory Cache​

Cache Business Central inventory data:

// Cache for 5 minutes
$cache_key = "gsm_bc_inventory_" . md5(implode(',', $skus));
$inventory = wp_cache_get($cache_key);

if (false === $inventory) {
    $inventory = $this->bc_inventory->get($skus);
    wp_cache_set($cache_key, $inventory, '', 300);
}

Load Testing​

Simulating High Volume​

Test webhook volume:

# Send 1000 test webhooks
for i in {1..1000}; do
  curl -X POST https://your-site.com/wp-json/gsm-middleware/v1/webhooks/products/5 \
    -H "Content-Type: application/json" \
    -d '{"data":{"id":'$i',"sku":"TEST-'$i'"}}' &
done

Test queue processing:

# Queue 100 inventory sync jobs
wp eval "
for (\$i = 0; \$i < 100; \$i++) {
    \$queue->enqueue('inventory_sync', ['site_id' => 5], 5);
}
"

Monitoring During Load Tests​

# Monitor queue size
watch -n 5 "mysql -e 'SELECT status, COUNT(*) FROM rm_queue GROUP BY status;'"

# Monitor memory
watch -n 5 "free -h"

# Monitor CPU
top -p $(pgrep php-fpm)

Best Practices​

  1. Start with Defaults

    • Don't over-optimize prematurely
    • Measure before tuning

  2. Monitor Regularly

    • Track sync duration trends
    • Set up alerts for slow syncs
    • Review failed jobs weekly

  3. Optimize Gradually

    • Change one thing at a time
    • Measure impact
    • Document changes

  4. Scale Horizontally

    • Use separate worker processes
    • Distribute load across servers
    • Consider Redis for queueing

  5. Plan for Growth

    • Design for 2x current size
    • Monitor resource usage
    • Set up capacity alerts

Hardware Recommendations​

Small Deployment (< 10,000 SKUs)​

  • CPU: 2 cores
  • RAM: 4 GB
  • Database: Shared MySQL
  • Bandwidth: 100 Mbps

Medium Deployment (10,000-50,000 SKUs)​

  • CPU: 4 cores
  • RAM: 8 GB
  • Database: Dedicated MySQL
  • Bandwidth: 1 Gbps

Large Deployment (50,000+ SKUs)​

  • CPU: 8+ cores
  • RAM: 16+ GB
  • Database: Clustered MySQL/PostgreSQL
  • Bandwidth: 10 Gbps
  • Queue: Redis/RabbitMQ