[sqlite] [Question] Does it necessary to sync directory on persist journal mode?
Dear sqlite-users, I have a question about directory sync. Question: In case of journal file already exists on disk with persist journal mode, does it necessary to sync directory for first fsync(unixSync) call? AFAIK, directory sync should be called when a file is created or deleted. Does it mean that calling directory sync doesn't necessary for existing files? When I tested it with 1,000 insert queries, about 1,000 fsync calls were decreased on persist journal mode. To compare this, I just changed "unixOpen" function as like as follows. int syncDir = 0; if(isCreate && (eType==SQLITE_OPEN_MASTER_JOURNAL || eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL)) { int exists = 0; unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS, &exists); if(!exists) { syncDir = 1; } } Thank you for reading this message. Best regards, YongIl Jang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] Does it necessary to sync directory on persist journal mode?
Please, don't forget my questions. 2012/9/10 Yongil Jang > Dear sqlite-users, > > I have a question about directory sync. > > Question: In case of journal file already exists on disk with persist > journal mode, >does it necessary to sync directory for first > fsync(unixSync) call? > > AFAIK, directory sync should be called when a file is created or deleted. > Does it mean that calling directory sync doesn't necessary for existing > files? > > When I tested it with 1,000 insert queries, about 1,000 fsync calls were > decreased on persist journal mode. > To compare this, I just changed "unixOpen" function as like as follows. > > int syncDir = 0; > if(isCreate && (eType==SQLITE_OPEN_MASTER_JOURNAL || > eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL)) > { > int exists = 0; > unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS, &exists); > if(!exists) { > syncDir = 1; > } > } > > Thank you for reading this message. > > Best regards, > YongIl Jang > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Question] Does it necessary to sync directory on persist journal mode?
On 09/11/2012 02:22 PM, Yongil Jang wrote: Please, don't forget my questions. 2012/9/10 Yongil Jang Dear sqlite-users, I have a question about directory sync. Question: In case of journal file already exists on disk with persist journal mode, does it necessary to sync directory for first fsync(unixSync) call? AFAIK, directory sync should be called when a file is created or deleted. Does it mean that calling directory sync doesn't necessary for existing files? I think you're right, in persistent journal mode once the file has been created the second and subsequent fsync() calls are redundant. Did removing the extra fsync() calls produce any performance improvement? In general, SQLite can't use this trick because in a multi-process environment there is no way to tell if an existing journal file has already been synced to disk (e.g. the process that created it may have crashed before calling fsync(), or it may have been created by a process running with synchronous=off). But if this does produce a performance improvement, perhaps we could do something in locking_mode=EXCLUSIVE mode. When I tested it with 1,000 insert queries, about 1,000 fsync calls were decreased on persist journal mode. To compare this, I just changed "unixOpen" function as like as follows. int syncDir = 0; if(isCreate&& (eType==SQLITE_OPEN_MASTER_JOURNAL || eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL)) { int exists = 0; unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS,&exists); if(!exists) { syncDir = 1; } } Thank you for reading this message. Best regards, YongIl Jang ___ 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
Re: [sqlite] [Question] Does it necessary to sync directory on persist journal mode?
Thank you for your answer! When I tested it on my Ubuntu server at the office, Performace was improved slightly. I don't remember that values exactly, but improved ratio was about 5~8%. Sys time was decreased a lot. Strace result shows almost same call counts because of osAccess call. Test SQL script is exported from some benchmark application on android market. It contains a lot of inserts, updates and delete. Some queries are executed on auto commit mode and some queries are executed on manual commit mode that contains from 1,000 to 25,000 queries in one transaction. Persistent journal mode is used for each test. I agree with your opinion that this cannot be used in general situation. However, in multi-process environment with persistent journal mode, this journal file is accessed by only one process, isn't it? In my understand, database file is changed by only one transaction that has exclusive lock on that database. And it means that a journal file is written by only one connection at same time. Because of this reason, I didn't care about multi-process. And, of course in my understand, whatever my process doesn't call directory sync, another process that created a new journal file may call directory sync at first file sync time. If power failure was occurred before directory call, then this journal file may be disappeared from file system. But, this is not a new problem because of there was no syncing operation to journal directory. Last... Too sorry about my poor english. :) Thanks a lot to everyone! 나의 iPad에서 보냄 2012. 9. 11. 오후 6:16 Dan Kennedy 작성: > On 09/11/2012 02:22 PM, Yongil Jang wrote: >> Please, don't forget my questions. >> >> 2012/9/10 Yongil Jang >> >>> Dear sqlite-users, >>> >>> I have a question about directory sync. >>> >>> Question: In case of journal file already exists on disk with persist >>> journal mode, >>>does it necessary to sync directory for first >>> fsync(unixSync) call? >>> >>> AFAIK, directory sync should be called when a file is created or deleted. >>> Does it mean that calling directory sync doesn't necessary for existing >>> files? > > I think you're right, in persistent journal mode once the file > has been created the second and subsequent fsync() calls are > redundant. > > Did removing the extra fsync() calls produce any performance > improvement? > > In general, SQLite can't use this trick because in a multi-process > environment there is no way to tell if an existing journal file has > already been synced to disk (e.g. the process that created it may > have crashed before calling fsync(), or it may have been created by > a process running with synchronous=off). But if this does produce a > performance improvement, perhaps we could do something in > locking_mode=EXCLUSIVE mode. > > > >>> When I tested it with 1,000 insert queries, about 1,000 fsync calls were >>> decreased on persist journal mode. >>> To compare this, I just changed "unixOpen" function as like as follows. >>> >>> int syncDir = 0; >>> if(isCreate&& (eType==SQLITE_OPEN_MASTER_JOURNAL || >>> eType==SQLITE_OPEN_MAIN_JOURNAL || eType==SQLITE_OPEN_WAL)) >>> { >>> int exists = 0; >>> unixAccess(pVfs, zPath, SQLITE_ACCESS_EXISTS,&exists); >>> if(!exists) { >>> syncDir = 1; >>> } >>> } >>> >>> Thank you for reading this message. >>> >>> Best regards, >>> YongIl Jang >>> >>> >> ___ >> 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