From: Justin Pryzby <pryzby@telsasoft.com>
To: Arya F <arya6000@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: 600 million rows of data. Bad hardware or need partitioning?
Date: Tue, 5 May 2020 20:37:41 -0500
Message-ID: <20200506013741.GH28974@telsasoft.com> (raw)
In-Reply-To: <CAFoK1ax7FnPx5vxQnMYysxLaC6BXBJRaEqpN6ZJDQQPCzBvNmA@mail.gmail.com>
References: <CAFoK1ay01_ND0QrCcAAHX7U=rTCssB8vfOiH+iv5b8Qh6WN80g@mail.gmail.com>
<CAHOFxGo_ScRpOiGd5GWARKsaizoB86O_L0nROb6XHYp9Dz-mtw@mail.gmail.com>
<CAFoK1azRs4pWL+5nQUC2r6E3G3jhrgNq08h9ve1xaECYMy==YA@mail.gmail.com>
<20200504092130.GN28974@telsasoft.com>
<CAFoK1ax7FnPx5vxQnMYysxLaC6BXBJRaEqpN6ZJDQQPCzBvNmA@mail.gmail.com>
On Tue, May 05, 2020 at 08:31:29PM -0400, Arya F wrote:
> On Mon, May 4, 2020 at 5:21 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> > I mentioned in February and March that you should plan to set shared_buffers
> > to fit the indexes currently being updated.
>
> The following command gives me
>
> select pg_size_pretty (pg_indexes_size('test_table'));
> pg_size_pretty > 5216 MB
>
> So right now, the indexes on that table are taking about 5.2 GB, if a
> machine has 512 GB of RAM and SSDs, is it safe to assume I can achieve
> the same update that takes 1.5 minutes in less than 5 seconds while
> having 600 million rows of data without partitioning?
I am not prepared to guarantee server performance..
But, to my knowledge, you haven't configured shared_buffers at all. Which I
think might be the single most important thing to configure for loading speed
(with indexes).
Couple months ago, you said your server had 4GB RAM, which isn't much, but if
shared_buffers is ~100MB, I think that deserves attention.
If you get good performance with a million rows and 32MB buffers, then you
could reasonably hope to get good performance (at least initially) with
100million rows and 320MB buffers. Scale that up to whatever you expect your
index size to be. Be conservative since you may need to add indexes later, and
you can expect they'll become bloated, so you may want to run a reindex job.
shared_buffers is frequently set to ~25% of RAM, and if you need to efficiently
use indexes larger than what that supports, then you should add RAM, or
implement partitioning.
--
Justin
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: <20200506013741.GH28974@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