uiDivStats Stat generation times

[Jack Yaz]

I've been watching this a while and using AMTM + Tooling since it was first delivered. Jack, any DB maint planned or happening WRT size / pruning by date or age? I'm asking b/c this seems a bit large and maybe needs pruning by date? The GUI basically stopped displaying anything. I'm running on our fastest USB/SATA/SSD + latest AMTM etc... I think I deleted the large DB a few days ago but here's how big it had grown.

View attachment 34078

it prunes every night such that only 30 days data is being kept. are you familiar with SQL if I give you some commands to run to check the oldest entry to make sure the trim is happening?

 

[chongnt]

it prunes every night such that only 30 days data is being kept. are you familiar with SQL if I give you some commands to run to check the oldest entry to make sure the trim is happening?

No wonder. I thought something wrong that mine just hovering around 330MB and not increasing in size anymore. Thanks Jack.

 

[visortgw]

some further gains. i'm excited to see what times AX88U and AX86U users will get!
View attachment 34046

Is that on development branch? I'd be willing to test on AX88U...

 

[Jack Yaz]

Is that on development branch? I'd be willing to test on AX88U...

it is, but at the moment there's a maintenance/index job that takes 30mins to complete (i haven't optimised it yet). it will run as part of the nightly trim so shouldn't impact usage, but develop is by no means stable so a lot is likely to break

 

[Jack Yaz]

it prunes every night such that only 30 days data is being kept. are you familiar with SQL if I give you some commands to run to check the oldest entry to make sure the trim is happening?

sqlite3 /opt/share/uiDivStats.d/dnsqueries.db

will open the database with sqlite. You can use Ctrl+D to exit out to the SSH prompt.

.headers on
SELECT * FROM dnsqueries ORDER BY Timestamp ASC LIMIT 10;

will produce the 10 oldest entries in the database

 

[aru]

I've noticed that the dnsqueries.db file is excessively large, reaching a size of 19.0G, causing the execution to be interrupted. I'm wondering if there are any methods to optimize or trim its size?

admins@GT-AX6000-0FC8:/tmp/home/root# ls -hl /opt/share/uiDivStats.d/dnsqueries.db
-rw-r--r--    1 admins   root       19.0G Nov 23 10:04 /opt/share/uiDivStats.d/dnsqueries.db

admins@GT-AX6000-0FC8:/tmp/home/root# time -v /jffs/scripts/uiDivStats fullrefresh
Starting stat full refresh


Command terminated by signal 15
        Command being timed: "/jffs/scripts/uiDivStats fullrefresh"
        User time (seconds): 1515.53
        System time (seconds): 313.75
        Percent of CPU this job got: 45%
        Elapsed (wall clock) time (h:mm:ss or m:ss): 1h 7m 13s
        Average shared text size (kbytes): 0
        Average unshared data size (kbytes): 0
        Average stack size (kbytes): 0
        Average total size (kbytes): 0
        Maximum resident set size (kbytes): 41104
        Average resident set size (kbytes): 0
        Major (requiring I/O) page faults: 3718
        Minor (reclaiming a frame) page faults: 324782
        Voluntary context switches: 9882777
        Involuntary context switches: 1275438
        Swaps: 0
        File system inputs: 0
        File system outputs: 0
        Socket messages sent: 0
        Socket messages received: 0
        Signals delivered: 0
        Page size (bytes): 4096
        Exit status: 0

 

[Ripshod]

I've noticed that the dnsqueries.db file is excessively large, reaching a size of 19.0G, causing the execution to be interrupted. I'm wondering if there are any methods to optimize or trim its size?

admins@GT-AX6000-0FC8:/tmp/home/root# ls -hl /opt/share/uiDivStats.d/dnsqueries.db
-rw-r--r--    1 admins   root       19.0G Nov 23 10:04 /opt/share/uiDivStats.d/dnsqueries.db

admins@GT-AX6000-0FC8:/tmp/home/root# time -v /jffs/scripts/uiDivStats fullrefresh
Starting stat full refresh


Command terminated by signal 15
        Command being timed: "/jffs/scripts/uiDivStats fullrefresh"
        User time (seconds): 1515.53
        System time (seconds): 313.75
        Percent of CPU this job got: 45%
        Elapsed (wall clock) time (h:mm:ss or m:ss): 1h 7m 13s
        Average shared text size (kbytes): 0
        Average unshared data size (kbytes): 0
        Average stack size (kbytes): 0
        Average total size (kbytes): 0
        Maximum resident set size (kbytes): 41104
        Average resident set size (kbytes): 0
        Major (requiring I/O) page faults: 3718
        Minor (reclaiming a frame) page faults: 324782
        Voluntary context switches: 9882777
        Involuntary context switches: 1275438
        Swaps: 0
        File system inputs: 0
        File system outputs: 0
        Socket messages sent: 0
        Socket messages received: 0
        Signals delivered: 0
        Page size (bytes): 4096
        Exit status: 0

I've seen similar. I just copied the db file out then deleted it followed by a reboot - it rebuilds itself. Since then the size of the file has maintained a much smaller size (91MB right now).
Edit: Found this. Is it related? Perhaps your db file is just too large for the trimdb to work on it?

1 0 * * * /jffs/scripts/uiDivStats trimdb #uiDivStats_trim#

I suggest delete and reboot.
(Heavily edited version)

 

[Jack Yaz]

You must have a very busy network! You should enable the option for a/aaaa queries if you haven't already. I can't recall if i got round to making it configurable but you should also adjust how long records aee kept for. If i didn't make that an option, you would need to manually modify the script

I've noticed that the dnsqueries.db file is excessively large, reaching a size of 19.0G, causing the execution to be interrupted. I'm wondering if there are any methods to optimize or trim its size?

admins@GT-AX6000-0FC8:/tmp/home/root# ls -hl /opt/share/uiDivStats.d/dnsqueries.db
-rw-r--r--    1 admins   root       19.0G Nov 23 10:04 /opt/share/uiDivStats.d/dnsqueries.db

admins@GT-AX6000-0FC8:/tmp/home/root# time -v /jffs/scripts/uiDivStats fullrefresh
Starting stat full refresh


Command terminated by signal 15
        Command being timed: "/jffs/scripts/uiDivStats fullrefresh"
        User time (seconds): 1515.53
        System time (seconds): 313.75
        Percent of CPU this job got: 45%
        Elapsed (wall clock) time (h:mm:ss or m:ss): 1h 7m 13s
        Average shared text size (kbytes): 0
        Average unshared data size (kbytes): 0
        Average stack size (kbytes): 0
        Average total size (kbytes): 0
        Maximum resident set size (kbytes): 41104
        Average resident set size (kbytes): 0
        Major (requiring I/O) page faults: 3718
        Minor (reclaiming a frame) page faults: 324782
        Voluntary context switches: 9882777
        Involuntary context switches: 1275438
        Swaps: 0
        File system inputs: 0
        File system outputs: 0
        Socket messages sent: 0
        Socket messages received: 0
        Signals delivered: 0
        Page size (bytes): 4096
        Exit status: 0

 

[aru]

I also have the same scheduled trimming task,

1 0 * * * /jffs/scripts/uiDivStats trimdb #uiDivStats_trim#

Upon examining the logs from the early morning, it seems that there are no apparent abnormalities.

Nov 23 00:01:00 GT-AX6000-0FC8 uiDivStats: Trimming records entries from database...
Nov 23 00:02:48 GT-AX6000-0FC8 uiDivStats: Record trimming complete
Nov 23 00:02:48 GT-AX6000-0FC8 uiDivStats: Checking database table indexes...
Nov 23 00:02:48 GT-AX6000-0FC8 uiDivStats: Database index checks complete
Nov 23 00:02:48 GT-AX6000-0FC8 uiDivStats: Running nightly database analysis and optimisation...

I'm trying to modify the command to see if I can gain more insight into the details.

1 0 * * * /usr/bin/time -v /jffs/scripts/uiDivStats trimdb |logger -t aruaddon #uiDivStats_trim#

 

[Ripshod]

I also have the same scheduled trimming task,

1 0 * * * /jffs/scripts/uiDivStats trimdb #uiDivStats_trim#

Observing the logs from the early morning, I noticed the following:

Nov 23 01:00:00 GT-AX6000-0FC8 uiDivStats: Stale lock file found (>600 seconds old) - purging lock
Nov 23 01:00:00 GT-AX6000-0FC8 uiDivStats: Starting stat update
Nov 23 01:01:46 GT-AX6000-0FC8 uiDivStats: Stats updated successfully

This log message indicates that during the execution of the scheduled trimming task, a stale lock file was detected, and the file has been present for more than 600 seconds. This could be a warning sign indicating a potential issue.


I wonder if there's any way to avoid this situation? For example, by adjusting the frequency and timing of the trimming process, or using SQLite3 commands to trim and retain information for only one day?

That's unrelated to the trim I believe. The trim runs at 00:01

Nov 19 00:01:00 ripshod uiDivStats: Trimming records entries from database...
Nov 19 00:01:00 ripshod uiDivStats: Record trimming complete
Nov 19 00:01:00 ripshod uiDivStats: Checking database table indexes...
Nov 19 00:01:00 ripshod uiDivStats: Database index checks complete
Nov 19 00:01:00 ripshod uiDivStats: Running nightly database analysis and optimisation...
Nov 19 00:01:31 ripshod uiDivStats: Database analysis and optimisation complete

If you check your logs after midnight you'll likely get a pointer to what's actually happening.

 

[aru]

That's unrelated to the trim I believe. The trim runs at 00:01

Apologies, I noticed some errors right after posting the content and made corrections. As you mentioned earlier, I will continue to observe and check. Thank you very much for your assistance!

 

[aru]

You must have a very busy network! You should enable the option for a/aaaa queries if you haven't already. I can't recall if i got round to making it configurable but you should also adjust how long records aee kept for. If i didn't make that an option, you would need to manually modify the script

I'm trying to shorten the retention days to see if it will improve. Thank you for your guidance.

Update: Oh, the minimum duration is already 30 days, it can't be reduced further...

 

[Jack Yaz]

I'm trying to shorten the retention days to see if it will improve. Thank you for your guidance.

View attachment 54434

Update: Oh, the minimum duration is already 30 days, it can't be reduced further...

You can possibly just edit a smaller number directly to the config file. If not i should be able to squeeze a small update out tonight

 

[aru]

You can possibly just edit a smaller number directly to the config file. If not i should be able to squeeze a small update out tonight

Thank you for your suggestion! I'll see if I can directly edit the configuration file. If not, I appreciate your offer to potentially release a small update tonight. Looking forward to it!

Update: If possible, could you consider adding a display for the database size on the WebUI? This would be helpful for adjusting the retention days based on the database size. Thank you for your consideration!