On 04/26/2011 08:13 AM, Nikolaus Rath wrote: > Nikolaus Rath<nikol...@rath.org> writes: > [ WAL Performance ] >> Really no one an idea of why this is happening? > > I tried to investigate this further myself and created a small example > program (attached). It fills a table with 500,000 entries without using > explicit transactions. The final db is 24 MB big. > > On my system, the required times are (the xxx in WAL(xxx) is the > wal_autocheckpoint value): > > synchronous=OFF, journal_mode=OFF: 13143.2 ms > synchronous=OFF, journal_mode=WAL(1000): 12130.1 ms > synchronous=NORMAL, journal_mode=WAL(1000): 68430.8 ms > synchronous=OFF, journal_mode=WAL(10000): 12279.0 ms > synchronous=NORMAL, journal_mode=WAL(10000): 33475.0 ms > synchronous=OFF, journal_mode=WAL(50000): 13058.9 ms > synchronous=NORMAL, journal_mode=WAL(50000): 27820.7 ms > > > Unfortunately these results just deepen my confusion: > > - Why is WAL faster than no journal if sync=OFF? Doesn't WAL require > every page to be written twice?
In a lot of cases, writing sequential data to a file is faster than writing the same amount of data to a bunch of random offsets. And if you do have to write to a bunch of offsets within a file, then it is faster if they are sorted in ascending order before doing so. This is what SQLite does in WAL mode. Writes data sequentially to the WAL file, then during a checkpoint writes to a bunch of different offsets within the database file (in sorted order). Maybe that is faster than writing each transaction directly to the database file in this case. > - Why is WAL(50000) so much slower than no journal with sync=NORM? If > the WAL file can grow up to 50MB, but the db is only 25MB big, it > seems to me that only one checkpoint (and therefore only one fsync()) > should be required. Am I missing something again? Maybe creating the large wal file is slow. Usually overwriting an existing region of a file is faster than extending one. If you run the experiment with sufficient inserts (so that the 50MB wal file is created, checkpointed and then overwritten a few times) the results might look better. Guesswork, obviously... _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
