[Suggestion]: Buffer the traffic stats in memory and improve database schema

[radiumatic]

Is your feature request related to a problem? Please describe.

If I understand correctly, traffic stats, like settings, are saved in SQLite. This causes write amplification, CPU spikes, I/O spikes and trashes storage devices. It also causes some strange bugs when there's a WAL queue.

Describe the solution you'd like

Storing the counters in memory and syncing it to disk every five minutes or so, in a batched transaction updating/inserting records for all clients/outbounds/inbounds at once.

Describe alternatives you've considered

I guess moving the database to tmpfs would improve the situation somewhat.

Additional context
Add any other context or screenshots about the feature request here.

On a similar tangent, please consider either dropping SQLite, using an ORM, and/or defining proper schemas. As of now it serves no purpose but to reduce performance and complicate things. A giant JSON file would the serve the current program just as well as SQLite.

From a cursory glance at the database, it seems that it doesn't use joined tables but rather string names. This means that there are roundtrips to the database for data that could be retrieved in a single transaction if properly specified. The settings are also all json. This makes migrating them more difficult.

[alireza0]

Your suggestion has two parts:

1. Using better database like ORM:
   It is against simplicity which is one of main targets.

2. Storing traffic chart data out of main database:
   It is one of my plans and tested several times with different packages.
   Currently disabling traffic data is configurable. But I will find another way to store traffic data and audit logs outside of the database.

[hedsbj]

SQLite's WAL (Write-Ahead Logging) mode is one of its core performance optimization techniques. Compared to the default Rollback Journal mode, WAL delivers significant performance improvements in most scenarios, especially in terms of concurrent read/write operations and disk I/O efficiency.

sqlite3 /usr/local/s-ui/db/s-ui.db "PRAGMA journal_mode=WAL;"
s-ui restart
会极大提高性能

[alireza0]

@hedsbj WAL is not a good option for small enough applications like this.
There is not enough concurrent operations and it is out of this design.
I have added the same in X-UI in the past.

[radiumatic]

Your suggestion has two parts:

1. Using better database like ORM:
   It is against simplicity which is one of main targets.
2. Storing traffic chart data out of main database:
   It is one of my plans and tested several times with different packages.
   Currently disabling traffic data is configurable. But I will find another way to store traffic data and audit logs outside of the database.

Storing the traffic stats outside sqlite isn't really needed. Aggregating the value should fix the problem.

As for using a proper schema and JOINs, it would reduce the latency of all operations by a large amount. You can also set up aggregation triggers and better data types for the traffic stats. At the moment the web UI hangs trying to aggregate monthly or weekly usage.

[alireza0]

Data will be sent to UI in json.
UI hangs, due to load and creating chart which depends on your browser and the CPU/GPU.
Aggregation is a good idea and could be added.