On 09/10/2012 03:15 AM, Daniel Frimerman wrote:
My apologies about the attachment; should have known better.
This should be better:
https://dl.dropbox.com/u/50838941/SQLite3_Test.zip
I only get the problem with DELETE and TRUNCATE journal_mode (synchronous
set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or
FULL) or WAL (synchronous set to FULL).
When using "PRAGMA journal_mode=DELETE", after you reboot the
system is there a *-journal file present in the directory
next to your database file?
If so and you rename it before opening the database, are all
50 records present?
The reason I think there has to be 50 rows is because on FULL mode for
example, the I/O buffers are flushed, and it's consistently missing 1
record as opposed to any other number of records. I insert 50 records, the
sqlite3 command line utility executes the script, and I get to a stage
where I can write commands to the console. What I mean is that as far as
sqlite is concerned, it has written the data to disk and also instructed
the OS to flush the buffers. Perhaps it finalises something from the last
insert only when the next insert comes in?
It could be a coincidence of some sort, by sheer difference of
implementation of different journals that the "problem" doesn't show itself
with PERSIST or WAL journals.
I turned off host I/O cache in VirtualBox, so any writes by the guest OS
have to be physically written to the virtual disk on the host. The guest
has "standard" I/O caching on disk, but FlushFileBuffers() should have done
its job. There is no reason why any link in the chain should report data
written to disk without actually doing it, unless there is a problem.
I suppose I should just go ahead and test it on the physical PC. If there
is a problem with that also, then I suppose I could blame the OS for not
flushing stuff to disk properly.
Now I gotta find me a machine............
Regards,
Dan
On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanov<paiva...@gmail.com> wrote:
Note: attachments are stripped out of this list. So if you want for
anybody else to see your zip file you need to put it on some website
and post link here.
About the problem you have: I wonder how are you sure that there
should be 50 rows in the database and not 49? If you are resetting the
OS before it has a chance to properly commit everything then it's okay
for last transaction to be missing. But if you are sure that you are
resetting the OS after everything is settled then maybe you are not
committing your last transaction properly? Or maybe there's some bug
in your virtualization layer and you need to try the same thing on a
real hardware?
Pavel
On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman
<danielfrimer...@gmail.com> wrote:
I am fairly new to sqlite and as a result of not reading the manual and
not
doing some performance testing, I got punished somewhat. I did not
anticipate that on journal_mode=DELETE and synchronous=FULL, I would get
no
more than 5 inserts (in auto-commit mode) per second. It crippled a
certain batch operation on a live system. That's water under the bridge;
it's the testing afterwards and a potential minor problem that I found is
what I am now interested in.
I tested all journal mode settings for sqlite, as well as the synchronous
setting. Some things that I discovered were not so obvious from reading
the docs, such as the WAL journal mode combined with NORMAL synchronous
setting, which is nowhere near as "durable" as NORMAL setting for other
journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves
all inserted data - reproducing the slightest of chances that consistency
is compromised was rather hard. This is reflected in performance
testing:
NORMAL is only slightly faster than FULL mode for non-WAL journal
settings
(btw, journal_mode=OFF was never tested in any of my tests). But, I
understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode
where consistency/corruption is concerned. That is, the database cannot
get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades off
durability and in my tests I easily reproduced that.
Okay, that was not really related to the possible bug I found. I've
attached a ZIP file containing some batch files that create a table,
insert
some rows, at which point you hard-reset the OS, log back in and check if
the number of rows in the DB matches what you inserted. Although the
non-WAL journal modes are somewhat similar, the little problem that I've
come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL.
The problem is basically as follows: in DELETE and TRUNCATE journal mode
combined with NORMAL/FULL synchronous mode, there is always 1 row missing
during my simulated power-cut.
I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14
(command line as well as through my testing application). In VirtualBox,
under storage settings for the VM, I used IDE Controller (afaik it's
single
threaded), turned off host I/O cache. Inside the guest, write-cache
should
be enabled in device manager under policies for the default disk
controller.
To test this, set your VM as above, copy the files from the attached ZIP
file, also download the latest sqlite3 command line shell. Restart the
guest once to ensure your files are flushed out before you start
resetting
the guest :)
Execute the following batch file: EXEC_DATA__DELETE_FULL.cmd, wait 2-3
seconds (or less) then hit HOST+R to hard reset the OS. When you reboot,
run READ_DATA.cmd, you'll see 49 rows, but there should be 50.
You can try the same with EXEC_DATA__DELETE_NORMAL.cmd,
EXEC_DATA__TRUNCATE_FULL.cmd, EXEC_DATA__TRUNCATE_NORMAL.cmd
50 rows if you try with EXEC_DATA__PERSIST_FULL.cmd and
EXEC_DATA__PERSIST_NORMAL.cmd and EXEC_DATA__WAL_FULL.cmd
What's with that?
Kind regards, Dan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
