Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #894 > unrolled thread

Postgresql database behaving unpredictably

Back to article view | Back to comp.databases.postgresql

Contents

  Postgresql database behaving unpredictably shariqtariq <shariqatariq@gmail.com> - 2021-01-14 17:08 -0800
    Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-15 16:53 -0500
      Re: Postgresql database behaving unpredictably Mladen Gogala <mgogala@yahoo.com> - 2021-01-21 04:15 +0000
        Re: Postgresql database behaving unpredictably George Neuner <gneuner2@comcast.net> - 2021-01-21 14:06 -0500

#894 — Postgresql database behaving unpredictably

Hello - I am running an application on postgresql 9.5 that features a 515 million record table. The following query used to run in under 10 seconds 

select  f.patient_num
from i2b2demodata.observation_fact f
where
f.concept_cd IN (select concept_cd from  i2b2demodata.concept_dimension   where concept_path LIKE '\\ACT\\Diagnosis\\ICD10\\V2_2018AA\\A20098492\\A18916316\\A27150507\\A17864418\\%')
group by  f.patient_num

The DDL for the large table is at the bottom

Overnight the query is starting to run in 4 plus minutes

------------ 

so I migrated the database to postgres 12 and now the query runs in a shade over 2 minutes

Is there something that may have happened to the database? Is there anything that I can do to tweak the database?
------------------------------
-- Table: i2b2demodata.observation_fact

-- DROP TABLE i2b2demodata.observation_fact;

CREATE TABLE i2b2demodata.observation_fact
(
  encounter_num integer NOT NULL,
  patient_num integer NOT NULL,
  concept_cd character varying(50) NOT NULL,
  provider_id character varying(50) NOT NULL,
  start_date timestamp without time zone NOT NULL,
  modifier_cd character varying(100) NOT NULL DEFAULT '@'::character varying,
  instance_num integer NOT NULL DEFAULT 1,
  valtype_cd character varying(50),
  tval_char character varying(255),
  nval_num numeric(18,5),
  valueflag_cd character varying(50),
  quantity_num numeric(18,5),
  units_cd character varying(50),
  end_date timestamp without time zone,
  location_cd character varying(50),
  observation_blob text,
  confidence_num numeric(18,5),
  update_date timestamp without time zone,
  download_date timestamp without time zone,
  import_date timestamp without time zone,
  sourcesystem_cd character varying(50),
  upload_id integer,
  text_search_index serial NOT NULL,
  CONSTRAINT observation_fact_pk PRIMARY KEY (patient_num, concept_cd, modifier_cd, start_date, encounter_num, instance_num, provider_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE i2b2demodata.observation_fact
  OWNER TO i2b2demodata;

-- Index: i2b2demodata.of_idx_allobservation_fact

-- DROP INDEX i2b2demodata.of_idx_allobservation_fact;

CREATE INDEX of_idx_allobservation_fact
  ON i2b2demodata.observation_fact
  USING btree
  (patient_num, encounter_num, concept_cd COLLATE pg_catalog."default", start_date, provider_id COLLATE pg_catalog."default", modifier_cd COLLATE pg_catalog."default", instance_num, valtype_cd COLLATE pg_catalog."default", tval_char COLLATE pg_catalog."default", nval_num, valueflag_cd COLLATE pg_catalog."default", quantity_num, units_cd COLLATE pg_catalog."default", end_date, location_cd COLLATE pg_catalog."default", confidence_num);

-- Index: i2b2demodata.of_idx_clusteredconcept

-- DROP INDEX i2b2demodata.of_idx_clusteredconcept;

CREATE INDEX of_idx_clusteredconcept
  ON i2b2demodata.observation_fact
  USING btree
  (concept_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_encounter_patient

-- DROP INDEX i2b2demodata.of_idx_encounter_patient;

CREATE INDEX of_idx_encounter_patient
  ON i2b2demodata.observation_fact
  USING btree
  (encounter_num, patient_num, instance_num);

-- Index: i2b2demodata.of_idx_modifier

-- DROP INDEX i2b2demodata.of_idx_modifier;

CREATE INDEX of_idx_modifier
  ON i2b2demodata.observation_fact
  USING btree
  (modifier_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_sourcesystem_cd

-- DROP INDEX i2b2demodata.of_idx_sourcesystem_cd;

CREATE INDEX of_idx_sourcesystem_cd
  ON i2b2demodata.observation_fact
  USING btree
  (sourcesystem_cd COLLATE pg_catalog."default");

-- Index: i2b2demodata.of_idx_start_date

-- DROP INDEX i2b2demodata.of_idx_start_date;

CREATE INDEX of_idx_start_date
  ON i2b2demodata.observation_fact
  USING btree
  (start_date, patient_num);

-- Index: i2b2demodata.of_idx_uploadid

-- DROP INDEX i2b2demodata.of_idx_uploadid;

CREATE INDEX of_idx_uploadid
  ON i2b2demodata.observation_fact
  USING btree
  (upload_id);

-- Index: i2b2demodata.of_text_search_unique

-- DROP INDEX i2b2demodata.of_text_search_unique;

CREATE UNIQUE INDEX of_text_search_unique
  ON i2b2demodata.observation_fact
  USING btree
  (text_search_index);

[toc] | [next] | [standalone]

#895

On Thu, 14 Jan 2021 17:08:33 -0800 (PST), shariqtariq
<shariqatariq@gmail.com> wrote:

>Hello - I am running an application on postgresql 9.5 that features a
>515 million record table. The following query used to run in under 10
>seconds 
>
>   SNIP QUERY
>
>Overnight the query is starting to run in 4 plus minutes

One or more of the indexes could be corrupted - you could try
rebuilding them, and also checking the size of your work buffers
because the grouping requires sort.

However, sudden drastic performance drop smells a lot like imminent
disk failure.  If you haven't already, I would move the database to
new storage and make sure your backups are good.


>so I migrated the database to postgres 12 and now the query runs in a
>shade over 2 minutes

12 has parallel query turned on by default, so that may be the reason
it ran faster.


Hope this helps,
George

[toc] | [prev] | [next] | [standalone]

#896

On Fri, 15 Jan 2021 16:53:12 -0500, George Neuner wrote:

> However, sudden drastic performance drop smells a lot like imminent disk
> failure.

Huh? I am an Oracle DBA with some experience in Postgres and have 
experienced many performance drops after a version upgrade and precisely 
0 of those performance drops were caused by "imminent disk failure".
I would check whether the plans have changed, what is the difference in 
parameters, check where the time is spent by using perf or strace and 
check the logs. If the plans have changed, try installing pg_hint_plan 
extension and force the desired plans. Then compare.



-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com

[toc] | [prev] | [next] | [standalone]

#897

On Thu, 21 Jan 2021 04:15:03 +0000 (UTC), Mladen Gogala
<mgogala@yahoo.com> wrote:

>On Fri, 15 Jan 2021 16:53:12 -0500, George Neuner wrote:
>
>> However, sudden drastic performance drop smells a lot like imminent disk
>> failure.
>
>Huh? I am an Oracle DBA with some experience in Postgres and have 
>experienced many performance drops after a version upgrade and precisely 
>0 of those performance drops were caused by "imminent disk failure".

The OP complained of major performance loss *BEFORE* upgrading, and
claimed better performance following the upgrade (which likely was due
to the new PG version having parallel query turned on by default).

It is (fairly) well known that PG neither transfers existing index
data, nor builds indexes during an upgrade.  The indexes are created
during the upgrade and marked to be rebuilt incrementally, but for
best performance immediately following the upgrade, the DBA must
rebuild the indexes manually.


>I would check whether the plans have changed, what is the difference in 
>parameters, check where the time is spent by using perf or strace and 
>check the logs. If the plans have changed, try installing pg_hint_plan 
>extension and force the desired plans. Then compare.

Assuming no query or configuration changes, "under 10 seconds" to "4
plus minutes" is a huge difference to be explained by a plan change.
Yes, it can happen ... but it would have to be the result of either
corrupted indexes or large changes to the table data.

The OP did not mention large changes to the table, and I did mention
that the indexes might have been corrupted.

George

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web