Re: [sqlite] sqlite3 performace

[John Stanton]

John Stanton wrote:

Philip Nick wrote:
I tracked down the problem. I was linking an old version of sqlite3 
(3.0.8).
That was the old version we used. I apparently doesn't cache between 
calls.


My code now performs IO very similar to the CLI. Each subsequent query 
once

the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work 
sends

reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the 
connection

into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my
code is using over 900k for every call. I have been looking into the 
CLI

source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite 



Cthulhu Bucks!
http://www.cthulhubucks.com


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 







We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

A note to add to this.  This is for pre 3.5.4. Sqlite which does not 
share connections.  It can be improved by using the latest sqlite and 
sharing a connection and cache.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 performace

[John Stanton]

Philip Nick wrote:

I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my

code is using over 900k for every call. I have been looking into the CLI
source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 performace

[Ken]

Upgrading to 3.5.4 will be bennificial. You should be able to pass the 
connection around to the various threads with no problems.

Just make sure you compile (.configure --enable-threadsafe) and you should be 
good. No need to add your own mutex as its done internally by the sqlite code.

Definately keep the DB open at startup. Close it at shutdown.

The most important thing I know to do from a performance stand point is to use 
transactions and mostly I Use transactions of the form "BEGIN IMMEDIATE" So 
that a lock is taken immediately at the start of the transaction. This allows 
for a much simpler sqlite_busy/sqlite_locked error handling.

Regards,
Ken


Philip Nick <[EMAIL PROTECTED]> wrote: I tracked down the problem. I was 
linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle  wrote:

> On Jan 17, 2008 3:48 PM, Philip Nick 
 wrote:
> > Greetings,
> >
> > Currently I am using sqlite3 in a multi-process/multi-threaded server
> > setting.
>
> opening a file is very slow. You need to move it out of your loop.
>
> > I have noticed my program basically reads the whole database every time
> the
> > query is run. The IO Read bytes increases by 900k for every query we
> run. We
> > also chew a good chunch of the cpu  I have tried keeping the database
> > connection open and open/closing for every query. But there was no
> change.
>
> > if I launch the CLI and run the query it runs instantly and monitoring
> the
> > IO read bytes is see only ~20 bytes of read to execute the query, when
> my
> > code is using over 900k for every call. I have been looking into the CLI
> > source to see what is done differently, but was hoping someone on here
> might
> > have some insight.
>
> The operating system will cache files it reads in memory.
> If your process reads the file and then you open the CLI
> it will still be in memory from before and will not give you
> comparable times.
>
> If your process reads the whole database for every call you didn't
> optimize
> your sql. You need to create indexes to optimize your query
>
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
>
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Philip Nick
E-Mail: [EMAIL PROTECTED]
Cell: 352-262-9067
Web: http://www.ruffasdagut.com

Re: [sqlite] sqlite3 performace

[Philip Nick]

I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

> On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> > Greetings,
> >
> > Currently I am using sqlite3 in a multi-process/multi-threaded server
> > setting.
>
> opening a file is very slow. You need to move it out of your loop.
>
> > I have noticed my program basically reads the whole database every time
> the
> > query is run. The IO Read bytes increases by 900k for every query we
> run. We
> > also chew a good chunch of the cpu  I have tried keeping the database
> > connection open and open/closing for every query. But there was no
> change.
>
> > if I launch the CLI and run the query it runs instantly and monitoring
> the
> > IO read bytes is see only ~20 bytes of read to execute the query, when
> my
> > code is using over 900k for every call. I have been looking into the CLI
> > source to see what is done differently, but was hoping someone on here
> might
> > have some insight.
>
> The operating system will cache files it reads in memory.
> If your process reads the file and then you open the CLI
> it will still be in memory from before and will not give you
> comparable times.
>
> If your process reads the whole database for every call you didn't
> optimize
> your sql. You need to create indexes to optimize your query
>
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
>
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Philip Nick
E-Mail: [EMAIL PROTECTED]
Cell: 352-262-9067
Web: http://www.ruffasdagut.com

Re: [sqlite] sqlite3 performace

[Jay Sprenkle]

On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> Greetings,
>
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.

opening a file is very slow. You need to move it out of your loop.

> I have noticed my program basically reads the whole database every time the
> query is run. The IO Read bytes increases by 900k for every query we run. We
> also chew a good chunch of the cpu  I have tried keeping the database
> connection open and open/closing for every query. But there was no change.

> if I launch the CLI and run the query it runs instantly and monitoring the
> IO read bytes is see only ~20 bytes of read to execute the query, when my
> code is using over 900k for every call. I have been looking into the CLI
> source to see what is done differently, but was hoping someone on here might
> have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 performace

[John Stanton]

Areyou doing an sqlite3_finalize and checking to see that you actually 
close the DB?


Philip Nick wrote:

Thanks for replying,

I have tried moving the Open/Close outside the mutex no change.

As for using our own mutex, we started with early versions of sqlite and had
to come up with our own solution. I was planning on looking into using the
built in mutex's, but first I need to solve the performance issues.

The only difference I can see between our code and the CLI is it uses
sqlite3_exec and a call back, while we do

sqlite3_prepare
Then we call sqlite3_column_count.
We loop through 0-num_col and call:
   sqlite3_column_name
   sqlite3_column_decltype

Then we do  while(sqlite3_step() == SQLITE_ROW)


From my understanding the sqlite3_exec() is doing the same thing and sending

the info too the callback.

Any ideas?

Thanks

On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote:


"Philip Nick" <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.
I use a Mutex to ensure only one process/thread can access the database

at

one time.

The current flow of events:
Get Mutex
Open Database connection
Run Query
Close Database connection
Release Mutex


SQLite does the mutexing automatically (assuming you are using
version 3.5.0 or later).
--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 performace

[Philip Nick]

Thanks for replying,

I have tried moving the Open/Close outside the mutex no change.

As for using our own mutex, we started with early versions of sqlite and had
to come up with our own solution. I was planning on looking into using the
built in mutex's, but first I need to solve the performance issues.

The only difference I can see between our code and the CLI is it uses
sqlite3_exec and a call back, while we do

sqlite3_prepare
Then we call sqlite3_column_count.
We loop through 0-num_col and call:
   sqlite3_column_name
   sqlite3_column_decltype

Then we do  while(sqlite3_step() == SQLITE_ROW)

>From my understanding the sqlite3_exec() is doing the same thing and sending
the info too the callback.

Any ideas?

Thanks

On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote:

> "Philip Nick" <[EMAIL PROTECTED]> wrote:
> > Greetings,
> >
> > Currently I am using sqlite3 in a multi-process/multi-threaded server
> > setting.
> > I use a Mutex to ensure only one process/thread can access the database
> at
> > one time.
> >
> > The current flow of events:
> > Get Mutex
> > Open Database connection
> > Run Query
> > Close Database connection
> > Release Mutex
> >
>
> SQLite does the mutexing automatically (assuming you are using
> version 3.5.0 or later).
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Philip Nick
E-Mail: [EMAIL PROTECTED]
Cell: 352-262-9067
Web: http://www.ruffasdagut.com

Re: [sqlite] sqlite3 performace

[drh]

"Philip Nick" <[EMAIL PROTECTED]> wrote:
> Greetings,
> 
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.
> I use a Mutex to ensure only one process/thread can access the database at
> one time.
> 
> The current flow of events:
> Get Mutex
> Open Database connection
> Run Query
> Close Database connection
> Release Mutex
> 

SQLite does the mutexing automatically (assuming you are using
version 3.5.0 or later).
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] sqlite3 performace

[James Dennett]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of
> Philip Nick
> Sent: Thursday, January 17, 2008 1:48 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite3 performace
> 
> Greetings,
> 
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.
> I use a Mutex to ensure only one process/thread can access the
database at
> one time.
> 
> The current flow of events:
> Get Mutex
> Open Database connection
> Run Query
> Close Database connection
> Release Mutex
> 
> This seems to work well except I have noticed some performance issue
when
> the database grows beyond a MB.

Why not move the Open/Close outside of the mutex, hold a connection
open, and re-use it for all queries?  Otherwise you're making SQLite
reload the schema definition every time you perform a query, as I
understand it.

-- James


-
To unsubscribe, send email to [EMAIL PROTECTED]
-