High IO on sqlite3

[Fever_Wits]

Hello,

I have been watching the last few days after I upgraded to 19.1 that sqlite3 is doing a high IO.
/usr/local/bin/sqlite3 /var/netflow/src_addr_details_086400.sqlite.fix

Somebody have an idea what's going on.
This makes redundancy over SSD about 1GB in 10 sec.

  9 Power_On_Hours 0x0032 100 100,000 Old_age Always - 5532
230 Media_Wearout_Indicator 0x0032 100 100,000 Old_age Always - 0x2f2b0e002f2b
232 Available_Reservd_Space 0x0033 100 100 005 Pre-fail Always - 100
233 NAND_GB_Written_TLC 0x0032 100 100 --- Old_age Always - 8328
234 NAND_GB_Written_SLC 0x0032 100 100,000 Old_age Always - 92355
241 Total_Host_GB_Written 0x0030 100 100,000 Old_age Offline - 33387
242 Total_Host_GB_Read 0x0030 100 100,000 Old_age Offline - 12507


If he continues to write. Soon I will have to change it.
I have enabled the /var and /tmp options in the ram disk at the moment.

[mimugmail]

Did you try to repair the DB? If not you can also reset it.

[Fever_Wits]

I tried to reset it, did not help.
I tried to fix it, did not help.
I deleted it and after I restart the server IO starts high.
A temporary solution is to use a disk in the RAM memory.

[franco]

That’s not a temporary solution.

The write load is probably what it is given your network traffic you want to have reported.


Cheers,
Franco

[Fever_Wits]

Hello,

I learned the hard way that it will be a permanent solution.

I executed the following command: opnsense-update -fp, rebooted, and then repaired the database. After 12 hours waiting to fix src_addr_details_086400.sqlite and many overwritten gigabytes. I realized that working in RAM disk is the best solution in my case
After rebooting the system and working in the disk. Database repair took about 1 to 2 hours.

After 2 days of work. I noticed that she was using a swap partition. After 2 days of work. I noticed that she was using a swap partition. I have 8GB RAM. I have free RAM.

Is this normal ?

Best regards