I'm trying to approximately graph how many new toots my instance has received. Does Mastodon keep track somewhere?
My current approach is to get the last 1000 entries from the statuses table (way too much, but reasonably quick thanks to the index on statuses.id) and then count those that were created in the past five minutes. I'm using munin, which polls for data about every five minutes - it's jittery thanks to cron and the variable run time of other plugins...
...but on the other hand I don't have to keep track of the previous invocation time. Also going to lose data when munin doesn't run or can't reach the instance. My current query looks mostly like this:
select count(id) from (select id,created_at from statuses order by id desc limit 1000) AS slast where slast.created_at >= now() - interval '5 minutes';
The results seem resonable so far.
Any better ideas that I could steal?
The social network of the future: No ads, no corporate surveillance, ethical design, and decentralization! Own your data with Mastodon!