Can the database be purged?

[Clubba]

My db has grown to 300mb making it much slower when calling data, can anyone tell me how to purge it to remove daily reports after I've checked them?
PS I'm using linux Dragon OS

[Faramir]

The database is SQLite so you need to use SQLite queries to manipulate the database. Before you work on the database make sure VRS is shut down if the database-writer plugin is used and make a copy of the database as a whole for safety!
I don't know if VRS writes data to different the tables but the Flights table often is the biggest. You can delete flights from that table or even complete sessions.
With the DB Browser for SQLite tool you can view the tables in the database. If you have deleted items from the database use the VACUUM command to rebuild the tables and 'defragment' them.

[Anmer]

My Basestation. sqb is over 3GB.  Loads pretty fast and no performance degradation once it's been loaded.

[Clubba]

I'm a bit perplexed about the whole issue. I've used Htop to see that the PC isn't under any real load and the feed incoming/outgoing wasn't heavy either.

When reading the daily report the report pages were good load speed but when using DBeditor data was taking up to 10 seconds to appear before I could edit, some pages were instant to appear but not many.
Was a bit frustrating as I wanted to get to the last page which would take me a good 3 hours normally but it was so slow on fetch, I just had to give up.

I'll have a peek with Sqlite browser to see if there is any issue or corruption within.

Thanks guys 

[rikgale]

You're running this on a Pi?

There are no CPU optimisations that VRS can make use of on a Pi and VRS on a Pi is rather slow doing certian things on a pi. E.g. database editing.
That is why there are a number of users who make use of pre-populated DBs, so they have to do a lot less editing.

rikgale
https://github.com/rikgale/VRSData/blob/main/BaseStation.zip  (works with rikgale's side views)

S92Driver
https://radarspotting.com/forum/index.php?action=tportal;sa=download;dl=cat52 (works with S92 drivers side views)

[Clubba]

Hi Rik,
This particular VRS is running on a Dell Mini 16gb RAM i5 CPU it's basically a standalone feed in/out server, pi is direct to FA and to the Dell mini.

Pi is good only running 4%CPU.

The Dell is running at less than 20% load on any core avg is less than 10% & around 17% when I call an edit, RAM is low use too no more than 3gb max load.
Swap file is isn't being used at any stage.

It seems the issue lies within the DB & DB editor fetch calls & only when the report pages are being viewed, if calling for data from the 'Tracking List' then all is good.
I'm only using 200 lines per page in the report so as not to cache too much info at once.
Also, I only call reports after midnight when feed data flow is minimal.

>>>What I have noticed in the DB is that the line count number and aircraft number are not the same so some lines are missing and a rebuild/repair command hasn't been issued to clean them up.
It looks like 5 lines are out of sync as the line count ends in 39 but aircraft number ends in 44, I havent found where it goes out yet as i haven't got that far.
I think/hope that once I figure out SQLite browser and it's commands then re-alignment should remove the delayed calls.
I'm using S92s DB & images on the box in question and I've filled in quite a few empty lines myself over the last few weeks using DBeditor.

[Clubba]

PS also spotted a few duplications where the stored HEX is different for the same craft, no doubt there is a fair few of those.

[s92driver]

PS also spotted a few duplications where the stored HEX is different for the same craft, no doubt there is a fair few of those.

If you are using my published SQB, then the duplications are actually a couple of thousand miscodes that are included.


Steve

[Clubba]

Hi Steve,
I did wonder if they were identical duplicates at 1st then I saw the Hex and I knew research was afoot
I'll enjoy looking up the details, I'm nerdy like that.

[s92driver]

Hi Steve,
I did wonder if they were identical duplicates at 1st then I saw the Hex and I knew research was afoot
I'll enjoy looking up the details, I'm nerdy like that.

Nerdy is good.   

[rikgale]

My DBEditor is slow sometimes, but i kinda got used to it.
Even on a Trigkey G4 N100.
Even slower when I am accessing from the office

[Clubba]

My DBEditor is slow sometimes, but i kinda got used to it.
Even on a Trigkey G4 N100.
Even slower when I am accessing from the office

I'm a pedant and impatient though, drives me  mad(der)