Dan,
We've killed the process (kill -9). Fired up sqlite3 again, closed it
down normally. The -wal files were removed.
Fired up sqlite3 again, turned off secure_delete, started to drop the
table again, reniced it down (long story to do with IOPS and our VPS
provider)
-wal file is empty, the -shm file is a steady 32768 bytes.
No idea if anything is actually happening now, but at least we don't
have a massive -wal file.
Suspect this could take the night (its 21:24 in London), so we'll detach
the screen session and come back later.
Many thanks for the help.
Rob
On 16 Jul 2018, at 21:17, Dan Kennedy wrote:
On 07/17/2018 03:12 AM, Rob Willett wrote:
Dan,
Thanks for the reply.
pragma secure_delete;
1
sqlite>
Which is a bit of a surprise as we have never seen it before. We had
to
look it up using your reference.
No idea why that is set (if 1 means it is).
Should we simply ctrl-C the deletion, turn secure_delete off and then
try again? My working assumption is that since there is a -wal file
we
are safe to do this.
That sounds fine. Without secure-delete, the wal file should be pretty
small.
You can always safely kill a process in the middle of an SQLite
transaction. So long as you don't do anything foolhardy like deleting
wal or journal files afterwards.
Dan.
Rob
On 16 Jul 2018, at 21:07, Dan Kennedy wrote:
On 07/17/2018 03:01 AM, rob.sql...@robertwillett.com wrote:
Hi,
We're doing a massive tidy on our database which is approx 50GB.
One table is approx 49GB of that 50GB which we need to delete as we
have
recorded the information in a far better format thats approx 99%
more
efficient. If only we had been this clever when we started....
We've just 'dropped' the table and were assuming that dropping the
table
would be quite quick. It's not. So far we've been waiting for 30
mins
and nothing has come back yet. We can see that the -wal file is
upto
2.5GB. We have this terrible feeling that it'll need to get to 49GB
or
so before the table gets dropped. We can just about handle that in
the
current filesystem.
We're now getting nervous about dropping this table. We had assumed
that
it would be a really quick and easy operation based on absolutely
no
checking whatsoever. When we looked on line all we could see was a
reference to a very, very old and outdated page
(https://sqlite.org/speed.html) which talks about speed and at the
bottom of that page the comments
"SQLite is slower than the other databases when it comes to
dropping
tables. This probably is because when SQLite drops a table, it has
to go
through and erase the records in the database file that deal with
that
table. MySQL and PostgreSQL, on the other hand, use separate files
to
represent each table so they can drop a table simply by deleting a
file,
which is much faster.
On the other hand, dropping tables is not a very common operation
so if
SQLite takes a little longer, that is not seen as a big problem."
Is this still the case, is it going to take a long time? If we
assume
that the table is 49GB then will we need to wait until the -wal
file is
at 49GB. By our estimates thats approximately 10 hours away.
Any help or idea or suggestions welcomed, but please be quick.
Is secure-delete turned on?
https://www.sqlite.org/pragma.html#pragma_secure_delete
Dan.
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
