Re: [sqlite] Asynchronous I/O Module For SQLite vs Single T hread mode

[Gilles]

Hi,

How pertinent is this source code (are the calls made in the right order?,...)?
Could this source code be a rather good basis for "Asynchronous I/O modules for
SQLite"?

Thanks a lot,

Best regards,

Gilles


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite vs Single T hread mode

[Gilles]

Hello,

Here follows, my Async IO implementation (at the moment, I just have tested the
Win32 part. I will test soon the pthreads part).

The main thread creates a worker thread (in charge of running the
sqlite3async_run function).

In "Build options/compiler/defines", it seems that the following is working:
SQLITE_THREADSAFE=0



#include 
#include 
#include "sqlite3.h"
#include "sqlite3async.h"

#if SQLITE_OS_WIN || defined(_WIN32) || defined(WIN32) || defined(__CYGWIN__) ||
defined(__MINGW32__) || defined(__BORLANDC__)
#include 
#else
#include 
#endif


static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  NotUsed=0;
  int i;
  for(i=0; ihThread = CreateThread(
NULL,   // default security attributes
0,  // use default stack size
(LPTHREAD_START_ROUTINE)sqlite3async_run,   // thread function 
name
NULL,   // argument to thread function
0,  // use default creation flags
NULL))){ // returns the thread identifier
return 0;
}

#else
/* Create a thread using pthreads API - Not tested at the moment */
if(pthread_create(>th, NULL, sqlite3async_run, NULL)){
return 0;
}
#endif

/* Database must exist */
if(sqlite3_open_v2(sDatabaseFile, >pdb, SQLITE_OPEN_EXCLUSIVE |
SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK){
/* display some error */
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(pasyncsql->pdb));
sqlite3_close(pasyncsql->pdb);
return 0;
}
return 1;
}

void shutdownSQLite(async_sqlite3 *pasyncsql, int force){
/* force <> 0 : HALT SQLITE NOW
   force = 0  : HALT WHEN IDLE */
sqlite3async_control(SQLITEASYNC_HALT, force ? SQLITEASYNC_HALT_NOW :
SQLITEASYNC_HALT_IDLE);

#if SQLITE_OS_WIN || defined(_WIN32) || defined(WIN32) || defined(__CYGWIN__) ||
defined(__MINGW32__) || defined(__BORLANDC__)
/* Wait for the sqlite3async_run thread completion using Windows API */
if(pasyncsql->hThread){
WaitForSingleObject(pasyncsql->hThread, INFINITE);
CloseHandle(pasyncsql->hThread);
pasyncsql->hThread = NULL;
}
#else
/* Wait for the sqlite3async_run thread completion using pthreads API */
if(pasyncsql->th){
pthread_join(pasyncsql->th, NULL);
pasyncsql->th = NULL;
}
#endif

/* close sqlite database */
if(pasyncsql->pdb){
sqlite3_close(pasyncsql->pdb);
pasyncsql->pdb = NULL;
}

/* shutdown async IO */
sqlite3async_shutdown();
}


int main(int argc, char **argv){
  async_sqlite3 asyncsql;
  char *zErrMsg = 0;
  int rc;

  if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
exit(1);
  }

if(!initSQLite(argv[1], 1, )){
exit(1);
}

  rc = sqlite3_exec(asyncsql.pdb, argv[2], callback, 0, );
  if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
/* This will free zErrMsg if assigned */
if (zErrMsg)
   free(zErrMsg);
  } else {
printf("ok\n");
  }

  shutdownSQLite(, 0);
  return 0;
}



___
sqlite-users mailing list
sqlite-users@sqlite.org

[sqlite] Asynchronous I/O Module For SQLite vs Single Thread mode

[Gilles]

Hello,

When sqlite3async ("Asynchronous I/O Module For SQLite" -
http://www.sqlite.org/asyncvfs.html) is used, is it possible to keep running
SQLite in the Single-thread mode (as described in
http://www.sqlite.org/threadsafe.html) or does this necessarily mean that the
Multi-threads or serialized modes must be used?

Thanks a lot,

Best regards,

Gilles

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

Thanks for the answers.  I did give compiling with 3.5.9 a try, but it looks 
like the changes in vfs are too great:

sqlite3async.c: In function `asyncOpen':
sqlite3async.c:1019: warning: initialization from incompatible pointer type
sqlite3async.c: In function `asyncAccess':
sqlite3async.c:1182: error: too many arguments to function
sqlite3async.c: At top level:
sqlite3async.c:1290: warning: initialization from incompatible pointer type
sqlite3async.c:1291: warning: initialization from incompatible pointer type
sqlite3async.c:1292: warning: initialization from incompatible pointer type
sqlite3async.c:1293: warning: initialization from incompatible pointer type
sqlite3async.c:1294: warning: initialization from incompatible pointer type
sqlite3async.c:1295: warning: initialization from incompatible pointer type
sqlite3async.c:1296: warning: initialization from incompatible pointer type
sqlite3async.c:1297: warning: initialization from incompatible pointer type
sqlite3async.c:1299: warning: initialization from incompatible pointer type

I was able to build and use the asynch i/o module with 3.6.15 without too much 
difficulty.

I need to re-verify this, but the 3.6.15 version seems to be faster than the 
3.5.9 version.

Also, I get the best performance (without the asynch io module coded in) is 
with these two pragmas together:

  sqlite3_exec(db, "pragma synchronous=off;", 0, 0, );
  sqlite3_exec(db, "pragma locking_mode=exclusive;", 0, 0, );

Since I only plan on using this db from a single process, and don't need to 
handle the case of power failure or other hardware issues, this combo seems to 
make the most sense.  My only concern is to get data out of memory as quickly 
as possible, and be able to restore it back to memory.  The latter is a very 
infrequent occurrence - less than 1% of the time.

Ray

-Original Message-
From: Dan [mailto:danielk1...@gmail.com]
Sent: Thursday, June 18, 2009 3:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Asynchronous I/O Module For SQLite


On Jun 18, 2009, at 11:45 PM, Rizzuto, Raymond wrote:

> Simon,
>
> I appreciate your input, but I don't think I have provided enough
> details on the constraints my application has to meet for you to
> evaluate whether asynch i/o is a good solution or not for me.
>
> I don't think it is practical for me to get into that level of
> detail, even if I wanted to.  I really just want some clarification
> on how to use asynch i/o module, as described on 
> http://sqlite.org/asyncvfs.html
> .  My questions are very simple:
>
> 1) sqlite3async.c and sqlite3async.h aren't in the amalgamation's.
> I can get them from sqlite-3.6.15.tar.gz, but I wanted to verify
> that this was the correct procedure, and that those 2 files are all
> I need.

That's all you need.

> 2) Since these two files are compiled into the app, and seem to
> stand apart from the core sqlite3, I was curious if I could use them
> with sqlite3 version 3.5.9 since that version is already in use in
> my organization.

Might very well work on unix systems. I think there were some
incompatibilities
with the windows OS layer that were fixed more recently than 3.5.9
though.

Dan.



> If no one can answer those questions, I'll just have to give it a
> try, and see if that meets my needs or not.  It may be that sqlite3,
> with or without the asynch i/o module, isn't the right choice for my
> needs.
>
> Ray
>
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Thursday, June 18, 2009 12:10 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Asynchronous I/O Module For SQLite
>
>
> On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote:
>
>> I'm actually memory limited, which is why I am looking at moving
>> infrequently needed object to persistent store with sqlite3.  I like
>> the idea of using the asynchronous i/o module so that the writing
>> can be done in a background thread, but still allow the main thread
>> to retrieve data that has been written or is in queue to be written.
>
> Aren't those two things in conflict ?  Caching and multiple concurrent
> threads are two features which chew up a great deal of memory: a
> cache /is/ memory, and each independent thread requires its own
> working-memory.  If you're trying to minimise memory-usage you tend to
> have no caching and single threads.
>
> Simon.
>
>
> IMPORTANT: The information contained in this email and/or its
> attachments is confidential. If you are not the intended recipient,
> please notify the sender immediately by reply and immediately delete
> this message and all its attachments. Any review, use, reproduction,
> disclosure or dissemination of this message or any attachment by an
> unintended recipient is 

Re: [sqlite] Asynchronous I/O Module For SQLite

[Dan]

On Jun 18, 2009, at 11:45 PM, Rizzuto, Raymond wrote:

> Simon,
>
> I appreciate your input, but I don't think I have provided enough  
> details on the constraints my application has to meet for you to  
> evaluate whether asynch i/o is a good solution or not for me.
>
> I don't think it is practical for me to get into that level of  
> detail, even if I wanted to.  I really just want some clarification  
> on how to use asynch i/o module, as described on 
> http://sqlite.org/asyncvfs.html 
> .  My questions are very simple:
>
> 1) sqlite3async.c and sqlite3async.h aren't in the amalgamation's.   
> I can get them from sqlite-3.6.15.tar.gz, but I wanted to verify  
> that this was the correct procedure, and that those 2 files are all  
> I need.

That's all you need.

> 2) Since these two files are compiled into the app, and seem to  
> stand apart from the core sqlite3, I was curious if I could use them  
> with sqlite3 version 3.5.9 since that version is already in use in  
> my organization.

Might very well work on unix systems. I think there were some  
incompatibilities
with the windows OS layer that were fixed more recently than 3.5.9  
though.

Dan.



> If no one can answer those questions, I'll just have to give it a  
> try, and see if that meets my needs or not.  It may be that sqlite3,  
> with or without the asynch i/o module, isn't the right choice for my  
> needs.
>
> Ray
>
> -Original Message-
> From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
> Sent: Thursday, June 18, 2009 12:10 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Asynchronous I/O Module For SQLite
>
>
> On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote:
>
>> I'm actually memory limited, which is why I am looking at moving
>> infrequently needed object to persistent store with sqlite3.  I like
>> the idea of using the asynchronous i/o module so that the writing
>> can be done in a background thread, but still allow the main thread
>> to retrieve data that has been written or is in queue to be written.
>
> Aren't those two things in conflict ?  Caching and multiple concurrent
> threads are two features which chew up a great deal of memory: a
> cache /is/ memory, and each independent thread requires its own
> working-memory.  If you're trying to minimise memory-usage you tend to
> have no caching and single threads.
>
> Simon.
>
>
> IMPORTANT: The information contained in this email and/or its  
> attachments is confidential. If you are not the intended recipient,  
> please notify the sender immediately by reply and immediately delete  
> this message and all its attachments. Any review, use, reproduction,  
> disclosure or dissemination of this message or any attachment by an  
> unintended recipient is strictly prohibited. Neither this message  
> nor any attachment is intended as or should be construed as an  
> offer, solicitation or recommendation to buy or sell any security or  
> other financial instrument. Neither the sender, his or her employer  
> nor any of their respective affiliates makes any warranties as to  
> the completeness or accuracy of any of the information contained  
> herein or that this message or any of its attachments is free of  
> viruses.
> ___
> 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] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

Simon,

I appreciate your input, but I don't think I have provided enough details on 
the constraints my application has to meet for you to evaluate whether asynch 
i/o is a good solution or not for me.

I don't think it is practical for me to get into that level of detail, even if 
I wanted to.  I really just want some clarification on how to use asynch i/o 
module, as described on http://sqlite.org/asyncvfs.html.  My questions are very 
simple:

1) sqlite3async.c and sqlite3async.h aren't in the amalgamation's.  I can get 
them from sqlite-3.6.15.tar.gz, but I wanted to verify that this was the 
correct procedure, and that those 2 files are all I need.

2) Since these two files are compiled into the app, and seem to stand apart 
from the core sqlite3, I was curious if I could use them with sqlite3 version 
3.5.9 since that version is already in use in my organization.

If no one can answer those questions, I'll just have to give it a try, and see 
if that meets my needs or not.  It may be that sqlite3, with or without the 
asynch i/o module, isn't the right choice for my needs.

Ray

-Original Message-
From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk]
Sent: Thursday, June 18, 2009 12:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Asynchronous I/O Module For SQLite


On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote:

> I'm actually memory limited, which is why I am looking at moving
> infrequently needed object to persistent store with sqlite3.  I like
> the idea of using the asynchronous i/o module so that the writing
> can be done in a background thread, but still allow the main thread
> to retrieve data that has been written or is in queue to be written.

Aren't those two things in conflict ?  Caching and multiple concurrent
threads are two features which chew up a great deal of memory: a
cache /is/ memory, and each independent thread requires its own
working-memory.  If you're trying to minimise memory-usage you tend to
have no caching and single threads.

Simon.


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[Simon Slavin]

On 18 Jun 2009, at 2:15pm, Rizzuto, Raymond wrote:

> I'm actually memory limited, which is why I am looking at moving  
> infrequently needed object to persistent store with sqlite3.  I like  
> the idea of using the asynchronous i/o module so that the writing  
> can be done in a background thread, but still allow the main thread  
> to retrieve data that has been written or is in queue to be written.

Aren't those two things in conflict ?  Caching and multiple concurrent  
threads are two features which chew up a great deal of memory: a  
cache /is/ memory, and each independent thread requires its own  
working-memory.  If you're trying to minimise memory-usage you tend to  
have no caching and single threads.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

I tried the pragma synchronous=off.  It has no significant effect on db on an 
nfs mounted file system, but does improve access to a db on a local file 
system.  I still would like to compare the pragma to the asynchronous i/o 
module, if the latter is still supported.


-Original Message-
From: D. Richard Hipp [mailto:d...@hwaci.com]
Sent: Thursday, June 18, 2009 9:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Asynchronous I/O Module For SQLite


On Jun 18, 2009, at 9:15 AM, Rizzuto, Raymond wrote:

> I'm actually memory limited, which is why I am looking at moving
> infrequently needed object to persistent store with sqlite3.  I like
> the idea of using the asynchronous i/o module so that the writing
> can be done in a background thread, but still allow the main thread
> to retrieve data that has been written or is in queue to be written.

You do not need the async I/O package for this.  Simply set "PRAGMA
synchronous=OFF" and it will behave as you desire (assuming your
operating system implements a filesystem cache).

>
>
> My main questions are about that module.  I am a bit confused about
> it since it is not distributed in the main amalgamations.  I wanted
> to make sure that I can just pull the 2 files from the source
> tarball, or if there are other files I would also need.
>
> Additionally, since asynchronous i/o seems to sit apart from the
> main sqlite3, I was wondering if it would be possible to use with an
> older (3.5.9) version of sqlite3 that we already use in house.


D. Richard Hipp
d...@hwaci.com





IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[D. Richard Hipp]

On Jun 18, 2009, at 9:15 AM, Rizzuto, Raymond wrote:

> I'm actually memory limited, which is why I am looking at moving  
> infrequently needed object to persistent store with sqlite3.  I like  
> the idea of using the asynchronous i/o module so that the writing  
> can be done in a background thread, but still allow the main thread  
> to retrieve data that has been written or is in queue to be written.

You do not need the async I/O package for this.  Simply set "PRAGMA  
synchronous=OFF" and it will behave as you desire (assuming your  
operating system implements a filesystem cache).

>
>
> My main questions are about that module.  I am a bit confused about  
> it since it is not distributed in the main amalgamations.  I wanted  
> to make sure that I can just pull the 2 files from the source  
> tarball, or if there are other files I would also need.
>
> Additionally, since asynchronous i/o seems to sit apart from the  
> main sqlite3, I was wondering if it would be possible to use with an  
> older (3.5.9) version of sqlite3 that we already use in house.


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

I'm actually memory limited, which is why I am looking at moving infrequently 
needed object to persistent store with sqlite3.  I like the idea of using the 
asynchronous i/o module so that the writing can be done in a background thread, 
but still allow the main thread to retrieve data that has been written or is in 
queue to be written.

My main questions are about that module.  I am a bit confused about it since it 
is not distributed in the main amalgamations.  I wanted to make sure that I can 
just pull the 2 files from the source tarball, or if there are other files I 
would also need.

Additionally, since asynchronous i/o seems to sit apart from the main sqlite3, 
I was wondering if it would be possible to use with an older (3.5.9) version of 
sqlite3 that we already use in house.

Ray

-Original Message-
From: D. Richard Hipp [mailto:d...@hwaci.com]
Sent: Wednesday, June 17, 2009 7:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Asynchronous I/O Module For SQLite


On Jun 17, 2009, at 7:18 PM, Rizzuto, Raymond wrote:

> I am interested in using sqlite3 for an application where I don't
> have a requirement that data persists after the using process ends.
> The db is used as a backing store for infrequently used records.

So use an in-memory database.  Open with sqlite3_open(":memory:",
).  Or if your content is too big for memory, use a temporary
database whose name is the empty string:  sqlite3_open("", ).

D. Richard Hipp
d...@hwaci.com





IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asynchronous I/O Module For SQLite

[D. Richard Hipp]

On Jun 17, 2009, at 7:18 PM, Rizzuto, Raymond wrote:

> I am interested in using sqlite3 for an application where I don't  
> have a requirement that data persists after the using process ends.   
> The db is used as a backing store for infrequently used records.

So use an in-memory database.  Open with sqlite3_open(":memory:",  
).  Or if your content is too big for memory, use a temporary  
database whose name is the empty string:  sqlite3_open("", ).

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] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

I am interested in using sqlite3 for an application where I don't have a 
requirement that data persists after the using process ends.  The db is used as 
a backing store for infrequently used records.  Performance requirements are 
~1500 "transactions" per second.

I would like to use the asynch i/o module since it sounds like it would meet my 
requirements.  However, I have a few questions:

-  The files for asynch i/o aren't in the amalgamations, but only in 
the tarball of the complete source  tree.  Can I just pull out the 2 files I 
need from that, and use it with the installed sqlite3?
-  We are currently using sqlite 3.5.9 in house.  Can I use the 
asynchronous i/o module with that version?

Thanks for any assistance,

Ray



Ray Rizzuto
raymond.rizz...@sig.com
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)




IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users