PostgreSQL: ضبط أداء PostgreSQL عمليًا

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=72) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (user_id = 1001)
  Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.032 ms

-- لا فهرس على status، المخطط يختار Seq Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM orders WHERE status = 'pending';

Aggregate (actual time=45.123..45.124 rows=1 loops=1)
  -> Seq Scan on orders (actual time=0.012..42.890 rows=50000 loops=1)
       Filter: (status = 'pending'::text)
       Rows Removed by Filter: 950000

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- انتقائية عالية: المخطط يستخدم Index Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders (actual time=0.015..0.018 rows=3 loops=1)
  Index Cond: (user_id = 1001)

-- انتقائية معتدلة: المخطط يختار Bitmap
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id BETWEEN 1000 AND 1100;

Bitmap Heap Scan on orders (actual time=0.523..2.145 rows=523 loops=1)
  Recheck Cond: (user_id >= 1000 AND user_id <= 1100)
  -> Bitmap Index Scan on idx_orders_user_id (actual time=0.412..0.412 rows=523 loops=1)
       Index Cond: (user_id >= 1000 AND user_id <= 1100)

-- جدول خارجي صغير + داخلي مفهرس = Nest Loop
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1001;

-- خارجي كبير + داخلي غير مرتب = Hash Join
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date > '2024-01-01';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- استعلام بطيء: Seq Scan على جدو�� كبير
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan, cost=0.00..15432.00, actual time=120ms

-- أضف فهرسًا
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- أعد التحليل لإحصائيات دقيقة
ANALYZE orders;

-- نفس الاستعلام الآن يستخدم Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Index Scan, cost=0.42..890.00, actual time=2ms

CREATE TABLE

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- أفضل 10 حسب الوقت الإجمالي
SELECT left(query, 80) AS query_preview,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       rows,
       round((100.0 * shared_blks_hit /
           nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

-- الاستعلامات ذات أكبر عدد قراءات قرص
SELECT left(query, 80) AS query_preview,
       shared_blks_read AS disk_reads,
       shared_blks_hit  AS cache_hits,
       calls,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

# pgbouncer.ini
[databases]
shop_db = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

# ضبط postgresql.conf لخادم 16GB RAM
shared_buffers = 4GB           # 25% من RAM
work_mem = 64MB                # ذاكرة لكل عملية ترتيب
effective_cache_size = 12GB    # 75% من RAM
maintenance_work_mem = 1GB     # لـ VACUUM, CREATE INDEX
effective_io_concurrency = 200 # SSD; 2 لـ HDD
random_page_cost = 1.1         # SSD; 4.0 لـ HDD

-- تحقق من الإعدادات الحالية
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

-- تغيير في وقت التشغيل (لا حاجة لإعادة تشغيل للبعض)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

-- المعلمات التي يجب إعادة التشغيل لها
SELECT name, setting, boot_val, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

ALTER TABLE

-- تحقق من نسبة الصفوف الميتة
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- لجداول الكتابة العالية، خفض عامل المقياس لـ vacuum أكثر تواترًا
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = '1ms',
    autovacuum_vacuum_cost_limit = 1000
);

-- للطلبات بـ 10M صف، يشغل vacuum عند:
-- 10M × 0.05 = 500K صف ميت (مقابل 2M الافتراضي)

-- SQL statement executed successfully

-- PG 12+: تتبع تقدم vacuum
SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum;

 count 
-------
     5
(1 row)

-- تمكين الاستعلام المتوازي (PG 10+)
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET parallel_tuple_cost = 0.001;
SET min_parallel_table_scan_size = '8MB';

-- التحقق من الخطة المتوازية
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*), category
FROM products
GROUP BY category;

Finalize Aggregate (actual time=12.3..12.4 rows=5 loops=1)
  -> Gather (actual time=12.1..12.3 rows=15 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       -> Partial Aggregate (actual time=8.5..8.6 rows=5 loops=3)
            -> Parallel Seq Scan on products (actual time=0.2..6.1 rows=33333 loops=3)

SELECT relname AS table_name,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       round(100.0 * idx_scan / nullif(idx_scan + seq_scan, 0), 2) AS idx_scan_pct,
       n_live_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- نمط مضاد 1: شرط OR يمنع استخدام الفهرس
-- سيء
SELECT * FROM orders WHERE user_id = 1001 OR status = 'pending';
-- جيد: استخدم UNION ALL
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1001;

-- نمط مضاد 2: دالة تلف عمودًا مفهرسًا
-- سيء
SELECT * FROM orders WHERE lower(status) = 'pending';
-- جيد
SELECT * FROM orders WHERE status = lower('PENDING');

-- نمط مضاد 3: LIKE بحرف بدل بادئ
-- سيء
SELECT * FROM products WHERE name LIKE '%phone%';
-- جيد: pg_trgm أو بحث نصي كامل
SELECT * FROM products WHERE name % 'phone';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- سير عمل تحسين الأداء الكامل لنظام التجارة الإلكترونية لـ Charlie

-- الخطوة 1: تحديد الاستعلامات البطيئة
SELECT left(query, 60) AS q, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- الخطوة 2: تحليل أسوأ استعلام
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- الخطوة 3: إضافة الفهارس المفقودة
CREATE INDEX idx_orders_date_status ON orders (order_date, status);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- الخطوة 4: تحديث الإحصائيات
ANALYZE orders;
ANALYZE order_items;
ANALYZE products;

-- الخطوة 5: ضبط autovacuum لجداول الكتابة العالية
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

-- الخطوة 6: زيادة work_mem للترتيبات المعقدة
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

-- الخطوة 7: التحقق من التحسن
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- الخطوة 8: مراقبة صحة الجدول
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'products')
ORDER BY n_dead_tup DESC;