Once the data has been loaded from the server and inserted into the local database, it appears we get into a bad state: - The WAL is 16MB big due to a big transaction (WAL limit is set to 512KB but can't be satisfied due to transaction size) - The WAL doesn't get truncated because we don't ask for it to be truncated, and because we never close the database, because there's no application shutdown on Android - During startup, we run a small write transaction (to set the version to the same version it's already at) which triggers a checkpoint - Odd circumstances make it so that this checkpoint re-applies the WAL changes even when they've already been applied before Profile: https://share.firefox.dev/3MuCOuJ It's doing 16MB of reads to build the WAL index, and then 16MB read + 16MB write to re-apply the WAL changes. I've attempted to create a more-or-less faithful reduced testcase: https://github.com/mstange/sqlite-testcase-repeated-wal-application I believe we just need the following remedies: - Run `PRAGMA wal_checkpoint(TRUNCATE)` after big write transactions - Potentially split the big insert transaction into multiple smaller transactions, so that the WAL never grows to 16MB in the first place - Potentially void an unnecessary write during DB opening when the version is already the current version
Bug 2005613 Comment 0 Edit History
Note: The actual edited comment in the bug view page will always show the original commenter’s name and original timestamp.
Once the data has been loaded from the server and inserted into the local database, it appears we get into a bad state: - The WAL is 16MB big due to a big transaction (WAL limit is set to 512KB but can't be satisfied due to transaction size) - The WAL doesn't get truncated because we don't ask for it to be truncated, and because we never close the database, because there's no application shutdown on Android - During startup, we run a small write transaction (to set the version to the same version it's already at) which triggers a checkpoint - Odd circumstances make it so that this checkpoint re-applies the WAL changes even when they've already been applied before Profile: https://share.firefox.dev/3MuCOuJ It's doing 16MB of reads to build the WAL index, and then 16MB read + 16MB write to re-apply the WAL changes. I've attempted to create a more-or-less faithful reduced testcase: https://github.com/mstange/sqlite-testcase-repeated-wal-application I believe we just need the following remedies: - Run `PRAGMA wal_checkpoint(TRUNCATE)` after big write transactions - Potentially split the big insert transaction into multiple smaller transactions, so that the WAL never grows to 16MB in the first place - Potentially avoid an unnecessary write during DB opening when the version is already the current version
Once the data has been loaded from the server and inserted into the local database, it appears we get into a bad state: - The WAL is 16MB big due to a big transaction (WAL limit is set to 512KB but can't be satisfied due to transaction size) - The WAL doesn't get truncated because we don't ask for it to be truncated, and because we never close the database, because there's no application shutdown on Android - During startup, we run a small write transaction (to set the version to the same version it's already at) which triggers a checkpoint - Odd circumstances make it so that this checkpoint re-applies the WAL changes even when they've already been applied before Profile: https://share.firefox.dev/3MuCOuJ It's doing 16MB of reads to build the WAL index, and then 16MB read + 16MB write to re-apply the WAL changes. I've attempted to create a more-or-less faithful reduced testcase: https://github.com/mstange/sqlite-testcase-repeated-wal-application I believe we just need the following remedies: - Run `PRAGMA wal_checkpoint(TRUNCATE)` after big write transactions - Potentially split the big insert transaction into multiple smaller transactions, so that the WAL never grows to 16MB in the first place - Potentially avoid an unnecessary write during DB opening when the version is already the current version - Maybe also truncate after opening the database, to fix up existing databases in this state
Once the data has been loaded from the server and inserted into the local database, it appears we get into a bad state (permanently): - The WAL is 16MB big due to a big transaction (WAL limit is set to 512KB but can't be satisfied due to transaction size) - The WAL doesn't get truncated because we don't ask for it to be truncated, and because we never close the database, because there's no application shutdown on Android - During startup, we run a small write transaction (to set the version to the same version it's already at) which triggers a checkpoint - Odd circumstances make it so that this checkpoint re-applies the WAL changes even when they've already been applied before Profile: https://share.firefox.dev/3MuCOuJ It's doing 16MB of reads to build the WAL index, and then 16MB read + 16MB write to re-apply the WAL changes. I've attempted to create a more-or-less faithful reduced testcase: https://github.com/mstange/sqlite-testcase-repeated-wal-application I believe we just need the following remedies: - Run `PRAGMA wal_checkpoint(TRUNCATE)` after big write transactions - Potentially split the big insert transaction into multiple smaller transactions, so that the WAL never grows to 16MB in the first place - Potentially avoid an unnecessary write during DB opening when the version is already the current version - Maybe also truncate after opening the database, to fix up existing databases in this state
Once the data has been loaded from the server and inserted into the local database, it appears we get into a bad state (permanently): - The WAL is 16MB big due to a big transaction (WAL limit is set to 512KB but can't be satisfied due to transaction size) - The WAL doesn't get truncated because we don't ask for it to be truncated, and because we never close the database, because there's no application shutdown on Android - During startup, we run a small write transaction (to set the version to the same version it's already at) which triggers a checkpoint - Odd circumstances make it so that this checkpoint re-applies the WAL changes even when they've already been applied before, during every subsequent startup Profile: https://share.firefox.dev/3MuCOuJ It's doing 16MB of reads to build the WAL index, and then 16MB read + 16MB write to re-apply the WAL changes. I've attempted to create a more-or-less faithful reduced testcase: https://github.com/mstange/sqlite-testcase-repeated-wal-application I believe we just need the following remedies: - Run `PRAGMA wal_checkpoint(TRUNCATE)` after big write transactions - Potentially split the big insert transaction into multiple smaller transactions, so that the WAL never grows to 16MB in the first place - Potentially avoid an unnecessary write during DB opening when the version is already the current version - Maybe also truncate after opening the database, to fix up existing databases in this state