Fork me on GitHub

daydayup863

人生就像一杯茶，不会苦一辈子，但总会苦一阵子。

0%

置顶 | 发表于 分类于 阅读次数： Valine：

vacuum processing is a maintenance process that facilitates the persistent operation of PostgreSQL. Its two main tasks are removing dead tuples and the freezing transaction ids, both of which are briefly mentioned in Section 5.10.

To remove dead tuples, vacuum processing provides two modes, i.e. Concurrent VACUUM and Full VACUUM. Concurrent VACUUM, often simply called VACUUM, removes dead tuples for each page of the table file, and other transactions can read the table while this process is running. In contrast, Full VACUUM removes dead tuples and defragments live tuples the whole file, and other transactions cannot access tables while Full VACUUM is running.

Despite the fact that vacuum processing is essential for PostgreSQL, improving its functionality has been slow compared to other functions. For example, until version 8.0, this process had to be executed manually (with the psql utility or using the cron daemon). It was automated in 2005 when the autovacuum daemon was implemented.

Since vacuum processing involves scanning whole tables, it is a costly process. In version 8.4 (2009), the Visibility Map (VM) was introduced to improve the efficiency of removing dead tuples. In version 9.6 (2016), the freeze process was improved by enhancing the VM.

阅读全文 »

置顶 | 发表于 分类于 阅读次数： Valine：

MySQL风格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
\pset border 2
 
postgres=# select * from test;
+----+----+
| id | c1 |
+----+----+
|  1 |  1 |
|  2 |  2 |
+----+----+
(2 rows)
 
postgres=#
 
--------------------------------------
 
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
 
mysql>
阅读全文 »

置顶 | 发表于 分类于 阅读次数： Valine：
Linux内核watchdog于监视系统是否正在运行。由于存在不可恢复的软件错误，应该自动重新启动挂起的系统

Watchdog的功能

Linux内核watchdog于监视系统是否正在运行。由于存在不可恢复的软件错误，应该自动重新启动挂起的系统。

看门狗模块特依赖所使用的硬件或芯片。个人计算机用户通常不需要看门狗，因为他们可以手动重置系统。但是，watchdog对于任务关键型系统和需要无需人工干预即可自行重启的系统很有用。

例如，需要自动硬件重置功能的远程位置的服务器。

阅读全文 »

置顶 | 发表于 分类于 阅读次数： Valine：

时不时地，有人对重置PostgreSQL中的统计信息, 以及重置统计信息对执行计划和数据库其他部分的影响感到困惑。也许文档对此描述的会更清晰一些，但是对于那些以前从未在PostgreSQL中处理过统计信息的人来说无疑是令人困惑的。但这不仅是关于新手的问题-我在9.3中为该区域编写了一个补丁，而且我有时也会感到困惑。对于大多数用户而言，最令人惊讶的事实是“统计信息”实际上可能意味着两件事, 描述数据分布的统计信息，以及监视统计信息，跟踪有关数据库系统本身操作的计数器。每种都有不同的用途，存储方式也不同，丢失数据时的影响也大不相同。因此，让我们看看这两种统计数据的目的是什么，常见的问题是什么，当数据由于某种原因丢失时会发生什么。

阅读全文 »

置顶 | 发表于 分类于 阅读次数： Valine：

psql增加\d[i|m|t]+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
commit 07f386ede026ae8c3f2adeba0c22139df19bf2ff
Author: Michael Paquier <michael@paquier.xyz>
Date:   Wed Sep 2 16:59:22 2020 +0900

    Add access method names to \d[i|m|t]+ in psql

    Listing a full set of relations with those psql meta-commands, without a
    matching pattern, has never showed the access method associated with
    each relation.  This commit adds the access method of tables, indexes
    and matviews, masking it for relation kinds where it does not apply.

    Note that when HIDE_TABLEAM is enabled, the information does not show
    up.  This is available when connecting to a backend version of at least
    12, where table AMs have been introduced.

    Author: Georgios Kokolatos
    Reviewed-by: Vignesh C, Michael Paquier, Justin Pryzby
    Discussion: https://postgr.es/m/svaS1VTOEscES9CLKVTeKItjJP1EEJuBhTsA0ESOdlnbXeQSgycYwVlliL5zt8Jwcfo4ATYDXtEqsExxjkSkkhCSTCL8fnRgaCAJdr0unUg=@protonmail.com

新增函数string_to_table

1
2
3
4
5
6
7
8
9
10
11
12
13
Add string_to_table() function.
    This splits a string at occurrences of a delimiter.  It is exactly like
    string_to_array() except for producing a set of values instead of an
    array of values.  Thus, the relationship of these two functions is
    the same as between regexp_split_to_table() and regexp_split_to_array().

    Although the same results could be had from unnest(string_to_array()),
    this is somewhat faster than that, and anyway it seems reasonable to
    have it for symmetry with the regexp functions.

    Pavel Stehule, reviewed by Peter Smith


逻辑复制增加正在处理的事物中的复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
commit 464824323e57dc4b397e8b05854d779908b55304
Author: Amit Kapila <akapila@postgresql.org>
Date:   Thu Sep 3 07:54:07 2020 +0530

    Add support for streaming to built-in logical replication.

    To add support for streaming of in-progress transactions into the
    built-in logical replication, we need to do three things:

    * Extend the logical replication protocol, so identify in-progress
    transactions, and allow adding additional bits of information (e.g.
    XID of subtransactions).

    * Modify the output plugin (pgoutput) to implement the new stream
    API callbacks, by leveraging the extended replication protocol.

    * Modify the replication apply worker, to properly handle streamed
    in-progress transaction by spilling the data to disk and then
    replaying them on commit.

    We however must explicitly disable streaming replication during
    replication slot creation, even if the plugin supports it. We
    don't need to replicate the changes accumulated during this phase,
    and moreover we don't have a replication connection open so we
    don't have where to send the data anyway.

    Author: Tomas Vondra, Dilip Kumar and Amit Kapila
    Reviewed-by: Amit Kapila, Kuntal Ghosh and Ajin Cherian
    Tested-by: Neha Sharma, Mahendra Singh Thalor and Ajin Cherian
    Discussion: https://postgr.es/m/688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com

新增视图pg_backend_memory_contexts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
commit 3e98c0bafb28de87ae095b341687dc082371af54 (HEAD -> master, origin/master, origin/HEAD)
Author: Fujii Masao <fujii@postgresql.org>
Date:   Wed Aug 19 15:34:43 2020 +0900

    Add pg_backend_memory_contexts system view.

    This view displays the usages of all the memory contexts of the server
    process attached to the current session. This information is useful to
    investigate the cause of backend-local memory bloat.

    This information can be also collected by calling
    MemoryContextStats(TopMemoryContext) via a debugger. But this technique
    cannot be uesd in some environments because no debugger is available there.
    And it outputs lots of text messages and it's not easy to analyze them.
    So, pg_backend_memory_contexts view allows us to access to backend-local
    memory contexts information more easily.

    Bump catalog version.

    Author: Atsushi Torikoshi, Fujii Masao
    Reviewed-by: Tatsuhito Kasahara, Andres Freund, Daniel Gustafsson, Robert Haas, Michael Paquier
    Discussion: https://postgr.es/m/72a656e0f71d0860161e0b3f67e4d771@oss.nttdata.com

log_line_prefix 增加 %P 显示并行leader

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
commit b8fdee7d0ca8bd2165d46fb1468f75571b706a01
Author: Michael Paquier <michael@paquier.xyz>
Date:   Mon Aug 3 13:38:48 2020 +0900

    Add %P to log_line_prefix for parallel group leader

    This is useful for monitoring purposes with log parsing.  Similarly to
    pg_stat_activity, the leader's PID is shown only for active parallel
    workers, minimizing the log footprint for the leaders as the equivalent
    shared memory field is set as long as a backend is alive.

    Author: Justin Pryzby
    Reviewed-by: Álvaro Herrera, Michael Paquier, Julien Rouhaud, Tom Lane
    Discussion: https://postgr.es/m/20200315111831.GA21492@telsasoft.com

阅读全文 »