1. Check the location of the parameter file
Use the show command to view it, and the most commonly used show config_file. You can also view the pg_settings data dictionary.
test=# show config_file;
config_file
------------------------------
/data/pgdata/
(1 row)
test=# show hba_file
test-# ;
hba_file
--------------------------
/data/pgdata/pg_hba.conf
(1 row)
test=# show ident_file ;
ident_file
----------------------------
/data/pgdata/pg_ident.conf
2. Check the parameter values of the current session
You can use the show command or view the pg_settings dictionary.
Use show all to view all parameter values. Show parameter name to view specified parameters
test=# show all;
-------------------------------------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_command | test ! -f /data/archive/%f && cp %p /data/archive/%f | Sets the shell command that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next WAL file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
...
test=# show work_mem;
work_mem
----------
4MB
(1 row)
test=# \x
Expanded display is on.
test=# select * from pg_settings where name in ('work_mem')
test-# ;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name | work_mem
setting | 4096
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context | user
vartype | integer
source | default
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 4096
reset_val | 4096
sourcefile |
sourceline |
pending_restart | f
3. Modify the parameter value of pg
1. Globally modify the parameters of pg.
Some parameters only take effect when the pg service is restarted. A typical example is shared_buffers, which defines the size of shared memory.
Many parameters can be modified when the pg service is running. After the change, the server performs a reload operation, forcing the pg to reread, so you only need to edit the file and then execute pg_ctl reload. For those that need to be restarted, you need to execute pg_ctl restart after modifying postgresql.
For versions after 9.5, you can check whether the parameters you set are valid by checking pg_file_settings. For example, if you set a parameter and need to restart the database to take effect or set an error, an error will appear in this dictionary.
test=# select * from pg_file_settings where error is not null;
sourcefile | sourceline | seqno | name | setting | applied | error
-----------------------------------+------------+-------+-----------------+---------+---------+------------------------------
/data/pgdata/ | 4 | 22 | max_connections | 10000 | f | setting could not be applied
(1 row)
For versions after 9.4, you can also use the alter system command to modify parameters. Using the alter system command will modify the file instead of this, which can protect the file well. Add a mess you made after using many alter system commands, so you just need to delete it and reload it.
test=# show work_mem; work_mem ---------- 4MB (1 row) test=# alter system set work_mem='8MB'; ALTER SYSTEM test=# show work_mem; work_mem ---------- 4MB (1 row)
Check:
[postgres@postgresql1 pgdata]$ cat # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. work_mem = '8MB'
Use pg_ctl reload to reload the configuration file and then view the parameter values:
test=# show work_mem ; work_mem ---------- 8MB (1 row)
2. Use the set command directly to modify it at the session level. After modification, it will be used for every future transaction and is only valid for the current session:
test=# test=# set work_mem='16MB'; SET test=# show work_mem; work_mem ---------- 16MB (1 row)
We open another session and check the work_mem parameter to find out whether work_mem or 4MB
postgres=# show work_mem; work_mem ---------- 4MB (1 row)
Add the local keyword after the command, which is only modified in the current transaction and is only valid in the current transaction:
test=# show work_mem; work_mem ---------- 16MB (1 row) test=# begin; BEGIN test=# set local work_mem='8MB'; SET test=# show work_mem; work_mem ---------- 8MB (1 row) test=# commit; COMMIT test=# show work_mem; work_mem ---------- 16MB
4. Use reset to restore the default value of the parameter
The pg_settings dictionary reset_val field indicates what the default value of this parameter is if reset is used.
Use reset parameter name to restore the default value of a certain parameter, and use reset all to restore all parameter values.
test=# show work_mem; work_mem ---------- 16MB (1 row) test=# reset work_mem; RESET test=# show work_mem; work_mem ---------- 4MB (1 row) test=# reset all; RESET
5. Set parameters for specific user groups
1. Set parameters for all users in a specific database, for example, set work_mem to 16MB for all connections in the test database:
test=# alter database test set work_mem='16MB'; ALTER DATABASE
2. Set parameters for a specific user in the database. For example, for a brent user, set work_mem to 2MB:
postgres=# alter role brent set work_mem='2MB'; ALTER ROLE
After testing, it was found that if you set specific parameters for both the database and the user, then the user shall prevail. For example, above, if I use the brent user to connect to the test database, then my work_mem should be 2MB:
postgres=# \c test brent You are now connected to database "test" as user "brent". test=> test=> test=> show work_mem; work_mem ---------- 2MB
3. Set parameters for a specific user to connect to a specific database. For example, set work_mem to 8MB for user brent in the database test.
test=# alter role brent in database test set work_mem='8MB'; ALTER ROLE
The three settings mentioned above are incremented, that is, if 1, 2, 3 is set, then the third one is the basis. If 1, 2 is set, then the second one is the basis, and so on.
The pg implementation method of this is exactly the same as when the user connects to the database and executes the set command manually immediately and immediately.
To see where your current parameter value is specified, you can get it by querying the source field in pg_settings, for example, if a database-level parameter is set. Then the query result should be as follows:
test=# select name,setting,source from pg_settings where name='work_mem'; name | setting | source ----------+---------+---------- work_mem | 16384 | database
Others, for example, the third type is set:
test=# \c test brent You are now connected to database "test" as user "brent". test=> select name,setting,source from pg_settings where name='work_mem'; name | setting | source ----------+---------+--------------- work_mem | 8192 | database user
Supplement: Analysis and optimization of important postgresql parameters
1，max_connections 200
Maximum number of client connections. Each connection will correspond to the corresponding process on the backend, consuming a certain amount of memory resources. If there are thousands of connections, you need to use the connection pooling tool.
2，shared_buffers 25% of total memory
The memory size of the database used to cache data. The default value of this parameter is very low (considering different system platforms) and needs to be adjusted. It should not be too large. Many practices have shown that more than 1/3 of the memory will reduce performance.
3，effective_cache_size 50%-75% of total memory
This is a guideline for how much memory you expect to be available in the OS and PostgreSQL buffer caches, not an allocation! This parameter is only used when the query optimizer is selected, and is not the actual allocated memory. The larger the parameter, the more the query optimizer prefers to select index scans.
4，checkpoint_segments 256 checkpoint_completion_target 0.9
Checkponit_segments How many numbers are wal checkponit, and there is also a parameter checkponit_timeout to control the maximum time checkpoint. For databases with larger writes, the larger the value, the better. But the larger the value, the longer the recovery time is performed.
checkpoint_completion_target controls checkponit write to scatter write, the larger the value, the more scattered it. The default value is 0.5 and 0.9 is a relatively suitable value.
5，work_mem
For sorting, the default value is as follows. Each connection will be allocated a certain work_mem. This is the memory that will be actually allocated. It should not be too large, the default value is as long as it is. If you want to use a larger sorting operation in the statement, you can set this parameter at the session level, set work_men = '2GB', to improve execution speed.
6，maintanance_work_mem
Memory used by maintenance operations. For example: vacuum ,create index,alter table add foreign key,restoring database dumps. When doing these operations, you can temporarily set the value size to speed up the execution. set session maintenance_work_mem = ‘2GB’;
7,random_page_cost (default value 4) seq_page_cost (default value 1)
Set the optimizer to get a cost for a random page, compared to the cost for a scanned page in the next order to 1.
When using faster storage, such as raid arrays, scsi, ssd, this value can be adjusted appropriately. Favorable for the optimizer to hang index scanning. When ssd, it can be set to 2.
8，autovacuum
—maintenance_work_mem 1-2GB
—autovacuum_max_workers
If there are multiple small tables, allocate more workers and fewer mem.
Large tables, more men, fewer workers.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.