OpenTracks
OpenTracks copied to clipboard
OpenTracksApp
Deleting a recorded track takes too long
Describe the bug When I delete a track it takes up to several minutes until the track is deleted. Few days ago while I was deleting several tracks I got the following message from Android:
This issue I've already experienced several month ago. Yesterday, when I've deleted the last recorded track, it took around 50 seconds of time. Deleting several tracks while deleting of one track is in progress it could lead to phone freezes and the shown screenshot above.
Technical information
- Device: Fairphone 3
- OS: Android 11, build 8901.4.A.0010 release keys
- OpenTracks version: 4.0.3
One reason is that after each track deletion we are vacuuming the db. An optimization would be to do this only once at the end.
Adding more tracks to be deleted should not lead to an ANR. We have to check why this is happening.
I remember I once implemented a mass delete function for tracks to have the VACUUM triggered only one time after all tracks are deleted and not after each one. This got changed to individual deletion with the track deletion service which had a notification with progress bar. In PR https://github.com/OpenTracksApp/OpenTracks/pull/1358 I changed this to one SQL call and an indeterminate progress. So now the long running vacuum happens only once.
But I think we should find another way to do the VACUUM. Not automatically after each delete. What about an extra DB maintenance task via the settings? So the user can trigger it manually. When can also remember the user from time to time or after such a mass delete.
We discussed this about 2 years ago and I was too cautious. Vacuum every now and then is sufficient. Who needs space right? ;)
One solution: https://stackoverflow.com/questions/36126842/count-deleted-records-after-a-delete
The deletion count could be a solution. Regarding the space: according to SQLite the free space in the db file is not lost. SQLite will reuse it to store new data. It will just not shrink the file without vacuuming.
Another observation because @Saboe is reporting that it takes minutes. I realized it takes far longer in debug mode than in normal release. You don't run an OT debug build, do you?
Edit: my last observation might be wrong, could also be dependent on the amount of tracks I have in the different OT installations.
How do we want to implement the deletion count?
- remove the auto-vacuum on delete
- count all deletes
- check on startup of OT if "threshold" is reached and ask the user to perform a vacuum and reset count
What about:
- count all deletes (store in shared preference)
- after a deletion of tracks finished check if vacuum should be done and do it if necessary
Somehow, implementing user interactions feels like too much.
The deletion of tracks triggers cascading deletes in the trackpoint and marker table.
Even with a 10s recording interval, you create at least 360 trackpoints per hour. Additional BLE sensors may add more rows.
What about 5K to 10K deleted rows?
select changes() should return this value directly; no need to count.
https://www.sqlite.org/c3ref/changes.html
https://www.sqlite.org/lang_corefunc.html#changes
I just tried it select changes() returns the same number as the return value of the delete statement itself, only the number of track rows deleted.
The documentation says
Only changes made directly by the INSERT, UPDATE or DELETE statement are considered - auxiliary changes caused by [triggers](https://www.sqlite.org/lang_createtrigger.html), [foreign key actions](https://www.sqlite.org/foreignkeys.html#fk_actions) or [REPLACE](https://www.sqlite.org/lang_replace.html) constraint resolution are not counted.
Here we have a foreign key action which triggers the track points which are not counted.
I managed to solve it with select total_changes(): https://www.sqlite.org/lang_corefunc.html#total_changes
This returns all affected rows, but since opening the DB connection. So I had to perform a before and after to get the total_changes of this delete only.
Observation: my 13km Tracks have around 2300 Trackpoints. So a threshold of 10k is triggering the vacuum latest after 5 tracks deleted.
Another observation because @Saboe is reporting that it takes minutes. I realized it takes far longer in debug mode than in normal release. You don't run an OT debug build, do you?
I'm using the latest version which F-Droid offers, currently it's 4.0.6. Today I've made three deletion test. At the beginning I've 168 stored tracks (too much because deletion takes ...). The results: (1) The deletion of the latest track (cycling, 37 km, total time 2:20:14) took 55 seconds. (2) Deleted an older track with 0 km, 0:00:00 total time took 50 seconds. (3) In the tracklist I've selected 4 sequential tracks, all together with 0 km and 0:00:00 total time as the track before, it took ... I don't know exactly, I would say more than 10 minutes. Because there isn't any visual thing that shows the user that something is ongoing. I've checked the activity by scrolling through the list of tracks. When this was impossible (frozen) OpenTracks (OT) is still busy. After 10 minutes OT still didn't respond/react so I wrote an email to me with screenshot and all notes I've made. After that OT was ready (IMO).
Edit: my last observation might be wrong, could also be dependent on the amount of tracks I have in the different OT installations.
I've only one version installed but the oldest track is dated 2020-01-26.
@Saboe thanks for your tests
Because there isn't any visual thing that shows the user that something is ongoing.
OT shows a notification as long as the deletion is in progress.
With my proposed change OT will run this DB vacuum process at most once. But even 1 time 50 seconds for a vacuum is rather long for your device. But at least it will be far better than 10 minutes. :wink:
With the proposed change, you should get the 50s that you reported in (2) [if a vacuum gets triggered]; if not, it should be way faster.
The proposed change will improve the situation a lot, but the issue will not be completely tackled for @Saboe. I assume that the database is relative large (in terms of file size) and thus even deleting empty tracks (without actual trackpoints), makes vacuuming a really expensive operation. And I presume that the storage device is relatively slow as the whole file is likely completely rewritten (aka written onto another space on the disk). Cheap guess: @Saboe could it be that
- (if OpenTracks is install on the internal storage) your storage is almost full?
- (if OpenTracks is install on the external storage) the sd card is really slow (and the device internal hardware as well)?
Anyhow, can you check how large OpenTrack's database.db actually is?
@Saboe PS/ nice usage period for OpenTracks :)
OT shows a notification as long as the deletion is in progress.
I saw nothing except a black screen (and the 'app not responding' message) or an empty list which shows something like "Press here to start recording" (the first screen after installing OT) or the frozen list.
The proposed change will improve the situation a lot, but the issue will not be completely tackled for @Saboe.
I'm the only one who has this problem?
- if OpenTracks is install on the internal storage) your storage is almost full?
I suppose that OT is installed on the internal storage. The X-plore internal APK viewer shows among other informations the following:
Data dir: /data/usr/0/de.dennisguse.opentracks
Source dir: /data/app/~~J5-boJfF..............
Install location: Auto
The Android App info shows: Storage and Cache --> App size: 35.29 MB, User data: 72.25 MB, Cache: 217 kB, Total: 108 MB Store: App defaults
The internal storage usage is 49 GB out of 64 GB = 77% used. The external storage is only a kind of a backup drive, nothing more. Size 29.7 GB (32 GB), free 5.12 GB = 83% used.
Obviously I haven't any access to /data, my phone is currently not rooted so I can't find a file called database.db somewhere on the phone but I suppose that the size of user data gives a sufficient estimation how large the database is.
@Saboe then I withdraw my hypothesis (slow I/O might still be a problem, but nothing you can do anything about it). Anyhow, if you don't have any photos taken with OpenTracks, then the database is 72MB in size. Feels okayish. Have you experienced any other problems in the I/O performance of OpenTracks?
slow I/O might still be a problem, but nothing you can do anything about it
I don´t believe that my phone is very slow or very fast, it's a medium performance phone I would say. (CPU: Qualcomm Snapdragon 632 SDM632, 64 bit, octa-core / 4 GB RAM / Flash EEPROM: 64 GB, Samsung KMRH60014A-B614, eMMC 5.1)
if you don't have any photos taken with OpenTracks
Nope, I didn't know that I can take photos with OT. I've checked this right now but I can't find anything that has to to with photos inside OT.
Have you experienced any other problems in the I/O performance of OpenTracks?
That was the only one. Another issue I've seen in the past was the type of activity and language setting which (was or still) is confusing. Could be that this has to do with an older version of OT or the transition from an older version to a newer one where things are changed in this subject. I don't know exactly, I've to check it out.
I know the FP3 is decent hardware. Was running an S4 until last year ;) Let's see how this behaves for you. I will draft a new release today and you will get it via F-Droid next week!