---
# System prepended metadata

title: ADPG 14.2_arenadata1 maintenance

---

# ADPG 14.2_arenadata1 maintenance

## 1. Features

  - Deploy PostgreSQL 14.2 on the single host.
  - Perform initdb.
  - Support PostGIS and all contrib extensions.
  - Manage sysctl parameters of the host.
  - Manage postgresql.conf and pg_hba.conf settings.

## 2. Deployment

Here is the diagram of ADPG deployment with ADCM.
![](https://i.imgur.com/WyiDybS.png)

Highlights:
  - All ADPG configurations have to be set in ADCM and than be applied with **Reconfigure & Reload** or **Reconfigure & Restart** actions. All changes made by user on host will be rewritten with reconfigure actions.
  - Some ADPG parameters can be applied with **Reconfigure & Reload**. Another ones can by applied only with restarting service. This information can be found in official PostgreSQL documentation, for example for [resource consumption parameters](https://www.postgresql.org/docs/current/runtime-config-resource.html).
  - Extensions are installed with **Install postgis** and **Install contrib** actions.
  - **postgresql.conf** section has two sections: base parameters and custom section in advanced mode.
  - There are several **postgresql.conf** settings in ADPG service configuration. They should be revised before cluster installation as they depends on host's hardware. For details, see [documentation](https://docs-new.arenadata.io/en/ADPG/1.0/how-to/recommended-settings.html).
    - shared_buffers
    - max_worker_processes
    - max_parallel_workers
    - max_parallel_workers_per_gather
    - max_parallel_maintenance_worker
    - max_connections
    - listen_addresses
    - port
![](https://i.imgur.com/ePYCG0X.png)
  - Custom section consists of any postgresql.conf parameters.
![](https://i.imgur.com/0gyZtvb.png)
  - We add some new **postgresql.conf** settings in the next release:
    - effective_cache_size
    - maintenance_work_mem
    - work_mem
    - min_wal_size
    - max_wal_size
    - wal_keep_size
    - hugepages
    - superuser_reserved_connections
  - It's recommended to enable pg_stat_statements extension.

### 2.1. Tunning postgresql.conf parameters
  - **shared_buffers** is about a quarter of RAM size. It can be increased to 0.75 RAM depending on the database load.
  - **effective_cache_size** is equal to quarter of RAM size.
  - **max_connections** ~= 2-4 x CPU (it's recommended to limit real conections with pgBouncer [< 100 connection])
  - [PGTune](https://pgtune.leopard.in.ua/) is fine in the most cases.
![](https://i.imgur.com/GQlbSMC.png)
  - [Cybertec PostgreSQL configurator](https://pgconfigurator.cybertec-postgresql.com/) is another one advanced tool.

### 2.2. Logging parameters
![](https://i.imgur.com/a3VGFPX.png)


## 3. Limitations

  - Backup with default tools: **pgdump** and **pg_basebackup**.
  - No pgBouncer package.
  - No monitoring integration. 
  - There is the way to connect ADPG clusters (cluster consists of the single instance) to HA cluster. But there were problems with update to the feature bundles.

## 4. Scaling best practices

### 4.1. Hardware recomendation

  - 1 CPU per 4-8 GB RAM.
  - 1 CPU per 1 Tb of data (OLTP load).
  - Reducing of hardware have to be done with multiplier x2 if performance of hardware is too high during database load testing.
  - EC2 [i4i](https://aws.amazon.com/ec2/instance-types/i4i/) instancies are generally excellent choice for high OLTP load databases.
![](images/i4i_instances.png).
![](https://i.imgur.com/OGAM13R.png)


### 4.2. Database optimizations

#### 4.2.1. OS settings

  - Swap size is about quarter of RAM size (min 16 Gb).
  - Set **vm.swappiness** to the low values (DataEgret recommends to use 1).
  - Set **kernel.numa_balancing = 0** to disable NUMA balancing.
  - Use hugepages for shared mememy. Enable them with **vm.nr_hugepages** and **vm. nr_overcommit_hugepages** sysctl settings. It has to correlate with **shared_buffers** postgresql.conf parameter.
  - Set ```vm.dirty_bytes = 1073741824``` and ```vm.dirty_background_bytes = 268435456``` in case of huge databases with RAM usage > 256 Gb.

### 4.2.2. Database settings
  - **superuser_reserved_connections** - 10-20
  - It's recommended to set ```hugepages=on`` to prevent starting of database without hugepages support.
  - Set bg_writer_* settings to aggressive high values (```bgwriter_delay = 10ms```, ```bgwriter_lru_maxpages = 5000```, ```bgwriter_lru_multiplier = 10```, ```bgwriter_flush_after = 2MB```).
  - Set ```checkpoint_timeout = 1h```. max_wal_size have to be corresponded with generated WAL files for 1h of database activity.
