Re: [sqlite] DELETE extremely slow (.expert command)
> It would/should have told you immediately that you needed those two > additional indexes, I should think. Unfortunately not. Someone told me about ".expert" some time ago and it's indeed helpful for me because I never know what indexes to create and why. But for this database everything seemed ok: SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> pragma foreign_keys; 1 sqlite> .expert sqlite> delete from dataset; (no new indexes) (null) sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow (.expert command)
On Friday, 1 November, 2019 14:52, Thomas Kurz wrote: >That's it!!! You're a genius! Thank you very very much! >Run Time: real 8.290 user 3.25 sys 1.906250 I believe that the SQLite3 CLI has a command .expert that you can use to turn on assistance to tell you if the next executed SQL statement will benefit from the creation of an index. I forgot all about this command or even when it was added (it is an experimental feature). It would/should have told you immediately that you needed those two additional indexes, I should think. eg: sqlite> create table x(a,b); sqlite> create table y(b,c); sqlite> .expert sqlite> select a, x.b, c from x,y where x.b == y.b; CREATE INDEX y_idx_0062 ON y(b); SCAN TABLE x (~1048576 rows) SEARCH TABLE y USING INDEX y_idx_0062 (b=?) (~10 rows) sqlite> >- Original Message - >From: Keith Medcalf >To: SQLite mailing list >Sent: Friday, November 1, 2019, 18:07:51 >Subject: [sqlite] DELETE extremely slow > >One of your triggers requires and index on item(nameid) and there is no >index on item(nameid). >Hence it is doing a table scan to find the rows matching this trigger. >That is why the plan has multiple "SCAN item" in it. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On 1 Nov 2019, at 10:11pm, Keith Medcalf wrote: > Just run ".schema" or ".schema --indent" But there's no need, since I think the problem has been solved, right ? It came down to lack of indexes. Which is consistent with the times and EXPLAIN QUERY PLANs posted. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Friday, 1 November, 2019 16:04, Thomas Kurtz wrote: > The database schema is not a secret. If it helps, I can post it, that's > no problem. Is it enough to run ".dump" on a database without data? Just run ".schema" or ".schema --indent" which will output only the schema definitions and not the data (if any). The extra --indent will attempt to format the output so it is more readable if you didn't format the SQL for readability when creating the database. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
The database schema is not a secret. If it helps, I can post it, that's no problem. Is it enough to run ".dump" on a database without data? - Original Message - From: Keith Medcalf To: SQLite mailing list Sent: Friday, November 1, 2019, 22:57:02 Subject: [sqlite] DELETE extremely slow On Friday, 1 November, 2019 15:12, Simon Slavin wrote: >So the slow-down in the DELETE FROM command is caused by a TRIGGER, but >there are no TRIGGERs on DELETE ? I don't understand that. Can someone >explain, please ? The code indicates that they are AFTER DELETE so presumably they are triggers, though they are fkey.abort and .abort, so they may be also the result of the implementation of ON DELETE conditions, which are internally implemented by sub-programs and cannot really be distinguished from triggers without seeing the full schema. You might be able to tell if explain comments were enabled at compile time (SQLITE_ENABLE_EXPLAIN_COMMENTS), though I can't really say whether the extra comments would make it obvious or not without being able to see the actual schema or doing a more detailed analysis of the VDBE code. Foreign key constraints are processed in-line since the default condition is to abort/rollback the entire statement only if the fkey violation count is not zero at the end of the statement execution. If you have ON conditions attached to the foreign key constraint, they are implemented by a sub-program called after the processing of each row rather than inline. This means, for example, that if you have ON DELETE RESTRICT specified against a foreign key constraint that a statement which has no violations will effectively be "half as efficient" as one that does not have ON DELETE RESTRICT because extra constraint will be implemented as a sub-program that will re-check the constraint during statement execution so that an immediate ABORT can be raised ... or at least I think that is how it is implemented. The same applies for ON ... CASCADE or ON ... SET NULL which must be implemented as a sub-program run per-row and is not easily distinguishable from a trigger which implements the same functionality. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ 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] DELETE extremely slow
On Friday, 1 November, 2019 15:12, Simon Slavin wrote: >So the slow-down in the DELETE FROM command is caused by a TRIGGER, but >there are no TRIGGERs on DELETE ? I don't understand that. Can someone >explain, please ? The code indicates that they are AFTER DELETE so presumably they are triggers, though they are fkey.abort and .abort, so they may be also the result of the implementation of ON DELETE conditions, which are internally implemented by sub-programs and cannot really be distinguished from triggers without seeing the full schema. You might be able to tell if explain comments were enabled at compile time (SQLITE_ENABLE_EXPLAIN_COMMENTS), though I can't really say whether the extra comments would make it obvious or not without being able to see the actual schema or doing a more detailed analysis of the VDBE code. Foreign key constraints are processed in-line since the default condition is to abort/rollback the entire statement only if the fkey violation count is not zero at the end of the statement execution. If you have ON conditions attached to the foreign key constraint, they are implemented by a sub-program called after the processing of each row rather than inline. This means, for example, that if you have ON DELETE RESTRICT specified against a foreign key constraint that a statement which has no violations will effectively be "half as efficient" as one that does not have ON DELETE RESTRICT because extra constraint will be implemented as a sub-program that will re-check the constraint during statement execution so that an immediate ABORT can be raised ... or at least I think that is how it is implemented. The same applies for ON ... CASCADE or ON ... SET NULL which must be implemented as a sub-program run per-row and is not easily distinguishable from a trigger which implements the same functionality. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
So the slow-down in the DELETE FROM command is caused by a TRIGGER, but there are no TRIGGERs on DELETE ? I don't understand that. Can someone explain, please ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Thursday, 31 October, 2019 16:54, Thomas Kurz : >I did it again, same file: >SQLite version 3.30.1 2019-10-10 20:19:45 >Enter ".help" for usage hints. >sqlite> pragma foreign_keys=on; >sqlite> .timer on >sqlite> delete from dataset; >Run Time: real 5249.891 user 2412.812500 sys 2606.531250 Well, this indicates only 5% idle time, so it is much better. However, the process is spending a *HUGE* amount of time in the OS (sys), about half the elapsed time. This indicates that I/O thrashing is still occurring. In the CLI the command ".stats on" will output a bunch of execution statistics after executing a command. Included in those are the Page cache hits/misses/writes/spills. The hit rate (hits/(hits+misses)) is probably very low and the miss rate (misses/(hits+misses)) very high indicating I/O thrashing to the underlying OS (which will be reflected by increased sys time). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
Yes, there are triggers. I didn't post them because I didn't see any correlation to the DELETE query as they are only INSERT and UPDATE triggers: CREATE TRIGGER item_inserted AFTER INSERT ON item BEGIN UPDATE trace SET typeid = (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id = new.traceid; END CREATE TRIGGER item_modified AFTER UPDATE OF traceid, freq, value, noiseflag ON item BEGIN UPDATE trace SET typeid = (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id IN (old.traceid, new.traceid); END CREATE TRIGGER meta_global_insert INSTEAD OF INSERT ON meta_global FOR EACH ROW BEGIN INSERT INTO metadata (parameter, value) VALUES (NEW.parameter, NEW.value); END CREATE TRIGGER meta_global_update INSTEAD OF UPDATE OF parameter, value ON meta_global BEGIN UPDATE metadata SET parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND datasetid IS NULL; END CREATE TRIGGER meta_dataset_insert INSTEAD OF INSERT ON meta_dataset FOR EACH ROW WHEN NEW.datasetid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid) VALUES (NEW.parameter, NEW.value, NEW.datasetid); END CREATE TRIGGER meta_dataset_update INSTEAD OF UPDATE OF datasetid, parameter, value ON meta_dataset WHEN NEW.datasetid IS NOT NULL BEGIN UPDATE metadata SET datasetid=NEW.datasetid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND traceid IS NULL; END CREATE TRIGGER meta_trace_insert INSTEAD OF INSERT ON meta_trace FOR EACH ROW WHEN NEW.traceid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid, traceid) SELECT NEW.parameter, NEW.value, datasetid, NEW.traceid FROM trace WHERE id=NEW.traceid; END CREATE TRIGGER meta_trace_update INSTEAD OF UPDATE OF traceid, parameter, value ON meta_trace WHEN NEW.traceid IS NOT NULL BEGIN UPDATE metadata SET datasetid=(SELECT datasetid FROM trace WHERE id=NEW.traceid), traceid=NEW.traceid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id AND itemid IS NULL; END CREATE TRIGGER meta_item_insert INSTEAD OF INSERT ON meta_item FOR EACH ROW WHEN NEW.itemid IS NOT NULL BEGIN INSERT INTO metadata (parameter, value, datasetid, traceid, traceid) SELECT NEW.parameter, NEW.value, t.datasetid, t.id, NEW.itemid FROM itemid i LEFT JOIN trace t ON i.traceid=t.id WHERE id=NEW.itemid; END CREATE TRIGGER meta_item_update INSTEAD OF UPDATE OF itemid, parameter, value ON meta_item WHEN NEW.itemid IS NOT NULL BEGIN UPDATE metadata SET datasetid=(SELECT t.datasetid FROM item i LEFT JOIN trace t ON i.traceid=t.id WHERE t.id=NEW.itemid), traceid=(SELECT traceid FROM item WHERE id=NEW.itemid), itemid=NEW.itemid, parameter=NEW.parameter, value=NEW.value WHERE id=OLD.id; END - Original Message - From: David Raymond To: SQLite mailing list Sent: Friday, November 1, 2019, 17:27:27 Subject: [sqlite] DELETE extremely slow Looks like you have triggers going on there. You only gave us the table and index definitions. What are the on delete triggers you have? -Original Message- From: sqlite-users On Behalf Of Thomas Kurz Sent: Thursday, October 31, 2019 6:54 PM To: SQLite mailing list Subject: Re: [sqlite] DELETE extremely slow > Do you have memory to run this in? Have you increased the sqlite cache size > because that looks (to me) an awful lot like I/O thrashing ... Sorry to disappoint you, Keith and Simon, but in all cases the database file has been located on a ramdisk. It's only about 50 MB in size, btw. > SQLite runs on the local machine. While MariaDB is client-server, so the > delete effectively runs on the server. Yes and no. Of curse, I had MariaDB run on the same machine, and its data files had been stored on the same ramdisk. > How much of the 88 minutes is "waiting" time? I did it again, same file: SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> .timer on sqlite> delete from dataset; Run Time: real 5249.891 user 2412.812500 sys 2606.531250 > You haven't shown the "explain query plan" Keith asked for Is the beginning of it enough or do I have to repeat the entire DELETE? Here is the output which I canceled after some seconds: QUERY PLAN |--SCAN TABLE dataset |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SCAN TABLE item |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) |--
Re: [sqlite] DELETE extremely slow
That's it!!! You're a genius! Thank you very very much! Run Time: real 8.290 user 3.25 sys 1.906250 - Original Message - From: Keith Medcalf To: SQLite mailing list Sent: Friday, November 1, 2019, 18:07:51 Subject: [sqlite] DELETE extremely slow One of your triggers requires and index on item(nameid) and there is no index on item(nameid). Hence it is doing a table scan to find the rows matching this trigger. That is why the plan has multiple "SCAN item" in it. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ 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] DELETE extremely slow
One of your triggers requires and index on item(nameid) and there is no index on item(nameid). Hence it is doing a table scan to find the rows matching this trigger. That is why the plan has multiple "SCAN item" in it. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
One of your delete triggers is causing a table scan of table item. Multiple scans of table item, for each row of dataset. From the code it looks like it is scanning for a match on "nameid". And there is not index on item(nameid ...) ... -- 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 On >Behalf Of Thomas Kurz >Sent: Thursday, 31 October, 2019 16:54 >To: SQLite mailing list >Subject: Re: [sqlite] DELETE extremely slow > >> Do you have memory to run this in? Have you increased the sqlite >cache size because that looks (to me) an awful lot like I/O thrashing ... > >Sorry to disappoint you, Keith and Simon, but in all cases the database >file has been located on a ramdisk. It's only about 50 MB in size, btw. > >> SQLite runs on the local machine. While MariaDB is client-server, so >the delete effectively runs on the server. > >Yes and no. Of curse, I had MariaDB run on the same machine, and its data >files had been stored on the same ramdisk. > >> How much of the 88 minutes is "waiting" time? > >I did it again, same file: > >SQLite version 3.30.1 2019-10-10 20:19:45 >Enter ".help" for usage hints. >sqlite> pragma foreign_keys=on; >sqlite> .timer on >sqlite> delete from dataset; >Run Time: real 5249.891 user 2412.812500 sys 2606.531250 > >> You haven't shown the "explain query plan" Keith asked for > >Is the beginning of it enough or do I have to repeat the entire DELETE? >Here is the output which I canceled after some seconds: > >QUERY PLAN >|--SCAN TABLE dataset >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 >(datasetid=?) >|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 >(datasetid=?) >|--SCAN TABLE item >|--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) >|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) >|--SCAN TABLE item >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) >|--SCAN TABLE item >|--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) >|--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) >`--SCAN TABLE item >addr opcode p1p2p3p4 p5 comment > - - -- - >0 Init 0 30000 Start at 30 >1 Null 0 1 000 r[1]=NULL >2 OpenRead 0 7 0 0 00 root=7 iDb=0; >dataset >3 Rewind 0 7 000 >4 Rowid 0 2 000 r[2]=rowid >5 RowSetAdd 1 2 000 >rowset(1)=r[2] >6 Next 0 4 001 >7 OpenWrite 0 7 0 3 00 root=7 iDb=0; >dataset >8 RowSetRead 1 29200 >r[2]=rowset(1) >9 NotExists 0 282 1 00 intkey=r[2] >10 Copy 2 3 000 r[3]=r[2] >11 OpenRead 2 170 k(3,,,)02 root=17 >iDb=0; metadata_idx_04 >12 IsNull 3 18000 if r[3]==NULL >goto 18 >13 Affinity 3 1 0 C 00 >affinity(r[3]) >14 SeekGE 2 183 1 00 key=r[3] >15IdxGT 2 183 1 00 key=r[3] >16FkCounter 0 1 000 fkctr[0]+=1 >17 Next 2 15000 >18 OpenRead 4 13869 0 k(2,,) 02 root=13869 >iDb=0; trace_idx_03 >19 IsNull 3 25000 if r[3]==NULL >goto 25 >20 Affinity 3 1 0 C 00 >affinity(r[3]) >21 SeekGE 4 253 1 00 key=r[3] >22IdxGT 4 253 1 00 key=r[3] >23FkCounter 0 1 000 fkctr[0]+=1 >24 Next 4 22100 >25 Delete 0 1 0 dataset00 >26 Program3 0 9 program00 Call: >fkey.abort >27 Program3 0 10program00 Call: >fkey.abort >28Goto
Re: [sqlite] DELETE extremely slow
Looks like you have triggers going on there. You only gave us the table and index definitions. What are the on delete triggers you have? -Original Message- From: sqlite-users On Behalf Of Thomas Kurz Sent: Thursday, October 31, 2019 6:54 PM To: SQLite mailing list Subject: Re: [sqlite] DELETE extremely slow > Do you have memory to run this in? Have you increased the sqlite cache size > because that looks (to me) an awful lot like I/O thrashing ... Sorry to disappoint you, Keith and Simon, but in all cases the database file has been located on a ramdisk. It's only about 50 MB in size, btw. > SQLite runs on the local machine. While MariaDB is client-server, so the > delete effectively runs on the server. Yes and no. Of curse, I had MariaDB run on the same machine, and its data files had been stored on the same ramdisk. > How much of the 88 minutes is "waiting" time? I did it again, same file: SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> .timer on sqlite> delete from dataset; Run Time: real 5249.891 user 2412.812500 sys 2606.531250 > You haven't shown the "explain query plan" Keith asked for Is the beginning of it enough or do I have to repeat the entire DELETE? Here is the output which I canceled after some seconds: QUERY PLAN |--SCAN TABLE dataset |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SCAN TABLE item |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) `--SCAN TABLE item addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 30000 Start at 30 1 Null 0 1 000 r[1]=NULL 2 OpenRead 0 7 0 0 00 root=7 iDb=0; dataset 3 Rewind 0 7 000 4 Rowid 0 2 000 r[2]=rowid 5 RowSetAdd 1 2 000 rowset(1)=r[2] 6 Next 0 4 001 7 OpenWrite 0 7 0 3 00 root=7 iDb=0; dataset 8 RowSetRead 1 29200 r[2]=rowset(1) 9 NotExists 0 282 1 00 intkey=r[2] 10 Copy 2 3 000 r[3]=r[2] 11 OpenRead 2 170 k(3,,,)02 root=17 iDb=0; metadata_idx_04 12 IsNull 3 18000 if r[3]==NULL goto 18 13 Affinity 3 1 0 C 00 affinity(r[3]) 14 SeekGE 2 183 1 00 key=r[3] 15IdxGT 2 183 1 00 key=r[3] 16FkCounter 0 1 000 fkctr[0]+=1 17 Next 2 15000 18 OpenRead 4 13869 0 k(2,,) 02 root=13869 iDb=0; trace_idx_03 19 IsNull 3 25000 if r[3]==NULL goto 25 20 Affinity 3 1 0 C 00 affinity(r[3]) 21 SeekGE 4 253 1 00 key=r[3] 22IdxGT 4 253 1 00 key=r[3] 23FkCounter 0 1 000 fkctr[0]+=1 24 Next 4 22100 25 Delete 0 1 0 dataset00 26 Program3 0 9 program00 Call: fkey.abort 27 Program3 0 10program00 Call: fkey.abort 28Goto 0 8 000 29Halt 0 0 000 30Transaction0 1 400 01 usesStmtJournal=0 31Goto 0 1 000 0 Init 0 1 000 Start at 1; Start: .abort (AFTER DELETE ON dataset) 1 Null 0 1 000 r[1]=NULL 2 OpenRe
Re: [sqlite] DELETE extremely slow
> Do you have memory to run this in? Have you increased the sqlite cache size > because that looks (to me) an awful lot like I/O thrashing ... Sorry to disappoint you, Keith and Simon, but in all cases the database file has been located on a ramdisk. It's only about 50 MB in size, btw. > SQLite runs on the local machine. While MariaDB is client-server, so the > delete effectively runs on the server. Yes and no. Of curse, I had MariaDB run on the same machine, and its data files had been stored on the same ramdisk. > How much of the 88 minutes is "waiting" time? I did it again, same file: SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> .timer on sqlite> delete from dataset; Run Time: real 5249.891 user 2412.812500 sys 2606.531250 > You haven't shown the "explain query plan" Keith asked for Is the beginning of it enough or do I have to repeat the entire DELETE? Here is the output which I canceled after some seconds: QUERY PLAN |--SCAN TABLE dataset |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_04 (datasetid=?) |--SCAN TABLE item |--SEARCH TABLE trace USING COVERING INDEX trace_idx_03 (datasetid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_03 (traceid=?) |--SCAN TABLE item |--SEARCH TABLE item USING COVERING INDEX item_idx_01 (traceid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) |--SEARCH TABLE metadata USING COVERING INDEX metadata_idx_06 (itemid=?) `--SCAN TABLE item addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 30000 Start at 30 1 Null 0 1 000 r[1]=NULL 2 OpenRead 0 7 0 0 00 root=7 iDb=0; dataset 3 Rewind 0 7 000 4 Rowid 0 2 000 r[2]=rowid 5 RowSetAdd 1 2 000 rowset(1)=r[2] 6 Next 0 4 001 7 OpenWrite 0 7 0 3 00 root=7 iDb=0; dataset 8 RowSetRead 1 29200 r[2]=rowset(1) 9 NotExists 0 282 1 00 intkey=r[2] 10 Copy 2 3 000 r[3]=r[2] 11 OpenRead 2 170 k(3,,,)02 root=17 iDb=0; metadata_idx_04 12 IsNull 3 18000 if r[3]==NULL goto 18 13 Affinity 3 1 0 C 00 affinity(r[3]) 14 SeekGE 2 183 1 00 key=r[3] 15IdxGT 2 183 1 00 key=r[3] 16FkCounter 0 1 000 fkctr[0]+=1 17 Next 2 15000 18 OpenRead 4 13869 0 k(2,,) 02 root=13869 iDb=0; trace_idx_03 19 IsNull 3 25000 if r[3]==NULL goto 25 20 Affinity 3 1 0 C 00 affinity(r[3]) 21 SeekGE 4 253 1 00 key=r[3] 22IdxGT 4 253 1 00 key=r[3] 23FkCounter 0 1 000 fkctr[0]+=1 24 Next 4 22100 25 Delete 0 1 0 dataset00 26 Program3 0 9 program00 Call: fkey.abort 27 Program3 0 10program00 Call: fkey.abort 28Goto 0 8 000 29Halt 0 0 000 30Transaction0 1 400 01 usesStmtJournal=0 31Goto 0 1 000 0 Init 0 1 000 Start at 1; Start: .abort (AFTER DELETE ON dataset) 1 Null 0 1 000 r[1]=NULL 2 OpenRead 11170 k(3,,,)02 root=17 iDb=0; metadata_idx_04 3 Param 0 2 000 r[2]=old.rowid 4 IsNull 2 11000 if r[2]==NULL goto 11 5 Affinity 2 1 0 C 00 affinity(r[2]) 6 SeekGE 11112 1 00 key=r[2] 7
Re: [sqlite] DELETE extremely slow
> According to the MariaDB reference manual, it does not "do anything" with > references clauses on columns. Thanks for that hint, I will try again tomorrow because I cannot say for sure now whether it worked correctly or not. (And I don't have that data available anymore.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Thursday, 31 October, 2019 03:51, Thomas Kurz wrote: >I experimentally imported the same data into a MariaDB database and tried >the same operation there (without paying attention to creating any >indexes, etc.). It takes only a few seconds there. According to the MariaDB reference manual, it does not "do anything" with references clauses on columns. They are merely for entertainment purposes. You have to use the table constraint syntax to declare enforceable foreign key constraints, which means you cannot use the same CREATE TABLE syntax for MariaDB as for SQLite3. From https://mariadb.com/kb/en/library/create-table/ Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE column definitions, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. Before MariaDB 10.2.1 this was also true for CHECK constraints. Only the syntax for indexes described below creates foreign keys. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Thursday, 31 October, 2019 10:52, Simon Slavin wrote: >On 31 Oct 2019, at 4:29pm, Keith Medcalf wrote: >> If the elapsed time is much greater than the sum of user+sys time then >> I would suspect it is still I/O thrashing (or the machine is really busy >> doing something else -- those original timings represent only a 57% >> process dispatch rate, which is pretty low). > I'm betting it's a rotating hard disk with a slow spin speed, and most of > the time is spent waiting for the disk to be in the right place. > As Dominique Devienne spotted, there is now some doubt about the > comparative figure. Was the MariaDB filespace hosted on this same hard > disk ? While nice, that is irrelevant. MariaDB is a client/server database so it probably uses a database cache that is considerably larger by default than the SQLite default. The SQLite default cache is 2 MB which is 500 pages at the default page size of 4K. >The original post had >> The largest table contains about 230'000 entries. > Five tables and eleven indexes. Sixteen pages and sixteen page indexes. > Even if you think that all the required indexes for FOREIGN KEY lookup > are now available, I think that 88 minutes is still longer than SQLite > should take for anything but a slow disk. That is why I asked about the cache_size. If the cache_size has not been increased from the default then a large operation will be thrashing and spilling pages sto perform a large operation like that. For I/O intensive operations the size of SQLite's page cache makes a huge difference, far more than an adequate filesystem or block cache in reducing I/O. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On 31 Oct 2019, at 4:29pm, Keith Medcalf wrote: > If the elapsed time is much greater than the sum of user+sys time then I > would suspect it is still I/O thrashing (or the machine is really busy doing > something else -- those original timings represent only a 57% process > dispatch rate, which is pretty low). I'm betting it's a rotating hard disk with a slow spin speed, and most of the time is spent waiting for the disk to be in the right place. As Dominique Devienne spotted, there is now some doubt about the comparative figure. Was the MariaDB filespace hosted on this same hard disk ? The original post had > The largest table contains about 230'000 entries. Five tables and eleven indexes. Sixteen pages and sixteen page indexes. Even if you think that all the required indexes for FOREIGN KEY lookup are now available, I think that 88 minutes is still longer than SQLite should take for anything but a slow disk. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Thursday, 31 October, 2019 10:01, Dominique Devienne wrote: >On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote: >> Yes, but I'd expect that MariaDB has to do the same, but takes clearly >> less than 1 minute instead of 88 minutes... :confused: >Are we comparing apples to oranges here? >SQLite runs on the local machine. While MariaDB is client-server, so the >delete effectively runs on the server. >Is your MariaDB server local to the same machine you're running SQLite >on? >Are both using the same local disks? (as opposed to networked and/or >different disks) >Still can't account for 88 minutes though. Well, 88 minutes is a lot better than 56 hours, of which 24 hours was "waiting" time. How much of the 88 minutes is "waiting" time? If the elapsed time is much greater than the sum of user+sys time then I would suspect it is still I/O thrashing (or the machine is really busy doing something else -- those original timings represent only a 57% process dispatch rate, which is pretty low). >You haven't shown the "explain query plan" Keith asked for, which would >help understand what's going on here. Well, having added the missing index I doubt that there is anything else to be found there. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote: > Yes, but I'd expect that MariaDB has to do the same, but takes clearly > less than 1 minute instead of 88 minutes... :confused: > Are we comparing apples to oranges here? SQLite runs on the local machine. While MariaDB is client-server, so the delete effectively runs on the server. Is your MariaDB server local to the same machine you're running SQLite on? Are both using the same local disks? (as opposed to networked and/or different disks) Still can't account for 88 minutes though. You haven't shown the "explain query plan" Keith asked for, which would help understand what's going on here. See https://www.sqlite.org/eqp.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
>pragma foreign_keys=on; >pragma journal_mode=wal; >.timer on >delete from dataset; >--> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 Wow. That is 14 hours each of System and User time and then and additional 24 hours of "waiting for something to happen" time. Do you have memory to run this in? Have you increased the sqlite cache size because that looks (to me) an awful lot like I/O thrashing ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On 31 Oct 2019, at 3:09pm, Thomas Kurz wrote: > The result of "DELETE FROM dataset" is now 88 minutes That still seems wrong. I hope the list can help you track down what's going on. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
It's not gonna account for 88 minutes, but out of curiosity is there a way to separately report the "delete and commit" time from the "dang this WAL needs to be checkpointed now" time? -Original Message- From: sqlite-users On Behalf Of Thomas Kurz Sent: Thursday, October 31, 2019 11:10 AM To: SQLite mailing list Subject: Re: [sqlite] DELETE extremely slow > Something is wrong. If you did multiple commands like > > DELETE FROM MyTable; > > to your child tables, they should be fast. Have you run an integrity check ? I created a new database now, added the missing index "trace(datasetid)" as suggested by Keith. The result of "DELETE FROM dataset" is now 88 minutes, which of course is better than before where it took hours, but not nearly as quick as I'd expect... @Warren: > Is that command representative of actual use, or are you deleting all rows > just for the purpose of benchmarking? Usually I want to delete only several datasets, but not all. I left out the where-clause for simplification now. @Keith: > and in the face of enforced foreign key constraints will always delete the > rows one by each even if dependent (child) tables have no rows. Yes, but I'd expect that MariaDB has to do the same, but takes clearly less than 1 minute instead of 88 minutes... :confused: ___ 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] DELETE extremely slow
> Something is wrong. If you did multiple commands like > > DELETE FROM MyTable; > > to your child tables, they should be fast. Have you run an integrity check ? I created a new database now, added the missing index "trace(datasetid)" as suggested by Keith. The result of "DELETE FROM dataset" is now 88 minutes, which of course is better than before where it took hours, but not nearly as quick as I'd expect... @Warren: > Is that command representative of actual use, or are you deleting all rows > just for the purpose of benchmarking? Usually I want to delete only several datasets, but not all. I left out the where-clause for simplification now. @Keith: > and in the face of enforced foreign key constraints will always delete the > rows one by each even if dependent (child) tables have no rows. Yes, but I'd expect that MariaDB has to do the same, but takes clearly less than 1 minute instead of 88 minutes... :confused: ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
"Four observations that may (or may not) explain the problem: - dataset.id declared "unique": useless since the primary key is unique by definition but it may create an index (not checked). A PK is not "more unique" if an additional "unique" constraint is declared." Declaring it as both "primary key" and "unique" makes an extra (duplicate) index, yes. With the added uniqueness checking on the duplicate index as well. SQLite version 3.29.0 2019-07-10 17:32:03 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo1 (pk integer primary key); sqlite> create table foo2(pk integer primary key unique); sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|foo1|foo1|2|CREATE TABLE foo1 (pk integer primary key) table|foo2|foo2|3|CREATE TABLE foo2(pk integer primary key unique) index|sqlite_autoindex_foo2_1|foo2|4| sqlite> explain insert into foo1 values (?); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 13000 Start at 13 1 OpenWrite 0 2 0 1 00 root=2 iDb=0; foo1 2 Variable 1 1 000 r[1]=parameter(1,) 3 NotNull1 5 000 if r[1]!=NULL goto 5 4 NewRowid 0 1 000 r[1]=rowid 5 MustBeInt 1 0 000 6 SoftNull 2 0 000 r[2]=NULL 7 Noop 0 0 000 uniqueness check for ROWID 8 NotExists 0 10100 intkey=r[1] 9 Halt 1555 2 0 foo1.pk02 10MakeRecord 2 1 3 D 00 r[3]=mkrec(r[2]) 11Insert 0 3 1 foo1 31 intkey=r[1] data=r[3] 12Halt 0 0 000 13Transaction0 1 2 0 01 usesStmtJournal=0 14Goto 0 1 000 sqlite> explain insert into foo2 values (?); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 22000 Start at 22 1 OpenWrite 0 3 0 1 00 root=3 iDb=0; foo2 2 OpenWrite 1 4 0 k(1,) 00 root=4 iDb=0; sqlite_autoindex_foo2_1 3 Variable 1 1 000 r[1]=parameter(1,) 4 NotNull1 6 000 if r[1]!=NULL goto 6 5 NewRowid 0 1 000 r[1]=rowid 6 MustBeInt 1 0 000 7 SoftNull 2 0 000 r[2]=NULL 8 Noop 0 0 000 uniqueness check for ROWID 9 NotExists 0 11100 intkey=r[1] 10Halt 1555 2 0 foo2.pk02 11Affinity 2 1 0 D 00 affinity(r[2]) 12Noop 0 0 000 uniqueness check for sqlite_autoindex_foo2_1 13SCopy 1 4 000 r[4]=r[1]; pk 14IntCopy1 5 000 r[5]=r[1]; rowid 15MakeRecord 4 2 300 r[3]=mkrec(r[4..5]); for sqlite_autoindex_foo2_1 16NoConflict 1 184 1 00 key=r[4] 17Halt 2067 2 0 foo2.pk02 18MakeRecord 2 1 600 r[6]=mkrec(r[2]) 19IdxInsert 1 3 4 1 10 key=r[3] 20Insert 0 6 1 foo2 31 intkey=r[1] data=r[6] 21Halt 0 0 000 22Transaction0 1 2 0 01 usesStmtJournal=0 23Goto 0 1 000 sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
If you are deleting all the rows of a table, then you can simply truncate the table (SQLite will do this). However if the table you are deleting all the rows from have dependent (child) tables *and* foreign key enforcement is turned on, then the rows have to be deleted on at a time so that the foreign key constraints can be checked, although if all the child tables have no rows you should be able to just truncate the parent. SQLite does not perform that optimization and in the face of enforced foreign key constraints will always delete the rows one by each even if dependent (child) tables have no rows. -- 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 On >Behalf Of Thomas Kurz >Sent: Thursday, 31 October, 2019 05:10 >To: SQLite mailing list >Subject: Re: [sqlite] DELETE extremely slow > >> Keith found the answer: you don't have the indexes required to make >your FOREIGN KEYs run quickly. > >Thanks, I will try that. > >> If you DELETE FROM the child tables first, do you get fast or slow >times ? > >Yes, I already tried deleting from each table individually. It's slow >everywhere. > >> Thee way you're doing it involves a lot of steps as SQlite works its >way through the parent table, deletes one row from that, and cascades >through the other tables, looking for and deleting related rows from >those. > >Ok, I might have errors in my declarations, but SQLite seems to have >problems as well, because MariaDB (without any explicit index defintion!) >handles the same deletion within seconds... > >___ >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] DELETE extremely slow
On Oct 31, 2019, at 3:51 AM, Thomas Kurz wrote: > > delete from dataset; Is that command representative of actual use, or are you deleting all rows just for the purpose of benchmarking? I ask because if you’re going to just delete all rows in a table, it’s usually faster to DROP TABLE and then CREATE TABLE. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
On 31 Oct 2019, at 11:09am, Thomas Kurz wrote: > Yes, I already tried deleting from each table individually. It's slow > everywhere. Something is wrong. If you did multiple commands like DELETE FROM MyTable; to your child tables, they should be fast. Have you run an integrity check ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
> Keith found the answer: you don't have the indexes required to make your > FOREIGN KEYs run quickly. Thanks, I will try that. > If you DELETE FROM the child tables first, do you get fast or slow times ? Yes, I already tried deleting from each table individually. It's slow everywhere. > Thee way you're doing it involves a lot of steps as SQlite works its way > through the parent table, deletes one row from that, and cascades through the > other tables, looking for and deleting related rows from those. Ok, I might have errors in my declarations, but SQLite seems to have problems as well, because MariaDB (without any explicit index defintion!) handles the same deletion within seconds... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
Four observations that may (or may not) explain the problem: - dataset.id declared "unique": useless since the primary key is unique by definition but it may create an index (not checked). A PK is not "more unique" if an additional "unique" constraint is declared.. - no "on delete <...>" on trace.datasetid; so, default "no action". Is it what you want? - no index on this foreign key, hence potential full scan to identify children rows in "trace" (or absence thereof). - index trace_idx_01 declared twice. Most often once is sufficient, even for large tables. Jean-Luc Hainaut Yes, please apologize, I indeed forgot to attach the table definitions: CREATE TABLE dataset ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING DEFAULT NULL COLLATE NOCASE, is_latest BOOLEAN NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE trace ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING DEFAULT NULL COLLATE NOCASE, datasetid INTEGER REFERENCES dataset (id) NOT NULL, quantityid INTEGER REFERENCES quantity (id) NOT NULL, stored DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX trace_idx_01 ON trace ( quantityid ); CREATE INDEX trace_idx_01 ON trace ( quantityid ); CREATE TABLE item ( idINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, traceid INTEGER REFERENCES trace (id) NOT NULL, freq BIGINT NOT NULL, value REALNOT NULL, noiseflag BOOLEAN DEFAULT NULL ); CREATE INDEX item_idx_01 ON item ( traceid ); CREATE TABLE metadata ( idINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, parameter STRING NOT NULL COLLATE NOCASE, value STRING NOT NULL COLLATE NOCASE, datasetid INTEGER DEFAULT NULL REFERENCES dataset (id), traceid INTEGER DEFAULT NULL REFERENCES trace (id), itemidINTEGER DEFAULT NULL REFERENCES item (id) ); CREATE INDEX metadata_idx_01 ON metadata ( parameter, value, datasetid, traceid, itemid ); CREATE INDEX metadata_idx_02 ON metadata ( datasetid, traceid ); CREATE INDEX metadata_idx_03 ON metadata ( traceid ); CREATE INDEX metadata_idx_04 ON metadata ( datasetid, itemid ); CREATE INDEX metadata_idx_05 ON metadata ( traceid, itemid ); CREATE INDEX metadata_idx_06 ON metadata ( itemid ); CREATE INDEX metadata_idx_07 ON metadata ( datasetid, parameter ); CREATE INDEX metadata_idx_08 ON metadata ( traceid, parameter ); CREATE INDEX metadata_idx_09 ON metadata ( parameter, traceid ); CREATE INDEX metadata_idx_10 ON metadata ( parameter, datasetid, traceid, itemid ); CREATE TABLE quantity ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING NOT NULL, unit STRING NOT NULL, sumrule[SMALLINT UNSIGNED] DEFAULT NULL, created_at DATETIMEDEFAULT CURRENT_TIMESTAMP, UNIQUE ( name, unit, sumrule ) ON CONFLICT IGNORE ); - Original Message - From: Dominique Devienne To: SQLite mailing list Sent: Thursday, October 31, 2019, 11:06:07 Subject: [sqlite] DELETE extremely slow On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote: I'm using a database with 5 hierarchically strcutured tables using foreign keys. The largest table contains about 230'000 entries. My problem is that deleting in this database is extremely slow: pragma foreign_keys=on; pragma journal_mode=wal; .timer on delete from dataset; --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 I experimentally imported the same data into a MariaDB database and tried the same operation there (without paying attention to creating any indexes, etc.). It takes only a few seconds there. Is there something I can check or do to improve deletion speed? You're not describing the schema enough IMHO. Is dataset the "top-most" table, containing the "parent" rows all other tables references (directly or indirectly), with all FKs having ON DELETE CASCADE? If that's the case, without some kind of optimization in SQLite, when the
Re: [sqlite] DELETE extremely slow
On 31 Oct 2019, at 9:51am, Thomas Kurz wrote: > I'm using a database with 5 hierarchically strcutured tables using foreign > keys. The largest table contains about 230'000 entries. My problem is that > deleting in this database is extremely slow: Keith found the answer: you don't have the indexes required to make your FOREIGN KEYs run quickly. But I have a question: If you DELETE FROM the child tables first, do you get fast or slow times ? Thee way you're doing it involves a lot of steps as SQlite works its way through the parent table, deletes one row from that, and cascades through the other tables, looking for and deleting related rows from those. SQLite has an optimization for DELETE FROM without a WHERE clause. But I don't know if it's smart enough to realise that if you do that to a parent table, you can also do it to all child tables. Or perhaps its not true and there might be child rows with no parent row, and SQLite is making sure it preserves them. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE extremely slow
You have no index on trace(datasetid) ... You have no index on metadata(datasetid) though the compound indexes in which datasetid is the first element *should* be sufficent. .eqp on or .eqp full before issuing the delete command will tell you what the plan is. -- 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 On >Behalf Of Thomas Kurz >Sent: Thursday, 31 October, 2019 04:25 >To: SQLite mailing list >Subject: Re: [sqlite] DELETE extremely slow > >Yes, please apologize, I indeed forgot to attach the table definitions: > >CREATE TABLE dataset ( >id INTEGER PRIMARY KEY AUTOINCREMENT >UNIQUE >NOT NULL, >name STRING DEFAULT NULL >COLLATE NOCASE, >is_latest BOOLEAN NOT NULL >DEFAULT 1, >created_at DATETIME DEFAULT CURRENT_TIMESTAMP >); > >CREATE TABLE trace ( >id INTEGER PRIMARY KEY AUTOINCREMENT >UNIQUE >NOT NULL, >name STRING DEFAULT NULL >COLLATE NOCASE, >datasetid INTEGER REFERENCES dataset (id) >NOT NULL, >quantityid INTEGER REFERENCES quantity (id) >NOT NULL, >stored DATETIME DEFAULT NULL, >created_at DATETIME NOT NULL >DEFAULT CURRENT_TIMESTAMP >); > >CREATE INDEX trace_idx_01 ON trace ( >quantityid >); > >CREATE INDEX trace_idx_01 ON trace ( >quantityid >); > >CREATE TABLE item ( >idINTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, >traceid INTEGER REFERENCES trace (id) > NOT NULL, >freq BIGINT NOT NULL, >value REALNOT NULL, >noiseflag BOOLEAN DEFAULT NULL >); > >CREATE INDEX item_idx_01 ON item ( >traceid >); > >CREATE TABLE metadata ( >idINTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, >parameter STRING NOT NULL > COLLATE NOCASE, >value STRING NOT NULL > COLLATE NOCASE, >datasetid INTEGER DEFAULT NULL > REFERENCES dataset (id), >traceid INTEGER DEFAULT NULL > REFERENCES trace (id), >itemidINTEGER DEFAULT NULL > REFERENCES item (id) >); > >CREATE INDEX metadata_idx_01 ON metadata ( >parameter, >value, >datasetid, >traceid, >itemid >); > >CREATE INDEX metadata_idx_02 ON metadata ( >datasetid, >traceid >); > >CREATE INDEX metadata_idx_03 ON metadata ( >traceid >); > >CREATE INDEX metadata_idx_04 ON metadata ( >datasetid, >itemid >); > >CREATE INDEX metadata_idx_05 ON metadata ( >traceid, >itemid >); > >CREATE INDEX metadata_idx_06 ON metadata ( >itemid >); > >CREATE INDEX metadata_idx_07 ON metadata ( >datasetid, >parameter >); > >CREATE INDEX metadata_idx_08 ON metadata ( >traceid, >parameter >); > >CREATE INDEX metadata_idx_09 ON metadata ( >parameter, >traceid >); > >CREATE INDEX metadata_idx_10 ON metadata ( >parameter, >datasetid, >traceid, >itemid >); > >CREATE TABLE quantity ( >id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, >name STRING NOT NULL, > unit STRING NOT NULL, >sumrule[SMALLINT UNSIGNED] DEFAULT NULL, >created_at DATETIMEDEFAULT CURRENT_TIMESTAMP, >UNIQUE ( >name, >unit, >sumrule >) >ON CONFLICT IGNORE >); > > > > > >- Original Message - >From: Dominique Devienne >To: SQLite mailing list >Sent: Thursday, October 31, 2019, 11:06:07 >Subject: [sqlite] DELETE extremely slow > >On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz >wrote: > >> I'm using a database with 5 hierarchically strcutured tables using >foreign >> keys. The largest table contains about 230'000 entries. My problem is >that >> deleting in this database is extremely slow: > >> pragma foreign_keys=on; >> pragma journal_mode=wal; >> .timer on >> delete from dataset; >> --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 > >> I exper
Re: [sqlite] DELETE extremely slow
Yes, please apologize, I indeed forgot to attach the table definitions: CREATE TABLE dataset ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING DEFAULT NULL COLLATE NOCASE, is_latest BOOLEAN NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE trace ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING DEFAULT NULL COLLATE NOCASE, datasetid INTEGER REFERENCES dataset (id) NOT NULL, quantityid INTEGER REFERENCES quantity (id) NOT NULL, stored DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX trace_idx_01 ON trace ( quantityid ); CREATE INDEX trace_idx_01 ON trace ( quantityid ); CREATE TABLE item ( idINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, traceid INTEGER REFERENCES trace (id) NOT NULL, freq BIGINT NOT NULL, value REALNOT NULL, noiseflag BOOLEAN DEFAULT NULL ); CREATE INDEX item_idx_01 ON item ( traceid ); CREATE TABLE metadata ( idINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, parameter STRING NOT NULL COLLATE NOCASE, value STRING NOT NULL COLLATE NOCASE, datasetid INTEGER DEFAULT NULL REFERENCES dataset (id), traceid INTEGER DEFAULT NULL REFERENCES trace (id), itemidINTEGER DEFAULT NULL REFERENCES item (id) ); CREATE INDEX metadata_idx_01 ON metadata ( parameter, value, datasetid, traceid, itemid ); CREATE INDEX metadata_idx_02 ON metadata ( datasetid, traceid ); CREATE INDEX metadata_idx_03 ON metadata ( traceid ); CREATE INDEX metadata_idx_04 ON metadata ( datasetid, itemid ); CREATE INDEX metadata_idx_05 ON metadata ( traceid, itemid ); CREATE INDEX metadata_idx_06 ON metadata ( itemid ); CREATE INDEX metadata_idx_07 ON metadata ( datasetid, parameter ); CREATE INDEX metadata_idx_08 ON metadata ( traceid, parameter ); CREATE INDEX metadata_idx_09 ON metadata ( parameter, traceid ); CREATE INDEX metadata_idx_10 ON metadata ( parameter, datasetid, traceid, itemid ); CREATE TABLE quantity ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name STRING NOT NULL, unit STRING NOT NULL, sumrule[SMALLINT UNSIGNED] DEFAULT NULL, created_at DATETIMEDEFAULT CURRENT_TIMESTAMP, UNIQUE ( name, unit, sumrule ) ON CONFLICT IGNORE ); - Original Message - From: Dominique Devienne To: SQLite mailing list Sent: Thursday, October 31, 2019, 11:06:07 Subject: [sqlite] DELETE extremely slow On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote: > I'm using a database with 5 hierarchically strcutured tables using foreign > keys. The largest table contains about 230'000 entries. My problem is that > deleting in this database is extremely slow: > pragma foreign_keys=on; > pragma journal_mode=wal; > .timer on > delete from dataset; > --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 > I experimentally imported the same data into a MariaDB database and tried > the same operation there (without paying attention to creating any indexes, > etc.). It takes only a few seconds there. > Is there something I can check or do to improve deletion speed? You're not describing the schema enough IMHO. Is dataset the "top-most" table, containing the "parent" rows all other tables references (directly or indirectly), with all FKs having ON DELETE CASCADE? If that's the case, without some kind of optimization in SQLite, when the first parent row is deleted, it triggers a cascade of deletes in "child" tables, looking for rows using the parent row. So if your FKs are not indexed for those column(s), that's a full table scan each time... That's "depth first". By analyzing the graph of FKs and their ON DELETE CASCADE state, and in the specific case of fully deleting the "main parent table", SQLite could decide switch to a smarter "breadth first" delete, but I suspect it's not a compelling enough use-case for Richard to invest time on this. Try indexing your FKs, and see what happens. --DD ___ sqlite-users mailin
Re: [sqlite] DELETE extremely slow
On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote: > I'm using a database with 5 hierarchically strcutured tables using foreign > keys. The largest table contains about 230'000 entries. My problem is that > deleting in this database is extremely slow: > > pragma foreign_keys=on; > pragma journal_mode=wal; > .timer on > delete from dataset; > --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 > > I experimentally imported the same data into a MariaDB database and tried > the same operation there (without paying attention to creating any indexes, > etc.). It takes only a few seconds there. > > Is there something I can check or do to improve deletion speed? > You're not describing the schema enough IMHO. Is dataset the "top-most" table, containing the "parent" rows all other tables references (directly or indirectly), with all FKs having ON DELETE CASCADE? If that's the case, without some kind of optimization in SQLite, when the first parent row is deleted, it triggers a cascade of deletes in "child" tables, looking for rows using the parent row. So if your FKs are not indexed for those column(s), that's a full table scan each time... That's "depth first". By analyzing the graph of FKs and their ON DELETE CASCADE state, and in the specific case of fully deleting the "main parent table", SQLite could decide switch to a smarter "breadth first" delete, but I suspect it's not a compelling enough use-case for Richard to invest time on this. Try indexing your FKs, and see what happens. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE extremely slow
I'm using a database with 5 hierarchically strcutured tables using foreign keys. The largest table contains about 230'000 entries. My problem is that deleting in this database is extremely slow: pragma foreign_keys=on; pragma journal_mode=wal; .timer on delete from dataset; --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 I experimentally imported the same data into a MariaDB database and tried the same operation there (without paying attention to creating any indexes, etc.). It takes only a few seconds there. Is there something I can check or do to improve deletion speed? Kind regards, Thomas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
