Re: [sqlite] SQLite version 3.6.14
Based on what I've read, it certainly could. As I understand it, there is a single queue for all the writes so the writes for multiple databases, journals, etc. are going to that single queue. Based on that, it is certainly "possible" that your overall throughput and throughput for any individual database could be negatively affected. Note that my file system knowledge is restricted to Windows platforms. Please don't extend my speculation to other platforms. I also note that I have done no performance testing with this VFS myself and have seen no data provided. Doug wrote: > Would this perhaps affect throughput in the case where multiple database > files are open? For example, I have a handful of databases (10?) that are > open at any given time, and reads and writes are taking place on separate > threads. Naturally writes that happen to the same database ultimately get > serialized by the database-level locks, but writes to other databases > continue to work. But using the async feature would serialize all reads and > writes to all databases, is that correct? > > Thanks > Doug > > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Christian Smith >> Sent: Friday, May 08, 2009 7:24 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQLite version 3.6.14 >> >> On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote: >> >>> On May 8, 2009, at 5:21 PM, Christian Smith wrote: >>> >>> >>>> On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: >>>> >>>>> A new optional extension is included that implements an >>>>> asynchronous I/ >>>>> O backend for SQLite on either windows or unix. The asynchronous >>>>> >> I/O >> >>>>> backend processes all writes using a background thread. This >>>>> >> gives >> >>>>> the appearance of faster response time at the cost of durability >>>>> >> and >> >>>>> additional memory usage. See http://www.sqlite.org/asyncvfs.html >>>>> >> for >> >>>>> additional information. >>>>> >>>> What are the benefits of using async I/O over "PRAGMA synchronous = >>>> OFF"? >>>> If AIO is used for the rollback journal as well, you've lost your >>>> >> ACID >> >>>> properties already, so you may as well just use "PRAGMA synchronous >>>> = OFF" >>>> anyway and keep the code simpler. >>>> >>> That's not the case. You lose the Durability property, in that a >>> >> COMMIT >> >>> statement may return before a transaction is stored on the persistent >>> media, >>> but transactions are still Atomic, Consistent and Isolated. >>> >>> When using the "PRAGMA synchronous=off" your database might be >>> >> corrupted >> >>> by a power failure or OS crash. When using asynchronous IO this >>> >> should >> >>> not >>> be possible (assuming the hardware is not being untruthful - just as >>> when >>> using regular "PRAGMA synchronous=full" mode without the async IO >>> >> VFS). >> >> >> Ah, the bulb has lit. Because the writes and syncs are processed by the >> single queue in order, journal writes are guaranteed to be synced and >> consistent before main in-place updates to the db file. >> >> Might be worth mentioning this in the documentation, as this is not >> clear without examining the source. >> >> In that case, I like it :) >> >> Is this something that might be made the default in the future, with >> the >> addition of some synchronization between foreground and background >> threads >> on the xSync messages to emulate the existing "PRAGMA synchronous=full" >> behaviour? >> >> >>> Dan. >>> >> Christian >> ___ >> 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
Re: [sqlite] SQLite version 3.6.14
Would this perhaps affect throughput in the case where multiple database files are open? For example, I have a handful of databases (10?) that are open at any given time, and reads and writes are taking place on separate threads. Naturally writes that happen to the same database ultimately get serialized by the database-level locks, but writes to other databases continue to work. But using the async feature would serialize all reads and writes to all databases, is that correct? Thanks Doug > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Christian Smith > Sent: Friday, May 08, 2009 7:24 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite version 3.6.14 > > On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote: > > > > On May 8, 2009, at 5:21 PM, Christian Smith wrote: > > > > > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: > > >> > > >> A new optional extension is included that implements an > > >> asynchronous I/ > > >> O backend for SQLite on either windows or unix. The asynchronous > I/O > > >> backend processes all writes using a background thread. This > gives > > >> the appearance of faster response time at the cost of durability > and > > >> additional memory usage. See http://www.sqlite.org/asyncvfs.html > for > > >> additional information. > > > > > > > > > What are the benefits of using async I/O over "PRAGMA synchronous = > > > OFF"? > > > If AIO is used for the rollback journal as well, you've lost your > ACID > > > properties already, so you may as well just use "PRAGMA synchronous > > > = OFF" > > > anyway and keep the code simpler. > > > > That's not the case. You lose the Durability property, in that a > COMMIT > > statement may return before a transaction is stored on the persistent > > media, > > but transactions are still Atomic, Consistent and Isolated. > > > > When using the "PRAGMA synchronous=off" your database might be > corrupted > > by a power failure or OS crash. When using asynchronous IO this > should > > not > > be possible (assuming the hardware is not being untruthful - just as > > when > > using regular "PRAGMA synchronous=full" mode without the async IO > VFS). > > > Ah, the bulb has lit. Because the writes and syncs are processed by the > single queue in order, journal writes are guaranteed to be synced and > consistent before main in-place updates to the db file. > > Might be worth mentioning this in the documentation, as this is not > clear without examining the source. > > In that case, I like it :) > > Is this something that might be made the default in the future, with > the > addition of some synchronization between foreground and background > threads > on the xSync messages to emulate the existing "PRAGMA synchronous=full" > behaviour? > > > > > Dan. > > Christian > ___ > 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] SQLite version 3.6.14
May I suggest an extension PRAGMA SYNCHRONOUS = 3 | ASYNC so that non-C bindings can use the async functionality? Thanks, this is a great enhancement! Jim >> On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: >>> >>> A new optional extension is included that implements an >>> asynchronous I/ >>> O backend for SQLite on either windows or unix. The asynchronous I/O >>> backend processes all writes using a background thread. This gives >>> the appearance of faster response time at the cost of durability and >>> additional memory usage. See http://www.sqlite.org/asyncvfs.html for >>> additional information. -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote: > > On May 8, 2009, at 5:21 PM, Christian Smith wrote: > > > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: > >> > >> A new optional extension is included that implements an > >> asynchronous I/ > >> O backend for SQLite on either windows or unix. The asynchronous I/O > >> backend processes all writes using a background thread. This gives > >> the appearance of faster response time at the cost of durability and > >> additional memory usage. See http://www.sqlite.org/asyncvfs.html for > >> additional information. > > > > > > What are the benefits of using async I/O over "PRAGMA synchronous = > > OFF"? > > If AIO is used for the rollback journal as well, you've lost your ACID > > properties already, so you may as well just use "PRAGMA synchronous > > = OFF" > > anyway and keep the code simpler. > > That's not the case. You lose the Durability property, in that a COMMIT > statement may return before a transaction is stored on the persistent > media, > but transactions are still Atomic, Consistent and Isolated. > > When using the "PRAGMA synchronous=off" your database might be corrupted > by a power failure or OS crash. When using asynchronous IO this should > not > be possible (assuming the hardware is not being untruthful - just as > when > using regular "PRAGMA synchronous=full" mode without the async IO VFS). Ah, the bulb has lit. Because the writes and syncs are processed by the single queue in order, journal writes are guaranteed to be synced and consistent before main in-place updates to the db file. Might be worth mentioning this in the documentation, as this is not clear without examining the source. In that case, I like it :) Is this something that might be made the default in the future, with the addition of some synchronization between foreground and background threads on the xSync messages to emulate the existing "PRAGMA synchronous=full" behaviour? > > Dan. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On May 8, 2009, at 5:21 PM, Christian Smith wrote: > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: >> >> A new optional extension is included that implements an >> asynchronous I/ >> O backend for SQLite on either windows or unix. The asynchronous I/O >> backend processes all writes using a background thread. This gives >> the appearance of faster response time at the cost of durability and >> additional memory usage. See http://www.sqlite.org/asyncvfs.html for >> additional information. > > > What are the benefits of using async I/O over "PRAGMA synchronous = > OFF"? > If AIO is used for the rollback journal as well, you've lost your ACID > properties already, so you may as well just use "PRAGMA synchronous > = OFF" > anyway and keep the code simpler. That's not the case. You lose the Durability property, in that a COMMIT statement may return before a transaction is stored on the persistent media, but transactions are still Atomic, Consistent and Isolated. When using the "PRAGMA synchronous=off" your database might be corrupted by a power failure or OS crash. When using asynchronous IO this should not be possible (assuming the hardware is not being untruthful - just as when using regular "PRAGMA synchronous=full" mode without the async IO VFS). Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. What are the benefits of using async I/O over "PRAGMA synchronous = OFF"? If AIO is used for the rollback journal as well, you've lost your ACID properties already, so you may as well just use "PRAGMA synchronous = OFF" anyway and keep the code simpler. Where I might be able to see the benefit of this background thread is if the background thread grouped all pending write requests into a single writev (or win32 equiv), which would reduce the system call count, but this may be offset by all the extra memory buffer copying that is occurring when copying a write request to the write queue. We now have 2 buffer copies when writing a buffer (once to the AIO queue, plus the copy to the OS.) Are there any benchmarks numbers that indicate AIO is better than the async PRAGMA? Cheers, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
Am Wed, 6 May 2009 22:36:50 -0400 schrieb "D. Richard Hipp" : > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous > I/ O backend for SQLite on either windows or unix. The asynchronous > I/O backend processes all writes using a background thread. This > gives the appearance of faster response time at the cost of > durability and additional memory usage. See > http://www.sqlite.org/asyncvfs.html for additional information. > > This release also includes many small bug fixes and documentation > improvements. > > As always, please let me know if you encounter any difficulties. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ==> Beginne build()... configure: error: configure script is out of date: configure $PACKAGE_VERSION = 3.6.13 top level VERSION file = 3.6.14 please regen with autoconf worked with running autoreconf here. -Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
Obviously i had not looked at the code. I see now that the async code is registered as a VFS... And thanks to Dan there is only one background thread and queue. One could implement the background thread and a function, mutex and condition variable so that the function would call the sqlite3async_control setting the SQLITEASYNC_HALT_IDLE. The function would then block on the condition awaiting the running thread to complete the work. The running thread would then signal the condition causing the "waiting" function to return. --- On Thu, 5/7/09, Virgilio Alexandre Fornazin wrote: > From: Virgilio Alexandre Fornazin > Subject: RE: [sqlite] SQLite version 3.6.14 and async vfs > To: kennethinbox-sql...@yahoo.com, "'General Discussion of SQLite Database'" > , "'Pavel Ivanov'" > Date: Thursday, May 7, 2009, 12:04 PM > This break purpose of VFS, all VFS > should work in same way, you must not > know if your VFS is asynchronous > or not. VFS close method should wait for all file I/O on > this database > handle (not all databases) to > finalize before returning, providing compatibility with all > other existing > VFS implementations. > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Ken > Sent: quinta-feira, 7 de maio de 2009 13:47 > To: General Discussion of SQLite Database; Pavel Ivanov > Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs > > > I see the confusion with the word "Shutdown". > > How about but a call that would block until the async > thread completes all > operations that are enqueued. Effectively a Close of the > async thread/queue > and db. The call could be sqlite3Async_close. > > Hope that clarifies my intent. > > > --- On Thu, 5/7/09, Pavel Ivanov > wrote: > > > From: Pavel Ivanov > > Subject: Re: [sqlite] SQLite version 3.6.14 and async > vfs > > To: kennethinbox-sql...@yahoo.com, > "General Discussion of SQLite Database" > > > Date: Thursday, May 7, 2009, 11:10 AM > > Shutdown is not an option at all. I > > need vfs to continue working on > > other databases but to be notified (or have > possibility to > > check) when > > one particular database is no longer opened. > > > > Pavel > > > > On Thu, May 7, 2009 at 12:00 PM, Ken > > wrote: > > > > > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin > > > wrote: > > > > > >> From: Virgilio Alexandre Fornazin > > >> Subject: Re: [sqlite] SQLite version 3.6.14 > and > > async vfs > > >> To: "'General Discussion of SQLite > Database'" > > > > >> Date: Thursday, May 7, 2009, 10:50 AM > > >> Close should wait for all file > > >> operations complete to meet that needs. > > >> I think asynchronous VFS should take care of > > waiting in > > >> sqlite3_close() > > >> call. > > >> > > >> -Original Message- > > >> From: sqlite-users-boun...@sqlite.org > > >> [mailto:sqlite-users-boun...@sqlite.org] > > >> On Behalf Of Pavel Ivanov > > >> Sent: quinta-feira, 7 de maio de 2009 12:33 > > >> To: General Discussion of SQLite Database > > >> Subject: Re: [sqlite] SQLite version 3.6.14 > and > > async vfs > > >> > > >> Hi! > > >> > > >> It's great to hear about performance > improvements > > and > > >> especially about > > >> asynchronous I/O extension. Thank you very > much > > for your > > >> work! > > >> > > >> I have one question though: taking quick look > at > > the > > >> sources of async > > >> vfs I've noticed that even closing the file > is > > just a task > > >> in the > > >> async queue and thus after closing sqlite > > connection file > > >> remains > > >> opened for some time. It sounds pretty > reasonable, > > but here > > >> stands the > > >> question: what if I want to do something with > the > > database > > >> file after > > >> I close sqlite connection to it (e.g. move to > the > > archive > > >> directory, > > >> zip it etc.)? With sync vfs I could be sure > that > > after > > >> closing > > >> connection file is closed and I can do with > it > > whatever I > > >&g
Re: [sqlite] SQLite version 3.6.14 and async vfs
This break purpose of VFS, all VFS should work in same way, you must not know if your VFS is asynchronous or not. VFS close method should wait for all file I/O on this database handle (not all databases) to finalize before returning, providing compatibility with all other existing VFS implementations. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ken Sent: quinta-feira, 7 de maio de 2009 13:47 To: General Discussion of SQLite Database; Pavel Ivanov Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs I see the confusion with the word "Shutdown". How about but a call that would block until the async thread completes all operations that are enqueued. Effectively a Close of the async thread/queue and db. The call could be sqlite3Async_close. Hope that clarifies my intent. --- On Thu, 5/7/09, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs > To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" > Date: Thursday, May 7, 2009, 11:10 AM > Shutdown is not an option at all. I > need vfs to continue working on > other databases but to be notified (or have possibility to > check) when > one particular database is no longer opened. > > Pavel > > On Thu, May 7, 2009 at 12:00 PM, Ken > wrote: > > > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin > wrote: > > > >> From: Virgilio Alexandre Fornazin > >> Subject: Re: [sqlite] SQLite version 3.6.14 and > async vfs > >> To: "'General Discussion of SQLite Database'" > > >> Date: Thursday, May 7, 2009, 10:50 AM > >> Close should wait for all file > >> operations complete to meet that needs. > >> I think asynchronous VFS should take care of > waiting in > >> sqlite3_close() > >> call. > >> > >> -Original Message- > >> From: sqlite-users-boun...@sqlite.org > >> [mailto:sqlite-users-boun...@sqlite.org] > >> On Behalf Of Pavel Ivanov > >> Sent: quinta-feira, 7 de maio de 2009 12:33 > >> To: General Discussion of SQLite Database > >> Subject: Re: [sqlite] SQLite version 3.6.14 and > async vfs > >> > >> Hi! > >> > >> It's great to hear about performance improvements > and > >> especially about > >> asynchronous I/O extension. Thank you very much > for your > >> work! > >> > >> I have one question though: taking quick look at > the > >> sources of async > >> vfs I've noticed that even closing the file is > just a task > >> in the > >> async queue and thus after closing sqlite > connection file > >> remains > >> opened for some time. It sounds pretty reasonable, > but here > >> stands the > >> question: what if I want to do something with the > database > >> file after > >> I close sqlite connection to it (e.g. move to the > archive > >> directory, > >> zip it etc.)? With sync vfs I could be sure that > after > >> closing > >> connection file is closed and I can do with it > whatever I > >> want. Is > >> there a way to catch the moment of actual file > closing with > >> async vfs? > >> > >> And another question just to be sure that I > understand it > >> correctly: > >> async vfs holds only one queue for all opened > database > >> files, right? > >> > >> Pavel > >> > >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp > > >> wrote: > >> > SQLite version 3.6.14 is now available on the > SQLite > >> website > >> > > >> > http://www.sqlite.org/ > >> > > >> > Version 3.6.14 contains performance enhances > in the > >> btree and pager > >> > subsystems. In addition, the query > optimizer now > >> knows how to take > >> > advantage of OR and IN operators on columns > of a > >> virtual table. > >> > > >> > A new optional extension is included that > implements > >> an asynchronous I/ > >> > O backend for SQLite on either windows or > unix. The > >> asynchronous I/O > >> > backend processes all writes using a > background > >> thread. This gives > >> > the appearance of faster response time at the > cost of > >> durability and > >> > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > >> > additi
Re: [sqlite] SQLite version 3.6.14 and async vfs
According to the fact that queue in async vfs is one for all databases and along with closing of one database there could be some writings to another ones, method of catching the return from sqlite3async_run() can be significantly delayed if work at all... Ok, thank you for the extension anyway. I will think what can I do in this situation. Pavel On Thu, May 7, 2009 at 12:54 PM, Dan wrote: > > On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote: > >> Hi! >> >> It's great to hear about performance improvements and especially about >> asynchronous I/O extension. Thank you very much for your work! >> >> I have one question though: taking quick look at the sources of async >> vfs I've noticed that even closing the file is just a task in the >> async queue and thus after closing sqlite connection file remains >> opened for some time. It sounds pretty reasonable, but here stands the >> question: what if I want to do something with the database file after >> I close sqlite connection to it (e.g. move to the archive directory, >> zip it etc.)? With sync vfs I could be sure that after closing >> connection file is closed and I can do with it whatever I want. Is >> there a way to catch the moment of actual file closing with async vfs? > > Not easily. With the current code you could call sqlite3async_control() > to configure the background thread to return when the write-queue is > empty > (SQLITEASYNC_HALT_IDLE). When the call to sqlite3async_run() returns you > can be sure that the queue is empty and thus any close-file operation > must > have been flushed through. > >> And another question just to be sure that I understand it correctly: >> async vfs holds only one queue for all opened database files, right? > > True statement. > > 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
Re: [sqlite] SQLite version 3.6.14 and async vfs
On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote: > Hi! > > It's great to hear about performance improvements and especially about > asynchronous I/O extension. Thank you very much for your work! > > I have one question though: taking quick look at the sources of async > vfs I've noticed that even closing the file is just a task in the > async queue and thus after closing sqlite connection file remains > opened for some time. It sounds pretty reasonable, but here stands the > question: what if I want to do something with the database file after > I close sqlite connection to it (e.g. move to the archive directory, > zip it etc.)? With sync vfs I could be sure that after closing > connection file is closed and I can do with it whatever I want. Is > there a way to catch the moment of actual file closing with async vfs? Not easily. With the current code you could call sqlite3async_control() to configure the background thread to return when the write-queue is empty (SQLITEASYNC_HALT_IDLE). When the call to sqlite3async_run() returns you can be sure that the queue is empty and thus any close-file operation must have been flushed through. > And another question just to be sure that I understand it correctly: > async vfs holds only one queue for all opened database files, right? True statement. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
I see the confusion with the word "Shutdown". How about but a call that would block until the async thread completes all operations that are enqueued. Effectively a Close of the async thread/queue and db. The call could be sqlite3Async_close. Hope that clarifies my intent. --- On Thu, 5/7/09, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs > To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" > > Date: Thursday, May 7, 2009, 11:10 AM > Shutdown is not an option at all. I > need vfs to continue working on > other databases but to be notified (or have possibility to > check) when > one particular database is no longer opened. > > Pavel > > On Thu, May 7, 2009 at 12:00 PM, Ken > wrote: > > > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin > wrote: > > > >> From: Virgilio Alexandre Fornazin > >> Subject: Re: [sqlite] SQLite version 3.6.14 and > async vfs > >> To: "'General Discussion of SQLite Database'" > > >> Date: Thursday, May 7, 2009, 10:50 AM > >> Close should wait for all file > >> operations complete to meet that needs. > >> I think asynchronous VFS should take care of > waiting in > >> sqlite3_close() > >> call. > >> > >> -Original Message- > >> From: sqlite-users-boun...@sqlite.org > >> [mailto:sqlite-users-boun...@sqlite.org] > >> On Behalf Of Pavel Ivanov > >> Sent: quinta-feira, 7 de maio de 2009 12:33 > >> To: General Discussion of SQLite Database > >> Subject: Re: [sqlite] SQLite version 3.6.14 and > async vfs > >> > >> Hi! > >> > >> It's great to hear about performance improvements > and > >> especially about > >> asynchronous I/O extension. Thank you very much > for your > >> work! > >> > >> I have one question though: taking quick look at > the > >> sources of async > >> vfs I've noticed that even closing the file is > just a task > >> in the > >> async queue and thus after closing sqlite > connection file > >> remains > >> opened for some time. It sounds pretty reasonable, > but here > >> stands the > >> question: what if I want to do something with the > database > >> file after > >> I close sqlite connection to it (e.g. move to the > archive > >> directory, > >> zip it etc.)? With sync vfs I could be sure that > after > >> closing > >> connection file is closed and I can do with it > whatever I > >> want. Is > >> there a way to catch the moment of actual file > closing with > >> async vfs? > >> > >> And another question just to be sure that I > understand it > >> correctly: > >> async vfs holds only one queue for all opened > database > >> files, right? > >> > >> Pavel > >> > >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp > > >> wrote: > >> > SQLite version 3.6.14 is now available on the > SQLite > >> website > >> > > >> > http://www.sqlite.org/ > >> > > >> > Version 3.6.14 contains performance enhances > in the > >> btree and pager > >> > subsystems. In addition, the query > optimizer now > >> knows how to take > >> > advantage of OR and IN operators on columns > of a > >> virtual table. > >> > > >> > A new optional extension is included that > implements > >> an asynchronous I/ > >> > O backend for SQLite on either windows or > unix. The > >> asynchronous I/O > >> > backend processes all writes using a > background > >> thread. This gives > >> > the appearance of faster response time at the > cost of > >> durability and > >> > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > >> > additional information. > >> > > >> > This release also includes many small bug > fixes and > >> documentation > >> > improvements. > >> > > >> > As always, please let me know if you > encounter any > >> difficulties. > >> > > >> > D. Richard Hipp > >> > d...@hwaci.com > >> > > >> > > > > > Without actually looking at the async code I think > that instead of using the sqlite3_close to cause a block > there should be a "shutdown" that would wait for the > shutdown of the async thread to complete. So maybe a better > name would be sqlite3Async_close or something similar. > > > > Ken > > > > > > ___ > > 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] SQLite version 3.6.14 and async vfs
Shutdown is not an option at all. I need vfs to continue working on other databases but to be notified (or have possibility to check) when one particular database is no longer opened. Pavel On Thu, May 7, 2009 at 12:00 PM, Ken wrote: > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin > wrote: > >> From: Virgilio Alexandre Fornazin >> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs >> To: "'General Discussion of SQLite Database'" >> Date: Thursday, May 7, 2009, 10:50 AM >> Close should wait for all file >> operations complete to meet that needs. >> I think asynchronous VFS should take care of waiting in >> sqlite3_close() >> call. >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] >> On Behalf Of Pavel Ivanov >> Sent: quinta-feira, 7 de maio de 2009 12:33 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs >> >> Hi! >> >> It's great to hear about performance improvements and >> especially about >> asynchronous I/O extension. Thank you very much for your >> work! >> >> I have one question though: taking quick look at the >> sources of async >> vfs I've noticed that even closing the file is just a task >> in the >> async queue and thus after closing sqlite connection file >> remains >> opened for some time. It sounds pretty reasonable, but here >> stands the >> question: what if I want to do something with the database >> file after >> I close sqlite connection to it (e.g. move to the archive >> directory, >> zip it etc.)? With sync vfs I could be sure that after >> closing >> connection file is closed and I can do with it whatever I >> want. Is >> there a way to catch the moment of actual file closing with >> async vfs? >> >> And another question just to be sure that I understand it >> correctly: >> async vfs holds only one queue for all opened database >> files, right? >> >> Pavel >> >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp >> wrote: >> > SQLite version 3.6.14 is now available on the SQLite >> website >> > >> > http://www.sqlite.org/ >> > >> > Version 3.6.14 contains performance enhances in the >> btree and pager >> > subsystems. In addition, the query optimizer now >> knows how to take >> > advantage of OR and IN operators on columns of a >> virtual table. >> > >> > A new optional extension is included that implements >> an asynchronous I/ >> > O backend for SQLite on either windows or unix. The >> asynchronous I/O >> > backend processes all writes using a background >> thread. This gives >> > the appearance of faster response time at the cost of >> durability and >> > additional memory usage. See http://www.sqlite.org/asyncvfs.html for >> > additional information. >> > >> > This release also includes many small bug fixes and >> documentation >> > improvements. >> > >> > As always, please let me know if you encounter any >> difficulties. >> > >> > D. Richard Hipp >> > d...@hwaci.com >> > >> > > > Without actually looking at the async code I think that instead of using the > sqlite3_close to cause a block there should be a "shutdown" that would wait > for the shutdown of the async thread to complete. So maybe a better name > would be sqlite3Async_close or something similar. > > Ken > > > ___ > 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] SQLite version 3.6.14 and async vfs
--- On Thu, 5/7/09, Virgilio Alexandre Fornazin wrote: > From: Virgilio Alexandre Fornazin > Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs > To: "'General Discussion of SQLite Database'" > Date: Thursday, May 7, 2009, 10:50 AM > Close should wait for all file > operations complete to meet that needs. > I think asynchronous VFS should take care of waiting in > sqlite3_close() > call. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Pavel Ivanov > Sent: quinta-feira, 7 de maio de 2009 12:33 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs > > Hi! > > It's great to hear about performance improvements and > especially about > asynchronous I/O extension. Thank you very much for your > work! > > I have one question though: taking quick look at the > sources of async > vfs I've noticed that even closing the file is just a task > in the > async queue and thus after closing sqlite connection file > remains > opened for some time. It sounds pretty reasonable, but here > stands the > question: what if I want to do something with the database > file after > I close sqlite connection to it (e.g. move to the archive > directory, > zip it etc.)? With sync vfs I could be sure that after > closing > connection file is closed and I can do with it whatever I > want. Is > there a way to catch the moment of actual file closing with > async vfs? > > And another question just to be sure that I understand it > correctly: > async vfs holds only one queue for all opened database > files, right? > > Pavel > > On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp > wrote: > > SQLite version 3.6.14 is now available on the SQLite > website > > > > http://www.sqlite.org/ > > > > Version 3.6.14 contains performance enhances in the > btree and pager > > subsystems. In addition, the query optimizer now > knows how to take > > advantage of OR and IN operators on columns of a > virtual table. > > > > A new optional extension is included that implements > an asynchronous I/ > > O backend for SQLite on either windows or unix. The > asynchronous I/O > > backend processes all writes using a background > thread. This gives > > the appearance of faster response time at the cost of > durability and > > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > > additional information. > > > > This release also includes many small bug fixes and > documentation > > improvements. > > > > As always, please let me know if you encounter any > difficulties. > > > > D. Richard Hipp > > d...@hwaci.com > > > > Without actually looking at the async code I think that instead of using the sqlite3_close to cause a block there should be a "shutdown" that would wait for the shutdown of the async thread to complete. So maybe a better name would be sqlite3Async_close or something similar. Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
Close should wait for all file operations complete to meet that needs. I think asynchronous VFS should take care of waiting in sqlite3_close() call. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: quinta-feira, 7 de maio de 2009 12:33 To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs Hi! It's great to hear about performance improvements and especially about asynchronous I/O extension. Thank you very much for your work! I have one question though: taking quick look at the sources of async vfs I've noticed that even closing the file is just a task in the async queue and thus after closing sqlite connection file remains opened for some time. It sounds pretty reasonable, but here stands the question: what if I want to do something with the database file after I close sqlite connection to it (e.g. move to the archive directory, zip it etc.)? With sync vfs I could be sure that after closing connection file is closed and I can do with it whatever I want. Is there a way to catch the moment of actual file closing with async vfs? And another question just to be sure that I understand it correctly: async vfs holds only one queue for all opened database files, right? Pavel On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp wrote: > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. > > This release also includes many small bug fixes and documentation > improvements. > > As always, please let me know if you encounter any difficulties. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > 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
Re: [sqlite] SQLite version 3.6.14 and async vfs
Hi! It's great to hear about performance improvements and especially about asynchronous I/O extension. Thank you very much for your work! I have one question though: taking quick look at the sources of async vfs I've noticed that even closing the file is just a task in the async queue and thus after closing sqlite connection file remains opened for some time. It sounds pretty reasonable, but here stands the question: what if I want to do something with the database file after I close sqlite connection to it (e.g. move to the archive directory, zip it etc.)? With sync vfs I could be sure that after closing connection file is closed and I can do with it whatever I want. Is there a way to catch the moment of actual file closing with async vfs? And another question just to be sure that I understand it correctly: async vfs holds only one queue for all opened database files, right? Pavel On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp wrote: > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. > > This release also includes many small bug fixes and documentation > improvements. > > As always, please let me know if you encounter any difficulties. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > 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] SQLite version 3.6.14
> A new optional extension is included that implements an asynchronous > I/ O backend for SQLite on either windows or unix. The asynchronous > I/O backend processes all writes using a background thread. This > gives the appearance of faster response time at the cost of > durability and additional memory usage. See > http://www.sqlite.org/asyncvfs.html for additional information. Is this extension compiled in your Win32 DLL binary? Thanks! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
Hello! On Thursday 07 May 2009 06:36:50 D. Richard Hipp wrote: > See http://www.sqlite.org/asyncvfs.html for > additional information. Can you add some diagramms of async I/O database usage? And usage scenarios may be very useful. Now I'm don't undertand when can applications to get help from this. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On 07/05/2009, at 04.36, D. Richard Hipp wrote: > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous > I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. > > This release also includes many small bug fixes and documentation > improvements. > > As always, please let me know if you encounter any difficulties. Only a compile warning: sqlite3.m: In function 'proxyGetLockPath': sqlite3.m:25276: warning: unused variable 'err' Easily fixed with: Index: sqlite3.m === --- sqlite3.m (revision 581) +++ sqlite3.m (working copy) @@ -25273,8 +25273,8 @@ len = strlcat(lPath, "sqliteplocks", maxLen); if( mkdir(lPath, SQLITE_DEFAULT_PROXYDIR_PERMISSIONS) ){ /* if mkdir fails, handle as lock file creation failure */ +# ifdef SQLITE_DEBUG int err = errno; -# ifdef SQLITE_DEBUG if( err!=EEXIST ){ fprintf(stderr, "proxyGetLockPath: mkdir(%s,0%o) error %d %s \n", lPath, SQLITE_DEFAULT_PROXYDIR_PERMISSIONS, err, strerror(err)); Thanks, jules ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On Wed, May 6, 2009 at 7:36 PM, D. Richard Hipp wrote: > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. Asynchronous I/O is already the name of some advanced I/O APIs designed for high scalability, where you start an I/O operation and are notified when it completes. Some databases use this form of I/O, so it might be even more confusing if developers are expecting the performance perks and features it provides. Might I suggest calling this "delayed" I/O as MySQL does (see INSERT DELAYED)? Or maybe "deferred", I always thought that was a better name for it. Thanks for the new release! -- Cory Nelson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.14
SQLite version 3.6.14 is now available on the SQLite website http://www.sqlite.org/ Version 3.6.14 contains performance enhances in the btree and pager subsystems. In addition, the query optimizer now knows how to take advantage of OR and IN operators on columns of a virtual table. A new optional extension is included that implements an asynchronous I/ O backend for SQLite on either windows or unix. The asynchronous I/O backend processes all writes using a background thread. This gives the appearance of faster response time at the cost of durability and additional memory usage. See http://www.sqlite.org/asyncvfs.html for additional information. This release also includes many small bug fixes and documentation improvements. As always, please let me know if you encounter any difficulties. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users