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 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/listinf >>>>>>>> o/sqlite-***> >>>>>>>> ***users<http://sqlite.org:**8080/cgi-****bin/mailman/** >>>>>>>> listinfo/sqlite-****users<http://sqlite.org:8080/cgi-****bin/ma >>>>>>>> ilman/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/mailma >>>>>>>> n/listinfo/sqlite-**users> >>>>>>>> > >>>>>>>> <http://sqlite.org:8080/**cgi-****bin/mailman/listinfo/**** >>>>>>>> sqlite-**users<http://sqlite.org:8080/**cgi-**bin/mailman/listi >>>>>>>> nfo/**sqlite-**users> >>>>>>>> <http://sqlite.**org:8080/**cgi-bin/mailman/** >>>>>>>> listinfo/**sqlite-users<http://sqlite.org:8080/**cgi-bin/mailma >>>>>>>> n/listinfo/**sqlite-users> >>>>>>>> > >>>>>>>> <http://sqlite.org:8080/**cgi-**bin/mailman/listinfo/**sqlite-* >>>>>>>> * >>>>>>>> users<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlit >>>>>>>> e-users> >>>>>>>> <http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-use >>>>>>>> rs<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/sqlit >>>>>>> e-***> >>>>>>> ***users<http://sqlite.org:**8080/cgi-****bin/mailman/** >>>>>>> listinfo/sqlite-****users<http://sqlite.org:8080/cgi-****bin/mai >>>>>>> lman/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/listin >>>>>>> fo/**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-user >>>>>>> s<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/sqlit >>>>> e-****users> >>>>> <http://sqlite.org:**8080/cgi-**bin/mailman/**listinfo/sqlite-**us >>>>> ers<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**use >>>>> rs> >>>>> > >>>>> <http://sqlite.org:8080/**cgi-**bin/mailman/listinfo/**sqlite-**us >>>>> ers<http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-use >>>>> rs> >>>>> <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<ht >>> tp://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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
