I guess you might be runing into the effect described here http://sqlite.org/wal.html :
" Very large write transactions. A checkpoint can only complete when no other transactions are running, which means the WAL file cannot be reset in the middle of a write transaction. So a large change to a large database might result in a large WAL file. The WAL file will be checkpointed once the write transaction completes (assuming there are no other readers blocking it) but in the meantime, the file can grow very big. As of SQLite version 3.11.0 (2016-02-15), the WAL file for a single transaction should be proportional in size to the transaction itself. Pages that are changed by the transaction should only be written into the WAL file once. However, with older versions of SQLite, the same page might be written into the WAL file multiple times if the transaction grows larger than the page cache. " Since your transaction deletes about 75% of your records, it is reasonable to assume that every single page will be modified. If you are runnning a version prior to 3.11.0, the same page may be written to the WAL file more than once after the page cache is overwhelmed by the transaction size. If you are running an SQLite version 3.11.0 or newer, only pages that are changed for the first time in the transaction are copied to the WAL file, thus extending it. While progressing the transaction, the rate of "newly changed" to "changed again" pages will shift towards re-reading and rewriting the WAL file copy of a page; this will tend to slow down the rate at which the WAL file is growing, even at a constant delete rate, until every page has been updated at least once, and then stop growing until all the remaining deletes have been processed. Thus, your underlying notion that delete rate corresponds to WAL file growth rate is plain wrong. To measure delete rates, you would have to register an sqlite3_update_hook() function. I expect you would find that the delete rate increases as a function of decreasing record count, quite to the contrary of what you are "measuring". -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dinu Gesendet: Montag, 18. Dezember 2017 14:06 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Atomic DELETE index optimisation? Simon Slavin-3 wrote > Once you’ve bust the cache things slow down. I do realize that. However, not illinearly. Once I bust the cache, throughput should drop X times and stay there. Here, the speed decreases with the progress. Let me put it this way: say the cache size was 0. I would expect the delete to work very slow, but near-linear with the # of rows being deleted or the progress of deleting them. Here the performance dramatically and constantly decreases with growing of the WAL file. You can literally see how every M of throughput is slower than the previous :) I am trying to find out why. For me it's very important for the migration process. Simon Slavin-3 wrote > Have you tried these things ? Did the time taken improve or get worse ? Not yet, we will probably implement what you proposed (it's been suggested before). But if I can find out the reason behind the nonlinear DELETE behavior, it would still help greatly. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
