From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Jean Baro <jfbaro@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: High concurrency same row (inventory)
Date: Mon, 29 Jul 2019 21:13:00 -0400
Message-ID: <e70237c9-5c90-a96d-5f4d-5cc185e45d1d@sqlexec.com> (raw)
In-Reply-To: <CA+fQee=E3cGB-2fcGZAmb5dLOgJA6C58Mf2=MBvkq=PC4SGFOw@mail.gmail.com>
References: <CA+fQeemRGiLsDADEQRAUhmWo8_bAraiLzqQ2-JDVWHNjWVpqeQ@mail.gmail.com>
<CAMAYy4+PNfBjDbKKsXFP8_A-B=knE9Qeqm-11DjPMnMteKdgKg@mail.gmail.com>
<d7f13411-f93e-50f4-f960-146b7a37aed0@sqlexec.com>
<CA+fQeem1JjONE4Czj27QGwC09FWwsdpoZsp4JY2POd3iA5AFWA@mail.gmail.com>
<CAHOFxGrOeTzASm9niuHnudnKowOYtZm_P7YeroN+NMSjJSrfWQ@mail.gmail.com>
<CA+fQee=sn-LTt17hVnN3twyAB+MXboPW29nRT0zgchcvsvQLjg@mail.gmail.com>
<CA+fQeemmZgztnq89G+y5qUuLz810BEfgE=opJemdAhm-voNe_w@mail.gmail.com>
<CA+fQee=E3cGB-2fcGZAmb5dLOgJA6C58Mf2=MBvkq=PC4SGFOw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: MichaelDBA@sqlexec.com, jfbaro@gmail.com, mlewis@entrata.com, rottenwindfish@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: High concurrency same row (inventory)
In-Reply-To: <e70237c9-5c90-a96d-5f4d-5cc185e45d1d@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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