PostgreSQL 9.3 improve pgstattuple
背景
pgstattuple可以用来查询tuple级别的统计信息.
PostgreSQL 9.3 加入了gin索引的pending信息输出.
统计时对被统计的数据加read lock.
所以如果数据正在变更或插入, 只要HeapTupleSatisfiesNow返回false, 就归入dead tuple.
例如 :
create table trgm_test(id serial4 primary key, info text);
create index trgm_test_gin on trgm_test using gin (info gin_trgm_ops);
测试脚本 :
vi ins.sql
insert into trgm_test (info) values(md5(random()::text));
pgbench -M prepared -r -n -f ./ins.sql -c 16 -j 1 -T 60
测试同时执行 :
digoal=# select * from pgstattuple('trgm_test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_pe
rcent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------
------
203603968 | 2981460 | 181869060 | 89.32 | 16 | 976 | 0 | 167688 |
0.08
(1 row)
digoal=# select * from pgstatindex('trgm_test_pkey');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-
-------------------
2 | 2 | 133308416 | 412 | 61 | 16211 | 0 | 0 | 48.73 |
0.56
(1 row)
digoal=# select * from pgstatginindex('trgm_test_gin');
version | pending_pages | pending_tuples
---------+---------------+----------------
1 | 69813 | 386169
(1 row)
测试完成后执行 :
digoal=# select * from pgstattuple('trgm_test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_pe
rcent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------
------
203603968 | 2981476 | 181870036 | 89.33 | 0 | 0 | 0 | 167688 |
0.08
(1 row)
digoal=# select * from pgstatindex('trgm_test_pkey');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-
-------------------
2 | 2 | 154779648 | 412 | 71 | 18822 | 0 | 0 | 48.7 |
0.48
(1 row)
digoal=# select * from pgstatginindex('trgm_test_gin');
version | pending_pages | pending_tuples
---------+---------------+----------------
1 | 0 | 0
(1 row)
输出列含义对照 :
Table F-21. pgstattuple Output Columns
| Column | Type | Description |
|---|---|---|
| table_len | bigint | Physical relation length in bytes |
| tuple_count | bigint | Number of live tuples |
| tuple_len | bigint | Total length of live tuples in bytes |
| tuple_percent | float8 | Percentage of live tuples |
| dead_tuple_count | bigint | Number of dead tuples |
| dead_tuple_len | bigint | Total length of dead tuples in bytes |
| dead_tuple_percent | float8 | Percentage of dead tuples |
| free_space | bigint | Total free space in bytes |
| free_percent | float8 | Percentage of free space |
pgstatindex :
| Column | Type | Description |
|---|---|---|
| version | integer | B-tree version number |
| tree_level | integer | Tree level of the root page |
| index_size | bigint | Total number of pages in index |
| root_block_no | bigint | Location of root block |
| internal_pages | bigint | Number of “internal” (upper-level) pages |
| leaf_pages | bigint | Number of leaf pages |
| empty_pages | bigint | Number of empty pages |
| deleted_pages | bigint | Number of deleted pages |
| avg_leaf_density | float8 | Average density of leaf pages |
| leaf_fragmentation | float8 | Leaf page fragmentation |
pgstatginindex :
| Column | Type | Description |
|---|---|---|
| version | integer | GIN version number |
| pending_pages | integer | Number of pages in the pending list |
| pending_tuples | bigint | Number of tuples in the pending list |
gin pending的由来, 主要是为了加速gin索引的更新速度.
57.3.1. GIN Fast Update Technique
Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes:
inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item).
为了加快gin索引的更新速度, 从8.4开始, gin引入了临时空间的方法, 也就是gin的条目先临时存放到一个内存空间中.
As of PostgreSQL 8.4, GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries.
When the table is vacuumed, or if the pending list becomes too large (larger than work_mem),
当临时的未排序的list大于work_mem或者表被vacuum时, 这些条目采用batch的方式写入GIN索引数据结构中.
the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation.
This greatly improves GIN index update speed, even counting the additional vacuum overhead.
Moreover the overhead work can be done by a background process instead of in foreground query processing.
主要的缺点是, 当临时空间中的数据量很大时, 查询速度会变慢, 因为需要查询gin索引同时还需要查询临时空间结构(未排序).
The main disadvantage of this approach is that searches must scan the list of pending entries in addition to searching the regular index, and so a large list of pending entries will slow searches significantly.
另一个缺点是, 触发too large(也就是超出work_mem时)batch update的那个插入会变得很慢.就是说当work_mem满了的时候, 后续的插入无法利用work_mem了, 只有等batch update完毕, work_mem清除了才会变快.
Another disadvantage is that, while most updates are fast, an update that causes the pending list to become "too large" will incur an immediate cleanup cycle and thus be much slower than other updates.
Proper use of autovacuum can minimize both of these problems.
如果要有比较均衡的速度, 最好使用autovacuum, 在work_mem满之前触发gin batch update.
If consistent response time is more important than update speed, use of pending entries can be disabled by turning off the FASTUPDATE storage parameter for a GIN index.
See CREATE INDEX for details.
参考
1. http://www.postgresql.org/docs/devel/static/pgstattuple.html
2. http://www.postgresql.org/docs/devel/static/gin-implementation.html#GIN-FAST-UPDATE