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

Reply via email to