-
- # shell
- # fetch information from the OS
- cat /proc/cpuinfo
- cat /proc/meminfo
- sysctl -a | grep shm # get kernel parameters of interest
-
-
- -- SQL
- -- show the current values of all settings
- SHOW ALL;
-
- -- display even more than all...
- SELECT * FROM pg_settings;
-
- -- opening postgresql.conf and reading the comments - the old school approach
-
-
- # old school: edit postgresql.conf and read the comments
-
+
+ # shell
+ # fetch information from the OS
+ cat /proc/cpuinfo
+ cat /proc/meminfo
+ sysctl -a | grep shm # get kernel parameters of interest
+
+
+ -- SQL
+ -- show the current values of all settings
+ SHOW ALL;
+
+ -- display even more than all...
+ SELECT * FROM pg_settings;
+
+ -- opening postgresql.conf and reading the comments - the old school approach
+
+
+ # old school: edit postgresql.conf and read the comments
+
-
+
-
-
Memory settings
+
+
Memory settings
-
-
shared_buffers - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the system memory (128MB -> 1GB)
-
effective_cache_size - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)
-
work_mem - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)
-
maintenance_work_mem - the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX. It's safe to set this value significantly larger than work_mem (64MB -> 256MB)
-
-
-
-
-
Logging and performance reports
+
+
shared_buffers - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the system memory (128MB -> 1GB)
+
effective_cache_size - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)
+
work_mem - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)
+
maintenance_work_mem - the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX. It's safe to set this value significantly larger than work_mem (64MB -> 256MB)
+
+
+
+
+
Logging and performance reports
-
pgBadger - a fast PostgreSQL log analysis report can be used for general analysis.
-
-
log_checkpoints - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them
-
log_connections - each attempted connection to the server to be logged, as well as successful completion of client authentication
-
log_disconnections - provides information similar to log_connections, plus the duration of the session
-
log_line_prefix - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '
-
log_lock_waits - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance
-
log_temp_files - when set to 0, a log entry is emitted for each temporary file when it is deleted
-
log_autovacuum_min_duration - set to 0 it logs all autovacuum actions
-
-
-
-
-
Other tools for analysis
+
pgBadger - a fast PostgreSQL log analysis report can be used for general analysis.
+
+
log_checkpoints - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them
+
log_connections - each attempted connection to the server to be logged, as well as successful completion of client authentication
+
log_disconnections - provides information similar to log_connections, plus the duration of the session
+
log_line_prefix - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '
+
log_lock_waits - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance
+
log_temp_files - when set to 0, a log entry is emitted for each temporary file when it is deleted
+
log_autovacuum_min_duration - set to 0 it logs all autovacuum actions
+
+
+
+
+
Other tools for analysis
-
-
ANALYZE - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
-
VACUUM - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (VACUUM vs. VACUUM FULL)
-
-
-
-
-
Sources
+
+
ANALYZE - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
+
VACUUM - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (VACUUM vs. VACUUM FULL)