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 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-***<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>
>>>>>>>> <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-***<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>
>>>>>>> <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>
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
