You page cache is probably too small to contain the working set of database pages that require modification. There should be no difference between "deleting" and "inserting". Both require changes to all applicable B+ and B- Trees.
Though of course, it can be VERY slow if you have SECURE DELETE enabled since that requires overwriting the row rather than merely removing it from the applicable trees. The WAL file contains images of changed pages AFTER they are changed. So firstly the fact that you see pages added to the WAL file means the changed pages cannot be maintained in the page cache (it is too small). Secondly it can mean that you have the database and WAL file stored on slow media (ie, a 360 RPM floppy disk rather than a 2600 MB/s NVMe disk). One of the resources on the computer is being consumed 100%. Once you figure out what that resource is, you can address the issue. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dinu Marina >Sent: Sunday, 17 December, 2017 14:06 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Atomic DELETE index optimisation? > >Update: we are also using WAL; I have noticed during a huge delete, >the >WAL also grows huge. Could this also be a reason for slowness, that >SQLite duplicates every block that hosts a delete? Is there any way >to >work around this? > > >On 17.12.2017 22:53, Dinu Marina wrote: >> Hi all, >> It became apparent to me from performance measurements that the >DELETE >> operation is very slow, when operating on large recordsets with >> indexes involved. My deduction is that SQLite updates the indexes >for >> every deleted row, which in painstakingly slow... >> >> Since the DELETE should be atomic, the index update could also be >> atomic, and a mass pruning of the index tree is hugely faster than >> individual removals. >> >> My question is: is my deduction correct? Is there any way to >> fix/improve this in userland? Or are there prospects for this to be >> improved in SQLite in the foreseeable future? >> >> Thanks, >> Dinu >> > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
