OwnPay Performance Tuning and Horizontal Scaling Guide

# Add to crontab (crontab -e)
* * * * * php /var/www/ownpay/public/index.php cron/run >> /dev/null 2>&1

-- Stored generated columns — indexed, no runtime JSON parsing
`invoice_id` BIGINT UNSIGNED GENERATED ALWAYS AS (
  CAST(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.invoice_id')) AS UNSIGNED)
) STORED,

KEY `idx_invoice_id` (`invoice_id`),
KEY `idx_payment_link_id` (`payment_link_id`)

# .docker/mariadb.cnf (or your production my.cnf)
[mysqld]
innodb_buffer_pool_size     = 256M    # Docker default
innodb_buffer_pool_instances = 1

# For a dedicated 8 GB DB server:
# innodb_buffer_pool_size    = 5G
# innodb_buffer_pool_instances = 4

# Financial data integrity — never reduce these
innodb_flush_log_at_trx_commit = 1
innodb_flush_method            = O_DIRECT

# Slow query logging (queries > 2s)
slow_query_log      = 1
slow_query_log_file = /var/lib/mysql/slow-queries.log
long_query_time     = 2

-- Weekly: update table statistics
ANALYZE TABLE op_transactions;
ANALYZE TABLE op_audit_log;
ANALYZE TABLE op_webhook_events;

-- Monthly: clean up expired temporary records
DELETE FROM op_idempotency_keys
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

DELETE FROM op_password_resets
WHERE expires_at < NOW();

DELETE FROM op_login_attempts
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

[ownpay]
listen = 127.0.0.1:9000

pm                   = dynamic
pm.max_children      = 20    # maximum simultaneous PHP workers
pm.start_servers     = 4
pm.min_spare_servers = 2
pm.max_spare_servers = 8
pm.max_requests      = 500   # recycle worker after 500 requests (prevents memory leaks)

slowlog                 = storage/logs/php-fpm-slow.log
request_slowlog_timeout = 10s

# Check average worker memory usage under load
ps -o rss= -C php-fpm | awk '{sum+=$1} END {print sum/NR/1024 " MB average"}'

# Formula: available RAM ÷ average worker memory
# Example: 3000 MB available / 80 MB per worker = 37 → set pm.max_children = 35

memory_limit       = 256M   # per worker
max_execution_time = 120    # seconds; covers report generation
bcmath.scale       = 10     # financial precision

opcache.enable                = 1
opcache.memory_consumption    = 128        # 128 MB bytecode cache
opcache.max_accelerated_files = 10000      # covers all OwnPay source files
opcache.revalidate_freq       = 0          # never check disk (production only)
opcache.validate_timestamps   = 0          # no filesystem stat() calls
opcache.jit                   = tracing    # PHP 8.3 JIT
opcache.jit_buffer_size       = 64M

realpath_cache_size = 4096K
realpath_cache_ttl  = 600

# .env
CACHE_DRIVER=redis
QUEUE_DRIVER=redis

REDIS_HOST=127.0.0.1
REDIS_PORT=6379
REDIS_PASSWORD=
REDIS_DB=0          # DB 0 for cache
REDIS_PREFIX=op:

# docker-compose.yml Redis service
redis:
  image: redis:7-alpine
  command: >
    redis-server
    --maxmemory 256mb
    --maxmemory-policy allkeys-lru
    --save ""
    --appendonly no

# Real-time command stream
redis-cli monitor

# Hit rate (target: > 80%)
redis-cli info stats | grep -E 'keyspace_hits|keyspace_misses'

# Queue depth
redis-cli llen "op:queue:default"

# Failed jobs
redis-cli llen "op:queue:failed"

-- Check webhook health
SELECT status, COUNT(*) as count, AVG(attempts) as avg_attempts
FROM op_webhook_events
GROUP BY status;

┌──────────────────────┐        ┌──────────────────────┐
│   App Server         │        │   DB Server          │
│   PHP-FPM + Nginx    │──────→ │   MariaDB 10.11      │
│   Redis (local)      │        │   innodb_buffer: 5G+ │
│   CACHE_DRIVER=redis │        │   max_connections:300│
└──────────────────────┘        └──────────────────────┘

DB_HOST=192.168.1.10   # dedicated DB server IP
DB_CONNECT_RETRIES=5   # more retries over network

             Load Balancer (Nginx/HAProxy)
                   │               │
     ┌─────────────┘               └─────────────┐
     │                                            │
App Server 1 (PHP-FPM + Nginx)   App Server 2 (PHP-FPM + Nginx)
     │                                            │
     └──────────────────┬─────────────────────────┘
                        │
            ┌───────────┴───────────┐
            │                       │
      MariaDB Primary          MariaDB Read Replica
      (writes)                 (reports/analytics)
            │
      Redis Cluster (3+ nodes)

# php-fpm pool config
php_value[session.save_handler] = redis
php_value[session.save_path]    = "tcp://redis:6379?prefix=op:sess:"

upstream ownpay_app {
    least_conn;
    server app1.internal:80;
    server app2.internal:80;
    keepalive 32;
}

server {
    listen 443 ssl;
    server_name pay.example.com;

    location / {
        proxy_pass http://ownpay_app;
        proxy_set_header Host              $host;
        proxy_set_header X-Real-IP         $remote_addr;
        proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

TRUSTED_PROXIES=10.0.0.1,10.0.0.2   # load balancer IPs or CIDR ranges

# Already in OwnPay's Nginx config
location ~* \.(css|js|ico|png|jpg|jpeg|gif|svg|woff|woff2|ttf|eot)$ {
    expires 30d;
    add_header Cache-Control "public, immutable";
    access_log off;
}

GET /api/v1/health

healthcheck:
  test: ["CMD", "curl", "-f", "http://localhost/api/v1/health"]
  interval: 30s
  timeout: 10s
  retries: 3

#!/bin/bash
echo "=== OwnPay Health Check ==="

echo "--- PHP-FPM ---"
curl -s http://127.0.0.1/fpm-status 2>/dev/null | grep -E 'pool|processes|listen queue'

echo "--- Redis ---"
redis-cli info stats | grep -E 'keyspace_hits|keyspace_misses|evicted_keys'

echo "--- MariaDB ---"
mysql -u root -p"$DB_ROOT_PASS" -e "SHOW STATUS LIKE 'Threads_connected';" 2>/dev/null
mysql -u root -p"$DB_ROOT_PASS" -e "SHOW STATUS LIKE 'Slow_queries';" 2>/dev/null

echo "--- Disk ---"
df -h /var/www/ownpay/storage

echo "--- Cron Locks ---"
ls -la /var/www/ownpay/storage/cron/

echo "--- Queue Depth ---"
redis-cli llen "op:queue:default" 2>/dev/null