Hi, can you try upgrading to 1.21? There were a few jdbc fixes that might unblock this

okay thanks. I’ve just done that, and I’ve also migrated to rocksdb

cool, did it have any impact? still hanging?

I don’t know yet. I had to rollback the rocksdb migration since it’s crashing the JVM

Oh, that sounds weird. Are you using an M1?

No amd64 ubuntu

The upgrade to 1.21 does not solve the problem. It works for a little while, but after a couple of minutes/hours it seems that new transactions aren’t being ingested by my node

If I reboot it recovers

hmm, and what does your RAM and CPU look like while this is happening?

Maybe there's some missing memory config

(defstate node
  :start (xtdb/start-node
          {:xtdb.jdbc/connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
                                       :db-spec (:db (config))}
           :xtdb/tx-log               {:xtdb/module     'xtdb.jdbc/->tx-log
                                       :connection-pool :xtdb.jdbc/connection-pool}
           :xtdb/document-store       {:xtdb/module     'xtdb.jdbc/->document-store
                                       :connection-pool :xtdb.jdbc/connection-pool}
           ; :xtdb.http-server/server   {:port 5000}
           ;; :xtdb/index-store {:kv-store {:xtdb/module 'xtdb.rocksdb/->kv-store
           ;;                               :db-dir (io/file "/tmp/rocksdb/db")}}
           })
  :stop (.close @node))

(:db (config)) is just a database url of the form :port/dbname

Sorry for the delay - thanks for sharing the extra info, although it's not clear what might be happening still :thinking_face:

What are you JVM memory configs (if any), i.e. -Xms -Xmx?

My next step would be to reach for a profiler to look for heap memory usage / GC pausing, using https://www.yourkit.com/java/profiler/features/ or similar

How much data are you loading in total? How many transactions? How many operations per transaction on average?

Do you know what is happening with the Postgres instance? Is it reporting that everything is healthy?

Here’s my slow query:

(defn active-deliverables [node]
  (->> (xtdb/q
        (xtdb/db node) '{:find [d cn (sum du) (pull d [*]) (distinct deps)]
                         :where [[d :type :deliverable]
                                 [d :project p]
                                 [p :code-name cn]
                                 (not [d :date nil])
                                 (not [d :status _])
                                 (or-join [du d]
                                          (and [tc :type :timecard]
                                               [tc :duration du]
                                               [tc :deliverable d])
                                          (and [tc :type :timecard]
                                               [tc :duration du]
                                               [tc :deliverable d2]
                                               [d2 :parent d])
                                          (and [(identity 0) du]
                                               [d :type :deliverable]))
                                 (or-join [deps d]
                                          (and
                                           [deps :type :deliverable]
                                           [deps :parent d])
                                          (and [(identity nil) deps]
                                               [d :type :deliverable]))]})
       (map (fn [[_ code-name ssf attr deps]] (merge attr
                                                     {:code-name code-name
                                                      :spent-so-far ssf
                                                      :dependencies deps})))))

{:type :project :codename x :status "active"}
{:xt/id #uuid "..." :type :deliverable :project p :status "completed" :parent d2}
{:type :timecard :duration du :deliverable d}

Is this still up to date? https://clojurians-log.clojureverse.org/xtdb/2021-06-10

did you try (xtdb.query/query-plan-for db ...query...)if it gives anything interesting

> Is there any documentation anywhere on how to improve the performance of queries? There's no central resource, but there have been a lot of discussions and issues that might hold clues. Looking at query-plan-for and the vars-in-join-order in particular is the best first step

I suspect the two (not ...) rule clauses are the source of the slowness though

You could try instead:

;; (not [d :date nil])
[d :date date]
[(some? date)]

(not [d :status _])

Thank you @U11SJ6Q0K and @U899JBRPF I will explore these ideas!

Hi @U026YA2AQSX we use the term 'migrating' to describe this. Given the large number of possible migration paths, we haven't yet built a generic tool for this, however you can attempt to use / draw inspiration from the last two commits here: https://github.com/xtdb/xtdb/tree/crux-migration-hack

If this is something urgent you need help with though, please feel free to DM me and I'm sure we can help

Thanks Jeremy! I'll take look and try to to make some POC with. For the moment it's not really critical, were are spending some times to have a strategy in case of kafka failure since database is constantly growing :thumbsup::skin-tone-3: