search

ohdsi-studies / Covid19CohortEvaluation

A central package for evaluation the cohorts used in the various Covid-19 studies
https://ohdsi-studies.github.io/Covid19CohortEvaluation/
1 stars 2 forks source link

Bradycardia instantiation cohort hanging #2

Open ChristopheLambert opened 4 years ago

ChristopheLambert commented 4 years ago

The bradycardia instantiation cohort portion of the package is hanging for me under PostgreSQL for my MDCR MarketScan database but not my CCAE MarketScan database. It has been running 9 hours on the smaller MDCR database, and appears to be hanging at CREATE TEMP TABLE qualified_events

The CCAE portion ran in 15.2 mins. I am running OMOP CDM 5.0.1, and share identical vocabularies. The only other thing I can think of is that I am running both the MDCR and CCAE studies in the same folder, though I specified different fftempdir and outputFolder subfolders and different schema.

SFBertolin commented 4 years ago

Same problem as @ChristopheLambert Now instantiation of bradycardia has been stuck at the same point for more than 10 hours and our database is under PostgreSQL too. Our CDM version is 5.3.1. The "never ending" SQL statement is the following:

CREATE TEMP TABLE qualified_events

AS WITH primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) AS ( -- Begin Primary Events select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id FROM ( select E.person_id, E.start_date, E.end_date, row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC) ordinal, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id FROM ( -- Begin Condition Occurrence Criteria SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + 1*INTERVAL'1 day')) as end_date, C.CONDITION_CONCEPT_ID as TARGET_CONCEPT_ID, C.visit_occurrence_id, C.condition_start_date as sort_date FROM ( SELE

ChristopheLambert commented 4 years ago

If you kill the query and rerun from the beginning, because it is in incremental mode, all of the cohorts up to and including bradycardia get skipped, and the job keeps going, presumably leaving an empty cohort for bradycardia.

SFBertolin commented 4 years ago

If you kill the query and rerun from the beginning, because it is in incremental mode, all of the cohorts up to and including bradycardia get skipped, and the job keeps going, presumably leaving an empty cohort for bradycardia.

Hi @ChristopheLambert, and it finishes or it's stuck in another process? I've tried this with a previous version of the package and got hung in another process. How is your execution now?

ChristopheLambert commented 4 years ago

It got a few more done, but right now it is taking a long time with end stage renal disease -- probably hanging. I ran devtools::install_github("ohdsi-studies/Covid19CohortEvaluation") beforehand and it said there were no changes since last install. It seems there have been no updates to this repository in the past 2 days.

schuemie commented 4 years ago

I think all these issues are related to server-side settings. Perhaps the server is running out of temp space, or some other resource. If so, there is nothing that can be done about it from the OHDSI tool side, these problems likely should be solved by your database admin.

@leeevans: you have experience setting up PostgreSQL to run OHDSI stuff. Any recommendations on server settings?

leeevans commented 4 years ago

I'd generally suggest using the below website - enter your hardware config (specifying 'data warehouses' as the DB Type) and compare the generated postgresql.conf with the one on your system:

https://pgtune.leopard.in.ua/#/

ChristopheLambert commented 4 years ago

I think it is unlikely to be server side settings, particularly space issues, as the code ran fine for the order of magnitude larger CCAE and failed for MDCR on the same machine.

ChristopheLambert commented 4 years ago

If you kill the query and rerun from the beginning, because it is in incremental mode, all of the cohorts up to and including bradycardia get skipped, and the job keeps going, presumably leaving an empty cohort for bradycardia.

Hi @ChristopheLambert, and it finishes or it's stuck in another process? I've tried this with a previous version of the package and got hung in another process. How is your execution now?

It turns out it was running from the start and the first hang was bradycardia. I can't confirm any other steps hang -- I was mistaken before about it picking up after bradycardia.

schuemie commented 4 years ago

I think it is unlikely to be server side settings, particularly space issues, as the code ran fine for the order of magnitude larger CCAE and failed for MDCR on the same machine.

The most likely cause is a server-side problem, exactly because the same query runs fine on other servers. There's nothing I can do until we diagnose the issue from the server.