I had a similar problem with a multi-terabyte database once upon a time.
The solution was simply to run the DELETE every time you insert, thus keeping the database continuously "pruned" by continuous removal of a small number of records rather than periodically trying to get rid of a lot all at once. --- 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 Keith Medcalf >Sent: Sunday, 17 December, 2017 15:43 >To: SQLite mailing list >Subject: Re: [sqlite] Atomic DELETE index optimisation? > > >(1) Since you are in WAL, have you set pragma SYNCHRONOUS=1 (NORMAL)? >(Don't know if it will make any difference in your particular case). > >(2) The page_cache size makes a HUGE difference. Modified pages are >ONLY flushed to the WAL file when they overflow the cache. The fewer >modified pages there are to flush, the less I/O is performed. The >default pagecache is about 20000 pages which is not much at all. If >you do not see I/O to the drive it is probably going to the OS >Filesystem cache. This is still slower than going directly to the >pagecache to access the tree data. This will appear as CPU usage >rather than I/O usage. Since it started out writing LOTS of modified >pages which slowly became less and less indicates that the pagecache >is too small and that the B-Tree structure is continuously being >reloaded. > >What is the version of SQLite? >The Operating System and its bitedness (32/64)? >The bitedness (32/64) of the shell? >How much RAM is in the machine? >How much RAM is the process using (both the VM commit size, and the >Working Set size)? >Have you set any pragma's other than JOURNAL_MODE=WAL? >Do you have any Foreign Keys defined on or against the table you are >deleting from? >If so, is foreign key enforcement turned on? >If so, are there recursive foreign keys? >Are the proper indexes defined for any foreign keys? On both parent >and child? >Are there any ON DELETE triggers? > >The simplest fix might very well be to compile your own shell with >UPDATE DELETE LIMIT enabled, and delete the records in smaller >batches by putting a LIMIT on the delete statement and running it >until all the records are deleted. Or perhaps dropping all the >indexes, deleting the records, then re-creating the indexes. > >--- >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 >>Sent: Sunday, 17 December, 2017 15:02 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: Re: [sqlite] Atomic DELETE index optimisation? >> >>Ok, so to address the size of the problem properly: >> >>We have ~32M records out of which we are deleting about ~24M. >>The raw size of the DB is about 20G out of which 15G goes away. >Under >>these >>circumstances any page cache becomes irrelevant. >>The delete operation, which is a single delete run from the sqlite3 >>tool, is >>running for 3 hours now. >>The WAL size is in excess of 9G atm. >>The WAL size is growing ever slower (was about 10M/s, now it's 1M >>every 2s, >>slowing down). This indicates to me a non-linear process that I can >>link >>only to the B-trees, it's the only non-linear component I can think >>of that >>could cause this slowing-down-to-a-drag. The CPU is capped up badly, >>the HDD >>is at idle level so this also hints to the same issue. >> >>In reply to your remarks: >>- I understand the B-trees need to be modified. However, if you >>prune, >>(maybe rebalance), write-to-disk every node at a time, that is >hugely >>stressful and inefficient when you are pruning half of a 32M nodes >>tree. >>Since the operation is atomic, the indexes could be updated one time >>and one >>time only. >> >>So, what to do? I think this delete may never end... by the time it >>took to >>write this reply, the WAL grow has succombed to 1M every 4s. >> >> >> >>-- >>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 > > > >_______________________________________________ >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
