On 19 Jan 2015, at 5:38am, Wei, Catherine <[email protected]> wrote:
> The time i'm looking at is the duration that function fsync or fdatasync
> executes, it's about 30ms. I don't know wether it's related to linux
> kernel or something related. I've tested it in another kind of set-up
> box and the duration of fsyn is only about 5ms.
> The data being written to is just an insert sql, very little. Every
> time after executing the "insert" sql, database transaction will be
> committed and fsync will be executed during commiting process.
The answer is that the computer you have that says 5s is lying to you.
A normal hard disk in a normal computer rotates at 5400 times a minute. This
means it completes one rotation in about 10ms. And that means that waiting for
the right part of the disk to pass under the read/write head takes an average
of 5ms.
Committing a transaction involves SQLite writing to the disk in a number of
different places: it has to move the transaction from the journal file to the
database file which involves at least two read and two write commands, usually
more than that. (I do not know enough about SQLite to know the proper numbers
but I'm sure they are more than that.) With an average latency of 5ms per
access, this means that in the best possible case committing a transaction will
take 20ms. This suggests that the timing of 30ms you're getting from your
second computer is about right.
So why is the first computer lying to you ? Well it's to make itself seem
faster. In this case when a 'write' command is issued to the disk drive it is
reporting "finished" immediately, before the change has actually been made to
disk. This doesn't matter when a computer is used for normal desktop purposes
(web browser, word processor) because you might just lose the last few
characters of a word processing file, but it matters a lot in a database like
SQLite because writing one row to a database involves many changes and if
they're not consistent then the database may be corrupted. So for good safety
of data in your database your 30ms computer is the good computer and your 5ms
computer is a bad computer.
It's worth noting that this long delay (30ms) is only for writing a
transaction, not for each change to the database. So
BEGIN
INSERT ...
INSERT ...
INSERT ...
97 more of these ...
END
only involves one long update, not 100 long updates.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
