📄 پایش و مانیتورینگ

پایش و مانیتورینگ

برای پایش سلامت و عملکرد کلاستر پایگاه‌داده، می‌توانید از پلتفرم مانیتورینگ ستون استفاده کنید. با استفاده از این پلتفرم می‌توانید عملکرد کلاستر‌های خود را نظاره کرده و یا متریک‌های مورد نیاز را Federate کرده و بر روی آن‌ها Alert مناسب قرار دهید. توصیه می‌شود که سنجه‌های مهم منابع (مانند مقدار مصرف شده‌ی دیسک) را همواره پایش کنید. برای اطلاعات بیشتر به مستندات پلتفرم مانیتورینگ مراجعه کنید.

همچنین این امکان وجود دارد تا با استفاده از برخی کوئری‌های نظارتی استاندارد PostgreSQL از عملکرد پایگاه داده اطمینان حاصل کنید

اتصالات فعال

برای مشاهده تعداد و جزئیات اتصالات فعال:

SELECT pid, usename, datname, client_addr, state, query_start, query
FROM pg_stat_activity
WHERE datname IS NOT NULL
ORDER BY query_start;

برای شمارش اتصالات به تفکیک پایگاه‌داده:

SELECT datname, count(*) AS connections
FROM pg_stat_activity
GROUP BY datname;

نسبت Cache Hit

نسبت Cache Hit نشان می‌دهد چه درصدی از خواندن‌ها از حافظه کش سرو می‌شوند. مقدار ایده‌آل برای اکثر کاربردها بالای ۹۹٪ است:

SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

اگر این مقدار به‌طور مداوم کمتر از ۹۹٪ باشد، ممکن است نیاز به افزایش حافظه کلاستر داشته باشید.

کوئری‌های کند

برای یافتن کوئری‌های با بیشترین زمان اجرا (نیازمند به فعال سازی افزونه pg_stat_statements):

SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

اسکن Index در مقابل Sequential

برای بررسی اینکه آیا جداول بزرگ از Index به‌درستی استفاده می‌کنند:

SELECT
  relname AS table_name,
  seq_scan,
  idx_scan,
  CASE WHEN seq_scan + idx_scan > 0
    THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 2)
    ELSE 0
  END AS index_scan_percent
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

اگر index_scan_percent برای جداول بزرگ به‌طور مداوم کمتر از ۹۹٪ باشد، ممکن است نیاز به افزودن Index مناسب باشد.

Deadlock

برای بررسی وضعیت قفل‌ها:

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

اندازه پایگاه‌داده و جداول

-- Size of each database
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 
-- Largest tables
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

وضعیت Replication

برای کلاستر‌های دارای Replication:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

مقدار replication_lag_bytes بالا می‌تواند نشان‌دهنده تأخیر تکثیر یا کمبود منابع باشد.

نگهداری (VACUUM و ANALYZE)

پس از وارد کردن حجم زیادی داده، اجرای ANALYZE به بهینه‌سازی برنامه‌ریز کوئری کمک می‌کند:

ANALYZE;

برای بررسی آخرین زمان VACUUM و ANALYZE جداول:

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;

نکات مهم

• برای نصب افزونه pg_stat_statements به مستند افزونه‌های PostgreSQL مراجعه کنید.
• اگر CPU کلاستر به‌طور مداوم بالا است، کوئری‌های کند را بررسی کرده این احتمال وجود دارد که ایندکس مناسب ساخته نشده و یا پایگاه داده به درستی از ایندکس‌های موجود استفاده نمی‌کند. پس از بررسی، در صورت نیاز منابع کلاستر را افزایش دهید.
• توصیه می‌شود فضای استفاده‌ شده‌ی دیسک را زیر ۹۰٪ نگه دارید؛ پر شدن دیسک می‌تواند عملکرد پایگاه‌داده را مختل کند. مصرف دیسک بیش از ۹۹٪ سبب می‌شود که پایگاه داده وارد حالت Readonly شده و کانکشن‌های فعلی و جدید Primary Endpoint همگی drop شوند. پس از ورود پایگاه داده به حالت Readonly تا زمانی که مصرف دیسک به کمتر از ۹۸٪ نرسد، کلاستر از حالت Readonly خارج نمی‌شود.
• برای جزئیات بیشتر درباره متریک‌های PostgreSQL، به مستندات رسمی مانیتورینگ مراجعه کنید.