Thread: 600 million rows of data. Bad hardware or need partitioning?
I have created the following table to duplicate my performance
numbers, but I have simplified the table for this question.
I'm running PostgreSQL 12 on the following hardware.
Dual Xeon Quad-Core E5320 1.86GHz
4GB of RAM
The table structure is
id uuid
address_api_url text
check_timestamp timestamp with time zone
address text
Indexes:
"new_table_pkey" PRIMARY KEY, btree (id)
"test_table_check_timestamp_idx" btree (check_timestamp)
Right now the table has 100 Million rows, but I expect it to reach
about 600-700 Million. I am faced with slow updates/inserts and the
issue is caused by the indices as it gets updates on each
insert/update, If I remove the indexes the insert performance remains
excellent with millions of rows.
To demonstrate the update performance I have constructed the following
query which updates the timestamp of 10000 rows
UPDATE test_table set check_timestamp = now() FROM(select id from
test_table limit 10000) AS subquery where test_table.id = subquery.id;
That update took about 1 minute and 44 seconds
Time: 104254.392 ms (01:44.254)
Below is the EXPLAIN ANALYZE
EXPLAIN ANALYZE UPDATE test_table set check_timestamp = now()
FROM(select id from test_table limit 10000) AS subquery where
test_table.id = subquery.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on test_table (cost=0.57..28234.86 rows=10000 width=160)
(actual time=102081.905..102081.905 rows=0 loops=1)
-> Nested Loop (cost=0.57..28234.86 rows=10000 width=160) (actual
time=32.286..101678.652 rows=10000 loops=1)
-> Subquery Scan on subquery (cost=0.00..514.96 rows=10000
width=56) (actual time=0.048..45.127 rows=10000 loops=1)
-> Limit (cost=0.00..414.96 rows=10000 width=16)
(actual time=0.042..26.319 rows=10000 loops=1)
-> Seq Scan on test_table test_table_1
(cost=0.00..4199520.04 rows=101204004 width=16) (actual
time=0.040..21.542 rows=10000 loops=1)
-> Index Scan using new_table_pkey on test_table
(cost=0.57..2.77 rows=1 width=92) (actual time=10.160..10.160 rows=1
loops=10000)
Index Cond: (id = subquery.id)
Planning Time: 0.319 ms
Execution Time: 102081.967 ms
(9 rows)
Time: 102122.421 ms (01:42.122)
with the right hardware can one partition handle 600 millions of rows
with good insert/update performance? if so what kind of hardware
should I be looking at? Or would I need to create partitions? I'd like
to hear some recommendations.
What kinds of storage (ssd or old 5400 rpm)? What else is this machine running?
What configs have been customized such as work_mem or random_page_cost?
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
On Mon, 4 May 2020 at 15:52, Arya F <arya6000@gmail.com> 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 How long does it take if you first do: SET enable_nestloop TO off; If you find it's faster then you most likely have random_page_cost set unrealistically low. In fact, I'd say it's very unlikely that a nested loop join will be a win in this case when random pages must be read from a mechanical disk, but by all means, try disabling it with the above command and see for yourself. If you set random_page_cost so low to solve some other performance problem, then you may wish to look at the effective_cache_size setting. Having that set to something realistic should allow indexes to be used more in situations where they're likely to not require as much random I/O from the disk. David
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
On Mon, May 4, 2020 at 12:44 AM David Rowley <dgrowleyml@gmail.com> wrote:
> How long does it take if you first do:
>
> SET enable_nestloop TO off;
I tried this, but it takes much longer
Time: 318620.319 ms (05:18.620)
Below is the EXPLAIN ANALYZE
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on test_table (cost=639.96..4581378.80 rows=10000 width=160)
(actual time=290593.159..290593.159 rows=0 loops=1)
-> Hash Join (cost=639.96..4581378.80 rows=10000 width=160)
(actual time=422.313..194430.318 rows=10000 loops=1)
Hash Cond: (test_table.id = subquery.id)
-> Seq Scan on test_table (cost=0.00..4200967.98
rows=101238898 width=92) (actual time=296.970..177731.611
rows=101189271 loops=1)
-> Hash (cost=514.96..514.96 rows=10000 width=56) (actual
time=125.312..125.312 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 959kB
-> Subquery Scan on subquery (cost=0.00..514.96
rows=10000 width=56) (actual time=0.030..123.031 rows=10000 loops=1)
-> Limit (cost=0.00..414.96 rows=10000
width=16) (actual time=0.024..121.014 rows=10000 loops=1)
-> Seq Scan on test_table test_table_1
(cost=0.00..4200967.98 rows=101238898 width=16) (actual
time=0.021..120.106 rows=10000 loops=1)
Planning Time: 0.304 ms
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 2.178 ms, Inlining 155.980 ms, Optimization
100.611 ms, Emission 39.481 ms, Total 298.250 ms
Execution Time: 290595.448 ms
(15 rows)
> If you find it's faster then you most likely have random_page_cost set
> unrealistically low. In fact, I'd say it's very unlikely that a nested
> loop join will be a win in this case when random pages must be read
> from a mechanical disk, but by all means, try disabling it with the
> above command and see for yourself.
It's much slower with SET enable_nestloop TO off. Any other suggestions?
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
(1 row)
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?
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
On Tue, May 5, 2020 at 9:37 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> 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).
>
Just wanted to give an update. I tried this on a VPS with 8GB ram and
SSDs, the same query now takes 1.2 seconds! What a huge difference!
that's without making any changes to postgres.conf file. Very
impressive.