بهینه‌سازی پایگاه داده پرهمزمانی: ایندکس‌گذاری پرس‌وجو، کش شیء Redis و تنظیم کرنل فراتر از افزونه‌ها

┌─────────────────────────────────────────────────────────────────┐
│                    لایه ۷: اپلیکیشن                             │
│         (الگوهای پرس‌وجو، رفتار ORM، حذف N+1)                   │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۶: کش                                   │
│         (کش شیء Redis، کش صفحه، کش Fragment)                    │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۵: پایگاه داده                          │
│         (ایندکس‌ها، پلن‌های اجرا، طراحی اسکیما)                 │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۴: مدیریت اتصال                         │
│         (Pooling، اتصالات پایدار، چرخه عمر اتصال)               │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۳: وب‌سرور                              │
│         (کش FastCGI، ارائه دارایی استاتیک، مسیریابی)            │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۲: مدیریت فرآیند                        │
│         (کارگران PHP-FPM، تخصیص حافظه، بازیافت درخواست)         │
├─────────────────────────────────────────────────────────────────┤
│                    لایه ۱: کرنل                                 │
│         (بافر شبکه، توصیف‌گرهای فایل، زمان‌بند I/O)             │
└─────────────────────────────────────────────────────────────────┘

EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days';

-- ایندکس‌های بدون استفاده (کاندیدهای حذف)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

-- اندازه ایندکس در برابر اندازه جدول
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
  pg_size_pretty(pg_indexes_size(tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

سنتی (بدون Pooling):
  کارگران PHP-FPM: ۲۰
  × اتصالات به ازای هر کارگر: ۲-۳
  = اتصالات پایدار: ۴۰-۶۰
  × حافظه به ازای هر اتصال: ~۱۰MB
  = ۴۰۰-۶۰۰MB فقط برای اتصالات

  در ۱۰۰ کارگر PHP-FPM: ۲۰۰-۳۰۰ اتصال → ۲-۳GB سربار
  PostgreSQL max_connections: ۲۰۰ (پیش‌فرض) → اتصال رد شد

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│ PHP-FPM     │     │             │     │             │
│ کارگر ۱     │────▶│             │     │             │
├─────────────┤     │   PgBouncer │     │  PostgreSQL │
│ PHP-FPM     │     │   (استخر)   │────▶│  (Backend   │
│ کارگر ۲     │────▶│             │     │   محدود)    │
├─────────────┤     │             │     │             │
│ PHP-FPM     │     │             │     │             │
│ کارگر N     │────▶│             │     │             │
└─────────────┘     └─────────────┘     └─────────────┘

اتصالات کارگر: ۶۰        Backendهای PostgreSQL: ۲۰
سربار حافظه: ۶۰۰MB       سربار حافظه: ۲۰۰MB

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Transaction Pooling: اتصال بعد از هر تراکنش برگردانده می‌شود
pool_mode = transaction

# اندازه استخر
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# سلامت اتصال
server_idle_timeout = 600
server_lifetime = 3600

┌─────────────────────────────────────────────────────────────┐
│                    نمونه Redis واحد                         │
│                     (سیاست allkeys-lru)                     │
│  ┌───────────────────────────────────────────────────────┐  │
│  │ پیشوند کلید: site1:* │ site2:* │ ...                  │  │
│  └───────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────┘

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   Redis ۱       │     │   Redis ۲       │     │   Redis N       │
│   (اپ A)        │     │   (اپ B)        │     │   (اپ N)        │
└─────────────────┘     └─────────────────┘     └─────────────────┘

# پیکربندی استخر PHP-FPM
env[REDIS_SOCKET] = /run/redis/redis.sock

Redis غیرقابل دسترس → WordPress با کش هر-درخواست ادامه می‌دهد
                     → سایت کار می‌کند، فقط کندتر
                     → ادمین از وضعیت کش مطلع می‌شود
                     → بدون خطا، بدون توقف

http {
    # منطقه کش: ۲۵۶MB فضا، منطقه کلید ۱۰۰MB
    fastcgi_cache_path /var/cache/nginx/wordpress
        levels=1:2 keys_zone=wp_cache:100m
        max_size=256m inactive=60m use_temp_path=off;

    fastcgi_cache_key "$scheme$request_method$host$request_uri";
    fastcgi_cache_lock on;
    fastcgi_cache_lock_timeout 5s;
    fastcgi_cache_use_stale error timeout invalid_header updating http_500 http_503;
    fastcgi_cache_background_update on;
}

set $skip_cache 0;

# درخواست‌های POST را هرگز کش نکن
if ($request_method = POST) { set $skip_cache 1; }

# Query-Stringها را هرگز کش نکن (جستجو، صفحه‌بندی)
if ($query_string != "") { set $skip_cache 1; }

# کاربران احراز هویت شده یا صفحات سبد خرید را هرگز کش نکن
if ($http_cookie ~* "comment_author|wordpress_[a-f0-9]+|wp-postpass|wordpress_logged_in|woocommerce_items_in_cart|woocommerce_cart_hash") {
    set $skip_cache 1;
}

# ادمین، ورود، API، سبد، تسویه را هرگز کش نکن
if ($request_uri ~* "/wp-admin/|/xmlrpc.php|wp-.*.php|/feed/|index.php|/wp-json/|/cart/|/checkout/|/my-account/") {
    set $skip_cache 1;
}

curl -I https://example.com/
# درخواست اول: X-FastCGI-Cache: MISS
# درخواست دوم: X-FastCGI-Cache: HIT

خیلی بالا: جهش ترافیک → کارگران بیشتری از RAM موجود Spawn می‌شود
          → OOM Killer کرنل کارگران را در میانه درخواست خاتمه می‌دهد
          → کاربران خطا می‌بینند

خیلی پایین: درخواست‌ها در صف می‌مانند در حالی که حافظه بیکار است
          → تأخیر بالا، توان عملیاتی ضعیف

RAM موجود = RAM کل - (Nginx + Redis + سربار OS)
حداکثر کارگران = RAM موجود / RSS متوسط فرآیند PHP

RAM موجود = ۲۰۴۸MB - ۴۰۰MB (سربار) = ۱۶۴۸MB
RSS متوسط PHP = ~۸۰MB
حداکثر کارگران = ۱۶۴۸ / ۸۰ ≈ ۲۰

; /etc/php/8.4/fpm/pool.d/www.conf
[www]
user = www-data
group = www-data

; Unix Socket (سریع‌تر از TCP برای اتصالات محلی)
listen = /run/php/php8.4-fpm.sock
listen.owner = www-data
listen.group = www-data

; مدیریت فرآیند پویا
pm = dynamic
pm.max_children = 20
pm.start_servers = 5
pm.min_spare_servers = 3
pm.max_spare_servers = 8

; بازیافت کارگران برای جلوگیری از نشت حافظه
pm.max_requests = 500

; گزارش درخواست‌های کند
slowlog = /var/log/php/www-slow.log
request_slowlog_timeout = 5s

; تنظیمات PHP برای پرهمزمانی
php_admin_value[memory_limit] = 256M
php_admin_value[max_execution_time] = 120
php_admin_flag[log_errors] = on

# /etc/sysctl.conf

# حداکثر اتصالات در صف Accept
net.core.somaxconn = 262144

# Backlog دستگاه شبکه
net.core.netdev_max_backlog = 262144

# اندازه بافر TCP (min، default، max) به بایت
net.ipv4.tcp_rmem = 8192 87380 134217728
net.ipv4.tcp_wmem = 8192 87380 134217728

# محدودیت‌های حافظه Socket
net.core.wmem_max = 134217728
net.core.rmem_max = 134217728

# حافظه TCP (پایین، فشار، بالا) در صفحات
net.ipv4.tcp_mem = 6093984 8125312 32777216

# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
www-data soft nofile 65535
www-data hard nofile 65535

# بررسی زمان‌بند فعلی
cat /sys/block/nvme0n1/queue/scheduler
# [none] mq-deadline kyber bfq

# تنظیم روی none برای NVMe
echo none > /sys/block/nvme0n1/queue/scheduler

# افزایش نواحی نقشه حافظه برای پایگاه‌های داده بزرگ
vm.max_map_count = 1600000

# غیرفعال کردن تعادل NUMA در سیستم‌های چند-Node
kernel.numa_balancing = 0

۱. اندازه‌گیری
   ├─ پایه: تأخیر، توان عملیاتی، نرخ خطا
   ├─ شناسایی لایه گلوگاه (اپلیکیشن، کش، DB، شبکه)
   └─ پروفایل: EXPLAIN ANALYZE، لاگ‌های کند، معیارهای سیستم

۲. بهینه‌سازی گلوگاه
   ├─ اگر محدود به DB: ایندکس‌ها، بازنویسی پرس‌وجو، Connection Pooling
   ├─ اگر محدود به کش: توپولوژی Redis، سیاست Eviction، Socket
   ├─ اگر محدود به PHP: کارگران FPM، max_requests، حافظه
   └─ اگر محدود به شبکه: بافرهای کرنل، somaxconn

۳. تأیید
   ├─ اندازه‌گیری مجدد تحت همان بار
   ├─ تأیید بهبود، بدون رگرسیون
   └─ مستندسازی تغییر و تأثیر

۴. تکرار
   └─ گلوگاه بعدی اکنون قابل مشاهده است