(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
