On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeau <[email protected]>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;
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.
>
>
>
> On 2012-01-13 15:35, Richard Hipp wrote:
>
>> On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau<nmongeau@theobjects.**
>> com <[email protected]>>wrote:
>>
>>
>>> On 2012-01-13 15:23, Richard Hipp wrote:
>>>
>>> On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau<nmongeau@theobjects.**
>>>> com<[email protected]>>**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
>>>>>> [email protected]
>>>>>>
>>>>>> 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
>>>>> [email protected]
>>>>> 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
>>> [email protected]
>>> 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
> [email protected]
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
