pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMat view sometimes taking 10x the time to refresh concurrently
5+ messages / 3 participants
[nested] [flat]
* Mat view sometimes taking 10x the time to refresh concurrently
@ 2021-05-27 16:15 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Wells Oliver @ 2021-05-27 16:15 UTC (permalink / raw)
To: pgsql-admin
Hey, we have a mat view that normally takes ~18m to re-materialize
concurrently, which we do daily. Maybe once a week, it takes ~180m to
refresh, and we're at a loss as to why.
We are running backs during the same general time window, where we do
backup the schema where this mat view is located, could this be an issue?
We have not noticed any lock errors in the backup or log, though.
Any tips on tracing this down would be appreciated.
pg 13.2
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mat view sometimes taking 10x the time to refresh concurrently
@ 2021-05-27 16:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-05-27 16:36 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> Hey, we have a mat view that normally takes ~18m to re-materialize
> concurrently, which we do daily. Maybe once a week, it takes ~180m to
> refresh, and we're at a loss as to why.
> We are running backs during the same general time window, where we do
> backup the schema where this mat view is located, could this be an issue?
> We have not noticed any lock errors in the backup or log, though.
> Any tips on tracing this down would be appreciated.
My own mindset would be to wonder if a different/worse plan is being
chosen. You could investigate that perhaps by running an EXPLAIN
on the matview's query just before each refresh, to see if it changes.
Checking pg_locks for ungranted locks while the REFRESH is running
would be good to do too, although I'd think that a backup vs. a
concurrent refresh shouldn't have that sort of problem.
It seems possible also that you're just maxing out the machine's
I/O capacity between these two tasks.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mat view sometimes taking 10x the time to refresh concurrently
@ 2021-05-27 16:38 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Vijaykumar Jain @ 2021-05-27 16:38 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
possible scenarios:
The tables in the view have much more data now than it was earlier. if you
track metrics for table size/db size, do you see growth there,
the tables and/or indexes may be bloated. you can vacuum / pg_repack on
tables used in the view and indexes if the bloat is too much.
can you run the query directly and see the explain plan. it might give some
hints if estimates are off.
Does the db server have enough resources ? you can try
bumping maintenance_work_mem and work_mem in a session and see if this
helps speed things up.
On Thu, 27 May 2021 at 21:45, Wells Oliver <wells.oliver@gmail.com> wrote:
> Hey, we have a mat view that normally takes ~18m to re-materialize
> concurrently, which we do daily. Maybe once a week, it takes ~180m to
> refresh, and we're at a loss as to why.
>
> We are running backs during the same general time window, where we do
> backup the schema where this mat view is located, could this be an issue?
> We have not noticed any lock errors in the backup or log, though.
>
> Any tips on tracing this down would be appreciated.
>
> pg 13.2
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mat view sometimes taking 10x the time to refresh concurrently
@ 2021-05-27 16:38 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Wells Oliver @ 2021-05-27 16:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks, Tom. I will dig into those ideas. I do think the I/O capacity might
ultimately be a big factor.
On Thu, May 27, 2021 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > Hey, we have a mat view that normally takes ~18m to re-materialize
> > concurrently, which we do daily. Maybe once a week, it takes ~180m to
> > refresh, and we're at a loss as to why.
> > We are running backs during the same general time window, where we do
> > backup the schema where this mat view is located, could this be an issue?
> > We have not noticed any lock errors in the backup or log, though.
> > Any tips on tracing this down would be appreciated.
>
> My own mindset would be to wonder if a different/worse plan is being
> chosen. You could investigate that perhaps by running an EXPLAIN
> on the matview's query just before each refresh, to see if it changes.
>
> Checking pg_locks for ungranted locks while the REFRESH is running
> would be good to do too, although I'd think that a backup vs. a
> concurrent refresh shouldn't have that sort of problem.
>
> It seems possible also that you're just maxing out the machine's
> I/O capacity between these two tasks.
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mat view sometimes taking 10x the time to refresh concurrently
@ 2021-05-27 17:05 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Vijaykumar Jain @ 2021-05-27 17:05 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
" although I'd think that a backup vs. a
concurrent refresh shouldn't have that sort of problem"
yep. I guess pg_dump requests for access share lock iirc.
*****************************************
session 1:
demo=# create table t(id int, name text);
CREATE TABLE
demo=# insert into t select x, md5(x::text) from generate_series(1, 1000) x;
INSERT 0 1000
demo=# CREATE MATERIALIZED VIEW tv as select id,name from t,pg_sleep(5)
where id > 10;
SELECT 990
demo=# create unique INDEX on tv(id);
CREATE INDEX
demo=# refresh materialized view concurrently tv;
REFRESH MATERIALIZED VIEW
demo=# refresh materialized view concurrently tv;
REFRESH MATERIALIZED VIEW
demo=# refresh materialized view concurrently tv;
REFRESH MATERIALIZED VIEW
session2:
postgres@go:/tmp$ pg_dump demo > abc.sql # while refresh running.
completes fine.
postgres@go:/tmp$ psql
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# begin;
BEGIN
demo=*# lock table t IN ACCESS SHARE MODE; -- simulate pg_dump
LOCK TABLE
demo=*# select * from tv limit 1; -- also just query the existing view
id | name
----+----------------------------------
11 | 6512bd43d9caa6e02c990b0a82652dca
(1 row)
***********************
so, pg_dump may not be the problem. if i simulated correctly. ( ofcourse
without any exclusive locks on base table t)
On Thu, 27 May 2021 at 22:08, Wells Oliver <wells.oliver@gmail.com> wrote:
> Thanks, Tom. I will dig into those ideas. I do think the I/O capacity
> might ultimately be a big factor.
>
> On Thu, May 27, 2021 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Wells Oliver <wells.oliver@gmail.com> writes:
>> > Hey, we have a mat view that normally takes ~18m to re-materialize
>> > concurrently, which we do daily. Maybe once a week, it takes ~180m to
>> > refresh, and we're at a loss as to why.
>> > We are running backs during the same general time window, where we do
>> > backup the schema where this mat view is located, could this be an
>> issue?
>> > We have not noticed any lock errors in the backup or log, though.
>> > Any tips on tracing this down would be appreciated.
>>
>> My own mindset would be to wonder if a different/worse plan is being
>> chosen. You could investigate that perhaps by running an EXPLAIN
>> on the matview's query just before each refresh, to see if it changes.
>>
>> Checking pg_locks for ungranted locks while the REFRESH is running
>> would be good to do too, although I'd think that a backup vs. a
>> concurrent refresh shouldn't have that sort of problem.
>>
>> It seems possible also that you're just maxing out the machine's
>> I/O capacity between these two tasks.
>>
>> regards, tom lane
>>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-05-27 17:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-27 16:15 Mat view sometimes taking 10x the time to refresh concurrently Wells Oliver <wells.oliver@gmail.com>
2021-05-27 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-27 16:38 ` Wells Oliver <wells.oliver@gmail.com>
2021-05-27 17:05 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-05-27 16:38 ` Vijaykumar Jain <vijaykumarjain.github@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