Simon,
No, we knew about normalisation, the database is normalised, that part
of the design hasn't changed in years.
The reasons for the massive reduction in database size is that we worked
out how to handle repeating lines of data that change just enough that
we thought they needed to be ke
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? M
Simon,
No, we knew about normalisation, the database is normalised, that part
of the design hasn't changed in years.
The reasons for the massive reduction in database size is that we worked
out how to handle repeating lines of data that change just enough that
we thought they needed to be ke
On 17 Jul 2018, at 1:10pm, R Smith wrote:
> What kind of data did you store (maybe some examples if possible) that you
> could condense it by ~99% like that?
I think that the OP's organisation discovered the 'relational' part of RDBMS
and implemented normalisation.
To Rob Willett: Ryan Smith'
On 2018/07/17 1:20 PM, Rob Willett wrote:
Thanks for this. You make some interesting points about cache hits and
misses. Up until now, we hadn't seen a massive performance hit, whilst
some database operations didn't work as fast as we would like them to,
nothing works as fast we would like.
On 17 Jul 2018, at 12:20pm, Rob Willett wrote:
> Do you have any pointers to stuff we can read up on? We don't understand your
> comment "SQLite spent that whole time accessing your 50GB database file in an
> apparently random order." and would like to try and get more information
> about it.
Simon,
Thanks for this. You make some interesting points about cache hits and
misses. Up until now, we hadn't seen a massive performance hit, whilst
some database operations didn't work as fast as we would like them to,
nothing works as fast we would like.
I'm not familiar with these issues
On 17 Jul 2018, at 8:37am, Rob Willett wrote:
> I suspect that part of the issue is the VPS provider we use has a rate
> limiter on IOPS which is not normally an issue for us, but that might have
> slowed it down somewhat. However I don't think that it would have slowed it
> down by hours.
Ac
The top table finally finished around 07:00 this morning. It took 9.5
hours to drop the circa 190M rows.
I suspect that part of the issue is the VPS provider we use has a rate
limiter on IOPS which is not normally an issue for us, but that might
have slowed it down somewhat. However I don't th
Richard,
Thanks for the comprehensive update.
We appreciate that there are tradeoffs and that dropping a table is a
rarer operation than most others. The upside of the Sqlite design is
that we treat a database as a single file which we copy around with
ease. You cannot do that with many other
Jay,
I think your approach would have been quicker. Ten hours so far and it's
still deleting the table :(
Rob
On 17 Jul 2018, at 2:16, Jay Kreibich wrote:
On Jul 16, 2018, at 3:01 PM, rob.sql...@robertwillett.com wrote:
Hi,
We're doing a massive tidy on our database which is approx 50GB.
> On Jul 16, 2018, at 3:01 PM, 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
On 7/16/18, Rob Willett wrote:
>
> It does look as if one of sqlite's weaknesses is dropping very, very
> large tables.
>
Right. If every table were stored in a separate file, a DROP TABLE
could be translated to a relatively fast unlink(). But then a
database would be a directory full of files,
Mmm It's still taking an awful long time, though the -wal file is
very small.
It does look as if one of sqlite's weaknesses is dropping very, very
large tables.
Oh well, lets let it run through the night.
Rob
On 16 Jul 2018, at 21:25, Rob Willett wrote:
Dan,
We've killed the process
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, th
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, tur
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? M
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 h
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 'dropp
19 matches
Mail list logo
