Re: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL
You are running afoul of the Linux OOM killer which is what the kernel uses when it experiences memory pressure. You could exclude PostgreSQL from the OOM killer, you'll need to look up on how to do that for your particular Linux distro. We've experienced this with queries that consume more RAM and swap than the server has available. -- Jeremiah From: PegoraroF10 Sent: Friday, February 8, 2019 4:11 PM To: pgsql-gene...@postgresql.org Subject: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. Well, now we have two queries which stops completelly our postgres server. That problem occurs on 10.6 and 11.1 versions. On both server the problem is the same. Linux logs of old crash are: Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 callbacks suppressed Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405791] postgres[10195]: segfault at 24 ip 555dc6a71cb0 sp 7ffc5f91db38 error 4 in postgres[555dc69b4000+6db000] Postgres log of old crash: 2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,3,,2019-02-01 18:31:37 -02,,0,LOG,0,"server process (PID 10195) was terminated by signal 11: Segmentation fault","Failed process was running: WITH StatusTrabalhando(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusDigitacao$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusVisita$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) AND Intkey NOT in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs,Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status""" 2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,4,,2019-02-01 18:31:37 -02,,0,LOG,0,"terminating any other active server processes","" Postgres logs of new crash: 2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25625,,2019-01-28 15:19:52 -02,,0,LOG,0,"server process (PID 10321) was terminated by signal 11: Segmentation fault","Failed process was running: with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador""" 2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25626,,2019-01-28 15:19:52 -02,,0,LOG,0,"terminating any other active server processes","" 2019-02-08 17:21:16.650 -02,"postgres","f10db",10138,"74.125.92.65:44342",5c5dcd7d.279a,2,"idle",2019-02-08 16:42:05 -02,107/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,"" 2019-02-08 17:21:16.650 -02,"postgres","f10db",9667,"173.194.101.228:63516",5c5dbdee.25c3,2,"idle",2019-02-08 15:35:42 -02,20/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command
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( 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. 1. Why is the materialized view refresh stalling and never completing? 2. Is there a setting I am missing that affects materialized view refreshes? 3. 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. CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.
Re: Refresh Materialized View Issue
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 CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.
Re: [EXTERNAL]Re: Refresh Materialized View Issue
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 -- Jeremiah CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.
