Re: [sqlite] Huge WAL log

Thanks for the documentation update. >From my point of view I would invite more details related to the term "large transaction". Specifically the role of indexes is important. (They are often overlooked, added by an admin after the development is over etc.) > Defenses against this failure mode

Re: [sqlite] Huge WAL log

On Thu, Jan 22, 2015 at 6:49 PM, Richard Hipp wrote: > Let me know if that helps. Note that I have only quickly read over my > writing so there is a high probability of typos, which I will be happy > to correct when brought to my attention. Thanks for the new doc. Very

Re: [sqlite] Huge WAL log

On 1/21/15, Jan Slodicka wrote: > > My suggestion to SQLite developers: > Please update the WAL documentation (namely the place where you warn > against > large transactions) by explaining potential risks. Thanks for the suggestion. I added some text here:

Re: [sqlite] Huge WAL log

Here is my final report. The problem was definitely in indexes. Simply the larger is the index table as compared to the page cache size, the faster growths the WAL log. My solution (all these measures were important): - Drop the index before the bulk insert, create it at the end. - Intermediate

Re: [sqlite] Huge WAL log

I'll add the results from the latest test that was running for the last 2 hours: This time I did not use intermediate commits. Instead, I dropped the table indexes before the bulk insert started and re-created them at the end. The results after a few tables (most of them small, a few contained

Re: [sqlite] Huge WAL log

Dan Kennedy-4 wrote > Is it correct that you have a single transaction inserting lots of data > into a table with multiple indexes on it? Something like 1GB? Depends on. It is the best option from the application point of view. Other solutions introduce additional risks. Apparently, the answer

Re: [sqlite] Huge WAL log

one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Jan Slodicka >Sent: Friday, 16 January, 2015 10:04 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Huge WAL log > >Simon Slavin-3

Re: [sqlite] Huge WAL log

On 01/17/2015 12:04 AM, Jan Slodicka wrote: Simon Slavin-3 wrote Thanks to your post I discovered multiple-row inserts so that I now understand what you asked. Just a note that multiple-row inserts were added to SQLite relatively recently (2012-03-20 (3.7.11)) and, because SQLite does only

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote >> Thanks to your post I discovered multiple-row inserts so that I now >> understand what you asked. > > Just a note that multiple-row inserts were added to SQLite relatively > recently (2012-03-20 (3.7.11)) and, because SQLite does only > database-level locking, its overhead

Re: [sqlite] Huge WAL log

RSmith wrote >> >> The code schema is as follows: >> >> foreach table >> { >> BEGIN >> INSERT INTO table VALUES() >> INSERT INTO table VALUES() >> ... >> COMMIT >> } >> >> Large column values are supplied as parameters, the rest (vast majority) >> is >> passed

Re: [sqlite] Huge WAL log

On 16 Jan 2015, at 12:39pm, Jan Slodicka wrote: > Thanks to your post I discovered multiple-row inserts so that I now > understand what you asked. Just a note that multiple-row inserts were added to SQLite relatively recently (2012-03-20 (3.7.11)) and, because SQLite does only

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > Unlike a rollback journal a WAL file can have multiple copies of the same > page. > > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" > > If your inserted records is 5million separate insertions then each >

Re: [sqlite] Huge WAL log

On 2015/01/16 11:33, Jan Slodicka wrote: The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large column values are supplied as parameters, the rest (vast majority) is passed through SQL

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote > However, other information in your message suggests that you have a > resource leak of some type somewhere. Especially, it should not take 12 > minutes to insert 3.5M rows into a simple table with an index or two > unless really long strings or blobs are involved. > >

Re: [sqlite] Huge WAL log

> I understand that the WAL log must take a lot of space. What I don't > understand is that it was 7x larger than the resulting DB size. (Actual > quotient is even larger because I compared to the DB size that contained > also other tables.) Unlike a rollback journal a WAL file can have multiple

Re: [sqlite] Huge WAL log

On 15 Jan 2015, at 3:44pm, Jan Slodicka wrote: > Index rebuild (using sqlite3 shell) took 123 sec. This would suggest that it > might be better to run with deleted indexes and rebuild them at the end. That is as expected, and is standard advice for cases where you are adding

Re: [sqlite] Huge WAL log

I'll add the results from additional tests. First of all, I forced a commit after each 100,000 records inserted into a single table. (A complication for us.) Some numbers for a table with a single index and 3,423,000 inserted records: Intermediate commits took subsequently 764 msec, 2164 msec,

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote >> - WAL log size 7.490 GB > > Please repeat your tests but as the first command after opening your > database file issue > > PRAGMA journal_size_limit = 100 > > With this change the WAL file may still grow to 7 GB while that particular > transaction is being executed

Re: [sqlite] Huge WAL log

Richard Hipp-3 wrote > What is your page size? 1024 Richard Hipp-3 wrote > Your original post said you inserted two rows for each transaction. > How big are those two rows? Sorry for misleading information. Here is a more formal algorithm: foreach table { BEGIN insert all downloaded

Re: [sqlite] Huge WAL log

On 01/15/2015 12:28 AM, Jan Slodicka wrote: Richard Hipp-3 wrote No other active readers or writers. Are you sure? Writers for sure. As far readers are concerned, the things are too complex to make an absolute statement. (I shall check once more.) Some APIs that might be helpful: *

Re: [sqlite] Huge WAL log

On 14 Jan 2015, at 3:36pm, Jan Slodicka wrote: > - WAL log size 7.490 GB Please repeat your tests but as the first command after opening your database file issue PRAGMA journal_size_limit = 100 With this change the WAL file may still grow to 7 GB while that particular

Re: [sqlite] Huge WAL log

On 1/14/15, Jan Slodicka wrote: > Richard Hipp-3 wrote >>> No other active readers or writers. >> >> Are you sure? > > Writers for sure. > > As far readers are concerned, the things are too complex to make an > absolute > statement. (I shall check once more.) However, I can add a

Re: [sqlite] Huge WAL log

Richard Hipp-3 wrote >> No other active readers or writers. > > Are you sure? Writers for sure. As far readers are concerned, the things are too complex to make an absolute statement. (I shall check once more.) However, I can add a few observations I made: WAL file size was about 70 MB (as

Re: [sqlite] Huge WAL log

On 1/14/15, Jan Slodicka wrote: > I understand that the WAL log uses less efficient storage rules than the > real > database, but this case was a real surprise for me. Here is the brief > description. > > We start from an empty database, create a few tables (each having a few >

[sqlite] Huge WAL log

I understand that the WAL log uses less efficient storage rules than the real database, but this case was a real surprise for me. Here is the brief description. We start from an empty database, create a few tables (each having a few indexes), then begin a transaction, do a couple of inserts into