Re: [sqlite] Sqlite delete too slow in 4 GB database
On 4 Jun 2018, at 6:59am, ayagmur wrote: > When I try to delete 100 MB data by cascade (1 parent record - 100 child > records) it takes too long time (almost 10 minute) to complete, and the > duration increase/decrease by size of data (100 Mb: 10 minute, 300 MB: 30 > minute,etc) What medium is your database stored on ? Is it a spinning-rust hard disk ? If so, do you have a way of finding out the rotation speed ? Is your database stored on the same computer running SQLite or is it being accessed across a network ? My other two questions are the ones Olivier Mascia asked, and you can use the SQLite command-line utility to answer them: What is the response to "PRAGMA secure_delete" ? What is the response to "PRAGMA auto_vacuum" ? For speed testing, try changing the settings on these and tell us whether that makes any difference. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite delete too slow in 4 GB database
If you don't already do this, you'll want to be sure the large blob(s) are the *last* fields in the table definition. On Mon, Jun 4, 2018 at 8:49 AM Paul Sanderson wrote: > Have you made sure aut_ovacuum is disabled? > > pragma *auto_vacuum * = 0 > > have you got a nice large pagesize if your records are that big? > > > > Paul > www.sandersonforensics.com > SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> > > On 4 June 2018 at 13:01, Olivier Mascia wrote: > > > Hello, > > > > > Sqlite delete too slow in 4 GB database > > > > What does: > > > > 'pragma secure_delete;' > > > > and > > > > 'pragma auto_vacuum;' > > > > say, on that db? > > > > -- > > Best Regards, Meilleures salutations, Met vriendelijke groeten, > > Olivier Mascia > > > > > > ___ > > 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] Sqlite delete too slow in 4 GB database
Have you made sure aut_ovacuum is disabled? pragma *auto_vacuum * = 0 have you got a nice large pagesize if your records are that big? Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 4 June 2018 at 13:01, Olivier Mascia wrote: > Hello, > > > Sqlite delete too slow in 4 GB database > > What does: > > 'pragma secure_delete;' > > and > > 'pragma auto_vacuum;' > > say, on that db? > > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > ___ > 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] Sqlite delete too slow in 4 GB database
Hello, > Sqlite delete too slow in 4 GB database What does: 'pragma secure_delete;' and 'pragma auto_vacuum;' say, on that db? -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite delete too slow in 4 GB database
On Sun, 3 Jun 2018 22:59:05 -0700 (MST) ayagmur escribió: > I have been using ext2 file system. I have a database which has 4 GB size. > Database consist of 1 parent table and 1 child table. Parent table has 10 > rows and child table has 4000 rows. 1 row of child table has 1 MB size. when > I delete a row in parent table, deletion cascades 1MB-sized child records. > (pragma foreign_keys is on) When I try to delete 100 MB data by cascade (1 > parent record - 100 child records) it takes too long time (almost 10 minute) > to complete, and the duration increase/decrease by size of data (100 Mb: 10 > minute, 300 MB: 30 minute,etc).I tried some pragma commands (synchronous, > temp_store, journal_mode) suggested by others posts and i also tried to add > index on foreign key, but those does not help solve my problem.(Actually, > after adding index on foreign key, 1 MB data deletion became faster/st, but > 100 MB data deletion duration did not change) Can you give me please any > suggestion to increase deletion performance? Don't hard-delete. You have 2 options to soft-delete (I use both on my big fs over sqlite): a) Add a new column to child, put it before the data blob column and set it to 0 if it is active or 1 when delete. From time to time select those actives and move them to a new fresh table. b) Parent has the foreign key to the childs, just set those foreign keys to 0 or to the new content. From time to time select all foreign keys to child on parent table and move them to a new fresh table. Disable foreign_key pragma. Now I use b) with parent (fs metadata) on one db file and child (file content) on another, it's faster and don't require an additional column on child. Clean child table/db when you want, I never did it on mines for now. -- Eduardo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite delete too slow in 4 GB database
I have been using ext2 file system. I have a database which has 4 GB size. Database consist of 1 parent table and 1 child table. Parent table has 10 rows and child table has 4000 rows. 1 row of child table has 1 MB size. when I delete a row in parent table, deletion cascades 1MB-sized child records. (pragma foreign_keys is on) When I try to delete 100 MB data by cascade (1 parent record - 100 child records) it takes too long time (almost 10 minute) to complete, and the duration increase/decrease by size of data (100 Mb: 10 minute, 300 MB: 30 minute,etc).I tried some pragma commands (synchronous, temp_store, journal_mode) suggested by others posts and i also tried to add index on foreign key, but those does not help solve my problem.(Actually, after adding index on foreign key, 1 MB data deletion became faster/st, but 100 MB data deletion duration did not change) Can you give me please any suggestion to increase deletion performance? -- 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
