pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Materialized view refreshing problem
4+ messages / 2 participants
[nested] [flat]

* Materialized view refreshing problem
@ 2023-08-24 16:54 Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-24 19:02 ` Re: Materialized view refreshing problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Hellen Jiang @ 2023-08-24 16:54 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi All,

We have one business critical materialized view( let’s called materialized_view_1) which contains around 16M records.
This materialized view is refreshed CONCURRENTLY every 1 minute, and got refreshed normally (without CONCURRENTLY) every 6 hours.

But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view.
In this case, we have to create new materialized view(materialized_view_1_new) with exactly the same definition, and the new materialized view(materialized_view_1_new) works well and the refresh finishes in 15 seconds.
And after about 1 months, we have to do this again: I mean use freshly new materialized view to replace the old one.

It looks like there are too much garbage in the old materialized view as time going.
Any idea how we can fix this materialized view refreshing problem?

Thanks

Hellen Jiang

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Materialized view refreshing problem
  2023-08-24 16:54 Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-24 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-24 19:36   ` Re: Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2023-08-24 19:02 UTC (permalink / raw)
  To: Hellen Jiang <hjiang@federatedwireless.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hellen Jiang <hjiang@federatedwireless.com> writes:
> But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view.

That sounds like a locking problem.  Have you looked into pg_locks
while this is happening, to see what may be waiting on what?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Materialized view refreshing problem
  2023-08-24 16:54 Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-24 19:02 ` Re: Materialized view refreshing problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-24 19:36   ` Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-24 19:57     ` Re: Materialized view refreshing problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Hellen Jiang @ 2023-08-24 19:36 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

When the materialized view was refreshing concurrently, I saw the pg_lock like this: there was no response within 30 seconds when the api calls this query, and we got api time out.

sasanalytics=> select relation::regclass, * from pg_locks where not granted;

                     relation                      | locktype | database |  relation  | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted | fastpath

---------------------------------------------------+----------+----------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------

 mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |    16401 | 1886512426 |      |       |            |               |         |       |          | 236/858            | 17332 | ExclusiveLock | f       | f

(1 row)

When the materialized view was refreshing normally( without concurrently), I saw the following pg_locks: there was no response within 30 seconds when the api calls this query, and we got api time out.

sasanalytics=> select relation::regclass, * from pg_locks where not granted;

                     relation                      | locktype | database |  relation  | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath

---------------------------------------------------+----------+----------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------

 mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |    16401 | 1886512426 |      |       |            |               |         |       |          | 245/1163           | 15932 | AccessExclusiveLock | f       | f

(1 row)


From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thursday, August 24, 2023 at 3:02 PM
To: Hellen Jiang <hjiang@federatedwireless.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Materialized view refreshing problem
Hellen Jiang <hjiang@federatedwireless.com> writes:
> But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view.

That sounds like a locking problem.  Have you looked into pg_locks
while this is happening, to see what may be waiting on what?

                        regards, tom lane

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Materialized view refreshing problem
  2023-08-24 16:54 Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-24 19:02 ` Re: Materialized view refreshing problem Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-24 19:36   ` Re: Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-24 19:57     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-08-24 19:57 UTC (permalink / raw)
  To: Hellen Jiang <hjiang@federatedwireless.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hellen Jiang <hjiang@federatedwireless.com> writes:
> When the materialized view was refreshing concurrently, I saw the pg_lock like this: there was no response within 30 seconds when the api calls this query, and we got api time out.

Well, that seems to confirm my idea that the REFRESH is blocked behind
somebody else's lock.  But you need to look to see which session is
holding a lock on mvw_cbsd_status_grants_lessinfo_active_softmarker
and what it's doing.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-08-24 19:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-24 16:54 Materialized view refreshing problem Hellen Jiang <hjiang@federatedwireless.com>
2023-08-24 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-24 19:36   ` Hellen Jiang <hjiang@federatedwireless.com>
2023-08-24 19:57     ` Tom Lane <tgl@sss.pgh.pa.us>

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