kotohoroshko
05/06/2024, 7:45 AMWITH minvalid AS ( SELECT xid, snapshot FROM relation_tuple_transaction WHERE timestamp >= NOW() - INTERVAL ? OR timestamp = (SELECT MAX(timestamp) FROM relation_tuple_transaction) ORDER BY timestamp ASC LIMIT ? ) SELECT minvalid.xid, minvalid.snapshot, pg_current_snapshot() FROM minvalid
https://cdn.discordapp.com/attachments/844600078948630559/1236946829476892682/image.png?ex=6639dc01&is=66388a81&hm=9f29050155f81a6466a7184d29b02bb1becacac5640645d1c390386bdb24aae4&vroldanbet
05/06/2024, 7:47 AMkotohoroshko
05/06/2024, 7:48 AMvroldanbet
05/06/2024, 8:05 AM--datastore-gc-interval duration amount of time between passes of garbage collection (postgres driver only) (default 3m0s)
--datastore-gc-max-operation-time duration maximum amount of time a garbage collection pass can operate before timing out (postgres driver only) (default 1m0s)
--datastore-gc-window duration amount of time before revisions are garbage collected (default 24h0m0s)
If GC completes cleaning up, you see the GC metrics get to a healthy state, which should look like:
- not much time spent on GC
- not many tuples / transactions being GC
and the problem still persists, then there may be something else going on.vroldanbet
05/06/2024, 8:05 AMkotohoroshko
05/06/2024, 8:18 AMvroldanbet
05/06/2024, 8:20 AMkotohoroshko
05/06/2024, 8:25 AMkotohoroshko
05/06/2024, 8:25 AMkotohoroshko
05/06/2024, 8:27 AMvroldanbet
05/06/2024, 8:41 AMvroldanbet
05/06/2024, 8:42 AMvroldanbet
05/06/2024, 8:47 AMminimize_latency
consistency, you could narrow your GC window. By default it's 24 hours. Making the GC window would limit the ability to go "back in time" (see at_exact_snapshot
or at_least_as_fresh
consistency). But if you do not care about that, then you can shorten the GC window, which means it greatly reduces the number of transactions stored in your database at a given time, which is likely what's causing your DB to spend much time computing that query you posted.Yaroslav
05/16/2024, 10:30 AM--datastore-gc-interval=3m
--datastore-gc-max-operation-time=3m
--datastore-gc-window duration=24h
but after this changes we have big memory leak for spice db (
should we add more memory or we can play with configs?Yaroslav
05/16/2024, 10:31 AMYaroslav
05/16/2024, 10:37 AMvroldanbet
05/16/2024, 10:39 AM