Server hangs on pg_repack

From: Roman Liverovskiy <r(dot)liverovskiy(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Server hangs on pg_repack
Date: 2021-02-25 13:24:08
Message-ID: CAN9jV38SFabnLXLVLYLMS1N=703bw4TkWQBTxz3Shhfdw=bMAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.
I have postgresql 12 with a 3.0 GB database with a table containing 10
millions of rows, this table also has 4 indexes.
I have an AWS EC2 server with two AMD EPYC 7571 cores, 2 GB of RAM and SSD
disk.
Because of index bloating I use pg_repack.
When I use default postgresql.conf file I have no issues, but when I tune
postgresql.conf and call pg_repack --only-indexes for my table, my server
hangs and I can not connect to the server until reboot from AWS console.

What is wrong in my postgresql.conf?

My tuned parameters are:

max_stack_depth = 5MB

random_page_cost = 1.1

# Connectivity
max_connections = 100
superuser_reserved_connections = 3

# Memory Settings
shared_buffers = 350MB
effective_cache_size = 500MB
maintenance_work_mem = 384MB
work_mem = 2MB

# Monitoring
shared_preload_libraries = 'pg_stat_statements,pg_repack'

# Checkpointing:
checkpoint_timeout = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# WAL writing
wal_buffers = -1
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB

# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0

--
Faithfully yours, Roman I. Liverovskiy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2021-02-25 13:35:54 Re: Deleting takes days, should I add some index?
Previous Message Pavel Stehule 2021-02-25 13:13:29 Re: Deleting takes days, should I add some index?