Re: [sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Yep OK. I stand corrected.

BTW thanks for your help.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-30-13 12:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

The documentation for BEGIN TRANSACTION
(http://www.sqlite.org/lang_transaction.html) does:

An implicit transaction (a transaction that is started automatically, not a
transaction started by BEGIN) is committed automatically when the last
active statement finishes. A statement finishes when its prepared statement
is reset or finalized.

The explicit COMMIT command runs immediately, even if there are pending
SELECT statements. However, if there are pending write operations, the
COMMIT command will fail with an error code SQLITE_BUSY.

The ROLLBACK will fail with an error code SQLITE_BUSY if there are any
pending queries. Both read-only and read/write queries will cause a ROLLBACK
to fail. A ROLLBACK must fail if there are pending read operations (unlike
COMMIT which can succeed) because bad things will happen if the in-memory
image of the database is changed out from under an active query.

Igor Tandetnik

On 10/30/2013 12:09 PM, Normand Mongeau wrote:
> Then IMO the documentation for either sqlite3_prepare_xx or 
> SQLITE_BUSY should state this. It would have been helpful in my case, 
> I was pulling my hair looking at the transaction model which was sound.
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: October-30-13 12:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problem with SQLITE_BUSY
>
>
> On 30 Oct 2013, at 4:03pm, Normand Mongeau <nmong...@theobjects.com>
wrote:
>
>> Well finally found the problem: a forgotten sqlite3_finalize() call.
>>
>> Very disturbing, I'd expect leakage, not the results I was seeing.
>
> SQLite has to keep the state of your SELECT available until you tell 
> it you're done with that SELECT.  After all, you might look at the 
> results you have retrieved and decide to make some changes based on what
you see there.
> This means SQLite has to stop everything from changing the database.
> Therefore it locks it.
>
> Simon.
> ___
> 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] Problem with SQLITE_BUSY

[Normand Mongeau]

Then IMO the documentation for either sqlite3_prepare_xx or SQLITE_BUSY
should state this. It would have been helpful in my case, I was pulling my
hair looking at the transaction model which was sound.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 12:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 4:03pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Well finally found the problem: a forgotten sqlite3_finalize() call.
> 
> Very disturbing, I'd expect leakage, not the results I was seeing.

SQLite has to keep the state of your SELECT available until you tell it
you're done with that SELECT.  After all, you might look at the results you
have retrieved and decide to make some changes based on what you see there.
This means SQLite has to stop everything from changing the database.
Therefore it locks it.

Simon.
___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Well finally found the problem: a forgotten sqlite3_finalize() call.

Very disturbing, I'd expect leakage, not the results I was seeing.

Normand


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Normand Mongeau
Sent: October-30-13 9:51 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Problem with SQLITE_BUSY

Using the straight C API of SQLite.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 9:39 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 1:00pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Could this be related to this: in A, the database connection is 
> created in the main program, but is passed down to a dll that loads 
> another dll that uses the connection to do the writes. Maybe the dll 
> should open its own connection?

Are you addressing SQLite though its own C API, or are you using a library
which says it does SQLite things for you ?  If you're using a library, which
one ?

Simon.

___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Using the straight C API of SQLite.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 9:39 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 1:00pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Could this be related to this: in A, the database connection is 
> created in the main program, but is passed down to a dll that loads 
> another dll that uses the connection to do the writes. Maybe the dll 
> should open its own connection?

Are you addressing SQLite though its own C API, or are you using a library
which says it does SQLite things for you ?  If you're using a library, which
one ?

Simon.

___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Actually, I inspected carefully the code and to avoid any deadlocks and make
the intentions very explicit, every time we're about to write we do:

Begin immediate transaction
Our updates
Commit transaction

Anytime we read we do

Begin transaction
read
rollback transaction (there was a mix of commit and rollback, but I made it
uniform by using rollback only)

I still can't find the hole in our code. More info: after the scenario is
done, any read in C (thus a begin/rollback) doesn't affect A, but _any_
write transaction (thus begin immediate/commit) results in SQLITE_BUSY.

Could this be related to this: in A, the database connection is created in
the main program, but is passed down to a dll that loads another dll that
uses the connection to do the writes. Maybe the dll should open its own
connection?

Normand



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-29-13 6:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

You haven't explained what the second connection in A is doing. My educated
guess is, the two connections enter into a deadlock. This is possible when
at least one connection starts as a reader and later attempts to write (the
other could be a straight writer). The scenario goes like this: the reader
acquires a SHARED lock and starts reading. 
Meanwhile, the writer is ready to write, so it gets a PENDING lock and waits
for readers to clear. But instead, the reader tries to start writing by
acquiring a RESERVED lock - which it cannot do for as long as the other
writer is active.

Once this deadlock happens, the only way to make progress is for one of the
two connections to roll back its transaction. One can avoid this scenario by
starting the transaction with BEGIN IMMEDIATE (or BEGIN
EXCLUSIVE) - this marks the transaction as a writer right away, by obtaining
a RESERVED (or EXCLUSIVE) lock from the start.

Igor Tandetnik

On 10/29/2013 6:26 PM, Normand Mongeau wrote:
> Hi,
>
>
>
> I have a situation where I always run into an SQLITE_BUSY error. It's 
> quite involved, here's the high picture:
>
>
>
> -3 processes (A, B and C) each have a connection to the same db. 
> Everybody has a busy handler set for 5 seconds.
>
>
>
> -1 of these processes (A) opens more than one connection, as it loads 
> a DLL that opens its own connection.
>
>
>
> -A needs to check for data at regular intervals, so every second it 
> does a begin transaction, read then commit transaction.
>
>
>
> -C deletes data within a transaction. Then it triggers about 400 
> transactions in B (it basically sends data to B via a TCP/IP layer, B 
> receives the data and writes in the DB).
>
>
>
> -A then sees that data has arrived, and does its own processing, 
> eventually recording some more data (always within a begin/write/commit).
>
>
>
> -After A is done, it goes back into its "check for data" at the same 
> regular interval.
>
>
>
> So far so good. I see the A's begin/read/commit succeed.
>
>
>
> As soon as I trigger another transaction in C (any transaction), A 
> gets the SQLITE_BUSY error, even though the transaction goes through 
> normally in C (i.e. begin transaction, write, commit transaction).
>
>
>
> Any ideas as to what's wrong in the above scenario? How do I avoid the 
> SQLITE_BUSY error? Is it possible to recover from that error? 
> (Apparently not. I close the B and C processes (proper shutdown) and A 
> still gets the error).
>
>
>
> Thanks,
>
>
>
> Normand
>


___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Hmm really?

Odd thing is that although I do have a 10 second timeout as soon as C goes
into a begin transaction A receives the SQLITE_BUSY error, in other words I
don't see any 10 second delay.

I'll try increasing the timeout.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-29-13 8:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 29 Oct 2013, at 10:26pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> I have a situation where I always run into an SQLITE_BUSY error. 

Set your timeout to something very large (a million milliseconds ?) and see
if the problem just turns into unexpected delays instead of errors.  You can
set timeout using either of these:

easy:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

difficult:

http://www.sqlite.org/c3ref/busy_timeout.html

Simon.
___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Actually I was mistaken (my apologies), A doesn't open a second connection,
I thought it did but under this scenario it doesn't.

So it simplifies my problem, yet I don't see what's wrong.

Normand

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-29-13 6:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

You haven't explained what the second connection in A is doing. My educated
guess is, the two connections enter into a deadlock. This is possible when
at least one connection starts as a reader and later attempts to write (the
other could be a straight writer). The scenario goes like this: the reader
acquires a SHARED lock and starts reading. 
Meanwhile, the writer is ready to write, so it gets a PENDING lock and waits
for readers to clear. But instead, the reader tries to start writing by
acquiring a RESERVED lock - which it cannot do for as long as the other
writer is active.

Once this deadlock happens, the only way to make progress is for one of the
two connections to roll back its transaction. One can avoid this scenario by
starting the transaction with BEGIN IMMEDIATE (or BEGIN
EXCLUSIVE) - this marks the transaction as a writer right away, by obtaining
a RESERVED (or EXCLUSIVE) lock from the start.

Igor Tandetnik

On 10/29/2013 6:26 PM, Normand Mongeau wrote:
> Hi,
>
>
>
> I have a situation where I always run into an SQLITE_BUSY error. It's 
> quite involved, here's the high picture:
>
>
>
> -3 processes (A, B and C) each have a connection to the same db. 
> Everybody has a busy handler set for 5 seconds.
>
>
>
> -1 of these processes (A) opens more than one connection, as it loads 
> a DLL that opens its own connection.
>
>
>
> -A needs to check for data at regular intervals, so every second it 
> does a begin transaction, read then commit transaction.
>
>
>
> -C deletes data within a transaction. Then it triggers about 400 
> transactions in B (it basically sends data to B via a TCP/IP layer, B 
> receives the data and writes in the DB).
>
>
>
> -A then sees that data has arrived, and does its own processing, 
> eventually recording some more data (always within a begin/write/commit).
>
>
>
> -After A is done, it goes back into its "check for data" at the same 
> regular interval.
>
>
>
> So far so good. I see the A's begin/read/commit succeed.
>
>
>
> As soon as I trigger another transaction in C (any transaction), A 
> gets the SQLITE_BUSY error, even though the transaction goes through 
> normally in C (i.e. begin transaction, write, commit transaction).
>
>
>
> Any ideas as to what's wrong in the above scenario? How do I avoid the 
> SQLITE_BUSY error? Is it possible to recover from that error? 
> (Apparently not. I close the B and C processes (proper shutdown) and A 
> still gets the error).
>
>
>
> Thanks,
>
>
>
> Normand
>


___
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] Problem with SQLITE_BUSY

[Normand Mongeau]

Hi,

 

I have a situation where I always run into an SQLITE_BUSY error. It's quite
involved, here's the high picture:

 

-3 processes (A, B and C) each have a connection to the same db. Everybody
has a busy handler set for 5 seconds.

 

-1 of these processes (A) opens more than one connection, as it loads a DLL
that opens its own connection.

 

-A needs to check for data at regular intervals, so every second it does a
begin transaction, read then commit transaction.

 

-C deletes data within a transaction. Then it triggers about 400
transactions in B (it basically sends data to B via a TCP/IP layer, B
receives the data and writes in the DB).

 

-A then sees that data has arrived, and does its own processing, eventually
recording some more data (always within a begin/write/commit).

 

-After A is done, it goes back into its "check for data" at the same regular
interval.

 

So far so good. I see the A's begin/read/commit succeed.

 

As soon as I trigger another transaction in C (any transaction), A gets the
SQLITE_BUSY error, even though the transaction goes through normally in C
(i.e. begin transaction, write, commit transaction).

 

Any ideas as to what's wrong in the above scenario? How do I avoid the
SQLITE_BUSY error? Is it possible to recover from that error? (Apparently
not. I close the B and C processes (proper shutdown) and A still gets the
error).

 

Thanks,

 

Normand

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

Re: [sqlite] SELECT and UPDATE?

[Normand Mongeau]

Thanks (to other repliers too),

That put me on the right track.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf
Sent: October-18-13 7:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT and UPDATE?


For multiple consumers you might do something like:

BEGIN;
UPDATE processingqueue SET status = 'failed' WHERE status = 'processing' AND
processor = :processor; UPDATE processingqueue SET status = 'processing',
processor = :processor, started = strftime('%s') WHERE queueid = (SELECT
queueid from processingqueue where status = 'ready' AND processor IS NULL
ORDER BY queueid LIMIT 1); SELECT * FROM processingqueue WHERE status =
'processing' AND processor = :processor; UPDATE processingqueue SET status =
'ready', processor = NULL WHERE status = 'failed' AND processor =
:processor; COMMIT;

and run it as a single statement providing the processor name binding to the
named variable :processor and you will get back one row to processes on
:processor, assuming that there is work to do.  Otherwise you will not get
back a row.

If the same :processor asks for more work to do and it is already
processing, then the currently dispatched task failed and you should get a
new one.  The failed job will then be returned to ready and can be
dispatched to any worker in need of work.

schema would look like:

create table processingqueue
(
   queueid integer primary key,
   status text collate nocase,
   started integer,
   ... other data you need ...
  unique (processor, status, queueid)
);



On Fri, 18 Oct 2013 19:04:54 -0400
 "James K. Lowden" <jklow...@schemamania.org> wrote:
>On Fri, 18 Oct 2013 13:57:18 -0400
>"Normand Mongeau" <nmong...@theobjects.com> wrote:
>
>> Also, the consuming should be a 2-step process because the
>processing
>> is involved and may fail for reasons too long to explain here. So in 
>> essence, select a record, modify it to indicate it's being
>processed,
>> and once the processing is done delete the record. Is there a way to 
>> do the initial selection in one swoop (select and update) or is it 
>> two SQL statements? I have to avoid two different processes
>selecting
>> and modifying the same record to minimize rollbacks/retries.
>
>Read after write, not write after read.  
>
>Counterintuitive, perhaps, but if the reading process begins by
>*updating* the record it's about to process, you have idempotent 
>processing without the need for a user-defined transaction.
>
>writer: 
>   insert ... (status, key, data) values ('queued', 1, 'foo');
>
>reader:
>   update ... set status = 'processing'
>   where key = (select min(key) ... where status <> 'done');
>   select ... where status = 'processing';
>   /* work work work */
>   update ... set status = 'done' where key = @key;
>
>SQL-92 IIRC defines an OUTPUT clause for UPDATE, which would do what 
>you want (select+output in one statement).  But that's not a SQLite 
>feature.
>
>--jkl
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


#include 
___
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] SELECT and UPDATE?

[Normand Mongeau]

Hi,

 

I have a scenario where I'm using an sqlite table as a queue for
inter-process communication. One or more processes feed the table, and one
or more different processes are meant to consume the data.

 

What's my best scenario in terms of minimizing conflicts?

 

Also, the consuming should be a 2-step process because the processing is
involved and may fail for reasons too long to explain here. So in essence,
select a record, modify it to indicate it's being processed, and once the
processing is done delete the record. Is there a way to do the initial
selection in one swoop (select and update) or is it two SQL statements? I
have to avoid two different processes selecting and modifying the same
record to minimize rollbacks/retries.

 

Any hints on what to do / not to do?

 

Thanks,

 

Normand

 

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

Re: [sqlite] Incompatible versions of SQLite on same system

[Normand Mongeau]

On 2012-01-24 16:09, Tim Streater wrote:

On 24 Jan 2012 at 20:02, Joe Winograd  wrote:


Thanks for the idea, but it will not install. The way this group operates
with excessive trimming/snipping ...

No it doesn't. It doesn't do *enough* trimming and snipping, and as a result 
our inboxes grow exponentially. If I want to read a thread I can sort by 
subject and then read it through. But this is made harder by the excessive 
repetition due to inadequate trimming (particularly of .sigs).

--
Cheers  --  Tim





Did you guys ever consider Google Groups or something of the like? Email 
lists are soo clumsy.


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

Re: [sqlite] Slow commits

[Normand Mongeau]

Thanks, you've been very helpful. Being a recent lurker here (but a seasoned
developer), let me commend you for your outstanding work and support.

Normand



-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Richard Hipp
Envoyé : 13 janvier 2012 19:35
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 6:49 PM, Normand Mongeau
<nmong...@theobjects.com>wrote:

>
>
>>>  Begin by doing:
>>
>> PRAGMA synchronous=OFF;
>>
>
> With the above, the total commitTransaction time goes down to 385 
> milliseconds... Impressive.
>
>
The "PRAGMA synchronous=OFF" command turns of syncing of content to the disk
surface.  Normally, SQLite will pause at critical points and wait for
content to actually make it to disk oxide.  This ensures that your
transactions commit, and the database file is undamaged, even if a power
loss occurs in the middle of a write.  But "PRAGMA synchronous=OFF" turns
that mechanism off, so that SQLite just sends a "write()" system call to the
operating system and lets the operating system get the content to the disk
surface at its leisure.  That will work fine, as long as the power never
goes out.  But pull the power plug in the middle of a write, and you might
corrupt your database file.


>
>
>> That will determine if the problem is a slow disk or if we need to 
>> look elsewhere.  I'm not suggesting you deploy with the above setting 
>> -- just use it for debugging.
>>
>> You might also try:
>>
>>PRAGMA synchronous=NORMAL;
>>PRAGMA journal_mode=WAL;
>>
>> And see if you get better performance that way.
>>
>
> with strictly synchronous=NORMAL, time is 63 seconds.  Combined with 
> WAL, time is 2.6 seconds.
>
> Not sure what it means exactly though.
>

The "PRAGMA journal_mode=WAL" uses a newer transaction mechanism that is
faster in many causes (such as yours).  The "PRAGMA synchronous=NORMAL"
means that syncs to disk only occur during a "checkpoint" operation, which
happens on a few commits, but rarely.  That is sufficient to ensure that the
database file is never corrupted by a power loss.  But one or more of the
most recent transactions might get rolled back by a power loss.  In other
words, you lose Durability.  If Durability is important to you (it probably
is not, unless you are a bank) then you can set "PRAGMA synchronous=FULL"
with "PRAGMA journal_mode=WAL" and it will sync after every transaction.
That will reduce performance somewhat.  Usually the reduction isn't
noticeable.  But on your machine..

So what I think this all means is that you ought to be using:

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

Actually, you only have to do the journal_mode=WAL once, when you first
create the database file.  But it doesn't hurt to do it every time.  And
doing it every time is a good safety mechanism in case some rogue user slips
in and turns the WAL mode back off without your program noticing.


>
>
>
>>
>>>
>>> On 2012-01-13 15:35, Richard Hipp wrote:
>>>
>>>  On Fri, Jan 13, 2012 at 3:34 PM, Normand 
>>> Mongeau<nmongeau@theobjects.**
>>>> com<nmong...@theobjects.com>>**wrote:
>>>>
>>>>
>>>>  On 2012-01-13 15:23, Richard Hipp wrote:
>>>>>
>>>>>  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmongeau@theobjects.
>>>>> **
>>>>>
>>>>>> com<nmong...@theobjects.com>>wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>  not really, no. This is a server that receives files, and the 
>>>>>> transaction
>>>>>>
>>>>>>  below means a file has arrived.
>>>>>>>
>>>>>>>  Does your server have a really, really slow disk drive?  
>>>>>>> Transaction
>>>>>>>
>>>>>>>  commit
>>>>>> normally takes milliseconds.  I'm not sure why you are having 
>>>>>> problems.
>>>>>>
>>>>>> Might another process be soaking up all the disk I/O bandwidth 
>>>>>> and making your process have to wait for an available slot?
>>>>>>
>>>>>>
>>>>>>  No, my machine is a normal PC, and I tried on several machines 
>>>>>> and
>>>>> they
>>>>> all react the same way.
>>>>>
>>>>>  What version of SQL

Re: [sqlite] Slow commits

[Normand Mongeau]

On 2012-01-13 18:10, Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 13/01/12 14:35, Normand Mongeau wrote:

It gets worse. On a clean empty database, the same 534 transactions
take 140 seconds.  That's a not very impressive rate of 3.8 inserts
per second. The FAQ says that SQLite should be able to do a "few dozen
transactions per second". I'd be happy to see that.

What file extension are you using for the database?  There is a long list
of extensions that System Restore monitors, and makes backups of the files
as they change.  This will kill your performance.


I was using .db as an extension, and changed it to something ludicrous, 
but it didn't make a difference.  Good idea though.


Thanks,

Normand




Extension list and terse details are at:

   http://msdn.microsoft.com/en-us/library/Aa378870

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8Quc4ACgkQmOOfHg372QQAowCgi0DlewfcHs6MIPIHSyjHw6mN
nFIAnjJch3erZfRF+I88yA3CzAkCQWVl
=HoSZ
-END PGP SIGNATURE-
___
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] Slow commits

[Normand Mongeau]

On 2012-01-13 17:45, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeau<nmong...@theobjects.com>wrote:


It gets worse. On a clean empty database, the same 534 transactions take
140 seconds.  That's a not very impressive rate of 3.8 inserts per second.
The FAQ says that SQLite should be able to do a "few dozen transactions per
second". I'd be happy to see that.

I don't have much experience with SQLite, but where does one start when
one wants to troubleshoot such issues?



Begin by doing:

 PRAGMA synchronous=OFF;


With the above, the total commitTransaction time goes down to 385 
milliseconds... Impressive.




That will determine if the problem is a slow disk or if we need to look
elsewhere.  I'm not suggesting you deploy with the above setting -- just
use it for debugging.

You might also try:

PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

And see if you get better performance that way.


with strictly synchronous=NORMAL, time is 63 seconds.  Combined with 
WAL, time is 2.6 seconds.


Not sure what it means exactly though.







On 2012-01-13 15:35, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau<nmongeau@theobjects.**
com<nmong...@theobjects.com>>wrote:



On 2012-01-13 15:23, Richard Hipp wrote:

  On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmongeau@theobjects.**

com<nmong...@theobjects.com>>**wrote:


  not really, no. This is a server that receives files, and the
transaction


below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction


commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and
making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.

  What version of SQLite are you using?  Have you tried running with all

anti-virus software disabled, to see if that makes a difference?




  Normand



   Normand


On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:


  begin immediate transaction

  insert 1 record in tableA

insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178


separate
transactions ?

Simon.
__**_
sqlite-users mailing list
sqlite-users@sqlite.org

http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-***
***users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
<http://sqlite.org:**8080/cgi-**bin/mailman/**listinfo/sqlite-**users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-**bin/mailman/listinfo/**sqlite-**users<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
<http://sqlite.org:**8080/cgi-**bin/mailman/**listinfo/sqlite-**users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-**bin/mailman/listinfo/**sqlite-**users<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<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] Slow commits

[Normand Mongeau]

It gets worse. On a clean empty database, the same 534 transactions take 
140 seconds.  That's a not very impressive rate of 3.8 inserts per 
second. The FAQ says that SQLite should be able to do a "few dozen 
transactions per second". I'd be happy to see that.


I don't have much experience with SQLite, but where does one start when 
one wants to troubleshoot such issues?




On 2012-01-13 15:35, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau<nmong...@theobjects.com>wrote:



On 2012-01-13 15:23, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmongeau@theobjects.**
com<nmong...@theobjects.com>>wrote:

  not really, no. This is a server that receives files, and the transaction

below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction

commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.


What version of SQLite are you using?  Have you tried running with all
anti-virus software disabled, to see if that makes a difference?





Normand




  Normand



On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction


insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178

separate
transactions ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<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] Slow commits

[Normand Mongeau]

On 2012-01-13 15:35, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau<nmong...@theobjects.com>wrote:



On 2012-01-13 15:23, Richard Hipp wrote:


On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmongeau@theobjects.**
com<nmong...@theobjects.com>>wrote:

  not really, no. This is a server that receives files, and the transaction

below means a file has arrived.

  Does your server have a really, really slow disk drive?  Transaction

commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they
all react the same way.


What version of SQLite are you using?  Have you tried running with all
anti-virus software disabled, to see if that makes a difference?



3.7.9 is the version I'm using (statically linked BTW). Running on 
another machine with no anti-virus gives me similar times.






Normand




  Normand



On 2012-01-13 15:16, Simon Slavin wrote:

  On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction


insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about
71
seconds) is spent on the commit transaction instruction.

  Can you put one transaction around the whole lot rather than 178

separate
transactions ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users>
<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<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] Slow commits

[Normand Mongeau]

On 2012-01-13 15:23, Richard Hipp wrote:

On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmong...@theobjects.com>wrote:


not really, no. This is a server that receives files, and the transaction
below means a file has arrived.


Does your server have a really, really slow disk drive?  Transaction commit
normally takes milliseconds.  I'm not sure why you are having problems.

Might another process be soaking up all the disk I/O bandwidth and making
your process have to wait for an available slot?



No, my machine is a normal PC, and I tried on several machines and they 
all react the same way.


Normand





Normand



On 2012-01-13 15:16, Simon Slavin wrote:


On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:

  begin immediate transaction

insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71
seconds) is spent on the commit transaction instruction.


Can you put one transaction around the whole lot rather than 178 separate
transactions ?

Simon.
__**_
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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<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] Slow commits

[Normand Mongeau]

Record sizes are approx 480 bytes for tableA, 380 bytes for tableB and 
800 bytes for tableC.


Storage is my hard drive, which is a normal SATA disk.


On 2012-01-13 15:23, Stephan Beal wrote:

On Fri, Jan 13, 2012 at 9:19 PM, Normand Mongeau<nmong...@theobjects.com>wrote:


not really, no. This is a server that receives files, and the transaction
below means a file has arrived.



You haven't told us how big the records are. If you are storing, e.g., 2GB
file uploads in each transaction then of course it will be slow. You also
haven't told us what type of storage you're using. Someone posted recently
about a server process which writes to an SD card (which is bound to be
somewhat slow).



--
*Normand Mongeau*
ORS (Object Research Systems Inc.)
760 St-Paul W, #101
Montreal, QC
Canada H3C 1M4
Web: www.theobjects.com <http://www.theobjects.com/>
Tel: +1.514.843.3861 #204
Fax: +1.514.543.5475
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow commits

[Normand Mongeau]

not really, no. This is a server that receives files, and the 
transaction below means a file has arrived.


Normand


On 2012-01-13 15:16, Simon Slavin wrote:

On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote:


begin immediate transaction
insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71 
seconds) is spent on the commit transaction instruction.

Can you put one transaction around the whole lot rather than 178 separate 
transactions ?

Simon.
___
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] Slow commits

[Normand Mongeau]

Hi,

I have an app using sqlite, and the main insertion point is very slow on 
commitTransaction.


This is what I do:

begin immediate transaction
insert 1 record in tableA
insert 1 record in tableB
insert 1 record in tableC
commit transaction

Inserting 534 records takes about 75 seconds. Most of the time (about 71 
seconds) is spent on the commit transaction instruction.


If it's any important, this is all using the C++ API, with prepared 
statements.


Table A has 14 columns, with 2 indexes (not unique) and 1 primary key
Table B has 9 columns, with 2 non-unique indexes and 1 primary key
Table C has 13 columns, 1 non-unique index and 1 primary key

Current record count is 12, 10 and 627. Reason for few records in tableA 
and tableB is that I insert duplicate primary keys and just ignore 
SQLITE_CONSTRAINT errors.


Any ideas on what could be causing this?

Thanks,

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