PostgreSQL查询性能分析和优化

介绍

PostgreSQL是一个支持几乎所有SQL构造的开源对象关系DBMS。 在本教程中,我们将尝试提供优化查询的工具。 当您需要提高数据库的性能时,请考虑如何使用受影响的表。 有时,表主要用于插入,在其他情况下仅用于选择数据,但通常您需要提前知道偏差。

查询规划

当PostgreSQL收到查询时,首先要解析它,并且可能会重写它,通常在考虑性能时这不是问题。 接下来的事情是生成一个执行计划,实际上它比这更多,但是让事情变得简单。

查询分析

要了解PostgreSQL如何生成计划,我们使用EXPLAIN查询,我们也使用ANALYZE。 以下是Postgresql的描述。

  • EXPLAIN:显示PostgreSQL计划程序为提供的语句生成的执行计划。
  • ANALYZE:收集有关数据库中表的内容的统计信息。

准备测试环境

我们不建议在生产服务器上执行EXPLAIN ANALYZE查询。 为此,我们需要使用完全相同的数据库模式来设置一个测试postgres。 使用此模式,我们建议使用受影响的表的随机行,并检查每列的数据分布。 拥有每个列值的随机分布将允许我们用更多的实际数据填充测试表。

现在我们有测试环境,我们需要配置PostgreSQL来记录慢查询。 要执行此操作搜索log_min_duration_statement键并将其设置为正值,如300毫秒。

log_min_duration_statement = 300

现在开始使用你的应用程序来知道哪些查询速度很慢。 我们将在后续步骤中分析这些查询。

这里有一个非常慢的查询示例:

2017-07-15 15:06:10 ART LOG:  duration: 300898.426 ms  statement: SELECT example_table.id AS example_table_id, example_table.identification AS example_table_identification, example_table.column1 AS example_table_column1, example_table.column2 AS example_table_column2, example_table.column4 AS example_table_column4, example_table.column3 AS example_table_column3, example_table.column5 AS example_table_column5, example_table."column6" AS "example_table_column6", example_table.psa AS example_table_psa, example_table.fingerprint AS example_table_fingerprint, example_table.duplicate AS example_table_duplicate
        FROM example_table
        WHERE example_table.column1 <= 444.53736 AND example_table.column1 >= 414.53736 AND example_table.column2 <= 4.0 AND example_table.column2 >= 4.0 AND example_table.column4 <= 1 AND example_table.column4 >= 1 AND example_table.column3 <= 6 AND example_table.column3 >= 6 AND example_table."column6" <= 4.7748 AND example_table."column6" >= 2.7748 AND example_table.column5 = 1

PostgreSQL配置调整

如果您使用postgres默认配置,那么在开始出现性能问题时,您应该查看该文件,因为配置是针对平均用例。 如果你有一个非常大的服务器机会大,你需要更改你的配置。。

Vacuum 检查测试环境

在测试环境中这样做是没有用的,因为Vacuum会优化内部数据库结构!Vacuum检查你的生产环境。

Vacuum是Postgres查询删除删除或废弃的元组。

首先检查lastime,Vacuum进行查询:

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public';

我的情况时,列last_vacuum显示它是在几个月前完成的。 我的用例是读取,因为当表已经被填充,没有更多的插入或更新完成。 以防万一,我做了Vacuum:

VACUUM ANALYZE table_name;

您可以配置自动Vacuum,但请记住,在执行VACUUM查询时,Vacuum过程可能会使数据库变慢。 在我们的示例中,我们使用10000行作为阈值,这意味着在10000次插入,更新或删除后,该表是自动Vacuum。

ALTER TABLE table_name
SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE table_name
SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE table_name
SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE table_name
SET (autovacuum_vacuum_threshold = 10000);

我们建议您使用一个积极的自动Vacuum设置来做VACUUM FULL。

查询分析示例

我们的表格模式如下:

Table "public.example_table"
         Column         |       Type        |                       Modifiers
------------------------+-------------------+-------------------------------------------------------
 id                     | integer           | not null default nextval('example_table_id_seq'::regclass)
 identification         | character varying |
 column1                | double precision  |
 column2                | integer           |
 column4                | integer           |
 column3                | integer           |
 column5                | integer           |
 column6                | double precision  |
 fingerprint            | character varying |
 duplicate              | boolean           |
Indexes:
    "example_table_pkey" PRIMARY KEY, btree (id)
    "example_table_identification_key" UNIQUE CONSTRAINT, btree (identification)
    "example_table_identification_idx" btree (identification)
    "multi_index" btree (column1, column2, column4, column3, "column6", column5)
Referenced by:
    TABLE "history" CONSTRAINT "history_id_fkey" FOREIGN KEY (decoy_id) REFERENCES example_table(id)
    TABLE "result" CONSTRAINT "result_id_fkey" FOREIGN KEY (decoy_id) REFERENCES example_table(id)

在我们的日志中使用慢查询,我们将使用explain analysis来诊断可能出现的问题。 我们的表格使用相同的生产环境数据分布填充了100 Millon行。

EXPLAIN ANALYZE SELECT example_table.id AS example_table_id, example_table.identification AS example_table_identification, example_table.column1 AS example_table_column1, example_table.column2 AS example_table_column2, example_table.column4 AS example_table_column4, example_table.column3 AS example_table_column3, example_table.column5 AS example_table_column5, example_table."column6" AS "example_table_column6", example_table.psa AS example_table_psa, example_table.fingerprint AS example_table_fingerprint, example_table.duplicate AS example_table_duplicate

以下是说明分析结果:

Index Scan using multi_index on example_table  (cost=0.57..664243.33 rows=98 width=212) (actual time=0.943..387378.003 rows=5685 loops=1)
   Index Cond: ((column1 <= 284.3018::double precision) AND (column1 >= 254.3018::double precision) AND (column4 <= 3) AND (column4 >= 3) AND (column3 <= 5) AND (column3 >= 5) AND ("column6" <= 2.4414::double precision) AND ("column6" >= 0.4414::double precision) AND (column5 = 0))
   Filter: (((column2)::numeric <= 5.0) AND ((column2)::numeric >= 5.0))
   Rows Removed by Filter: 27429
 Total runtime: 387403.832 ms
(5 rows)

对表执行计数

SELECT count(\*) from example_table;

正如我们可以看到,postgres正在使用我们期望的multi_index。 但执行时间是完全不可接受的。 由于我们有一个索引扫描,我们还执行一个VACUUM FULL。 由于我们在column5和column3上使用相等性,所以我们尝试在这些列中添加部分索引。

特别是column5将我们的表分成两部分,但column3并非如此。 我们通常执行其他值的查询,如2和1。

CREATE INDEX column3_and_column5_idx ON example_table ON example_table where column5=0 and column3=1 or column3=2 or column3=5; CREATE INDEX column3_and_column5_idx ON example_table ON example_table where column5=1 and column3=1 or column3=2 or column3=5;

现在这是输出的解释

MongoDB 的详细介绍:请点这里
MongoDB 的下载地址:请点这里

相关推荐