8.2 Monitoring and Tuning

Determining a Backup Strategy

Consider these tips when defining your database backup strategy:

Online Backups

An online backup is performed against a running database. Backing up a running database provides a snapshot of the database where the data is in a consistent state, even though other users are modifying the database. This is referred to as a logical backup because the data is backed up, not the database files.

You can use the PostgreSQL pg_dump command to do a full online backup. This extracts the PostgreSQL database into an archive file.

pg_dump -U zenadmin -p 54327 -W -d zenworks > zenworks_dump.sql

For more information about pg_dump, see pg_dump on the PostgreSQL website.

Offline Backups

An offline database backup requires the database server to be stopped. Once the database is not running, you can make a backup by copying the database files to another location. You should only perform an offline backup when the database is not running and when the database server has shut down properly.

This method can be used in conjunction with a scheduling mechanism, such as Windows Task Scheduler or crontab, to automate the process. Offline backups can use incremental backup (transaction logs only) or full backup and lend itself better to do a full backup quickly. They are used with full backups at less frequent intervals.

For information about copying the database files, see File System Level Backup on the PostgreSQL website.

shared_buffers

This parameter designates the amount of shared memory dedicated to the server for caching data.

For more information about shared_buffers, see Resource Consumption on the PostgreSQL website.

work_mem

This parameter specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. If a lot of complex sorts are happening, and you have enough memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which will be faster than disk-based equivalents.

For more information about work_mem, see Resource Consumption on the PostgreSQL website.

maintenance_work_mem

This parameter specifies the maximum amount of memory used by maintenance operations such as VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY. Since only one of these operations can be executed at a time by a database session and a PostgreSQL installation doesn’t have many of them running concurrently, it is safe to set the value of maintenance_work_mem significantly larger than work_mem.

For more information about maintenance_work_mem, see Resource Consumption on the PostgreSQL website.

effective_cache_size

The effective_cache_size should be set to an estimate of how much memory is available for disk caching by the operating system and within the database itself. This is a guideline for how much memory you expect to be available in the operating system and PostgreSQL buffer caches, not an allocation.

For more information about effective_cache_size, see Query Planning on the PostgreSQL website.

temp_buffers

This parameter sets the maximum number of temporary buffers used by each database session. The session local buffers are used only for access to temporary tables. These will be cleared when the connection is closed.

For more information about temp_buffers, see Resource Consumption on the PostgreSQL website.

max_locks_per_transaction & max_pred_locks_per_transaction

The max_locks_per_transaction value indicates the number of database objects that can be locked simultaneously. By default, it's set to 64, which means that PostgreSQL is prepared to track up to 64 X number of open transactions locks. The reason to have a limit is to avoid using dedicated shared memory if you don't need more locks than that.

To see if you need to increase this value, check for "ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction" messages in the following PostgreSQL log files:

For more information about max_locks_per_transaction and max_pred_locks_per_transaction, see Lock Management on the PostgreSQL website.

logging_collector

This parameter must be enabled in order to log any activity in PostgreSQL. The backend process responsible for logging database activity is called logger and it gets started when logging_collector is set to ON. Changing this parameter requires a PostgreSQL restart.

log_line_prefix

This parameter helps you customize every log line being printed in the PostgreSQL log file. You can log the process id, application name, database name and other details for every statement as required. The following log_line_prefix may be helpful in most scenarios:

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'

The above setting records the following for every statement being logged:

log_duration

This parameter records the duration of every completed statement in PostgreSQL log, irrespective of any duration limit enforced by the log_min_duration_statement parameter. Note that, as with log_min_duration_statement, enabling this parameter may increase log file usage and affect the server’s general performance. For this reason, if you already have log_min_duration_statement enabled it is often suggested to disable log_duration unless you have a specific need to keep track of both.

log_rotation_size

This parameter defines the size limit for each log file; once it reaches this threshold the log file is rotated. The following example limits the size of each log file to 500 MB:

log_rotation_size = '500MB'

log_rotation_age

This parameter determines the maximum life span for a log file, forcing its rotation once this threshold is reached. This parameter is usually set in terms of hours or days; the minimum granularity is a minute. However, if log_rotation_size is reached first, the log gets rotated anyway, irrespective of this setting. The following example sets the rotation age to one day:

log_rotation_age = 1d

log_statement

This parameter controls what type of SQLs are logged. The recommended setting is DDL, which logs all DDLs that are executed. Tracking DDLs allow you to later audit when a given DDL was executed and by whom. By monitoring and understanding the amount of information it may write to the log file you may consider modifying this setting.

Other possible values are none, mod (includes DDLs plus DMLs), and all.

log_directory

This parameter defines the directory in which log files are created. Please note that if you have enabled detailed logging it is recommended to have a separate disk—different from the data directory disk—allocated for log_directory. Example:

log_directory = /this_is_a_new_disk/pg_log

Dynamic Statistics Views

The PostgreSQL statistics collector is a subsystem that supports collection and reporting of information about server activity. The collector tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one.PostgreSQL supports reporting dynamic information about exactly what is going on in the system right now, such as the exact command currently being executed by other server processes, and which other connections exist in the system. A couple of useful predefined views that you should be aware of are pg_stat_activity and pg_stat_progress_vacuum.

For these views to be enabled you need to make sure that the system configuration parameter track_activities is on. To have enable these views for all server processes, set the parameter in the postgresql.conf file. The file is located in:

You can also turn on the parameter for individual sessions by using the SET command.

SELECT
     p.pid,
     now() - a.xact_start AS duration,
     coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
     CASE
       WHEN a.query ~ '^autovacuum.*to prevent wraparound' THEN 'wraparound'
       WHEN a.query ~ '^vacuum' THEN 'user'
       ELSE 'regular'
     END AS mode,
     round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
     round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
     p.index_vacuum_count,
     round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;

System Views

PostgreSQL provides several built-in views that provide access to commonly used queries on the system catalogs. The pg_locks view provides real-time information about the current locks held by active processes in the system.

If you think that long lock waits are impacting performance, you can: