Recommendations for PostgreSQL

This topic provides recommendations for the appropriate database configuration for PostgreSQL in highly distributed environments. BMC recommends PostgreSQL on Linux server for better performance.

Disk layout

When you run the Create_TS.pgsql.sql script, which is provided for tablespace creation on your database, BMC recommends that you segregate the tablespaces on different disks, as shown in the following table:

Tablespaces for tables

Tablespaces for indexes

Minimum requirement disk layout

Recommended best disk layout

Storage capacity sizing

When sizing storage capacity for a PostgreSQL system, you need to consider the following PostgreSQL database components:

• Database files
• Transaction log files
• Backup

System tuning recommendations for PostgreSQL

File access times

Applying the noatime attribute can significantly improve the file I/O performance of your server. Edit the /etc/fstab file and add the noatime attribute as follows:

Read ahead

Modifying the Read-Ahead parameter can improve the read performance of a disk.

Type # blockdev --getra /dev/sda to check the Read-Ahead value of an individual disk. The default value is generally 256.

To permanently increase this value to 4096, edit the /etc/rc.local file by adding the following block to the end of the file:

Read caching and swapping

Modifying the following parameters increases database performance:

To permanently change these parameter values, edit the /etc/sysctl.conf file as follows:

To set the values without rebooting, run the sysctl -p command after saving and exiting the sysctl.conf file.

Shared memory and semaphores

A large PostgreSQL installation can quickly exhaust various operating system resource limits. The following example shows how to increase the shared memory to 16 GB of RAM:

Configure PostgreSQL database

1. Go to  PGTune   and provide basic information about the hardware configuration.
   
2. Select your DB version from the DB version list.
3. Select Linux from the OS Type list.
4. Select Mixed type of applications from the DB Type list.
5. For Total Memory (RAM) and Number of CPUs see Verify-the-database-server-requirements.
6. Select 100 from the Number of Connections list.
7. Select the type of hard drive which PostgreSQL uses as storage from the Data Storage list.
8. Click Generate and go to the ALTER SYSTEM tab. The configuration for PostgreSQL is calculated based on the provided hardware configuration.
   
9. Click Copy configuration.

10. Connect to the PostgreSQL database.

    sudo -u postgresql psql
    postgres=#
    paste the copy configuration
    ALTER SYSTEM SET max_connections = '100';
    ALTER SYSTEM SET shared_buffers = '4GB';
    ALTER SYSTEM SET effective_cache_size = '12GB';
    ALTER SYSTEM SET maintenance_work_mem = '1GB';
    ALTER SYSTEM SET checkpoint_completion_target = '0.9';
    ALTER SYSTEM SET wal_buffers = '16MB';
    ALTER SYSTEM SET default_statistics_target = '100';
    ALTER SYSTEM SET random_page_cost = '4';
    ALTER SYSTEM SET effective_io_concurrency = '2';
    ALTER SYSTEM SET work_mem = '5242kB';
    ALTER SYSTEM SET min_wal_size = '1GB';
    ALTER SYSTEM SET max_wal_size = '4GB';
    ALTER SYSTEM SET max_worker_processes = '8';
    ALTER SYSTEM SET max_parallel_workers_per_gather = '4';
    ALTER SYSTEM SET max_parallel_workers = '8';
    ALTER SYSTEM SET max_parallel_maintenance_workers = '4';

11. Add the following settings to perform autovacuum.

    ALTER SYSTEM SET bgwriter_lru_maxpages = '1000';
    ALTER SYSTEM SET bgwriter_lru_multiplier = '4.0';
    ALTER SYSTEM SET random_page_cost = '2.0';
    ALTER SYSTEM SET cpu_tuple_cost = '0.03';
    ALTER SYSTEM SET log_autovacuum_min_duration = '0';
    ALTER SYSTEM SET autovacuum_max_workers = '5';
    ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; 
12. Enter # \q to quit the PostgreSQL database.

13. Restart the database.

    sudo systemctl restart postgresql-13

14. To check whether these settings are running, execute the following command.

    sudo -u postgresql psql
    postgres=# show shared_buffers;
    shared_buffers
    ----------------
    4GB