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
