Hmmm. 4.6 GB in the Filesystem cache (the Level 2 cache), and only 20000 pages (78 MB or thereabouts) in the process pagecache (the Level 1 cache). And with only 4.6 GB in the Level 2 cache, the I/O rate drops to almost zero. If you "moved" that memory (or some of it) from Level 2 to Level 1 you would increase performance tremendously.
pragma cache_size=262144; -- 1 GB page cache Will increase the page cache to 1 GB. Might not be big enough, but should be much better. --- 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:55 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Atomic DELETE index optimisation? > >SQLite 3.19.3 >CentOS 7 (64 bit) >RAM: 6G total, 4.4G buff/cache, 0.6G unused >Sqlite memory: 133M RES, 0 SWAP, 0 DIRTY - don't know if it's >relevant >anymore, the query seems to have entered into another execution >phase, it >looks like now it's flushing the WAL. >No other PRAGMA >No FKs, no triggers. > > > > > > >-- >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