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
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? |