資料庫效能低落

此問題的典型症狀如下：

• 高磁碟輸入/輸出 (I/O) 或記憶體使用率
• 緩慢查詢

最常見的原因是正在檢視慢查詢。 請完成以下步驟以確認是否屬此情況：

1. 列出長期運行的查詢：

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. 驗證查詢阻塞：

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. 檢查查詢執行計劃：

   Explain <query>;
   

4. 檢查資料庫統計資料是否遺漏。 請使用以下查詢進行驗證：操作 > 執行分析 或者，執行以下命令：

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. 檢查索引是否從表格中遺失。 PostgreSQL 提供索引方法，例如 B-樹、雜湊、SP-GiST,GiST, GIN 和 BRIN。 請使用以下任一方法建立索引。

6. 檢查資料表是否膨脹。 執行以下查詢以進行驗證。 動作 > 執行真空

   或者，使用以下陳述：

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. 請考慮資料庫或物件是否已使用 pg_dump 或 建立 pg_restore。 使用 「動作」>「更新統計數據」來刷新。

8. 若查詢正在執行排序操作，請確保分配 work_mem 足夠的資源。

9. 使用 pg_stat 陳述式來找出以下資訊：

   • 查詢平均執行時間與呼叫次數

   • 查詢所佔用的 CPU 百分比使用率

   • 查詢佔用的記憶體百分比

pg_stat_activity, pg_stat_bgwriter，以及 pg_buffercache 是 中 PostgreSQL 用於監控和理解資料庫效能的重要工具。

部署監控與資料庫負載監控

Databases for PostgreSQL 部署提供與服務 IBM Cloud Monitoring 的整合功能，用於監控您部署中的資源使用情況。 使用此服務監控您的部署（磁碟和記憶體）以及資料庫負載。

使用 Cloud Databases 儀表板設定 CPU、記憶體及磁碟 IOPS 閾值的警示。 許多現有的指標（例如磁碟使用量和 IOPS）對於協助您在部署中設定 自動擴展功能 相當有用。 自動擴展功能預設為停用狀態，因此您必須手動進行設定。

觀察使用趨勢並設定自動擴展功能以因應這些趨勢，有助於在資料庫因資源耗盡而變得不穩定之前，預先緩解效能問題。 例如，磁碟輸入輸出、CPU 或記憶體故障的變化導致效能問題。

磁碟 IOPS

每秒輸入/輸出操作數（IOPS）受儲存卷的類型與大小所限制。 部署 Databases for PostgreSQL 的儲存容量預先配置於 Block Storage 耐力卷上，採用每GB 10 IOPS的等級。

若您的運作負載達到飽和或超過 IOPS 限制，資料庫請求與操作將被延遲，直至儲存子系統能趕上進度為止。 長時間的重負載可能導致您的部署無法處理查詢，從而實際上無法使用。 您可以透過增加磁碟容量來提升部署環境的可用 IOPS 數量。 舉例來說，對於每 GB 層級為 10 IOPS 的配置，您可以透過增加卷宗大小來提升 IOPS 效能。

即使僅有40-50%的磁碟使用率持續較長時間，也可能對資料庫效能造成顯著負面影響。 為生產環境分配至少 100 GB 磁碟空間（1,000 IOPS）。 IOPS = 10 × 分配的 GB 數（例如，100 GB = 1,000 IOPS）

記憶體用量

記憶體是存取與處理資料最快捷、最高效的方式。 正因如此，資料庫使用記憶體存取資料時，幾乎總是比從磁碟讀取資料更快。 還有其他指標需要檢視與考量，例如快取命中率、讀取區塊數以及命中區塊數。 單純看到記憶體使用率達到100%本身並不足以引發擔憂。 若記憶體耗盡而需使用交換頁，效能將大幅下降。

您可透過調整設定 Databases for PostgreSQL 檔中的 shared_buffers 參數，來設定專供資料庫共享緩衝區池使用的記憶體容量。 建議的最大值為部署總記憶體的 25%。 若為共享緩衝區分配過多記憶體，可能導致系統缺乏其他用途所需的記憶體，進而影響效能，甚至可能使資料庫無法運作。

資料庫負載監控

您有兩種方式可檢查資料庫負載：

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

您可採取的進一步行動以提升效能

您亦可考慮採取以下措施來排除效能問題：

• 優化慢查詢

  • 對執行緩慢的查詢執行 EXPLAIN (ANALYZE, BUFFERS)。 這顯示資料庫如何執行查詢，以及查詢速度慢的地方。 例如：

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • 尋找：

    • 佔用大量記憶體或磁碟空間的查詢

    • 按需添加 IBM Cloud 資源

    • 擴充磁碟/記憶體以提升每秒輸入輸出操作次數與記憶體容量

• 索引遺失或效能不佳是導致查詢速度緩慢的常見原因。 使用 EXPLAIN 來識別順序掃描，並考慮添加索引。

• 執行 VACUUM 命令以協助進行資料庫健康分析。

• 請考慮使用連線池來處理更多連線。Databases for PostgreSQL 將資料 Databases for PostgreSQL 庫的最大連線數設定為 115。 其中 15 個連線保留給超級使用者，用於維護資料庫的狀態與完整性；其餘 100 個連線可供您與您的應用程式使用。 達到連接限制後，任何嘗試建立新連接的操作都會導致錯誤。 為避免部署因連線數量過多而不堪負荷，請採用連線池機制，或擴展部署規模並提高其連線限制。 如需更多資訊，請參閱《 管理 PostgreSQL 連線池 》。

• 檢查是否有任何執行備份和批次上傳資料的程序，例如： 自動備份每日執行完成，並採用簡單的30天保留時程表進行保存。 若備份卡住，您可檢查「可用備份」區段，並在資料庫執行個體的雲端使用者介面頁面中識別卡住的備份。

• 請檢查您的 IBM Cloud 通知，確認是否有任何維護作業。 例如，資料庫修補程式。

• 若您認為此為平台問題（例如系統維護），請提供資料庫CRN編號聯繫 IBM 支援部門。