Achilles on Postgres — analysis 117 extremely slow, Query drops silently, numThreads fails

General
1

Environment: Achilles 1.8 / DatabaseConnector 6.3.2 / R 4.5.1 / CDM 5.3 / PostgreSQL

Hello everyone,

We’re running Achilles against a PostgreSQL CDM (~4M rows in observation_period) and have a few questions:

  1. Analyses 117, 1409, 1410, etc. normally run in ~18 seconds but sometimes take an hour or more. The query plan shows 1B+ estimated intermediate rows from a range join on a computed expression, which a standard index doesn’t help with. Has anyone found an effective indexing approach?
  2. On long-running analyses, the connection sometimes disappears from pg_stat_activity with no error - possibly statement_timeout or a network/firewall timeout. Has anyone run into this?
  3. Using numThreads fails with: “Unable to evaluate the ‘server’ argument… connection is being established in a separate R thread…” Has anyone encountered this error?

Thanks in advance for any guidance.

2

Hi @Jaya ! I can help with the last one: Connection details as created by createConnectionDetails() do not store the secret values in memory. Instead, they store the expressions, and only evaluate them when connecting. That does require that the expression can be evaluated in the environment where the connection is made. If you use multi-threading, local variables will not be available.

In other words, if you do something like this:

myServer <- "server.company.com"
connectionDetails <- createConnectionDetails(server = myServer, ...)

Then that would cause the error you noted because myServer won’t exist in threaded environments.

Best practice is to use credential managers for sensitive information, which will be available in all environments. For example:

connectionDetails <- createConnectionDetails(server = keyring::key_get("myServer"), ...)

The expression keyring::key_get("myServer") can be evaluated in any environment. This of course requires you’ve set a key myServer in your keyring.

3

Hello All,
I am a co-worker with Jaya and appreciate the Reponse.
If we cannot seem to address these database connection issues Can we run Achilles manually?

It is my understanding that we can do this:

  1. Run Achilles with the “sqlonly” parameter that will generate an achilles.sql for our database type (postgres)
  2. We can then run that entire set of sql commands independent of the R packages and end with the same final results into 2 files: achilles_results_dist, and achilles_results

Is that correct?
Thanks, Jeff

4

you can also enumerate the analysesIDs that you want to run

so you can do all but 117 like this

library(Achilles)
details <- Achilles::getAnalysisDetails()

all <- details$ANALYSIS_ID
subset <- setdiff(all, c(117))

(my code may have errors)