Обсуждение: Refresh Materialized View Issue
Hello all,
We are having an issue with a materialized view refresh never finishing, any help is appreciated. It will run at 100% CPU and no IO traffic indefinitely after about 15 minutes of parallel workers and the parent worker consuming CPU and IO.
PostgreSQL Version: 15.5
Due to some design decisions, we have a large table that we need a distinct list of associated id's out of for an application interface. This table has approximately 1,650,000,000 rows with a table size of 175GB and 250GB of indexes.
I anonymized the column names in the table structure, but this is the table structure of our large table.
create table public.large_table(
create table public.large_table(
large_table_id bigint NOT NULL DEFAULT nextval(public.large_table_id_seq'::regclass),
nmrc numeric(30,5),
id1 bigint NOT NULL,
id2 bigint NOT NULL,
id3 bigint NOT NULL,
id4 bigint NOT NULL,
id5 bigint NOT NULL,
last_updt_ts timestamp without time zone,
last_db_updt_ts timestamp without time zone,
charval character varying(30)
)
The materialized view is defined as:
create materialized view public.vw_distinct_list_of_ids as (
create materialized view public.vw_distinct_list_of_ids as (
select distinct id1, id2, id3 from public.large_table
) with no data;
When refreshed the materialized view will contain approximately 59,000,000 rows.
It takes approximately 12 minutes to run the select statement from the view definition. When you refresh the materialized view for the first time, it will spawn several parallel workers that run at 100% CPU and produce some I/O for about 12 or 13 minutes, then they finish their work and terminate. The parent worker will continue to run at 100% CPU until I terminate the process. I've let it run for over 24 hours.
What I find interesting is that if I do this:
create table public.table_of_distinct_list_of_ids as (
select distinct id1, id2, id3 from public.large_table
);
It will complete in 12 or 13 minutes, the same as the select statement. I have three questions.
- Why is the materialized view refresh stalling and never completing?
- Is there a setting I am missing that affects materialized view refreshes?
- Should I just go with a table that is dropped and recreated instead? I was hoping to avoid this.
--
Sincerely,
Jeremiah Bauer
Agri Stats, Inc.
On Thu, Jan 11, 2024 at 3:31 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
Hello all,We are having an issue with a materialized view refresh never finishing, any help is appreciated. It will run at 100% CPU and no IO traffic indefinitely after about 15 minutes of parallel workers and the parent worker consuming CPU and IO.PostgreSQL Version: 15.5Due to some design decisions, we have a large table that we need a distinct list of associated id's out of for an application interface. This table has approximately 1,650,000,000 rows with a table size of 175GB and 250GB of indexes.I anonymized the column names in the table structure, but this is the table structure of our large table.
create table public.large_table(large_table_id bigint NOT NULL DEFAULT nextval(public.large_table_id_seq'::regclass),nmrc numeric(30,5),id1 bigint NOT NULL,id2 bigint NOT NULL,id3 bigint NOT NULL,id4 bigint NOT NULL,id5 bigint NOT NULL,last_updt_ts timestamp without time zone,last_db_updt_ts timestamp without time zone,charval character varying(30))The materialized view is defined as:
create materialized view public.vw_distinct_list_of_ids as (select distinct id1, id2, id3 from public.large_table) with no data;When refreshed the materialized view will contain approximately 59,000,000 rows.It takes approximately 12 minutes to run the select statement from the view definition. When you refresh the materialized view for the first time, it will spawn several parallel workers that run at 100% CPU and produce some I/O for about 12 or 13 minutes, then they finish their work and terminate. The parent worker will continue to run at 100% CPU until I terminate the process. I've let it run for over 24 hours.What I find interesting is that if I do this:create table public.table_of_distinct_list_of_ids as (select distinct id1, id2, id3 from public.large_table);It will complete in 12 or 13 minutes, the same as the select statement. I have three questions.
My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3.
- Why is the materialized view refresh stalling and never completing?
- Is there a setting I am missing that affects materialized view refreshes?
- Should I just go with a table that is dropped and recreated instead? I was hoping to avoid this.
A materialized view isn't too different from an unlogged table.
My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3.
There is not, after further investigation. There are these 4 indexes that involve id1, id2, and id3. Should I try creating an index on all three of the columns?
CREATE INDEX IF NOT EXISTS idx_large_table_id1
ON public.large_table USING btree
(id1 ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_large_table_id2
ON public.large_table USING btree
(id2 ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_large_table_id3
ON public.large_table USING btree
(id3 ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
ON public.large_table USING btree
(id2 ASC NULLS LAST, id3 ASC NULLS LAST)
TABLESPACE pg_default;
A materialized view isn't too different from an unlogged table.
So an unlogged table would also be an appropriate solution?
--
Jeremiah
On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
My question is: what indexes are on public.large_table? Hopefully there's a compound b-tree index on id1, id2, id3.There is not, after further investigation. There are these 4 indexes that involve id1, id2, and id3. Should I try creating an index on all three of the columns?CREATE INDEX IF NOT EXISTS idx_large_table_id1
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id2
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id3
[snip]
CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
[snip]
I'd strongly think about creating such an index, since the current indices don't help much.
Adding id1 to the end of idx_large_table_id2_id3 might be a better option, since it'll reuse much of the existing disk space.
A materialized view isn't too different from an unlogged table.So an unlogged table would also be an appropriate solution?
Sure, since materialized views are effectively tables with a SELECT statement bound to it, and are logged. Thus, unlogged tables are faster to create. Of course, being unlogged is a two-edged sword: any unclean shutdown makes the data go away; you'd have to rebuild the table.
On 1/11/24 3:40 PM, Ron Johnson wrote: > On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer <jbauer@agristats.com > <mailto:jbauer@agristats.com>> wrote: > > My question is: what indexes are on public.large_table? > Hopefully there's a compound b-tree index on id1, id2, id3. > > There is not, after further investigation. There are these 4 > indexes that involve id1, id2, and id3. Should I try creating an > index on all three of the columns? > > CREATE INDEX IF NOT EXISTS idx_large_table_id1 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id2 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id3 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3 > > [snip] > I'd strongly think about creating such an index, since the current > indices don't help much. That'd be a band-aid at best, because we know that the query used to define the materialized view runs in a reasonable amount of time on it's own, as does a CTAS. So either the REFRESH is doing something odd when writing into the new relation (which looking at the code seems very unlikely), or REFRESH is getting a different query plan for some reason. Unfortunately, I don't know of any easy way to get the query plan for the REFRESH (it might be possible via gdb, but I'm not sure). We do at least know that the REFRESH is using parallel workers. Can you post the output of EXPLAIN ANALYZE for the SELECT? That might provide some clues. -- Jim Nasby, Data Architect, Austin TX
That'd be a band-aid at best, because we know that the query used to
define the materialized view runs in a reasonable amount of time on it's
own, as does a CTAS. So either the REFRESH is doing something odd when
writing into the new relation (which looking at the code seems very
unlikely), or REFRESH is getting a different query plan for some reason.
Unfortunately, I don't know of any easy way to get the query plan for
the REFRESH (it might be possible via gdb, but I'm not sure). We do atleast know that the REFRESH is using parallel workers.Can you post the output of EXPLAIN ANALYZE for the SELECT? That mightprovide some clues.
Sure, here is the explain analyze for the select:
HashAggregate (cost=123986470.20..129486707.63 rows=164493082 width=24) (actual time=697250.385..741548.965 rows=59015171 loops=1)
Group Key: id1, id2, id3
Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 6750176kB
-> Gather (cost=64653301.50..107228737.47 rows=328986164 width=24) (actual time=357598.331..594226.355 rows=161151623 loops=1)
Workers Planned: 2
Workers Launched: 2
-> HashAggregate (cost=64652301.50..74329121.07 rows=164493082 width=24) (actual time=357572.082..578038.457 rows=53717208 loops=3)
Group Key: id1, id2, id3
Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 25774088kB
Worker 0: Batches: 65 Memory Usage: 328209kB Disk Usage: 25375784kB
Worker 1: Batches: 65 Memory Usage: 328209kB Disk Usage: 25382936kB
-> Parallel Seq Scan on large_table (cost=0.00..29740358.40 rows=685387840 width=24) (actual time=12.954..99596.289 rows=548310252 loops=3)
Planning Time: 5.380 ms
Execution Time: 745750.371 ms
--
Jeremiah
On 1/12/24 6:34 AM, Jeremiah Bauer wrote: > > That'd be a band-aid at best, because we know that the query used to > define the materialized view runs in a reasonable amount of time on it's > own, as does a CTAS. So either the REFRESH is doing something odd when > writing into the new relation (which looking at the code seems very > unlikely), or REFRESH is getting a different query plan for some reason. > Unfortunately, I don't know of any easy way to get the query plan for > the REFRESH (it might be possible via gdb, but I'm not sure). We do at > least know that the REFRESH is using parallel workers. > > Can you post the output of EXPLAIN ANALYZE for the SELECT? That might > provide some clues. > > Sure, here is the explain analyze for the select: > > HashAggregate (cost=123986470.20..129486707.63 rows=164493082 width=24) > (actual time=697250.385..741548.965 rows=59015171 loops=1) > Group Key: id1, id2, id3 > Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk > Usage: 6750176kB > -> Gather (cost=64653301.50..107228737.47 rows=328986164 width=24) > (actual time=357598.331..594226.355 rows=161151623 loops=1) > Workers Planned: 2 > Workers Launched: 2 > -> HashAggregate (cost=64652301.50..74329121.07 > rows=164493082 width=24) (actual time=357572.082..578038.457 > rows=53717208 loops=3) > Group Key: id1, id2, id3 > Planned Partitions: 64 Batches: 65 Memory Usage: > 328209kB Disk Usage: 25774088kB > Worker 0: Batches: 65 Memory Usage: 328209kB Disk > Usage: 25375784kB > Worker 1: Batches: 65 Memory Usage: 328209kB Disk > Usage: 25382936kB > -> Parallel Seq Scan on large_table > (cost=0.00..29740358.40 rows=685387840 width=24) (actual > time=12.954..99596.289 rows=548310252 loops=3) > Planning Time: 5.380 ms > Execution Time: 745750.371 ms Ok, so that's using a parallel query as well, just like REFRESH, so no help there. At this point I think you'd need to do some OS-level investigation using a tool like perf to determine what the parent process is doing once the workers finish. BTW, I did just come across a script[1] that will print the query plan for a running query. Based on what you've said I suspect it wouldn't tell us much here, but I wanted to mention it. 1: https://github.com/StarfishStorage/explain-running-query -- Jim Nasby, Data Architect, Austin TX