The way you mentioned "only involves one long update, not 100 long updates" is good, however, I met the problem in the performance test and found that the results in two kinds of set-up box are in great difference in committing transaction. Maybe it's related to hardware performance. What's strange is that when I remove the disk, the difference still exists. It takes long time for fsync working with no disks, which has confused me for several days. Thank you for your patience and great help.
On 01/19/2015 06:23 PM, Wei, Catherine wrote: > Thank you for your answer very much. But I have removed the disk from > my set-up box, the data will be saved in memory and the system doesn't > know there's no disk, it will still call fsync. What do you think in > this case? > Appreciate your quick response and great help. > > On 01/19/2015 05:25 PM, Simon Slavin wrote: >> On 19 Jan 2015, at 5:38am, Wei, Catherine <catherine....@arris.com> 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 >> 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
