Re: [sqlite] SQLite version 3.6.14

2009-05-08 Thread Mark Spiegel 
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

2009-05-08 Thread Doug 
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

2009-05-08 Thread Jim Wilcoxson 
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

2009-05-08 Thread Christian Smith 
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

2009-05-08 Thread Dan 

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

2009-05-08 Thread Christian Smith 
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

2009-05-07 Thread Andreas Radke 
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

2009-05-07 Thread Ken 

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 <virgilioforna...@gmail.com> 
wrote:

> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> Subject: RE: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "'General Discussion of SQLite Database'" 
> <sqlite-users@sqlite.org>, "'Pavel Ivanov'" <paiva...@gmail.com>
> 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 <paiva...@gmail.com>
> wrote:
> 
> > From: Pavel Ivanov <paiva...@gmail.com>
> > Subject: Re: [sqlite] SQLite version 3.6.14 and async
> vfs
> > To: kennethinbox-sql...@yahoo.com,
> "General Discussion of SQLite Database"
> <sqlite-users@sqlite.org>
> > 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 <kennethinbox-sql...@yahoo.com>
> > wrote:
> > >
> > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin
> <virgilioforna...@gmail.com>
> > wrote:
> > >
> > >> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> > >> Subject: Re: [sqlite] SQLite version 3.6.14
> and
> > async vfs
> > >> To: "'General Discussion of SQLite
> Database'"
> > <sqlite-users@sqlite.org>
> > >> 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread Virgilio Alexandre Fornazin 
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 <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database"
<sqlite-users@sqlite.org>
> 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 <kennethinbox-sql...@yahoo.com>
> wrote:
> >
> > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin
<virgilioforna...@gmail.com>
> wrote:
> >
> >> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >> To: "'General Discussion of SQLite Database'"
> <sqlite-users@sqlite.org>
> >> 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
> <d...@hwaci.com>
> >> 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread Pavel Ivanov 
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

2009-05-07 Thread Dan 

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

2009-05-07 Thread Ken 

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 <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> 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 <kennethinbox-sql...@yahoo.com>
> wrote:
> >
> > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> wrote:
> >
> >> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >> To: "'General Discussion of SQLite Database'"
> <sqlite-users@sqlite.org>
> >> 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
> <d...@hwaci.com>
> >> 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

2009-05-07 Thread Pavel Ivanov 
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 <kennethinbox-sql...@yahoo.com> wrote:
>
> --- On Thu, 5/7/09, Virgilio Alexandre Fornazin <virgilioforna...@gmail.com> 
> wrote:
>
>> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
>> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
>> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org>
>> 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 <d...@hwaci.com>
>> 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

2009-05-07 Thread Ken 

--- On Thu, 5/7/09, Virgilio Alexandre Fornazin <virgilioforna...@gmail.com> 
wrote:

> From: Virgilio Alexandre Fornazin <virgilioforna...@gmail.com>
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org>
> 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 <d...@hwaci.com>
> 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

2009-05-07 Thread Virgilio Alexandre Fornazin 
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 <d...@hwaci.com> 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

2009-05-07 Thread Pavel Ivanov 
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

2009-05-07 Thread Lukas Gebauer 
> 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

2009-05-07 Thread Alexey Pechnikov 
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

2009-05-07 Thread Jules Colding 

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

2009-05-06 Thread Cory Nelson 
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