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 is different if we adopt database point of view. Dan Kennedy-4 wrote > When an SQL write transaction is performed, SQLite begins by modifying > database pages within its internal page-cache. If the transaction > modifies only a few pages, all dirty pages stay in the cache until the > user executes "COMMIT", at which point they are appended to the *-wal > file. However, the page-cache is of limited size (by default 2000 > pages), and once it is completely full of dirty pages SQLite begins > appending them to the *-wal file mid-transaction in order to free up > space. At this point each time SQLite needs to modify a page that is not > already in the cache it must select a dirty page to write out to the > *-wal file so as to free up space to load the new page into the cache > where it can be modified. This means that a transaction with a large > working set may append more than one copy of a single page to the *-wal > file. Maybe many, many copies. > > SQLite indexes are b-trees. Each b-tree node is stored on a database > page. So if you're inserting keys in random order into a large index > (one too large to fit entirely within the page-cache), then virtually > all inserts result in an existing dirty page being flushed from the > cache and appended to the *-wal file. Thanks for clarifying. In fact I was slowly iterating to similar conclusions. Don't you think that SQLite documentation should discuss this risk in greater detail than it is currently done? Dan Kennedy-4 wrote > Inserting keys in sorted order avoids the problem because > all writes go to the right-most leaf node of the index b-tree, which > will almost always be present in the page-cache. > > To change the size of the page-cache, see the "PRAGMA cache_size" and > "PRAGMA page_size" commands. > > One way to get keys to be inserted in order is to create all indexes > after populating the table. SQLite sorts the data before creating the > index b-tree in this case. > > The other is to create a temp (or non temp) table with *exactly the > same* columns and indexes as the table to be populated and insert the > new rows into it. Then running: > > INSERT INTO target_tbl SELECT * FROM temp_tbl; > > In this case, SQLite detects the similar schemas and copies keys in > sorted order from the indexes on "temp_tbl" to the corresponding index > on "target_tbl". Good tips, thanks. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80146.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
