postgresql - 如何调整我的 Postgres 服务器？

Question

我有一个 4 GB 服务器和 80 GB 硬盘。在页面之间切换时，该网站真的很慢。我检查了 70 -100 % cpu 使用率的顶级进程和 postgres 进程不断弹出。它们通常不会持续超过 4 秒，然后是新的。在我的 postgres 数据库表中，我有大约 12 个表，但其中一个表有大约 9000 万个条目(行)。这是导致减速的表。但是我如何调整我的 postgres 参数，如 shared_buffer 大小和所有参数以获得最佳性能？

它运行在 Ubuntu 16 操作系统上，它是一个 django webapp。

我的数据库名称是 roctim，它的大小为 15 GB(截至目前，但还在增加)

postgres=# select datname, pg_size_pretty(pg_database_size(oid)) as db_size from pg_database;

  datname  | db_size
-----------+---------
 template1 | 6857 kB
 template0 | 6857 kB
 postgres  | 6992 kB
 roctim    | 15 GB

我最大的表的大小是 15 GB，称为“Webapp_sensordata”。它收集和存储来自 splinter 机、传送带等机器的实时数据。

当我运行 select query from pg_stat_statements order by total_time desc; 时，我明白了

SELECT "Webapp_sensordata"."id", "Webapp_sensordata"."timestamp",
       "Webapp_sensordata"."value", "Webapp_sensordata"."machine_id",
       "Webapp_sensordata"."type_id"
FROM "Webapp_sensordata"
WHERE "Webapp_sensordata"."machine_id" = ?
ORDER BY "Webapp_sensordata"."timestamp" DESC
LIMIT ?

和

SELECT "Webapp_sensordata"."id", "Webapp_sensordata"."timestamp",
       "Webapp_sensordata"."value", "Webapp_sensordata"."machine_id",
       "Webapp_sensordata"."type_id"
FROM "Webapp_sensordata"
WHERE ("Webapp_sensordata"."type_id" = ?
  AND "Webapp_sensordata"."machine_id" = ?)
ORDER BY "Webapp_sensordata"."timestamp" DESC
LIMIT ?

作为前两个查询，它们都访问大表传感器数据。

我的 shared_buffer 和 working_mem 现在也设置为默认值(128mb 和 4mb)

当我运行 EXPLAIN ANALYZE 上面显示的第二个查询时，我得到了

QUERY PLAN
----------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
----------------------
Sort  (cost=62127.86..62135.64 rows=3111 width=24) (actual 
time=781.051..781.230 rows=2860 loops=1)
Sort Key: "timestamp" DESC
Sort Method: quicksort  Memory: 320kB
->  Index Scan using "Webapp_sensordata_machine_id_e353fc5a" on 
"Webapp_sensordata"  (cost=0.57..61947.37 rows=3111 width=24) (actual 
time=4.190..779.783 rows=2860 loops=1)
     Index Cond: (machine_id = 3)
     Filter: (type_id = 1)
     Rows Removed by Filter: 31440
Planning time: 4.572 ms
Execution time: 781.449 ms
(9 rows)

Answer 1

如果条件和排序都可以在索引扫描期间由多列索引处理，您的查询会更快:

CREATE INDEX ON "Webapp_sensordata" (type_id, machine_id, timestamp);