TTL node statistics

Node Operators
1

I would like to see statistic of data stored in my node about TTL. How many percentage are 1week/8week or never expire…
Is it possible?

2

Perhaps yes. You need to use info from the piece_expiration.db DB, it has a schema:

CREATE TABLE piece_expirations (
                                                satellite_id       BLOB      NOT NULL,
                                                piece_id           BLOB      NOT NULL,
                                                piece_expiration   TIMESTAMP NOT NULL, -- date when it can be deleted
                                                deletion_failed_at TIMESTAMP, trash INTEGER NOT NULL DEFAULT 0,
                                                PRIMARY KEY (satellite_id, piece_id)
                                        );
CREATE INDEX idx_piece_expirations_piece_expiration ON piece_expirations(piece_expiration);
CREATE INDEX idx_piece_expirations_deletion_failed_at ON piece_expirations(deletion_failed_at);
CREATE TABLE versions (version int, commited_at text);
CREATE INDEX idx_piece_expirations_trashed
                                                ON piece_expirations(satellite_id, trash)
                                                WHERE trash = 1;

If you would like to see this info on something like Grafana, then you need to implement a custom exporter for Prometheus.

3

Thanks! I’m going to work on this

4

I offer my help with this, lot of people around this forum will get this info very helpfull

I can write an exporter for prometheus with python or go, i have no idea about sql or databases but i can code the exporter itself

2 Likes
5 
SELECT
  DATE(piece_expiration) AS expiration_date,
  COUNT(*) AS piece_count
FROM piece_expirations
GROUP BY DATE(piece_expiration)
ORDER BY expiration_date;

This should give you a date column then number of pieces that would expire on the given date. Use piece_expiration.db as Alexey mentioned.

1 Like