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)

Sources


https://www.postgresql.org/docs/ - The official documentation

https://wiki.postgresql.org - The official Wiki

Q & A