On May 14, 2009, at 4:08 AM, Ofir Neuman wrote: > Thanks Dan this was very helpful it seems that I have a similar > problem. > > Didn't try the patch yet but I find out a scenario to reproduce the > problem. > > 1. Process A perform UPDATE on bunch of records (within transaction) > 2. Afterward, process B perform the same UPDATE operation but only > on a > single record > 3. The next time process A will try to perform the same UPDATE again > (bunch > of records) the results will be bad. > Looking at the task manager I can see that process A doesn't consume > any CPU > usage just I/O. (the CPU should increase like in step 1) > > One question regarding the patch, should I add only the following > line as > suggested? > Pcache1RemoveFromHash(pPage);
No. That is almost, but not quite, correct. Apply the patch to pcache1.c as shown here: http://www.sqlite.org/cvstrac/chngview?cn=6619 Or, if you want to do it by hand, add the line: pCache->nPage--; where the original ticket suggests Pcache1RemoveFromHash(). Dan. > Thanks, > Ofir. > > > -------Original Message------- > > From: Dan > Date: 05/13/09 07:23:16 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Bad UPDATE performance > > On May 13, 2009, at 3:15 AM, Ofir Neuman wrote: > >> Hi, >> >> Using version 3.6.10.0. >> >> I have a problem that my application becomes slow after awhile with >> no >> specific reasonI got a chance to reproduce it on development >> environment and >> have noticed that UPDATE of 200 records within transaction takes >> more than >> 20 sec. >> >> When the application first run the same update for the same records >> takes >> something like 1-2 sec. >> >> Since I had the problem while debugging I try to figure out the >> problem and >> have noticed the following things: >> >> 1. When I suffer bad UPDATE performance the journal file is 3 times >> bigger >> than on regular run (when UPDATE command takes 1-2 sec) >> 2. Most of the time spent on the UPDATE itself and not on the COMMIT >> operation. >> 3. I tried to open new db connection while the application still >> running and >> do the same operation and guess what... Everything works fine the >> application is responsive and the same update took something like a >> second. >> >> I didn't find a specific scenario but I can reproduce it if I do the >> same >> operation on the DB multiple times, after a while everything will >> become >> slow. > > Maybe try this patch: > > http://www.sqlite.org/cvstrac/chngview?cn=6619 > > Dan. > > _______________________________________________ > 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
