Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Thank you all for the replies,
I will hack this problem one way or another after the hoildays and let you
know how it went.
In the mean time, I wish you all happy peaceful holidays, and a great New
Year!

Dinu



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[R Smith]

On 2017/12/20 11:01 AM, Dominique Devienne wrote:

"extremely efficient" is a bit exaggerated IMHO.

More space efficient definitely, see serial types 8 and 8 in [1], so only
the record's header section need to be consulted,
w/o the need to decode the whole record or decode the varint [2], thus more
cpu efficient too (since less to do), but the
record's page still needs to be paged in/out on reads/writes, and that's
like to dominate anyway. Just my guess though,


Not to mention that unless you remake the table (which the OP was 
insistent to avoid), that INT column will be at the very end of a list 
of much bigger columns, which would make reading it the single most 
inefficient column to read in the whole table, although the subsequent 
index that you are surely to add on that Column will undoubtedly be one 
of the fastest, second only to the rowid.



no hard data to back that up. Perhaps Ryan will measure the difference he
often does :) --DD


LOL, I would, but a quick glance at the importantometer for this result 
revealed that the exercise will be scheduled right between "Some other 
time" and "Never".    :)


Cheers!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dominique Devienne]

On Tue, Dec 19, 2017 at 6:05 PM, Simon Slavin  wrote:

> On 19 Dec 2017, at 4:15pm, Dinu  wrote:
> > 3) "Deleted" bit field - presumably the "soft delete" as you call it;
> If you do try this, the 'bit' column should be declared as INTEGER and the
> values stored should be 0 and 1.  SQLite is extremely efficient at
> storing/sorting these values.
>

"extremely efficient" is a bit exaggerated IMHO.

More space efficient definitely, see serial types 8 and 8 in [1], so only
the record's header section need to be consulted,
w/o the need to decode the whole record or decode the varint [2], thus more
cpu efficient too (since less to do), but the
record's page still needs to be paged in/out on reads/writes, and that's
like to dominate anyway. Just my guess though,
no hard data to back that up. Perhaps Ryan will measure the difference he
often does :) --DD

[1] https://www.sqlite.org/fileformat.html#record_format
[2] https://www.sqlite.org/fileformat2.html#varint
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[R Smith]

On 2017/12/19 6:15 PM, Dinu wrote:

2) Structure alterations; either drop table, or drop indexes : I am
reluctant to do this; my evangelical instinct tells me hacking the semantics
of life might lead to implosion of Earth :)


Oh the irony

Your instinct (like most such instincts) is simply wrong.

But don't take our word for it. Take a copy of the data somewhere else, 
construct some readers mimicking the in-use case (or whatever it takes 
to adequately convince yourself) and try the suggested fix.


Not only will it work, it will do so really fast and prove to be not 
only the best, but also the absolutely correct fix.
The good people here have even tried to build suggested scripts around 
your irrational fears, but in truth the simplest version would work the 
best, which is:


In a Transaction -
- Rename the Table,
- Recreate a new empty version of it,
- Copy the rows you want to keep.
- Drop the renamed table.
- Recreate any Indexes/Triggers.

A solution that might be slightly slower but much less complicated and 
still very fast is simply:

In a Transaction -
- Drop the Indexes/Triggers,
- Do the deletes,
- Recreate the Indexes/Triggers.

This will work 100% even while other readers are active (thanks to the 
WAL [1]) and without making them implode (perhaps pause a little bit[2], 
but not implode).


Why am I (and others here) so confident this will work? Because this is 
a fundamental design principle and indeed a requirement of a 
Transactional and Relational Database (Which SQLite is) - Plus we do it 
all the time very much relying on exactly those characteristics. There 
is no "hacking" going on in any of our suggestions, this is quite the 
mainstream way in which it works.


It's just like learning to ride a bicycle. Initially your fears feel 
justified and the physics seem impossible magic from where you watch the 
others... until that first day you find your balance and soar  Then 
pretty soon, you do it without hands on the steering wheel, just like 
the other cool kids.  :)


Cheers,
Ryan

[1] - It will work even with other Journal modes than WAL, it's just 
that the readers then will wait quite a bit more on the transaction to 
finish, whereas the WAL allows updates to not affect concurrent readers 
until a checkpoint/commit.


[2] - The file is several Gigabytes in size, it's never going to be 
instant, there WILL be some waiting, but it won't take very long - try 
do it at a quiet time though. You will get a feeling for the time-frame 
if you do the test-case thing.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

Dinu

Option 2, dropping and recreating the index with the transaction, seems to
be the way forward - I would suggest that if the author of SQlite (Dr Hipp)
has put this forward as a solution, as he did earlier in this thread,  then
it is probably a safe option and will not lead to an implosion of anything.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 19 December 2017 at 16:15, Dinu  wrote:

> Hi sub sk79,
> I have so far from this thread the following suggestions:
> 1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was
> supposed
> to be TRUNCATE semantics (DELETE FROM without WHERE) has the same
> performance as with WHERE.
> 2) Structure alterations; either drop table, or drop indexes : I am
> reluctant to do this; my evangelical instinct tells me hacking the
> semantics
> of life might lead to implosion of Earth :)
> 3) "Deleted" bit field - presumably the "soft delete" as you call it; I am
> analyzing this, but here the question is whether we include the bit in the
> indexes. If so, performing a heap of UPDATEs should be even more
> inefficient; if we don't include it in the index, the problem of the cost
> of
> filtering the row needs some analysis which I will probably do; the problem
> with this solution is that is has residual effects: we run some pretty
> complex queries against this table, with complicated joins and we already
> got some surprising execution plans that needed query rewriting. So with
> this "deleted" bit out of the index pool, we need to check various other
> queries to make sure they are still optimized to what we need.
>
> All this said and done, 3 hours to delete 15G of data seems atrocious even
> if you do it by standards resulted from generations of DOD and NSA
> inbreeding... so I'm still hopeful for some DB-related solution.
>
>
>
> --
> 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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 19 Dec 2017, at 4:15pm, Dinu  wrote:

> 3) "Deleted" bit field - presumably the "soft delete" as you call it;

If you do try this, the 'bit' column should be declared as INTEGER and the 
values stored should be 0 and 1.  SQLite is extremely efficient at 
storing/sorting these values.

Including this column in indexes should not slow things down much.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Hi sub sk79,
I have so far from this thread the following suggestions:
1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was supposed
to be TRUNCATE semantics (DELETE FROM without WHERE) has the same
performance as with WHERE.
2) Structure alterations; either drop table, or drop indexes : I am
reluctant to do this; my evangelical instinct tells me hacking the semantics
of life might lead to implosion of Earth :)
3) "Deleted" bit field - presumably the "soft delete" as you call it; I am
analyzing this, but here the question is whether we include the bit in the
indexes. If so, performing a heap of UPDATEs should be even more
inefficient; if we don't include it in the index, the problem of the cost of
filtering the row needs some analysis which I will probably do; the problem
with this solution is that is has residual effects: we run some pretty
complex queries against this table, with complicated joins and we already
got some surprising execution plans that needed query rewriting. So with
this "deleted" bit out of the index pool, we need to check various other
queries to make sure they are still optimized to what we need.

All this said and done, 3 hours to delete 15G of data seems atrocious even
if you do it by standards resulted from generations of DOD and NSA
inbreeding... so I'm still hopeful for some DB-related solution.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Subsk79]

On Dec 17, 2017, at 3:53 PM, Dinu Marina 
 Is there any way to fix/improve this in userland? 

I think the thread already has a viable solution but still if userland fix is 
an option: would a soft-delete based design work for you?

https://dba.stackexchange.com/questions/125431/hard-delete-vs-soft-delete-performance

-SK

___
*StepSqlite* enhanced-PL/SQL 
on SQLite and BerkeleyDB.
**Coming Soon**
 Be Done at the Speed of Lite! --->
 https://metatranz.com/stepsqlite/ShowSignUp?guestlogin=ON




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[R Smith]

On 2017/12/18 12:59 AM, Dinu wrote:
Thanks Richard, as mentioned earlier, any structure change is 
unacceptable due to concurrent reader clients. 


Within a transaction it matters none to those concurrent readers - 
that's the entire point of the WAL + Transaction. As long as you do all 
the Index dropping and recreating within the same transaction, no reader 
will be affected or any wiser.


From the point of the reader, there would be no actual schema change. 
It would be impossible to fail on one and succeed on the other (unless 
your actual CREATE INDEX clauses differ from their predecessors or it 
has been taught to fail when the schema version counter goes up, but 
that would be the worst design in DB history, so I doubt it).


Also, I assume you have copied the file (taken it off-line) and is 
experimenting on it, yes?


If so, set up some concurrent readers, and do the transaction Richard 
suggested. If that fails (and it won't) then come back and share with 
us, because then we will know some other Gremlin is afoot, but right now 
it seems you are hampered by the preconceived notions of what the DB is 
doing (or how schema changes will affect things on the inside) via 
Sherlockian deduction rather than actual scientific testing - This is 
not a judgement, we all do it, repeating tests at those sizes for hours 
and hours is not my favourite thing, but I've been wrong enough times 
and learned the hard way.



Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Simon Slavin-3 wrote
> DELETE FROM MyTable

We have tried it and DELETE FROM table (without any WHERE) behaves exactly
the same! I reiterate there are no FKs or triggers defined. So this is no
fix...



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Simon Slavin-3 wrote
> Once you’ve bust the cache things slow down.

I do realize that. However, not illinearly. Once I bust the cache,
throughput should drop X times and stay there. Here, the speed decreases
with the progress. Let me put it this way: say the cache size was 0. I would
expect the delete to work very slow, but near-linear with the # of rows
being deleted or the progress of deleting them. Here the performance
dramatically and constantly decreases with growing of the WAL file. You can
literally see how every M of throughput is slower than the previous :) I am
trying to find out why. For me it's very important for the migration
process.


Simon Slavin-3 wrote
> Have you tried these things ?  Did the time taken improve or get worse ?

Not yet, we will probably implement what you proposed (it's been suggested
before). But if I can find out the reason behind the nonlinear DELETE
behavior, it would still help greatly.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 18 Dec 2017, at 12:28pm, Dinu  wrote:

> Actually I realized that the DB page size is 1K. Is this bad? I tried to run
> the pragma query with 1M pages, to amount to the same 1G; there seems to be
> a dramatic improvement in throughput at the beginning of the query,

The problem is not the page size, or the total number of pages.  The problem is 
the total data size (e.g. number of pages times page size).

> but it
> quickly succombs to nonlinear slow-down-to-a-drag nonetheless.
> 
> Richard, leaving alternate strategies aside (DROP, TRUNCATE etc.), what
> could be the reason behing this non-linear delete behavior?

This is not behaviour built into the source code for SQLite.  What you are 
seeing is the result of the size of a cache.  If the changes fit within a 
certain cache size.  Once you’ve bust the cache things slow down.

Which cache is being bust can be harder to figure out.  There are at least 
three involved, and only one of them is under SQLite control.

In this thread we’ve given you several things to check.  The latest seem to be:

1) Change your journal mode from WAL to PERSIST (or vice versa) and see if that 
helps.

2) Try this:

BEGIN IMMEDIATE
create a temporary table with the same columns as MyTable, but no indexes
copy the rows you need to keep to the temporary table
DELETE FROM MyTable
INSERT INTO MyTable (SELECT * FROM TempTable)
COMMIT

Have you tried these things ?  Did the time taken improve or get worse ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 18 Dec 2017, at 10:03am, Dinu  wrote:

> I honestly don't see how in any DB system the client process would not crash
> if the index it's running a curson on were to be removed.

SQLite doesn’t run cursors.  There are no cursor commands in the SQLite API.

SQLite does not lock tables or indexes.  If anything needs locking (for 
example, if a transaction starts to make a change) then the entire database is 
locked.  If a connection regains access to its data (because another connection 
has released its lock) it does not assume nothing has changed.  This cannot be 
changed without a radical rethink and rewrite  of SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Richard Hipp-3 wrote
> DROP, CREATE, and ALTER are transactional in SQLite, just like DELETE,
> INSERT, and UPDATE.  This has always been the case, and always shall
> be.

Thanks! But still, isn't this incompatible with any lower-than-DB-level
transaction locking mechanism? I.E. should you ever have row-level locking,
this absolutely needs a persistent, cross-connection ROWID index; while any
FOR UPDATE locking semantics need persistent gap-locking indexes... Just a
thought for the distant future, I realize it's not a discussion to have now
:)


Keith Medcalf wrote
> pragma cache_size=262144; -- 1 GB page cache

Actually I realized that the DB page size is 1K. Is this bad? I tried to run
the pragma query with 1M pages, to amount to the same 1G; there seems to be
a dramatic improvement in throughput at the beginning of the query, but it
quickly succombs to nonlinear slow-down-to-a-drag nonetheless.

Richard, leaving alternate strategies aside (DROP, TRUNCATE etc.), what
could be the reason behing this non-linear delete behavior? Why does it slow
down to a grinding halt? It would be tremendously helping for me to know; we
are in the process of migrating more than just this table to SQLite so
knowledge about the inner workings of SQLite helps us tremendously. Thanks!



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Richard Hipp]

On 12/18/17, Dinu  wrote:
> Rowan Worth-2 wrote
>> The entire point of SQL transactions is to implement this magic!
>
> I beg to differ; you proposed:
>> (1) BEGIN TRANSACTION
>> > (2) DROP all indexes from the table
> This is by no means valid SQL semantics; in all RDBBMS I've worked with,
> structure changes' relation to a transaction is undefined at best.

DROP, CREATE, and ALTER are transactional in SQLite, just like DELETE,
INSERT, and UPDATE.  This has always been the case, and always shall
be.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Rowan Worth-2 wrote
> The entire point of SQL transactions is to implement this magic!

I beg to differ; you proposed:
> (1) BEGIN TRANSACTION 
> > (2) DROP all indexes from the table 
This is by no means valid SQL semantics; in all RDBBMS I've worked with,
structure changes' relation to a transaction is undefined at best. Even if
it works now, there's no guarantee the "BEGIN; DROP" behavior will be
consistent any time in the future. So in repect to this, the alternative of
copying to a different table, TRUNCATE, copy back, looks much more
semantically acceptable.

Rowan Worth-2 wrote
> WAL has different concurrency characteristics.

Yeap, it's supposed to do just this, keep readers from starving until a
write is complete; the WAL flush works quite well it seems, keeping the HDD
at a respectable 100% and so the WAL flush will take less than 30s.
Populating the table, on the other hand, takes much longer. 



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Keith Medcalf wrote
> 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

Thanks, I will try that!




--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Rowan Worth]

On 18 December 2017 at 18:03, Dinu  wrote:

> Rowan Worth-2 wrote
> > I'm not sure what you're worried about? Dropping and recreating identical
> > indices within a transaction won't cause a visible structure change to
> > concurrent readers -- that's the point of a transaction.
>
> I honestly don't see how in any DB system the client process would not
> crash
> if the index it's running a curson on were to be removed. Even if SQLite
> were to pull this magic out of the hat,


The entire point of SQL transactions is to implement this magic! All other
clients see the state either before or after the transaction.


> starving client processes for the
> lack of an index (a full scan query would probably take in excess of 30s)
> would quickly pile up the clients to the point where one would have to kill
> them anyway.
>

The other clients won't run without an index. They won't have their current
index ripped out from under them either.

They _will_ have to wait for the deletion transaction to finish up. But
that's an issue regardless - once the delete transaction's memory cache
spills, it will have exclusive access to the database until the transaction
COMMI-- ah but wait, I'm talking about rollback journal mode, because
that's what I'm familiar with. WAL has different concurrency
characteristics. I'll leave the details to someone else, but I'm 100% sure
you still get atomic transactions.


> So with this in mind, I'm really not looking for a barbaric fix to this,
> I'm
> more of tryng to understand the problem and find a viable, semantically
> stable solution (and maybe trigger some improvements in SQLite, if there's
> a
> system bug).
>

Seeing what effect dropping/recreating the indices has on the overall speed
_IS_ a step in understanding the problem, no?
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

Not sure how relevant it might be, but what page size is the DB set to and
what is the average size of a record?


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 December 2017 at 10:03, Dinu  wrote:

> Rowan Worth-2 wrote
> > I'm not sure what you're worried about? Dropping and recreating identical
> > indices within a transaction won't cause a visible structure change to
> > concurrent readers -- that's the point of a transaction.
>
> I honestly don't see how in any DB system the client process would not
> crash
> if the index it's running a curson on were to be removed. Even if SQLite
> were to pull this magic out of the hat, starving client processes for the
> lack of an index (a full scan query would probably take in excess of 30s)
> would quickly pile up the clients to the point where one would have to kill
> them anyway.
> So with this in mind, I'm really not looking for a barbaric fix to this,
> I'm
> more of tryng to understand the problem and find a viable, semantically
> stable solution (and maybe trigger some improvements in SQLite, if there's
> a
> system bug).
>
>
>
>
> --
> 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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Rowan Worth-2 wrote
> I'm not sure what you're worried about? Dropping and recreating identical
> indices within a transaction won't cause a visible structure change to
> concurrent readers -- that's the point of a transaction.

I honestly don't see how in any DB system the client process would not crash
if the index it's running a curson on were to be removed. Even if SQLite
were to pull this magic out of the hat, starving client processes for the
lack of an index (a full scan query would probably take in excess of 30s)
would quickly pile up the clients to the point where one would have to kill
them anyway.
So with this in mind, I'm really not looking for a barbaric fix to this, I'm
more of tryng to understand the problem and find a viable, semantically
stable solution (and maybe trigger some improvements in SQLite, if there's a
system bug).




--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Rowan Worth]

On 18 December 2017 at 06:59, Dinu  wrote:

> Richard Hipp-3 wrote
> > Can you try this:
>
> (1) BEGIN TRANSACTION
> > (2) DROP all indexes from the table
> > (3) Do the DELETE operation
> > (4) CREATE all indexes again
> > (5) COMMIT
> Thanks Richard, as mentioned earlier, any structure change is unacceptable
> due to concurrent reader clients.
>

I'm not sure what you're worried about? Dropping and recreating identical
indices within a transaction won't cause a visible structure change to
concurrent readers -- that's the point of a transaction. Unless perhaps
those readers are s old they're using the deprecated sqlite3_prepare
rather than prepare_v2, and fall over when encountering SQLITE_SCHEMA?

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Keith Medcalf]

Hmmm.  4.6 GB in the Filesystem cache (the Level 2 cache), and only 2 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

Re: [sqlite] Atomic DELETE index optimisation?

[Keith Medcalf]

Ok, just checking.  The database I was referring to was an Historian Collector 
so the amount of data that it needed to maintain was time limited so it was 
more or less a FIFO by tag.

---
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:58
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Atomic DELETE index optimisation?
>
>Keith Medcalf wrote
>> I had a similar problem with a multi-terabyte database once upon a
>time.
>
>Na, they are not a single-time use rows, otherwise I'd have used a
>FIFO :)
>Every now and then, a large portion of the table becomes obsolete by
>external factors.
>
>
>
>--
>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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 17 Dec 2017, at 10:57pm, Dinu  wrote:

> Na, they are not a single-time use rows, otherwise I'd have used a FIFO :)
> Every now and then, a large portion of the table becomes obsolete by
> external factors.

Are you issuing one DELETE command with a WHERE clause which deletes lots of 
rows or are you issuing lots of DELETE commands ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Richard Hipp-3 wrote
> Can you try this:

Thanks Richard, as mentioned earlier, any structure change is unacceptable
due to concurrent reader clients.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Keith Medcalf wrote
> I had a similar problem with a multi-terabyte database once upon a time.  

Na, they are not a single-time use rows, otherwise I'd have used a FIFO :)
Every now and then, a large portion of the table becomes obsolete by
external factors.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

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

Re: [sqlite] Atomic DELETE index optimisation?

[Keith Medcalf]

I had a similar problem with a multi-terabyte database once upon a time.  

The solution was simply to run the DELETE every time you insert, thus keeping 
the database continuously "pruned" by continuous removal of a small number of 
records rather than periodically trying to get rid of a lot all at once.

---
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 Keith Medcalf
>Sent: Sunday, 17 December, 2017 15:43
>To: SQLite mailing list
>Subject: Re: [sqlite] Atomic DELETE index optimisation?
>
>
>(1) Since you are in WAL, have you set pragma SYNCHRONOUS=1 (NORMAL)?
>(Don't know if it will make any difference in your particular case).
>
>(2) The page_cache size makes a HUGE difference.  Modified pages are
>ONLY flushed to the WAL file when they overflow the cache.  The fewer
>modified pages there are to flush, the less I/O is performed.  The
>default pagecache is about 2 pages which is not much at all.  If
>you do not see I/O to the drive it is probably going to the OS
>Filesystem cache.  This is still slower than going directly to the
>pagecache to access the tree data.  This will appear as CPU usage
>rather than I/O usage.  Since it started out writing LOTS of modified
>pages which slowly became less and less indicates that the pagecache
>is too small and that the B-Tree structure is continuously being
>reloaded.
>
>What is the version of SQLite?
>The Operating System and its bitedness (32/64)?
>The bitedness (32/64) of the shell?
>How much RAM is in the machine?
>How much RAM is the process using (both the VM commit size, and the
>Working Set size)?
>Have you set any pragma's other than JOURNAL_MODE=WAL?
>Do you have any Foreign Keys defined on or against the table you are
>deleting from?
>If so, is foreign key enforcement turned on?
>If so, are there recursive foreign keys?
>Are the proper indexes defined for any foreign keys?  On both parent
>and child?
>Are there any ON DELETE triggers?
>
>The simplest fix might very well be to compile your own shell with
>UPDATE DELETE LIMIT enabled, and delete the records in smaller
>batches by putting a LIMIT on the delete statement and running it
>until all the records are deleted.  Or perhaps dropping all the
>indexes, deleting the records, then re-creating the indexes.
>
>---
>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:02
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: Re: [sqlite] Atomic DELETE index optimisation?
>>
>>Ok, so to address the size of the problem properly:
>>
>>We have ~32M records out of which we are deleting about ~24M.
>>The raw size of the DB is about 20G out of which 15G goes away.
>Under
>>these
>>circumstances any page cache becomes irrelevant.
>>The delete operation, which is a single delete run from the sqlite3
>>tool, is
>>running for 3 hours now.
>>The WAL size is in excess of 9G atm.
>>The WAL size is growing ever slower (was about 10M/s, now it's 1M
>>every 2s,
>>slowing down). This indicates to me a non-linear process that I can
>>link
>>only to the B-trees, it's the only non-linear component I can think
>>of that
>>could cause this slowing-down-to-a-drag. The CPU is capped up badly,
>>the HDD
>>is at idle level so this also hints to the same issue.
>>
>>In reply to your remarks:
>>- I understand the B-trees need to be modified. However, if you
>>prune,
>>(maybe rebalance), write-to-disk every node at a time, that is
>hugely
>>stressful and inefficient when you are pruning half of a 32M nodes
>>tree.
>>Since the operation is atomic, the indexes could be updated one time
>>and one
>>time only.
>>
>>So, what to do? I think this delete may never end... by the time it
>>took to
>>write this reply, the WAL grow has succombed to 1M every 4s.
>>
>>
>>
>>--
>>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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Richard Hipp]

On 12/17/17, Dinu  wrote:
>
> We have ~32M records out of which we are deleting about ~24M.

Can you try this:

(1) BEGIN TRANSACTION
(2) DROP all indexes from the table
(3) Do the DELETE operation
(4) CREATE all indexes again
(5) COMMIT
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Keith Medcalf]

(1) Since you are in WAL, have you set pragma SYNCHRONOUS=1 (NORMAL)? (Don't 
know if it will make any difference in your particular case).

(2) The page_cache size makes a HUGE difference.  Modified pages are ONLY 
flushed to the WAL file when they overflow the cache.  The fewer modified pages 
there are to flush, the less I/O is performed.  The default pagecache is about 
2 pages which is not much at all.  If you do not see I/O to the drive it is 
probably going to the OS Filesystem cache.  This is still slower than going 
directly to the pagecache to access the tree data.  This will appear as CPU 
usage rather than I/O usage.  Since it started out writing LOTS of modified 
pages which slowly became less and less indicates that the pagecache is too 
small and that the B-Tree structure is continuously being reloaded.

What is the version of SQLite?  
The Operating System and its bitedness (32/64)?  
The bitedness (32/64) of the shell?  
How much RAM is in the machine?  
How much RAM is the process using (both the VM commit size, and the Working Set 
size)?
Have you set any pragma's other than JOURNAL_MODE=WAL?
Do you have any Foreign Keys defined on or against the table you are deleting 
from?
If so, is foreign key enforcement turned on?
If so, are there recursive foreign keys?
Are the proper indexes defined for any foreign keys?  On both parent and child?
Are there any ON DELETE triggers?

The simplest fix might very well be to compile your own shell with UPDATE 
DELETE LIMIT enabled, and delete the records in smaller batches by putting a 
LIMIT on the delete statement and running it until all the records are deleted. 
 Or perhaps dropping all the indexes, deleting the records, then re-creating 
the indexes.

---
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:02
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Atomic DELETE index optimisation?
>
>Ok, so to address the size of the problem properly:
>
>We have ~32M records out of which we are deleting about ~24M.
>The raw size of the DB is about 20G out of which 15G goes away. Under
>these
>circumstances any page cache becomes irrelevant.
>The delete operation, which is a single delete run from the sqlite3
>tool, is
>running for 3 hours now.
>The WAL size is in excess of 9G atm.
>The WAL size is growing ever slower (was about 10M/s, now it's 1M
>every 2s,
>slowing down). This indicates to me a non-linear process that I can
>link
>only to the B-trees, it's the only non-linear component I can think
>of that
>could cause this slowing-down-to-a-drag. The CPU is capped up badly,
>the HDD
>is at idle level so this also hints to the same issue.
>
>In reply to your remarks:
>- I understand the B-trees need to be modified. However, if you
>prune,
>(maybe rebalance), write-to-disk every node at a time, that is hugely
>stressful and inefficient when you are pruning half of a 32M nodes
>tree.
>Since the operation is atomic, the indexes could be updated one time
>and one
>time only.
>
>So, what to do? I think this delete may never end... by the time it
>took to
>write this reply, the WAL grow has succombed to 1M every 4s.
>
>
>
>--
>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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

INSERTs are taking constant time as they should, the DELETE is quite
obviously working is a blatantly non-linear progress.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Thanks, it's something worth trying.
However, no, that can't be the only reason. Populating scriptically with the
same data takes less than 10 minutes, including side processing... So the
disproportion between insert and delete performance is huge.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 17 Dec 2017, at 10:18pm, Dinu  wrote:

> For the table swap operation, that is out of the question, the DB is running
> on a live system that has multiple clients. Foreign keys or not, any
> structure change crashes all clients (DB structure has changed). Too high a
> price to pay for a delete thay may happen routinely.

Okay.  In that case you might try this.  The reason it may be fast is that 
SQLite has DROP without a WHERE clause optimized.  It happens very quickly.

BEGIN IMMEDIATE
create a temporary table with the same columns as MyTable, but no indexes
copy the rows you need to keep to the temporary table
DELETE FROM MyTable
copy all rows from the temporary table back to the original table
COMMIT

As with the last suggestion, the only reason I think these my be faster is that 
you are keeping less than half the rows in the original table.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

For the table swap operation, that is out of the question, the DB is running
on a live system that has multiple clients. Foreign keys or not, any
structure change crashes all clients (DB structure has changed). Too high a
price to pay for a delete thay may happen routinely.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

By "one time only", I mean in my understanding, the way most DBs do on a
DELETE is this: cache the ROWIDs while deleting data rows from the main and
from the indexes, then when all ROWIDS are explored, sort the ROWID stream,
and prune the trees from a sorted stream. This is both highly efficient
(just like inserts, deletes of already ordered records are very efficient)
and highly parallelizable.



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 17 Dec 2017, at 10:01pm, Dinu  wrote:

> The CPU is capped up badly, the HDD
> is at idle level so this also hints to the same issue.

Apparently your program/OS is trying to do everything in cache.  This may be 
self-defeating for this operation.

Your description of what’s happening is reasonable, and suggests SQLite is 
working the way it should do.

> We have ~32M records out of which we are deleting about ~24M.

It might be faster to create a new table with the rows which survive, then 
delete the whole old table.

1) Rename old table.
2) CREATE new table.
3) Copy the rows which survive from the old table.  Depending on your selection 
criteria you might be able to use

INSERT INTO MyTable (SELECT FROM OldTable WHERE survive=1)

4) DROP OldTable
5) Create the indexes you need on MyTable

If you have FOREIGN KEYs it may be problematic to do this.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Clemens Ladisch]

Dinu wrote:
> The WAL size is in excess of 9G atm.
> The WAL size is growing ever slower (was about 10M/s, now it's 1M every 2s,
> slowing down). This indicates to me a non-linear process that I can link
> only to the B-trees, it's the only non-linear component I can think of that
> could cause this slowing-down-to-a-drag.

The WAL file contains the new version(s) of any modified page; for every page
access, the database has to search in the WAL for the newest version.

Consider using DELETE/TRUNCATE/PERSIST journal mode instead; any changed
pages are copied to the journal before being modified, so there is never
more than one copy of a page in the journal.  And during normal operation,
the database never has to look at the contents of an old page.

Alternatively, try copying all the other rows to a new table, and dropping
the old one.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu]

Ok, so to address the size of the problem properly:

We have ~32M records out of which we are deleting about ~24M.
The raw size of the DB is about 20G out of which 15G goes away. Under these
circumstances any page cache becomes irrelevant.
The delete operation, which is a single delete run from the sqlite3 tool, is
running for 3 hours now.
The WAL size is in excess of 9G atm.
The WAL size is growing ever slower (was about 10M/s, now it's 1M every 2s,
slowing down). This indicates to me a non-linear process that I can link
only to the B-trees, it's the only non-linear component I can think of that
could cause this slowing-down-to-a-drag. The CPU is capped up badly, the HDD
is at idle level so this also hints to the same issue.

In reply to your remarks:
- I understand the B-trees need to be modified. However, if you prune,
(maybe rebalance), write-to-disk every node at a time, that is hugely
stressful and inefficient when you are pruning half of a 32M nodes tree.
Since the operation is atomic, the indexes could be updated one time and one
time only.

So, what to do? I think this delete may never end... by the time it took to
write this reply, the WAL grow has succombed to 1M every 4s. 



--
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

Re: [sqlite] Atomic DELETE index optimisation?

[Simon Slavin]

On 17 Dec 2017, at 8:53pm, Dinu Marina  wrote:

> It became apparent to me from performance measurements that the DELETE 
> operation is very slow, when operating on large recordsets with indexes 
> involved. My deduction is that SQLite updates the indexes for every deleted 
> row, which in painstakingly slow...
> 
> Since the DELETE should be atomic, the index update could also be atomic, and 
> a mass pruning of the index tree is hugely faster than individual removals.
> 
> My question is: is my deduction correct? Is there any way to fix/improve this 
> in userland? Or are there prospects for this to be improved in SQLite in the 
> foreseeable future?

The data for the table is held in one tree, occupying one set of pages.
The data for each index is held in a separate tree, occupying its own set of 
pages.
So if you have N indexes on the table, N+1 sets of pages need to be updated.

If a DELETE deletes more than one row, then SQLite does the caching you’d 
expect when working out which pages to update.  SQLite does this efficiently 
and work went into optimizing for speed.

If the timing of DELETE statements is important to you, use journal mode of 
PERSIST, MEMORY or WAL.  You might want to compare PERSIST and WAL and find out 
which performs better for you.  Remember that journal mode is saved with the 
database: you only need to set it once for the file, and SQLite will retrieve 
the setting when it reopens the file.

If you are doing more than one DELETE at a time, enclose them all in a 
transaction:

BEGIN
DELETE …
DELETE …
COMM|T

This can be far faster than individual commands, since the 'dirty' pages are 
written back once at the end of the transaction rather than the end of each 
command.

On 17 Dec 2017, at 9:06pm, Dinu Marina  wrote:

> Update: we are also using WAL; I have noticed during a huge delete, the WAL 
> also grows huge. Could this also be a reason for slowness, that SQLite 
> duplicates every block that hosts a delete? Is there any way to work around 
> this?

Correct.  While a transaction with DELETE is being carried out, SQLite must 
temporarily store both the old and the new versions of all pages affected.  
This is to allow recover if your computer crashes or loses power before the 
transaction is complete.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Keith Medcalf]

You page cache is probably too small to contain the working set of database 
pages that require modification.  There should be no difference between 
"deleting" and "inserting".  Both require changes to all applicable B+ and B- 
Trees.

Though of course, it can be VERY slow if you have SECURE DELETE enabled since 
that requires overwriting the row rather than merely removing it from the 
applicable trees.

The WAL file contains images of changed pages AFTER they are changed.  So 
firstly the fact that you see pages added to the WAL file means the changed 
pages cannot be maintained in the page cache (it is too small).  Secondly it 
can mean that you have the database and WAL file stored on slow media (ie, a 
360 RPM floppy disk rather than a 2600 MB/s NVMe disk).

One of the resources on the computer is being consumed 100%.  Once you figure 
out what that resource is, you can address the issue.

---
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 Marina
>Sent: Sunday, 17 December, 2017 14:06
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Atomic DELETE index optimisation?
>
>Update: we are also using WAL; I have noticed during a huge delete,
>the
>WAL also grows huge. Could this also be a reason for slowness, that
>SQLite duplicates every block that hosts a delete? Is there any way
>to
>work around this?
>
>
>On 17.12.2017 22:53, Dinu Marina wrote:
>> Hi all,
>> It became apparent to me from performance measurements that the
>DELETE
>> operation is very slow, when operating on large recordsets with
>> indexes involved. My deduction is that SQLite updates the indexes
>for
>> every deleted row, which in painstakingly slow...
>>
>> Since the DELETE should be atomic, the index update could also be
>> atomic, and a mass pruning of the index tree is hugely faster than
>> individual removals.
>>
>> My question is: is my deduction correct? Is there any way to
>> fix/improve this in userland? Or are there prospects for this to be
>> improved in SQLite in the foreseeable future?
>>
>> Thanks,
>> Dinu
>>
>
>___
>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

Re: [sqlite] Atomic DELETE index optimisation?

[Dinu Marina]

Update: we are also using WAL; I have noticed during a huge delete, the 
WAL also grows huge. Could this also be a reason for slowness, that 
SQLite duplicates every block that hosts a delete? Is there any way to 
work around this?



On 17.12.2017 22:53, Dinu Marina wrote:

Hi all,
It became apparent to me from performance measurements that the DELETE 
operation is very slow, when operating on large recordsets with 
indexes involved. My deduction is that SQLite updates the indexes for 
every deleted row, which in painstakingly slow...


Since the DELETE should be atomic, the index update could also be 
atomic, and a mass pruning of the index tree is hugely faster than 
individual removals.


My question is: is my deduction correct? Is there any way to 
fix/improve this in userland? Or are there prospects for this to be 
improved in SQLite in the foreseeable future?


Thanks,
Dinu



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users