postgresql 10.1 参数的初步了解 Preset Options
postgresql 的参数也是比较繁多，好处就是可以灵活控制，不好的地方就是 ··· ··· ，记不住
select distinct ps.category,count(1)
from pg_settings ps
where 1=1
group by ps.category
order by ps.category
;
category | count
-------------------------------------------------------------------+-------
Autovacuum | 11
Client Connection Defaults / Locale and Formatting | 14
Client Connection Defaults / Other Defaults | 5
Client Connection Defaults / Shared Library Preloading | 3
Client Connection Defaults / Statement Behavior | 22
Connections and Authentication / Connection Settings | 9
Connections and Authentication / Security and Authentication | 14
Customized Options | 9
Developer Options | 9
Error Handling | 2
File Locations | 5
Lock Management | 3
Preset Options | 12
Process Title | 2
Query Tuning / Genetic Query Optimizer | 7
Query Tuning / Other Planner Options | 6
Query Tuning / Planner Cost Constants | 9
Query Tuning / Planner Method Configuration | 11
Replication | 1
Replication / Master Server | 2
Replication / Sending Servers | 4
Replication / Standby Servers | 7
Reporting and Logging / What to Log | 18
Reporting and Logging / When to Log | 4
Reporting and Logging / Where to Log | 13
Resource Usage / Asynchronous Behavior | 5
Resource Usage / Background Writer | 4
Resource Usage / Cost-Based Vacuum Delay | 5
Resource Usage / Disk | 1
Resource Usage / Kernel Resources | 1
Resource Usage / Memory | 11
Statistics / Monitoring | 4
Statistics / Query and Index Statistics Collector | 5
Version and Platform Compatibility / Other Platforms and Clients | 1
Version and Platform Compatibility / Previous PostgreSQL Versions | 10
Write-Ahead Log / Archiving | 3
Write-Ahead Log / Checkpoints | 6
Write-Ahead Log / Settings | 12
(38 rows)
注意到其中有些 Preset Options，特意查看一下
select *
from pg_settings ps
where 1=1
and ps.category = 'Preset Options'
order by ps.name
;
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
-----------------------+---------+------+----------------+----------------------------------------------------------------+------------+----------+---------+----------+---------+---------+----------+----------+-----------+------------+------------+-----------------
block_size | 8192 | | Preset Options | Shows the size of a disk block. | | internal | integer | default | 8192 | 8192 | | 8192 | 8192 | | | f
data_checksums | off | | Preset Options | Shows whether data checksums are turned on for this cluster. | | internal | bool | override | | | | off | off | | | f
debug_assertions | off | | Preset Options | Shows whether the running server has assertion checks enabled. | | internal | bool | default | | | | off | off | | | f
integer_datetimes | on | | Preset Options | Datetimes are integer based. | | internal | bool | default | | | | on | on | | | f
max_function_args | 100 | | Preset Options | Shows the maximum number of function arguments. | | internal | integer | default | 100 | 100 | | 100 | 100 | | | f
max_identifier_length | 63 | | Preset Options | Shows the maximum identifier length. | | internal | integer | default | 63 | 63 | | 63 | 63 | | | f
max_index_keys | 32 | | Preset Options | Shows the maximum number of index keys. | | internal | integer | default | 32 | 32 | | 32 | 32 | | | f
segment_size | 131072 | 8kB | Preset Options | Shows the number of pages per disk file. | | internal | integer | default | 131072 | 131072 | | 131072 | 131072 | | | f
server_version | 10.1 | | Preset Options | Shows the server version. | | internal | string | default | | | | 10.1 | 10.1 | | | f
server_version_num | 100001 | | Preset Options | Shows the server version as an integer. | | internal | integer | default | 100001 | 100001 | | 100001 | 100001 | | | f
wal_block_size | 8192 | | Preset Options | Shows the block size in the write ahead log. | | internal | integer | default | 8192 | 8192 | | 8192 | 8192 | | | f
wal_segment_size | 2048 | 8kB | Preset Options | Shows the number of pages per write ahead log segment. | | internal | integer | default | 2048 | 2048 | | 2048 | 2048 | | | f
(12 rows)
注意到
segment_size = 131072 * 8kB = 1024MB ,大小为 toast 的分割大小
wal_segment_size = 2048 * 8kB = 16MB ,大小为 pg_xlog/wal 的大小
如果要改变这些 就不能用yum方式了，只能自己编译时添加参数。
–with-segsize
–with-blocksize
–with-wal-segsize
–with-wal-blocksize
–with-segsize=SEGSIZE
Set the segment size, in gigabytes. Large tables are divided into multiple operating-system files, each of size equal to the segment size. This avoids problems with file size limits that exist on many platforms. The default segment size, 1 gigabyte, is safe on all supported platforms. If your operating system has “largefile” support (which most do, nowadays), you can use a larger segment size. This can be helpful to reduce the number of file descriptors consumed when working with very large tables. But be careful not to select a value larger than is supported by your platform and the file systems you intend to use. Other tools you might wish to use, such as tar, could also set limits on the usable file size. It is recommended, though not absolutely required, that this value be a power of 2. Note that changing this value requires an initdb.
–with-blocksize=BLOCKSIZE
Set the block size, in kilobytes. This is the unit of storage and I/O within tables. The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 32 (kilobytes). Note that changing this value requires an initdb.
–with-wal-segsize=SEGSIZE
Set the WAL segment size, in megabytes. This is the size of each individual file in the WAL log. It may be useful to adjust this size to control the granularity of WAL log shipping. The default size is 16 megabytes. The value must be a power of 2 between 1 and 1024 (megabytes). Note that changing this value requires an initdb.
–with-wal-blocksize=BLOCKSIZE
Set the WAL block size, in kilobytes. This is the unit of storage and I/O within the WAL log. The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 64 (kilobytes). Note that changing this value requires an initdb.
下面查看yum安装的pgsql10.1的参数
$ /usr/pgsql-10/bin/pg_config
BINDIR = /usr/pgsql-10/bin
DOCDIR = /usr/pgsql-10/doc
HTMLDIR = /usr/pgsql-10/doc/html
INCLUDEDIR = /usr/pgsql-10/include
PKGINCLUDEDIR = /usr/pgsql-10/include
INCLUDEDIR-SERVER = /usr/pgsql-10/include/server
LIBDIR = /usr/pgsql-10/lib
PKGLIBDIR = /usr/pgsql-10/lib
LOCALEDIR = /usr/pgsql-10/share/locale
MANDIR = /usr/pgsql-10/share/man
SHAREDIR = /usr/pgsql-10/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-10/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-rpath' '--prefix=/usr/pgsql-10' '--includedir=/usr/pgsql-10/include' '--mandir=/usr/pgsql-10/share/man' '--datadir=/usr/pgsql-10/share' '--with-icu' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-10/doc' '--htmldir=/usr/pgsql-10/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic' 'LDFLAGS=-Wl,--as-needed' 'PKG_CONFIG_PATH=:/usr/lib64/pkgconfig:/usr/share/pkgconfig' 'ICU_CFLAGS=-I/usr/include' 'ICU_LIBS=-L/usr/lib64 -licui18n -licuuc -licudata'
CC = gcc
CPPFLAGS = -DFRONTEND -I/usr/include -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
CFLAGS_SL = -fPIC
LDFLAGS = -L../../src/common -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 10.1
