Postgres Tuning

https://blog.crunchydata.com/blog/optimize-postgresql-server-performance

Query failed: ERROR: could not resize shared memory segment "/PostgreSQL.980262028" to 2097152 bytes: No space left on device

listen_addresses = '*'
max_connections = 100                   # (change requires restart)
shared_buffers = 4GB                    # min 128kB
temp_buffers = 24MB                     # min 800kB
work_mem = 100MB                                # min 64kB
maintenance_work_mem = 640MB            # min 1MB
dynamic_shared_memory_type = posix      # the default is the first option
max_wal_size = 4GB
min_wal_size = 80MB
effective_cache_size = 8GB
log_timezone = 'Etc/UTC'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'en_US.utf8'                      # locale for system error message
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting
default_text_search_config = 'pg_catalog.english'

/dev/shm default is 64GB

spec:
  volumes:
  - name: dshm
    emptyDir:     
        medium: Memory
  - name: hostpath
    hostPath:
      path: /path/to/host/directory # Change to your directory
  containers:
  - image:  image-name #specify your image name here
    volumeMounts:
      - mountPath: /dev/shm
        name: dshm
      - mountPath: /workspace/container
        name: hostpath

https://medium.com/dive-into-ml-ai/raising-shared-memory-limit-of-a-kubernetes-container-62aae0468a33

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/395973-zabbix-not-write-data-to-db-postgresql-grow-queue

postgres tuned by timescaledb-tune(with small manual tune ) on both system.

grep -v "^#" postgresql.conf|cut -d "#" -f1|grep -v "^[[:space:]]*$"
listen_addresses='*'
max_connections = 512
shared_buffers = 7978MB
work_mem = 10212kB
maintenance_work_mem = 2047MB
dynamic_shared_memory_type = posix
effective_io_concurrency = 200
max_worker_processes = 19
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
synchronous_commit = off
wal_buffers = 32MB
wal_writer_delay = 2000ms
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_cache_size = 23936MB
default_statistics_target = 500
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_error_verbosity = verbose
log_line_prefix = '%m [%p] '
log_timezone =---------
autovacuum = off
autovacuum_max_workers = 10
autovacuum_naptime = 10
datestyle = 'iso, mdy'
timezone = ------------
lc_messages = 'en_US.UTF8'
lc_monetary = 'en_US.UTF8'
lc_numeric = 'en_US.UTF8'
lc_time = 'en_US.UTF8'
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'timescaledb'
max_locks_per_transaction = 256
timescaledb.max_background_workers = 8
timescaledb.last_tuned = '2019-12-11T15:09:26+02:00'
timescaledb.last_tuned_version = '0.7.0'

we turn off synchronous_commit
and increase wal_buffers.