On Fri, Dec 25, 2015 at 12:51:55PM +0000, Simon Slavin wrote: > > On 25 Dec 2015, at 12:39pm, Valentin Davydov <sqlite-user at soi.spb.ru> > wrote: > > > It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. > > Presently it is generally useless for indexed databases which don't fit > > entirely in RAM (and which usually need checking much strongly than > > smaller ones). > > Valentin, could you expand on that ?
As far as I understand, INTEGRITY_CHECK simply iterates over the records (of tables and indices) one by one in some arbitrary order. So, if the database is too big to fit in the available memory (sqlite's own cache, system file cache etc), then each iteration implies a random seek on disk(s), or even several ones in some scenarios. So, check of a few terabytes database with some tens billions of records and a dozen of indices would take more than 10^11 disk operations of more than 10 milliseconds each. That is, years. My wish is to involve some intermediate level of cache (perhaps bitmaps?) which could reside in reasonable amount of memory (say, < 1% of the total database size) and obviate the need to read each page more than once. So, things would speed up in proportion of page size to record size. > PRAGMA schema.foreign_key_check; > > ? Does it have the same problem as far as you're concerned ? I don't have access to a huge databases with foreign keys now. Valentin Davydov.
