PostgreSQL

Postgresql tips and recipes¶

This section contains advice on postgresql database server tuning, performing backups and working with read replicas.

Ladění výkonu PostgreSQL¶

Tuning PostgreSQL is required to achieve a high-performing system but is optional in terms of getting DHIS2 to run. The various settings can be specified in the postgresql.conf configuration file or, preferably, in a specific file in the conf.d directory. The settings is based on allocating 8 GB RAM to PostgreSQL and should be adjusted accordingly to the environment.

sudo nano /etc/postgresql/12/main/postgresql.conf

Set the following properties.

jit = off

This is important to set for postgresql versions 12 and greater. The jit compiler functionality causes a significant slowdown on many DHIS2 specific queries, eg Program Indicator queries. For versions 11 and below, the setting is off by default.

max_connections = 200

Určuje maximální počet připojení, které PostgreSQL povolí.

shared_buffers = 3GB

Určuje, kolik paměti má být přiděleno výhradně pro cachování PostgreSQL. Toto nastavení řídí velikost sdílené paměti jádra, která má být vyhrazena pro PostgreSQL. Mělo by být nastaveno na přibližně 40 % celkové paměti vyhrazené pro PostgreSQL.

work_mem = 24MB

Určuje množství paměti použité pro interní operace třídění a hashování. Toto nastavení se vztahuje na jedno připojení a jeden dotaz, takže při jeho příliš vysokém zvýšení může dojít ke spotřebě velkého množství paměti. Správné nastavení této hodnoty je zásadní pro výkon agregace DHIS2.

maintenance_work_mem = 1GB

Určuje množství paměti, které může PostgreSQL využít pro údržbové operace, jako je vytváření indexů, spouštění vakua, přidávání cizích klíčů. Zvýšení této hodnoty může zlepšit výkonnost vytváření indexů během procesů generování analýz.

temp_buffers = 16MB

Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables.

effective_cache_size = 8GB

An estimate of how much memory is available for disk caching by the operating system (not an allocation) and is used by PostgreSQL to determine whether a query plan will fit into memory or not. Setting it to a higher value than what is really available will result in poor performance. This value should be inclusive of the shared_buffers setting. PostgreSQL has two layers of caching: The first layer uses the kernel shared memory and is controlled by the shared_buffers setting. PostgreSQL delegates the second layer to the operating system disk cache and the size of available memory can be given with the effective_cache_size setting.

checkpoint_completion_target = 0.8

Nastaví paměť použitou pro vyrovnávací paměť během procesu zápisu WAL. Zvýšení této hodnoty může zlepšit propustnost v systémech náročných na zápis.

synchronous_commit = off

Určuje, zda odevzdání transakce počká na zapsání záznamů WAL na disk před návratem ke klientovi, nebo ne. Nastavení této hodnoty na vypnuto výrazně zlepší výkon. Znamená to také, že mezi ohlášením úspěšné transakce klientovi a jejím skutečným zabezpečením je mírná prodleva, ale stav databáze nemůže být poškozen a je to dobrá alternativa pro systémy náročné na výkon a zápis, jako je DHIS2.

wal_writer_delay = 10s

Určuje prodlevu mezi operacemi zápisu WAL. Nastavení této hodnoty na vysokou hodnotu zlepší výkon na systémech náročných na zápis, protože během jednoho zápisu na disk může být provedeno potenciálně mnoho operací zápisu.

random_page_cost = 1.1

Pouze SSD. Nastavuje odhad plánovače dotazů na cenu za stránku disku, která není sekvenčně načítána. Nízká hodnota způsobí, že systém upřednostní indexové skenování před sekvenčním skenováním. Nízká hodnota má smysl pro databáze běžící na jednotkách SSD nebo s velkou mezipamětí v paměti. Výchozí hodnota je 4,0, což je u tradičních disků rozumné.

max_locks_per_transaction = 96

Určuje průměrný počet zámků objektů přidělených pro každou transakci. Toto je nastaveno hlavně proto, aby bylo možné dokončit upgradovací rutiny, které se dotýkají velkého počtu tabulek.

track_activity_query_size = 8192

Určuje počet bajtů vyhrazených pro sledování aktuálně prováděného příkazu pro každou aktivní relaci. Užitečné pro zobrazení celého řetězce dotazu pro monitorování aktuálně běžících dotazů.

Restartujte PostgreSQL zadáním následujícího příkazu:

sudo systemctl restart postgresql

Přečtěte si konfiguraci replikace databáze¶

DHIS 2 allows for utilizing read only replicas of the master database (the main DHIS 2 database). The purpose of read replicas is to enhance the performance of database read queries and scale out the capacity beyond the constraints of a single database. Read-heavy operations such as analytics and event queries will benefit from this.

The configuration requires that you have created one or more replicated instances of the master DHIS 2 database. PostgreSQL achieves this through a concept referred to as streaming replication. Configuring read replicas for PostgreSQL is not covered in this guide.

Read replicas can be defined in the dhis.conf configuration file. You can specify up to 5 read replicas per DHIS 2 instance. Each read replica is denoted with a number between 1 and 5. The JDBC connection URL must be defined per replica. The username and password can be specified; if not, the username and password for the master database will be used instead.

The configuration for read replicas in dhis.conf looks like the below. Each replica is specified with the configuration key readN prefix, where N refers to the replica number.

# Read replica 1 configuration

# Database connection URL, username and password
read1.connection.url = jdbc:postgresql://127.0.0.11/dbread1
read1.connection.username = dhis
read1.connection.password = xxxx

# Read replica 2 configuration

# Database connection URL, username and password
read2.connection.url = jdbc:postgresql://127.0.0.12/dbread2
read2.connection.username = dhis
read2.connection.password = xxxx

# Read replica 3 configuration

# Database connection URL, fallback to master for username and password
read3.connection.url = jdbc:postgresql://127.0.0.13/dbread3

Note that you must restart your servlet container for the changes to take effect. DHIS 2 will automatically distribute the load across the read replicas. The ordering of replicas has no significance.

Práce s databází PostgreSQL¶

Common operations when managing a DHIS2 instance are dumping and restoring databases. Note that when making backups of the DHIS 2 database, it is good practise to exclude tables which are generated by the system, such as the resource and analytics tables. To make a dump (copy) of your database to a file, you can invoke the following command.

pg_dump {database} -U {user} -T "_*" -T "analytics*"  -f {filename}

pg_dump dhis2 -U dhis -T "analytics*" -T "_*" -f dhis2.sql

It is good practice to compress the If you want to compress the output file with gzip, which can be done like this:

pg_dump dhis2 -U dhis -T "analytics*" -T "_*" | gzip > dhis2.sql.gz

To restore the database copy on another system, you first need to create an empty database as described in the installation section. You also need to gunzip the copy if you created a compressed version. To restore the copy you can invoke the following command:

psql -d dhis2 -U dhis -f dhis2.sql