PostgreSQL和用于分析频繁大插入性能成本的工具

huangapple
go评论77阅读模式
英文:

PostgreSQL and Tools for Analyzing Performance Cost For Frequent Large Inserts

问题

我正在设计一个将多行插入到多个表的Ingest过程。我们在Azure的PostgreSQL Flexible服务器上运行PostgreSQL。目前，向该服务器写入/插入速度似乎是提高负载吞吐量的瓶颈（通过REST API处理JSON负载，通过队列以目标完成速率每分钟100个负载）。

是否有类似于我们可以使用EXPLAIN ANALYZE来分析SELECT性能的PostgreSQL /数据库工具来分析INSERT性能？我知道索引和外键可能会影响INSERT速度。我想实际的每个表的列类型以及插入的字符串长度也可能会影响。

以下是两个较大表的一些测量插入速度。我们在Python中使用psycopg2包批量执行"INSERT INTO ... VALUES"：

Resource表，性能更好且对行数不太敏感。

"Resource Insert: 35 ms, 120 rows, 0.2916666666666667 ms/row" # 1 Slide model
"Resource Insert: 36 ms, 120 rows, 0.3 ms/row"
"Resource Insert: 47 ms, 120 rows, 0.39166666666666666 ms/row"
"Resource Insert: 99 ms, 381 rows, 0.25984251968503935 ms/row" # 10 Slides
"Resource Insert: 97 ms, 381 rows, 0.2545931758530184 ms/row"
"Resource Insert: 117 ms, 381 rows, 0.30708661417322836 ms/row"
"Resource Insert: 654 ms, 2991 rows, 0.21865596790371114 ms/row" # 100 Slides
"Resource Insert: 683 ms, 2991 rows, 0.22835172183216315 ms/row"
"Resource Insert: 665 ms, 2991 rows, 0.2223336676696757 ms/row"
"Resource Insert: 5604 ms, 29091 rows, 0.19263689800969372 ms/row" # 1000 Slides
"Resource Insert: 5498 ms, 29091 rows, 0.1889931593963769 ms/row"
"Resource Insert: 5021 ms, 29091 rows, 0.17259633563645113 ms/row"
"Resource Insert: 4743 ms, 29091 rows, 0.16304011549963907 ms/row"
"Resource Insert: 8428 ms, 29091 rows, 0.2897115946512667 ms/row"
"Resource Insert: 7788 ms, 29091 rows, 0.26771166340105185 ms/row"
"Resource Insert: 7367 ms, 29091 rows, 0.25323983362551994 ms/row"

Resource Value表，插入性能较差且随着行数的增加而扩展性差。

"Resource Val Insert: 378 ms, 113 rows, 3.3451327433628317 ms/row" ~ 1 Slide + Acc,Pat,Cli,TestOrder,Block
"Resource Val Insert: 365 ms, 113 rows, 3.230088495575221 ms/row"
"Resource Val Insert: 356 ms, 113 rows, 3.150442477876106 ms/row"
"Resource Val Insert: 422 ms, 113 rows, 3.734513274336283 ms/row"
"Resource Val Insert: 439 ms, 113 rows, 3.8849557522123894 ms/row"
"Resource Val Insert: 354 ms, 113 rows, 3.1327433628318584 ms/row"
"Resource Val Insert: 1498 ms, 365 rows, 4.104109589041096 ms/row" # 10 Slides
"Resource Val Insert: 1509 ms, 365 rows, 4.134246575342465 ms/row"
"Resource Val Insert: 1568 ms, 365 rows, 4.295890410958904 ms/row"
"Resource Val Insert: 1607 ms, 365 rows, 4.402739726027397 ms/row"
"Resource Val Insert: 1553 ms, 365 rows, 4.254794520547946 ms/row"
"Resource Val Insert: 14187 ms, 2885 rows, 4.917504332755633 ms/row" # 100 Slides
"Resource Val Insert: 14239 ms, 2885 rows, 4.935528596187175 ms/row"
"Resource Val Insert: 13494 ms, 2885 rows, 4.677296360485268 ms/row"
"Resource Val Insert: 14489 ms, 2885 rows, 5.0221837088388215 ms/row"
"Resource Val Insert: 14044 ms, 2885 rows, 4.867937608318891 ms/row"
"Resource Val Insert: 131665 ms, 28085 rows, 4.688089727612605 ms/row" # 1000 Slides
"Resource Val Insert: 133214 ms, 28085 rows, 4.743243724408047 ms/row"
"Resource Val Insert: 132070 ms, 28085 rows, 4.7025102367812 ms/row"

以下是相关表的DDL，这可能是一个因素：平均性能插入-资源表

                                                    Table "public.resource"
      Column       |            Type             | Collation | Nullable |   Default    | Storage  | Stats target | Description
-------------------+-----------------------------+-----------+----------+--------------+----------+--------------+-------------
 resource_id       | integer                     |           | not null |              | plain    |              |
 uuid              | uuid                        |           | not null |              | plain    |              |
 name              | character varying           |           |          |              | extended |              |
 url               | character varying           |           |          |              | extended |              |
 desc              | character varying           |           |          |              | extended |              |
 barcode           | character varying           |           |          |              | extended |              |
 barcode_type      | character varying           |           |          |              | extended |              |
 meta              | jsonb                       |           |          |              | extended |              |
 cls               | integer                     |           | not null |              | plain    |              |
 archived          | boolean                     |           | not null |              | plain    |              |
 view_template     | character varying           |           | not null |              | extended |              |
 created_timestamp | timestamp without time zone |           | not null |              |

<details>
<summary>英文:</summary>

I am designing an Ingest process that inserts many rows into several tables. We are running PostgreSQL on Azure&#39;s PostgreSQL Flexible server. It currently appears that write/insertion speed to this server is the bottleneck for increasing payload throughput (processing JSON payloads with a REST API through a Queue with a target completion rate of 100 payloads/per minute).

Are there any PostgreSQL / database tools for analyzing INSERT performance similar to how we can analyze SELECT performance with EXPLAIN ANALYZE? I know that Indexes and Foreign Keys can have an impact to INSERT speed. I imagine the actual column types for each table, and the length of inserted strings could also have an impact.

Here are some measured insert speeds for 2 of the larger tables. We are using psycopg2 package in Python to execute_values in bulk &quot;INSERT INTO ... VALUES&quot;:

Resource table, better performance &amp; less sensitivity to row count.

"Resource Insert: 35 ms, 120 rows, 0.2916666666666667 ms/row" # 1 Slide model
"Resource Insert: 36 ms, 120 rows, 0.3 ms/row"
"Resource Insert: 47 ms, 120 rows, 0.39166666666666666 ms/row"
"Resource Insert: 99 ms, 381 rows, 0.25984251968503935 ms/row" # 10 Slides
"Resource Insert: 97 ms, 381 rows, 0.2545931758530184 ms/row"
"Resource Insert: 117 ms, 381 rows, 0.30708661417322836 ms/row"
"Resource Insert: 654 ms, 2991 rows, 0.21865596790371114 ms/row" # 100 Slides
"Resource Insert: 683 ms, 2991 rows, 0.22835172183216315 ms/row"
"Resource Insert: 665 ms, 2991 rows, 0.2223336676696757 ms/row"
"Resource Insert: 5604 ms, 29091 rows, 0.19263689800969372 ms/row" # 1000 Slides
"Resource Insert: 5498 ms, 29091 rows, 0.1889931593963769 ms/row"
"Resource Insert: 5021 ms, 29091 rows, 0.17259633563645113 ms/row"
"Resource Insert: 4743 ms, 29091 rows, 0.16304011549963907 ms/row"
"Resource Insert: 8428 ms, 29091 rows, 0.2897115946512667 ms/row"
"Resource Insert: 7788 ms, 29091 rows, 0.26771166340105185 ms/row"
"Resource Insert: 7367 ms, 29091 rows, 0.25323983362551994 ms/row"


Resource Value table, insert performance worse &amp; scales poorly with higher row counts

"Resource Val Insert: 378 ms, 113 rows, 3.3451327433628317 ms/row" ~ 1 Slide + Acc,Pat,Cli,TestOrder,Block
"Resource Val Insert: 365 ms, 113 rows, 3.230088495575221 ms/row"
"Resource Val Insert: 356 ms, 113 rows, 3.150442477876106 ms/row"
"Resource Val Insert: 422 ms, 113 rows, 3.734513274336283 ms/row"
"Resource Val Insert: 439 ms, 113 rows, 3.8849557522123894 ms/row"
"Resource Val Insert: 354 ms, 113 rows, 3.1327433628318584 ms/row"
"Resource Val Insert: 1498 ms, 365 rows, 4.104109589041096 ms/row" # 10 Slides
"Resource Val Insert: 1509 ms, 365 rows, 4.134246575342465 ms/row"
"Resource Val Insert: 1568 ms, 365 rows, 4.295890410958904 ms/row"
"Resource Val Insert: 1607 ms, 365 rows, 4.402739726027397 ms/row"
"Resource Val Insert: 1553 ms, 365 rows, 4.254794520547946 ms/row"
"Resource Val Insert: 14187 ms, 2885 rows, 4.917504332755633 ms/row" # 100 Slides
"Resource Val Insert: 14239 ms, 2885 rows, 4.935528596187175 ms/row"
"Resource Val Insert: 13494 ms, 2885 rows, 4.677296360485268 ms/row"
"Resource Val Insert: 14489 ms, 2885 rows, 5.0221837088388215 ms/row"
"Resource Val Insert: 14044 ms, 2885 rows, 4.867937608318891 ms/row"
"Resource Val Insert: 131665 ms, 28085 rows, 4.688089727612605 ms/row" # 1000 Slides
"Resource Val Insert: 133214 ms, 28085 rows, 4.743243724408047 ms/row"
"Resource Val Insert: 132070 ms, 28085 rows, 4.7025102367812 ms/row"


Here is the DDL for the related tables, which likely is a factor: Average Performance Inserts - resource table

                                                Table &quot;public.resource&quot;
  Column       |            Type             | Collation | Nullable |   Default    | Storage  | Stats target | Description

-------------------+-----------------------------+-----------+----------+--------------+----------+--------------+-------------
resource_id | integer | | not null | | plain | |
uuid | uuid | | not null | | plain | |
name | character varying | | | | extended | |
url | character varying | | | | extended | |
desc | character varying | | | | extended | |
barcode | character varying | | | | extended | |
barcode_type | character varying | | | | extended | |
meta | jsonb | | | | extended | |
cls | integer | | not null | | plain | |
archived | boolean | | not null | | plain | |
view_template | character varying | | not null | | extended | |
created_timestamp | timestamp without time zone | | not null | | plain | |
updated_timestamp | timestamp without time zone | | not null | | plain | |
owner_resource_id | integer | | | | plain | |
tenant | character varying | | not null | CURRENT_USER | extended | |
Indexes:
"resource_pk" PRIMARY KEY, btree (resource_id, tenant)
"_uuid_tenant_uc" UNIQUE CONSTRAINT, btree (uuid, tenant)
"param_group_unique_name" UNIQUE, btree (name) WHERE cls = 600
"resource_resource_id_key" UNIQUE CONSTRAINT, btree (resource_id)
"ix_resource_barcode" btree (barcode)
"ix_resource_desc" btree ("desc")
"ix_resource_name" btree (name)
"ix_resource_uuid" btree (uuid)
"lab7_role_unique_name" btree (lower(name::text)) WHERE cls = 221 OR cls = 222 OR cls = 220
"resource_created_timestamp_idx" btree (created_timestamp DESC)
"resource_updated_timestamp_idx" btree (updated_timestamp DESC)
Check constraints:
"resource_check" CHECK (resource_id <> owner_resource_id)
Foreign-key constraints:
"resource_owner_resource_id_fkey" FOREIGN KEY (owner_resource_id) REFERENCES resource(resource_id)
Referenced by:
TABLE "address" CONSTRAINT "address_address_id_fkey" FOREIGN KEY (address_id) REFERENCES resource(resource_id)
# .... and many more references! This is our most basic table that acts sort of like an abstract base class


Poorly Performing Table Inserts

\d+ resource_val
Table "public.resource_val"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------+-------------------+-----------+----------+--------------+----------+--------------+-------------
resource_val_id | integer | | not null | | plain | |
resource_var_id | integer | | | | plain | |
value_group_id | integer | | | | plain | |
sort_id | integer | | | | plain | |
value | character varying | | | | extended | |
expression | character varying | | | | extended | |
dropdown | jsonb | | | | extended | |
bound_resource_id | integer | | | | plain | |
error_msg | character varying | | | | extended | |
dropdown_error_msg | character varying | | | | extended | |
tenant | character varying | | not null | CURRENT_USER | extended | |
step_instance_sample_id | integer | | | | plain | |
Indexes:
"resource_val_pkey" PRIMARY KEY, btree (resource_val_id)
"idx_res_val_hash" hash (value)
"ix_r_val_bound_resource_id" btree (bound_resource_id)
"ix_r_val_sis_id" btree (step_instance_sample_id)
Foreign-key constraints:
"resource_val_bound_resource_id_fkey" FOREIGN KEY (bound_resource_id) REFERENCES resource(resource_id)
"resource_val_resource_val_id_fkey" FOREIGN KEY (resource_val_id) REFERENCES resource(resource_id)
"resource_val_resource_var_id_fkey" FOREIGN KEY (resource_var_id) REFERENCES resource_var(resource_var_id)
"resource_val_step_instance_sample_id_fkey" FOREIGN KEY (step_instance_sample_id) REFERENCES step_instance_sample(association_id)
"resource_val_value_group_id_fkey" FOREIGN KEY (value_group_id) REFERENCES resource_val_group(id)


</details>


# 答案1
**得分**: 1

`EXPLAIN (ANALYZE, BUFFERS)`对于INSERT语句确实有效。虽然它对于INSERT来说并不像对于SELECT那样有用，但它仍然是你最好的起点。它至少将外键约束的时间单独列出，尽管它不会详细分析索引维护的时间。

对我来说，你所说的"规模较差"并不正确。性能下降很小，并且不一致。例如，大多数情况下，“100张幻灯片”的时间比“1000张幻灯片”的时间糟糕。假设演示文稿是按时间顺序排列的，也许随着时间的推移，还发生了其他事情，比如从云提供商那里用尽了“配额”，或者操作系统缓存被脏页拥塞，或者索引增长到无法再保留在缓存中的程度（但这种差异应该比你看到的要大得多）。如果要令人信服，你需要使用更多的测试用例，并且批处理大小的顺序应该是随机的，而不是可预测的。如果差异（超过批处理大小）是真实的，它是否足够大需要担心呢？

现在，插入操作确实不太容易扩展，但这与插入的批处理大小无关，而是与正在插入的表的索引大小有关。当不良行为开始时，它就像跌入悬崖一样，性能不仅仅下降了20%。

通常，调查这种情况的最佳方法（假设查看`EXPLAIN (ANALYZE, BUFFERS)`不够用）就是去掉一些约束/索引，看看会发生什么。

<details>
<summary>英文:</summary>

`EXPLAIN (ANALYZE, BUFFERS)` does work for INSERT.  It isn&#39;t as useful for them as it is for SELECT, but it is still the best starting point you have.  It does at least break out the timing for foreign key constraints separately, although it doesn&#39;t break down the index maintenance time.

Your &quot;scales poorly&quot; doesn&#39;t ring true to me.  The performance drop is small, and is inconsistent.  Most times for &quot;100 slides&quot; are worse than any time for &quot;1000 slides&quot;, for example.  Assuming the presentation is in chronological order, maybe something else is going on as time progresses, like running out of &quot;credits&quot; from your cloud provider or getting the OS cache congested with dirty pages, or the just the indexes growing to the point they can&#39;t stay in cache anymore (but that difference would be much larger than you are seeing).  To be convincing you would need to run the tests with more of them, and with batch sizes in random order not in a predictable progression.  And if the difference (over batch size) is real, is it large enough to worry about?

Now inserts do scale poorly, but that is related to the size of the indexes of the table being inserted into, not the size of the batches being inserted.  And when the bad behavior kicks in, it is like falling off a cliff, it is not just a 20% drop in performance.

Usually the best way to investigate this kind of thing, assuming looking at `EXPLAIN (ANALYZE, BUFFERS)` isn&#39;t enough, is just to drop some of the constraints/indexes and see what happens.



</details>

huangapple
  • 本文由 发表于 2023年3月7日 07:26:39
  • 转载请务必保留本文链接：https://go.coder-hub.com/75656735.html
  • azure
  • database
  • performance
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定