Module5_Configuration Flashcards
how to change parameter for a single session
using the SET command
how to show all parameter in DB
show all;
how to see what parameter are set in which file and if they need restart to become active.
select * from pg_file_settings;
how to reload cluster config from psql?
SELECT pg_reload_config();
how to reset parameter to initial value?
alter system RESET parameter_name;
how can multiple cluster on 1 machine use 1 postgresql.conf
by using INCLUDE or INCLUDE_DIR to point to that central postgresql.conf
how to alter parameter on DATABASE level and NOT on whole cluster
alter database DATABASE_NAME set PARAMETER_NAME=1 ;
how to change parameter for 1 USER not whole cluster.
alter user USER_NAME set parameter_name=1;
what overwrites the parameter cluster level, database level and user level.
database level overwrites cluster level parameter
user level overwrites database level parameters
what parameter specified from which hosts PG can accept connections
LISTEN_ADDRESSES
what parameter define how many connections are allowd on the cluster and what is the default?
parameter name is max_connections and is default 100
how do always reserve connection for superuser in PG
superuser_reserved_connections (default 3 sessions)
what do the parameters unix_socket_directory unix_socket_permissions do?
when local user is usings socket on linux and the permission on that directory (default /tmp)
what is autentication_timeout parameter?
a timeframe where a client need to perform the authentication (default 1 min) longer than that and connection will be terminated.
what does parameter row_security do?
this parameter controls row security policy behavior (default is on)
what is parameter password_encryption
what is the encryprion method to store password. Default is scram_sha_256
how to enable SSL on PG?
enable parameter SSL (default off)
what are these SSL parameter:
ssl_ca_file
ssl_cert_file
ssl_key_file
ssl_ciphers
ssl_dh_params_file
ssl_ca_file: specifies name of file containing ssl CERTIFICATE AUTHORITY (CA)
ssl_cert_file: specifies name of file containing ssl CERTIFICATES
ssl_key_file: specifies name of file containing ssl server private key
ssl_ciphers: list of ssl ciphers that may be used for secure connections
ssl_dh_params_file: specifies name of file for custom OPENSSL DH parameters
what are these MEMORY SETTINGS:
shared_buffers
temp_buffers
work_mem
maintenance_work_mem
autovacuum_work_mem
temp_file_limit
shared_buffers: size of shared buffel pool for a cluster (server side parameter)
temp_buffers: amount of mem used for caching temp tables (session side parameter)
work_mem: amount of mem used for sorting and hashing operations (session side parameter)
maintenance_work_mem: amount of mem used for maintenance tasks like reindexing (session side parameter)
autovacuum_work_mem: amount of mem used for autovacume worker (session side parameter)
temp_file_limit: amount of mem used for temporary files (session side parameter)
what are the background writer settings:
bgwriter_delay
bgwriter_lru_maxpages
bgwriter_lru_multiplier
bgwriter_delay: specfies time between activity rounds for the background writer (default 200ms)
bgwriter_lru_maxpages: max numbers of pages that background writer may client per activity round
bgwriter_lru_multiplier: multiplier on buffers scanned per round. by default if system thinks 10 pages will be needed it cleans 10 * bgwriter_lru_multiplier (lets say 2.0) = 20
what are these query planning settings:
- random_page_cost
- seq_page_cost
- effective_cache_size
- enable_hints
- plan_cache_mode
- random_page_cost: estimate cost of random page fetch (default 4.0)
- seq_page_cost: estimate cost of sequential page fetch (default 1.0) must be >=random_page_cost
- effective_cache_size (default 4gb): used to estimate cost of index scan (best practice 75% of system memory)
- enable_hints(default true): uses hints in query. for ADVANCED SERVER ONLY NOT PSQL COMMUNITY EDITION
- plan_cache_mode (default auto): controls custom of generic plan exectutions for prepared statements. can be set to auto, force_custom_plan and force_generic_plan
what are these statement behavior parameters?
- search_path
- default_tablespace
- temp_tablespace
- statement_timeout
- idle_in_transaction_session_timeout
- search_path: order of schema’s where objects are searched (default “user”, public)
- default_tablespace: name of TS objects are default created
- temp_tablespaces: name of TS which TEMPORARY objects are created
- statement_timeout: timeout to abort query if reaches that time. default is OFF/0 value is in MS.
- idle_in_transaction_session_timeout: terminates when sessions idle IN TRANSACTION reach timeout (time in MS). not just an idle connection but must be in a transaction.
what are these write ahead log settings:
- wal_level
- fsync
- wal_buffers
- min_wal_size
- max_wal_size
- checkpoint_timeout
- wal_compression
- wal_level (default replica): determines how much information written to wall parameter can also be minimal,logical (logical streaming).
- fsync (default on): force WAL buffer flush at each commit. Turning off can lead to corruptions.
- wal_buffers (default -1, autotune): the amount memory used in shared memory for WAL data. default is 1.32 about 3% of shared buffers.
- min_wal_size (default 80mb): the WAL size to start recycling wal files.
- max_wal_size (default 1GB): the WALL size to start checkpoint. Controls the number of wal segments (16 mb each) after which checkpoint is forces.
- checkpoint_timeout (default 5 min). Maximum time between checkpoints.
- wal_compression (default off). The WALL of full pages will be compressed and written.
what does de log_destination parameter do?
controls logging type for database cluster. can be set to csvlog, syslog (linux) and evenlog (windows)
what does logging_collector parameter do?
enables logger process to capture stderr and csv logging messages. then be redirecten to location defined in config (log_directory)
default on.
what do these parameters do:
log_min_messages
log_min_error_statments
log_min_duration_statement
log_autovacuum_min_duration
log_statement_sample_rate
log_transaction_sample_rate
log_min_messages: messages of this severity or higher will be sent to the server log
log_min_error_statments: when a message of this severity or hight will be sent to loggen together with the query that caused it
log_min_duration_statement: when query exceeds this parameter it will be logged.
log_autovacuum_min_duration: logs any autovacuum operation running for at least this long
log_statement_sample_rate: percentage of queries (above log_autovacuum_min_duration) to be logged.
log_transaction_sample_rate: samples a percentage of transactions by logging statement.
where are these parameters used for:
log_connection:
log_disconnections:
log_temp_files:
log_checkpoints:
log_lock_waits:
log_error_verbosity:
log_line_prefix:
log_statment:
log_connection: log succesfull connections to the server log
log_disconnections: log some information when sessions are DISconnected including duration of the session
log_temp_files: logs temp files of this size of bigger to the server log (kilobytes)
log_checkpoints: log when checkpoints happen
log_lock_waits: log lock waits >= deadlock_timeout
log_error_verbosity: how detailed the logges messages are (terse, default, or verbose messages) default=default
log_line_prefix: additional details to log with each line. default timestamp and process ID
log_statment: log sql statments like DDL, DML, ALL or none
what does this parameters do:
max_parallel_workers_per_gather
parallel_tuple_cost
parallel_setup_cost
min_parallel_table_scan_size
min_parallel_index_scan_size
force_parallel_mode
max_parallel_maintenance_workers
max_parallel_workers_per_gather (default 2): how many parallel workers can be used for a read_only quey
parallel_tuple_cost (default 0.1): estimate cost of tranferring one tuple from a parallel worker process to another
parallel_setup_cost (default 1000): estimate cost of launching parallel worker process
min_parallel_table_scan_size (default 8mb): set MIN amount of table data that ust be scanned on order or parallel scan.
min_parallel_index_scan_size (default 512kb): set MIN amount of index data that ust be scanned on order or parallel scan.
force_parallel_mode: usefull when testing parallel query scan even when there is no perfromance benefit
max_parallel_maintenance_workers (default 2): enables parallel BTREE index creation
what are these parameters for:
- autovacuum
- log_autovacuum_min_duration
- autovacuum_max_workers
- autovacuum_max_mem
autovacuum (default on): controls when the autovacuum launcher runs and starts worker processes to vacuum and analyse tables
log_autovacuum_min_duration (default -1): autovacuum tasks running longer than this duration are logged
autovacuum_max_workers (defaul 3): max numbers of autovacuum worker processes which may be running at one time.
autovacuum_max_mem (default -1, to use maintenace_work_mem): max amount of memory used by EACH autovacuum worker
