2016-04-19 15:27 GMT+02:00 Olivier Mascia <om at integral.be>:
> > Le 19 avr. 2016 ? 14:49, Cecil Westerhof <cldwesterhof at gmail.com> a
> ?crit :
> >
> > SECURE_DELETE
>
> Are you aware of the net effect of SQLITE_SECURE_DELETE?
>
> https://www.sqlite.org/compile.html#secure_delete
>
> The documentation talks about a "small performance penalty", yet on very
> large tables, especially if overflowing by large the cache size, the amount
> of IO for overwriting deleted data with zeroes can become substantial. Not
> to say this is the sole reason for your issue, but it should have an impact
> on it.
>
> You should run "PRAGMA secure_delete=0" before doing this bulk deletion
> and see how it impacts your timings.
>
?I have some interesting metrics. Certainly the
PRAGMA SECURE_DELETE=0;
has significant influence. But that makes the DELETE before the DROP
only having a bigger influence.
I am not hindered by any deep knowledge with low level database IO, so it
is possible that not everything I say makes sense when you have this
knowledge. Just educate me. :-)
First of all DELETE before DROP with SECURE_DELETE looks to be always a
very good option. Travelling home yesterday and doing other stuff it took
7 minutes, while without the DELETE it took 14 minutes. At the moment I
have not reproduced it again and sadly I do not have the data from that
run. :'-( If I manage to do it again, I will share the data.
As a programmer I am lazy, so I have made a Bash script to make life
easier. I have attached it with two logs. The first is when I ran the
script without doing something else, the second is while Iceweasel was
running. (Firefox on Debian.)
On a clean system:
SECURE_DELETE=1, DROP ONLY 17 minutes
SECURE_DELETE=1, DELETE and DROP 14 minutes
SECURE_DELETE=0, DROP only 4? minutes
SECURE_DELETE=0, DELETE and DROP ? minute
With Iceweasel running:
SECURE_DELETE=1, DROP ONLY 45 minutes
SECURE_DELETE=1, DELETE and DROP 41 minutes
SECURE_DELETE=0, DROP only 11 minutes
SECURE_DELETE=0, DELETE and DROP ? minute
I am baffled. Still DELETE before DROP is a lot more efficient. And it
looks that it is not bothered when other programs are running (most of
the time). I would think that a DROP should take the least time: it only
has to mark a complete table to be free space. So why does it take
significantly more time? And why is the DELETE before DROP not
influenced by running other programs while DROP only is?
Also: I understand that SECURE_DELETE=1 takes more time, but the
difference seems excessive. What could be happening here?
The SECURE_DELETE=1 certainly is something to warn people for. (In the
current implementation.) By the way: I agree that it is default on.
?
--
Cecil Westerhof
