Just a wild suggestion: In theory maybe we can cache it such that each update for a particular window will only do 1 read and 1 write.

• On startup, calculate the full uptime, cache values total_duration and uptime_duration
• Then on each heartbeat, read the last value that is leaving the calculation window, e.g. the last beat exactly 24 hours before now
• If this beat is up, we subtract its duration from both total_duration and uptime_duration, otherwise we only subtract it from total_duration
• Store the latest beat, add its duration to total_duration and uptime_duration depending on the status
• Calculate percentage from the updated values

Haven't tried this and don't know if there are edge cases to handle tho.

Edit: Oops I realized if the beat leaving the window has a longer duration than the current rate, it would lead to the same beat being subtracted multiple times...

I guess if we can somehow handle these 2 cases it would work?

Good point. I also recheck the current logic, it seems that it is also not handled the edge cases correctly. But I am not quite sure.

I think for this part, maybe it is good to start with writing test cases first.

The uptime calculation actually make me a bit headache, because when I tried to look into it, there are a lot of weird cases to be handled.

I am rethinking the time-series database option, it seems that QuestDB is quite promising, because it could sum up a large set of data with a simple sql. If it is really gaining a lot of performance, it maybe an ultimate solution for #1740 too.

But I don't know the ram usage, I will try to import 1,000,000 heartbeat records into QuestDB and test it.

Reference from QuestDB's README:

After some tests, I think QuestDB is really the way to go.

For example, I try to sum up 30-day uptime:

QuestDB Result

Oracle Cloud free instance (2cores + 1GB RAM)

The execution time: around 2ms - 7ms

SQLite Result

My notebook (11gen i7 8cores + 16GB RAM)

I don't know how to use the sqlite command to display the execution time, so I ran it on my pc.

The execution time: around 69ms - 75ms

So even though the oracle cloud instance is weak, it is still faster than sqlite on my pc.

But unfortunately, the memory usage of QuestDB is too large.

Will look into these databases later:

• RedisTimeSeries
• InfluxDB

But unfortunately, the memory usage of QuestDB is too large.

Will look into these databases later:

• RedisTimeSeries
• InfluxDB

Hello

I find it strange that QuestDB is so energy intensive, because when I see their presentation

Docker hub introduction --> QuestDB is an open-source database designed to make time-series lightning fast and easy

Is it possible to test with QuestDB on external docker?

What is the final strategy? have a TSDB + relational database?(external-mariadb OR mysqlite)?

Otherwise, on my side, I find REDIS-TSDB / InfluxDB / QuestDB very good in addition to improving the graphic part

I likely stick back to SQLite/MariaDB, as the setup is easier and it won't use a lot of RAM.

I may look into the sliding window or rolling window algorithm later.

By using PostgreSQL we could use timescaledb as an extension and activate that for some tables only, also I love Postgres more than MySQL🙂

Do you already have a timeframe for when this feature will be released?

Do you already have a timeframe for when this feature will be released?

See #2720 (comment)

I think most time-series database use too many memory, which is not ideal for Uptime Kuma (I hope Uptime Kuma could be relatively lightweight), so I go back to my original plan - aggregate tables.

It is going well, getting a 1-year uptime is less than 1ms.

# Subtest: Worst case
    # Subtest: get1YearUptime()
    ok 2 - get1YearUptime()
      ---
      duration_ms: 0.587246

Also, I added support for native Node.js test runner along with this pr.
https://nodejs.org/api/test.html

I think most time-series database use too many memory, so I go back to my original plan - aggregate tables.

It is going well, getting a 1-year uptime is less than 1ms.

# Subtest: Worst case
    # Subtest: get1YearUptime()
    ok 2 - get1YearUptime()
      ---
      duration_ms: 0.587246

The size of the database will get bigger and bigger soon I think 🤔

But query performance is great...

I think most time-series database use too many memory, so I go back to my original plan - aggregate tables.
It is going well, getting a 1-year uptime is less than 1ms.

# Subtest: Worst case
    # Subtest: get1YearUptime()
    ok 2 - get1YearUptime()
      ---
      duration_ms: 0.587246

The size of the database will get bigger and bigger soon I think 🤔

But query performance is great...

I tried to make these tables as small as possible, they are all int and float.

Also I will try to save some space by eliminating duplicate strings in #3595

I think only one record for each monitor in aggregated tables is enough.

Can you tell me if we need them?
If yes, we could have a more restricted retention policy on them

For that we could use a custom CreateOrUpdate function to check if record already exist or not?

Table size is a decent downside if performance is great.

Table size is a decent downside if performance is great.

Big tables may make select queries slower...

Big tables may make select queries slower...

Isn't the whole point of a database to have big tables?

is this WIP or already a feature? cause i cant find setting to change status page uptime to monthly etc in latest version

There is no such feature available.