[sqlite] Journal Files

[Mark Riehl]

I'm working with version 3.5.2 under Linux.  I've got a database that
is being shared between two processes and I'm running into issues with
the journal file that doesn't go away.  When that happens, one process
appears to have the lock on the database and the other process is
essentially locked out.

A few questions for you:

1. Is there a way to look at the contents of the journal file so that
I know which insert statements are currently queued up?  This way, I
can try and work backwards from there and see what the issue is.

2. Is there a way to determine the current database state (unlocked,
shared, pending, reserved, exclusive) ?

Thanks,
Mark

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

Re: [sqlite] Re: Access from Multiple Processes

[Mark Riehl]

I guess the other thing I should add is that we're using Qt/Qtopia.
In theory, it shouldn't matter, but, I thought I'd mention it just in
case.

Mark

On 1/21/08, James Dennett <[EMAIL PROTECTED]> wrote:
> (Top-posting and overquoting fixed.)
>
> On Monday, January 21, 2008 1:57 PM, Mark Riehl wrote:
> >
> > On Jan 21, 2008 4:48 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> > > Mark Riehl <[EMAIL PROTECTED]>
> wrote:
> > > > For some reason I haven't been able to track down, SQLite is
> creating
> > > > a journal file after the C++ process connects, however, the
> journal
> > > > file doesn't go away.
> > >
> > > Are you, by any chance, opening a transaction and keeping it open?
> > >
> > > Igor Tandetnik
> >
> > I'm just executing SQL insert statements.  I'm not using the BEGIN
> > TRANSACTION; ... END TRANSACTION; wrappers around the inserts.
> >
> > I thought that there was an implied BEGIN ...COMMIT around every
> > INSERT statement?
>
> There is.  And if your code has no active transactions, SQLite allows
> other processes to access the database.  So something odd is happening,
> but I don't think you've shared enough information for this list to
> guess what.
>
> -- James
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] Re: Access from Multiple Processes

[Mark Riehl]

I'm just executing SQL insert statements.  I'm not using the BEGIN
TRANSACTION; ... END TRANSACTION; wrappers around the inserts.

I thought that there was an implied BEGIN ...COMMIT around every
INSERT statement?

Mark

On Jan 21, 2008 4:48 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Mark Riehl <[EMAIL PROTECTED]> wrote:
> > For some reason I haven't been able to track down, SQLite is creating
> > a journal file after the C++ process connects, however, the journal
> > file doesn't go away.
>
> Are you, by any chance, opening a transaction and keeping it open?
>
> Igor Tandetnik
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] Access from Multiple Processes

[Mark Riehl]

I'm running version SQLite 3.5.2 under Linux on a small ARM processor
with a flash drive.

I've got a single-threaded C++ process that inserts data into an
SQLite database, and a series of PHP (v5.2.4) scripts using PDO that
insert to and read from the same database.  In terms of the
percentages, the C++ process is responsible for ~99% of the data going
into the database.  I open the connection to the SQLite database when
the C++ application starts, and close the connection when it shuts
down.  The PHP opens and closes the connection during each session.

For some reason I haven't been able to track down, SQLite is creating
a journal file after the C++ process connects, however, the journal
file doesn't go away.  At this point, the database is locked for the
PHP scripts and remains locked until I manually remove the journal
file.

Questions:
- Why would the journal file remain?
- Is there a problem leaving the connection open from the C++ process?
- Looking at the SQLite documentation, I see that there are 5 possible
locked states.  Is there a way for me to query the database (maybe via
sqlite3) to determine which state the database is in?

Thanks for the help,
Mark

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

Re: [sqlite] Dump w/o Schema

[Mark Riehl]

Is there a way to merge these in such a way that I could make this
from a system call?  For example, let's say I want to create this dump
file from a Perl or shell script using a system call.  Can I do that?

Thanks,
Mark

On Jan 15, 2008 4:59 PM, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Tue, 15 Jan 2008, Rob Sciuk wrote:
>
> >> Is there a way to dump out the contents of the database w/o having the
> >> CREATE TABLE statements?  For example, I can do the following:
>
> >> However, foo.dmp contains all of the CREATE TABLE statements.  I just
> >> want all of the INSERT INTO statements associated with this database.
>
> > Actually, this looks like a reasonable enhancement request.  As it does 
> > *NOT*
> > affect the API in any way, and PostGres allows separate schema and data 
> > dumps
> > (the default being both) in psql, there should be no reason not to put it
> > into the sqlite3 tool, unless I'm missing something??  Am I?
>
>Not needed; it already exists.
>
>Change the mode to 'insert', select a file name for your output, then run
> the select statement. For example:
>
> sqlite> .m insert
> sqlite> .o table-values.sql
> sqlite> Select * from Tablename;
> sqlite> .o stdout
> sqlite> .m list
>
>The last two commands reset the output and mode back to the interactive
> values.
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] Dump w/o Schema

[Mark Riehl]

I'm coming from a MySQL background and the mysqldump utility supports
schema-only, data-only, and schema plus data dumps.  I thought that
there was an SQLite trick I was missing somewhere.

Mark

On Jan 15, 2008 4:10 PM, Rob Sciuk <[EMAIL PROTECTED]> wrote:
>
>
> On Tue, 15 Jan 2008, Mark Riehl wrote:
>
> > Is there a way to dump out the contents of the database w/o having the
> > CREATE TABLE statements?  For example, I can do the following:
> >
> > sqlite3 foo.db .dump > foo.dmp
> >
> > However, foo.dmp contains all of the CREATE TABLE statements.  I just
> > want all of the INSERT INTO statements associated with this database.
> >
> > I looked through the documentation but didn't find the answer to this
> > one, I apologize if it's already in the docs.
> >
> > Thanks,
> > Mark
>
> Actually, this looks like a reasonable enhancement request.  As it does
> *NOT* affect the API in any way, and PostGres allows separate schema and
> data dumps (the default being both) in psql, there should be no reason not
> to put it into the sqlite3 tool, unless I'm missing something??  Am I?
>
> Cheers,
> Rob Sciuk
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] Dump w/o Schema

[Mark Riehl]

Is there a way to dump out the contents of the database w/o having the
CREATE TABLE statements?  For example, I can do the following:

sqlite3 foo.db .dump > foo.dmp

However, foo.dmp contains all of the CREATE TABLE statements.  I just
want all of the INSERT INTO statements associated with this database.

I looked through the documentation but didn't find the answer to this
one, I apologize if it's already in the docs.

Thanks,
Mark

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

[sqlite] Occasional Database is Locked Error

[Mark Riehl]

I'm running SQLite 3.4.1 under Linux.  I have a C++ application that
inserts records into the database.

During testing, I lauch the C++ app and I start sqlite3 from a console
and open the database that is being written to by the C++ application.
 I perform selects using sqlite3 (no inserts) to look at the collected
data and occasionally get "Error: database is locked".  Sometimes get
the error a few times in a row, other times, it doesn't happen at all.

I'm not inserting a lot of records - worst case, there are ~25 records
per second.

Am I just getting lucky and issuing selects at the same time I'm
writing from the other process?  I'm planning on using a web-based app
to periodically poll the database (to provide summary information)
while the C++ app performs the inserts.  What is the best way to
reduce/minimize the lock issue?

Thanks for the help,
Mark

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

[sqlite] Unix Epoch Time Support

[Mark Riehl]

All - We're in the process of porting an application that used MySQL
to an embedded platform running SQLite.  Two of the built-in functions
we used frequently were the functions to create and convert from Unix
epochs (UNIX_TIMESTAMP(), FROM_UNIXTIME).

Are there any plans to add this functionality to SQLite?

Thanks,
Mark

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

[sqlite] Writing to Flash Memory

[Mark Riehl]

I'm developing an application to run on an ARM-based PDA-like device.
It's running Linux and I've got SQLite 3.4.1 installed.

I'm trying to optimize my insert statements (in a different discussion
thread) I've gotten some good suggestions to use the PRAGMA statement
to disable the syncronization.  On my Shuttle running Fedora 3, I see
insert times of ~0.2 ms.  However, the same test application running
on the PDA is taking ~25 ms for the same insert.

The media doesn't appear to be that slow - writing the same  insert
statement to a text file takes ~0.2ms.  Granted, there is more going
on with the SQLite insert than the file right, but, I wouldn't expect
such a discrepancy.

Does anyone have any experience using SQLite and flash?  Any
particular settings that would be helpful?

Thanks,
Mark

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

Re: [sqlite] Single row insert speeds

[Mark Riehl]

All - After reviewing some of my calculations, I realized I made a
mistake and that the inserts didn't improve as much as I thought.  I
used the PRAGMA synchronouse = OFF and I'm averaging about 0.21 ms for
an insert on a Core 2 Shuttle running Fedora 3.

Here is the table I've defined:
CREATE TABLE sampleTable (
 logHost varchar(64) DEFAULT NULL,
 compId smallint(5) DEFAULT NULL,
 pid int(10) DEFAULT NULL,
 version varchar(8) DEFAULT NULL,
 rptTime decimal(20,6) DEFAULT NULL,
 rptStatus tinyint(3) DEFAULT NULL,
 data text
);

Here is a typical insert:

INSERT INTO sampleTable
VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.06533598
8998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak)
;\")";

For testing, I've modified the the insert to look like this:
char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO
sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\",
\"1196303669.065335988998\",
\"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0
peak);\");COMMIT;";

Is this the correct syntax for the PRAGMA statement?  Can I issue it
once and will it remain active as long as the connection is open?

Thanks,
Mark


On Dec 3, 2007 6:45 PM, Mark Riehl <[EMAIL PROTECTED]> wrote:
> I used the PRAGMA statement and turned off the synchronous option.  It
> made a huge difference.  Single inserts were ranging from 5 - 50 ms,
> now, they're at ~.04 ms.
>
> However, I guess there is a tradeoff between the safety of the
> synchronous operation (in case power is lost) versus the insert
> speeds.
>
> Thanks for the help,
> Mark
>
>
> On Dec 3, 2007 12:59 PM,  <[EMAIL PROTECTED]> wrote:
> > "P Kishor" <[EMAIL PROTECTED]> wrote:
> > > I get 1000+ inserts a second for a random 100 byte string
> > > insert (
> >
> > I get 5+ inserts/sec on my Linux box.
> >
> > Insert speed is not the issue.  It is COMMIT speed.  At
> > each commit, SQLite waits until all data is on oxide before
> > continuing.  That will typically take at least two rotations
> > of the disk platter, or about 17 millisecond, depending on
> > your disk drive.  Waiting for data to get to oxide is
> > part of being ACID.  You can set:
> >
> >PRAGMA synchronous=OFF;
> >
> > and your COMMITs will go *much* faster because it will no
> > longer wait on the disk drive.  But if you lose
> > power in the middle of a commit, you might corrupt your
> > database file.
> >
> > Note that there is an implied BEGIN...COMMIT around every
> > INSERT statement if you do not explicitly start a transaction
> > using your own BEGIN.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
>

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

Re: [sqlite] Single row insert speeds

[Mark Riehl]

I used the PRAGMA statement and turned off the synchronous option.  It
made a huge difference.  Single inserts were ranging from 5 - 50 ms,
now, they're at ~.04 ms.

However, I guess there is a tradeoff between the safety of the
synchronous operation (in case power is lost) versus the insert
speeds.

Thanks for the help,
Mark

On Dec 3, 2007 12:59 PM,  <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote:
> > I get 1000+ inserts a second for a random 100 byte string
> > insert (
>
> I get 5+ inserts/sec on my Linux box.
>
> Insert speed is not the issue.  It is COMMIT speed.  At
> each commit, SQLite waits until all data is on oxide before
> continuing.  That will typically take at least two rotations
> of the disk platter, or about 17 millisecond, depending on
> your disk drive.  Waiting for data to get to oxide is
> part of being ACID.  You can set:
>
>PRAGMA synchronous=OFF;
>
> and your COMMITs will go *much* faster because it will no
> longer wait on the disk drive.  But if you lose
> power in the middle of a commit, you might corrupt your
> database file.
>
> Note that there is an implied BEGIN...COMMIT around every
> INSERT statement if you do not explicitly start a transaction
> using your own BEGIN.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] Single row insert speeds

[Mark Riehl]

I'm running SQLite 3.1.13 under Fedora 3.  Our real app is written in
C++.  The sample I wrote to debug the insert times is written in C
(gcc 3.4.2).

Here is the table I've defined:
CREATE TABLE sampleTable (
  logHost varchar(64) DEFAULT NULL,
  compId smallint(5) DEFAULT NULL,
  pid int(10) DEFAULT NULL,
  version varchar(8) DEFAULT NULL,
  rptTime decimal(20,6) DEFAULT NULL,
  rptStatus tinyint(3) DEFAULT NULL,
  data text
);

And I have an index on the compId column.

Here is a typical insert:

INSERT INTO sampleTable
VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.06533598
8998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak)
;\")";

As I said, this is taking anywhere from 5-50 ms.

Running 10 times in a loop (with a 1 sec sleep in between to let it
finish), here is what I see for insert times:

Insert time in ms 57.444000
Insert time in ms 6.031000
Insert time in ms 5.048000
Insert time in ms 6.372000
Insert time in ms 4.786000
Insert time in ms 11.169000
Insert time in ms 4.02
Insert time in ms 5.211000
Insert time in ms 7.112000
Insert time in ms 3.962000

Thanks,
Mark


On Dec 3, 2007 12:30 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> you would need to give more info about your db, the data, etc. On my
> Macbook Pro, I get 1000+ inserts a second for a random 100 byte string
> insert (that is, less than one per ms). That includes the time to
> generate the string, and is all in Perl, while I am listening to
> iTunes, and no funny pragma trix. Code follows...
>
> I am using SQLite 3.4.2
>
> CREATE TABLE foo (a);
>
> sub speedtest {
>   $sth->execute( str() )
> }
>
> sub str {
>   my $str;
>   for (0 .. 99) {
> $str .= ('a' .. 'z')[int(rand(25)) + 1];
>   }
>   return $str;
> }
>
> timethis(1000, \);
>
> $ ./db.pl
> timethis 1000:  2 wallclock secs ( 0.20 usr +  0.75 sys =  0.95 CPU) @
> 1052.63/s (n=1000)
>
>
> On 12/3/07, Mark Riehl <[EMAIL PROTECTED]> wrote:
> > I've got an application that logs real-time data.  Some of the data is
> > periodic (every few secs), other data comes more frequently.
> > Basically, I'm not dealing with bulk inserts, so, I can't queue things
> > up and insert all at once.
> >
> > I'm noticing that my insert times are pretty slow (~5-50 ms on a Intel
> > Core 2) for a single record of ~100 bytes.
> >
> > Any suggestions for speeding up single row inserts?  I saw some of the
> > other threads on using transactions, but, not sure if that applied to
> > single inserts.
> >
> > Here is a simple app I wrote to test the timing:
> >
> >int rc = sqlite3_open("mydb.db", );
> >
> > for (i = 0; i < 10; i++) {
> >
> >   printf("Executing %s\n", insertStatement);
> >
> >   gettimeofday(, 0);
> >   rc = sqlite3_exec(db, insertStatement, NULL, NULL, );
> >   gettimeofday(, 0);
> >   if (rc != SQLITE_OK) {
> >  ...
> > }
> >   }
> >
> >   printf("Before %d %d\n", before.tv_sec, before.tv_usec);
> >   printf("After %d %d\n", after.tv_sec, after.tv_usec);
> >
> >   sleep(1);
> > }
> >
> > sqlite3_close(db);
> >
> > Thanks for the help,
> > Mark
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
>
>
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies
> http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo)
> http://www.osgeo.org/
> Summer 2007 S Policy Fellow, The National Academies
> http://www.nas.edu/
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] Single row insert speeds

[Mark Riehl]

I've got an application that logs real-time data.  Some of the data is
periodic (every few secs), other data comes more frequently.
Basically, I'm not dealing with bulk inserts, so, I can't queue things
up and insert all at once.

I'm noticing that my insert times are pretty slow (~5-50 ms on a Intel
Core 2) for a single record of ~100 bytes.

Any suggestions for speeding up single row inserts?  I saw some of the
other threads on using transactions, but, not sure if that applied to
single inserts.

Here is a simple app I wrote to test the timing:

   int rc = sqlite3_open("mydb.db", );

for (i = 0; i < 10; i++) {

  printf("Executing %s\n", insertStatement);

  gettimeofday(, 0);
  rc = sqlite3_exec(db, insertStatement, NULL, NULL, );
  gettimeofday(, 0);
  if (rc != SQLITE_OK) {
 ...
}
  }

  printf("Before %d %d\n", before.tv_sec, before.tv_usec);
  printf("After %d %d\n", after.tv_sec, after.tv_usec);

  sleep(1);
}

sqlite3_close(db);

Thanks for the help,
Mark

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