pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh concurrency same row (inventory)
11+ messages / 5 participants
[nested] [flat]
* High concurrency same row (inventory)
@ 2019-07-29 06:17 Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:53 ` Re: High concurrency same row (inventory) Jayadevan M <maymala.jayadevan@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Jean Baro @ 2019-07-29 06:17 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello there.
I am not an PG expert, as currently I work as a Enterprise Architect (who
believes in OSS and in particular PostgreSQL 😍). So please forgive me if
this question is too simple. 🙏
Here it goes:
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:
1) Set up Shared_buffer to 25% of the RAM on the RDS instance;
2) Install a pair (HA) of PGBouncers (session) in front of PG and setup it
in a way that it would keep only 32 connections (4 per core) open to the
database at the same time, but all connections going to PGBouncer (might be
thousands) would be queued as soon as there is more than 32 active
connections to the Database. We have reached more than 500 concurrent
connections so far. But these numbers will grow.
3) set work_mem to 3 times the size of largest temp file;
4) set maintenance_work_mem to 2GB;
5) set effective_cache_size to 50% of total memory.
The most used update is already a HOT UPDATE, as it (or any trigger)
doesn't change indexed columns.
It seems to me the kind of problem we have is similar to those systems
which sell limited number of tickets to large concerts/events, like
googleIO used to be... Where everyone tried to buy the ticket as soon as
possible, and the system had to keep a consistent number of available
tickets. I believe that's a hard problem to solve. So that's way I am
asking for suggestions/ideas from the experts.
Thanks so much!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-29 12:35 ` Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 1 reply; 11+ messages in thread
From: Rick Otten @ 2019-07-29 12:35 UTC (permalink / raw)
To: Jean Baro <jfbaro@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jul 29, 2019 at 2:16 AM Jean Baro <jfbaro@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
@ 2019-07-29 12:55 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: MichaelDBA @ 2019-07-29 12:55 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: Jean Baro <jfbaro@gmail.com>; pgsql-performance@lists.postgresql.org
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 <jfbaro@gmail.com
> <mailto:jfbaro@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
@ 2019-07-29 18:09 ` Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jean Baro @ 2019-07-29 18:09 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
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 <MichaelDBA@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 <jfbaro@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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-29 18:12 ` Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Lewis @ 2019-07-29 18:12 UTC (permalink / raw)
To: Jean Baro <jfbaro@gmail.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
Can you share the schema of the table(s) involved and an example or two of
the updates being executed?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
@ 2019-07-30 00:04 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 00:06 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jean Baro @ 2019-07-30 00:04 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
All the failures come from the Bucket Table (see image below).
I don't have access to the DB, neither the code, but last time I was
presented to the UPDATE it was changing (incrementing or decrementing)
*qty_available*, but tomorrow morning I can be sure, once the developers
and DBAs are back to the office. I know it's quite a simple UPDATE.
Table is called Bucket:
{autovacuum_vacuum_scale_factor=0.01}
[image: Bucket.png]
On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis <mlewis@entrata.com> wrote:
> Can you share the schema of the table(s) involved and an example or two of
> the updates being executed?
>
Attachments:
[image/png] Bucket.png (14.6K, ../../CA+fQee=sn-LTt17hVnN3twyAB+MXboPW29nRT0zgchcvsvQLjg@mail.gmail.com/3-Bucket.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-30 00:06 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 00:26 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jean Baro @ 2019-07-30 00:06 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
The UPDATE was something like:
UPDATE bucket SET qty_available = qty_available + 1 WHERE bucket_uid =
0940850938059380590
Thanks for all your help guys!
On Mon, Jul 29, 2019 at 9:04 PM Jean Baro <jfbaro@gmail.com> wrote:
> All the failures come from the Bucket Table (see image below).
>
> I don't have access to the DB, neither the code, but last time I was
> presented to the UPDATE it was changing (incrementing or decrementing)
> *qty_available*, but tomorrow morning I can be sure, once the developers
> and DBAs are back to the office. I know it's quite a simple UPDATE.
>
> Table is called Bucket:
> {autovacuum_vacuum_scale_factor=0.01}
>
> [image: Bucket.png]
>
>
> On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis <mlewis@entrata.com> wrote:
>
>> Can you share the schema of the table(s) involved and an example or two
>> of the updates being executed?
>>
>
Attachments:
[image/png] Bucket.png (14.6K, ../../CA+fQeemmZgztnq89G+y5qUuLz810BEfgE=opJemdAhm-voNe_w@mail.gmail.com/3-Bucket.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-30 00:06 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-30 00:26 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 01:13 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-30 01:23 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Jean Baro @ 2019-07-30 00:26 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
[image: image.png]
The dead tuples goes up at a high ratio, but then it gets cleaned.
if you guys need any further information, please let me know!
On Mon, Jul 29, 2019 at 9:06 PM Jean Baro <jfbaro@gmail.com> wrote:
> The UPDATE was something like:
>
> UPDATE bucket SET qty_available = qty_available + 1 WHERE bucket_uid =
> 0940850938059380590
>
> Thanks for all your help guys!
>
> On Mon, Jul 29, 2019 at 9:04 PM Jean Baro <jfbaro@gmail.com> wrote:
>
>> All the failures come from the Bucket Table (see image below).
>>
>> I don't have access to the DB, neither the code, but last time I was
>> presented to the UPDATE it was changing (incrementing or decrementing)
>> *qty_available*, but tomorrow morning I can be sure, once the developers
>> and DBAs are back to the office. I know it's quite a simple UPDATE.
>>
>> Table is called Bucket:
>> {autovacuum_vacuum_scale_factor=0.01}
>>
>> [image: Bucket.png]
>>
>>
>> On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis <mlewis@entrata.com> wrote:
>>
>>> Can you share the schema of the table(s) involved and an example or two
>>> of the updates being executed?
>>>
>>
Attachments:
[image/png] Bucket.png (14.6K, ../../CA+fQee=E3cGB-2fcGZAmb5dLOgJA6C58Mf2=MBvkq=PC4SGFOw@mail.gmail.com/3-Bucket.png)
download | view image
[image/png] image.png (4.2K, ../../CA+fQee=E3cGB-2fcGZAmb5dLOgJA6C58Mf2=MBvkq=PC4SGFOw@mail.gmail.com/4-image.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-30 00:06 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-30 00:26 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-30 01:13 ` MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 11+ messages in thread
From: MichaelDBA @ 2019-07-30 01:13 UTC (permalink / raw)
To: Jean Baro <jfbaro@gmail.com>; +Cc: Michael Lewis <mlewis@entrata.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
Looks like regular updates not HOT UPDATES
Jean Baro wrote on 7/29/2019 8:26 PM:
> image.png
>
> The dead tuples goes up at a high ratio, but then it gets cleaned.
>
> if you guys need any further information, please let me know!
>
>
>
> On Mon, Jul 29, 2019 at 9:06 PM Jean Baro <jfbaro@gmail.com
> <mailto:jfbaro@gmail.com>> wrote:
>
> The UPDATE was something like:
>
> UPDATE bucket SET qty_available = qty_available + 1 WHERE
> bucket_uid = 0940850938059380590
>
> Thanks for all your help guys!
>
> On Mon, Jul 29, 2019 at 9:04 PM Jean Baro <jfbaro@gmail.com
> <mailto:jfbaro@gmail.com>> wrote:
>
> All the failures come from the Bucket Table (see image below).
>
> I don't have access to the DB, neither the code, but last time
> I was presented to the UPDATE it was changing (incrementing or
> decrementing) *qty_available*, but tomorrow morning I can be
> sure, once the developers and DBAs are back to the office. I
> know it's quite a simple UPDATE.
>
> Table is called Bucket:
> {autovacuum_vacuum_scale_factor=0.01}
>
> Bucket.png
>
>
> On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis
> <mlewis@entrata.com <mailto:mlewis@entrata.com>> wrote:
>
> Can you share the schema of the table(s) involved and an
> example or two of the updates being executed?
>
Attachments:
[image/png] image.png (2.5K, ../../e70237c9-5c90-a96d-5f4d-5cc185e45d1d@sqlexec.com/3-image.png)
download | view image
[image/png] image.png (15.6K, ../../e70237c9-5c90-a96d-5f4d-5cc185e45d1d@sqlexec.com/4-image.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Re: High concurrency same row (inventory) Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` Re: High concurrency same row (inventory) MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Re: High concurrency same row (inventory) Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-30 00:06 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-30 00:26 ` Re: High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-30 01:23 ` Jean Baro <jfbaro@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jean Baro @ 2019-07-30 01:23 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Rick Otten <rottenwindfish@gmail.com>; pgsql-performance@lists.postgresql.org
Michael Vitale --> No, there is only postgreSQL running in this server...
it is in fact an RDS server.
SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as
"deletes", n_tup_hot_upd as "hot updates", n_live_tup as "live_tuples",
n_dead_tup as "dead_tuples"
FROM pg_stat_user_tables
WHERE schemaname = 'schemaFOO' and relname = 'bucket';
[image: image.png]
On Mon, Jul 29, 2019 at 9:26 PM Jean Baro <jfbaro@gmail.com> wrote:
> [image: image.png]
>
> The dead tuples goes up at a high ratio, but then it gets cleaned.
>
> if you guys need any further information, please let me know!
>
>
>
> On Mon, Jul 29, 2019 at 9:06 PM Jean Baro <jfbaro@gmail.com> wrote:
>
>> The UPDATE was something like:
>>
>> UPDATE bucket SET qty_available = qty_available + 1 WHERE bucket_uid =
>> 0940850938059380590
>>
>> Thanks for all your help guys!
>>
>> On Mon, Jul 29, 2019 at 9:04 PM Jean Baro <jfbaro@gmail.com> wrote:
>>
>>> All the failures come from the Bucket Table (see image below).
>>>
>>> I don't have access to the DB, neither the code, but last time I was
>>> presented to the UPDATE it was changing (incrementing or decrementing)
>>> *qty_available*, but tomorrow morning I can be sure, once the
>>> developers and DBAs are back to the office. I know it's quite a simple
>>> UPDATE.
>>>
>>> Table is called Bucket:
>>> {autovacuum_vacuum_scale_factor=0.01}
>>>
>>> [image: Bucket.png]
>>>
>>>
>>> On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis <mlewis@entrata.com>
>>> wrote:
>>>
>>>> Can you share the schema of the table(s) involved and an example or two
>>>> of the updates being executed?
>>>>
>>>
Attachments:
[image/png] Bucket.png (14.6K, ../../CA+fQeennK4mHkN0Kp5_xFDAU8dK=sY6-=J6ftpJmN9OympOEUg@mail.gmail.com/3-Bucket.png)
download | view image
[image/png] image.png (4.2K, ../../CA+fQeennK4mHkN0Kp5_xFDAU8dK=sY6-=J6ftpJmN9OympOEUg@mail.gmail.com/4-image.png)
download | view image
[image/png] image.png (4.4K, ../../CA+fQeennK4mHkN0Kp5_xFDAU8dK=sY6-=J6ftpJmN9OympOEUg@mail.gmail.com/5-image.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High concurrency same row (inventory)
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
@ 2019-07-29 12:53 ` Jayadevan M <maymala.jayadevan@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jayadevan M @ 2019-07-29 12:53 UTC (permalink / raw)
To: Jean Baro <jfbaro@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jul 29, 2019 at 11:46 AM Jean Baro <jfbaro@gmail.com> wrote:
> Hello there.
>
> I am not an PG expert, as currently I work as a Enterprise Architect (who
> believes in OSS and in particular PostgreSQL 😍). So please forgive me if
> this question is too simple. 🙏
>
> Here it goes:
>
> 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.
>
Monitoring the locks and activities, as described here, may help -
https://wiki.postgresql.org/wiki/Lock_Monitoring
Regards,
Jayadevan
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2019-07-30 01:23 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-29 06:17 High concurrency same row (inventory) Jean Baro <jfbaro@gmail.com>
2019-07-29 12:35 ` Rick Otten <rottenwindfish@gmail.com>
2019-07-29 12:55 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-29 18:09 ` Jean Baro <jfbaro@gmail.com>
2019-07-29 18:12 ` Michael Lewis <mlewis@entrata.com>
2019-07-30 00:04 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 00:06 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 00:26 ` Jean Baro <jfbaro@gmail.com>
2019-07-30 01:13 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-07-30 01:23 ` Jean Baro <jfbaro@gmail.com>
2019-07-29 12:53 ` Jayadevan M <maymala.jayadevan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox