From: Justin Pryzby <pryzby@telsasoft.com>
To: Arya F <arya6000@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: 600 million rows of data. Bad hardware or need partitioning?
Date: Mon, 4 May 2020 04:21:30 -0500
Message-ID: <20200504092130.GN28974@telsasoft.com> (raw)
In-Reply-To: <CAFoK1azRs4pWL+5nQUC2r6E3G3jhrgNq08h9ve1xaECYMy==YA@mail.gmail.com>
References: <CAFoK1ay01_ND0QrCcAAHX7U=rTCssB8vfOiH+iv5b8Qh6WN80g@mail.gmail.com>
<CAHOFxGo_ScRpOiGd5GWARKsaizoB86O_L0nROb6XHYp9Dz-mtw@mail.gmail.com>
<CAFoK1azRs4pWL+5nQUC2r6E3G3jhrgNq08h9ve1xaECYMy==YA@mail.gmail.com>
On Sun, May 03, 2020 at 11:51:44PM -0400, Arya F wrote:
> On Sun, May 3, 2020 at 11:46 PM Michael Lewis <mlewis@entrata.com> wrote:
> > What kinds of storage (ssd or old 5400 rpm)? What else is this machine running?
>
> Not an SSD, but an old 1TB 7200 RPM HDD
>
> > What configs have been customized such as work_mem or random_page_cost?
>
> work_mem = 2403kB
> random_page_cost = 1.1
I mentioned in February and March that you should plan to set shared_buffers
to fit the indexes currently being updated.
Partitioning can help with that *if* the writes mostly affect 1-2 partitions at
a time (otherwise not).
On Wed, Feb 05, 2020 at 11:15:48AM -0600, Justin Pryzby wrote:
> > Would that work? Or any recommendations how I can achieve good performance
> > for a lot of writes?
>
> Can you use partitioning so the updates are mostly affecting only one table at
> once, and its indices are of reasonable size, such that they can fit easily in
> shared_buffers.
On Sun, Mar 22, 2020 at 08:29:04PM -0500, Justin Pryzby wrote:
> On Sun, Mar 22, 2020 at 09:22:50PM -0400, Arya F wrote:
> > I have noticed that my write/update performance starts to dramatically
> > reduce after about 10 million rows on my hardware. The reason for the
> > slowdown is the index updates on every write/update.
>
> It's commonly true that the indexes need to fit entirely in shared_buffers for
> good write performance. I gave some suggestions here:
> https://www.postgresql.org/message-id/20200223101209.GU31889%40telsasoft.com
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: pryzby@telsasoft.com, arya6000@gmail.com, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
Subject: Re: 600 million rows of data. Bad hardware or need partitioning?
In-Reply-To: <20200504092130.GN28974@telsasoft.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