Thanks guys! This is really good and useful information! :) During the day we can see some exceptions coming from Postgres (alway when the load is the highest), only in the MAIN UPDATE:
- How to overcome the error "current transaction is aborted, commands ignored until end of transaction block" - Deadlock detected Thanks On Mon, Jul 29, 2019 at 9:55 AM MichaelDBA <michael...@sqlexec.com> wrote: > Does pg_stat_user_tables validate that the major updates are indeed "hot > updates"? Otherwise, you may be experiencing bloat problems if autovacuum > is not set aggressively. Did you change default parameters for > autovacuum? You should. They are set very conservatively right outa the > box. Also, I wouldn't increase work_mem too much unless you are > experiencing query spill over to disk. Turn on "log_temp_files" (=0) and > monitor if you have this spillover. If not, don't mess with work_mem. > Also, why isn't effective_cache_size set closer to 80-90% of memory instead > of 50%? Are there other servers on the same host as postgres? As the > other person mentioned, tune checkpoints so that they do not happen too > often. Turn on "log_checkpoints" to get more info. > > Regards, > Michael Vitale > > Rick Otten wrote on 7/29/2019 8:35 AM: > > > On Mon, Jul 29, 2019 at 2:16 AM Jean Baro <jfb...@gmail.com> wrote: > >> >> We have a new Inventory system running on its own database (PG 10 AWS >> RDS.m5.2xlarge 1TB SSD EBS - Multizone). The DB effective size is less than >> 10GB at the moment. We provided 1TB to get more IOPS from EBS. >> >> As we don't have a lot of different products in our catalogue it's quite >> common (especially when a particular product is on sale) to have a high >> rate of concurrent updates against the same row. There is also a frequent >> (every 30 minutes) update to all items which changed their current >> stock/Inventory coming from the warehouses (SAP), the latter is a batch >> process. We have just installed this system for a new tenant (one of the >> smallest one) and although it's running great so far, we believe this >> solution would not scale as we roll out this system to new (and bigger) >> tenants. Currently there is up to 1.500 transactions per second (mostly >> SELECTS and 1 particular UPDATE which I believe is the one being >> aborted/deadlocked some tImes) in this inventory database. >> >> I am not a DBA, but as the DBAs (most of them old school Oracle DBAs who >> are not happy with the move to POSTGRES) are considering ditching >> Postgresql without any previous tunning I would like to understand the >> possibilities. >> >> Considering this is a highly concurrent (same row) system I thought to >> suggest: >> >> >> > Another thing which you might want to investigate is your checkpoint > tunables. My hunch is with that many writes, the defaults are probably not > going to be ideal. > Consider the WAL tunables documentation: > https://www.postgresql.org/docs/10/wal-configuration.html > > > >
