+
+
+ PostgreSQL tuning
+ Data Science @ Beryl
+
+ Simeon Simeonov
+
+
+
+
+
+ Agenda
+
+
+ - Generic tools for gathering information
+ - Memory settings
+ - Logging and performance reports
+ - Other tools for analysis
+
+
+
+
+
+
+ General tools for gathering information
+
+
+
+ # 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
+
+
+ - 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
+
+
+ - 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)
+
+
+
+
+
+
+
+