Re: [sqlite] sqlite abnormal IO writing
Just for "fun" try turning synchronous = OFF and see what kind of speed boost you get. On Sun, Apr 21, 2013 at 9:41 PM, 刘运杰 wrote: > > > My database connection option: > > > PRAGMA synchronous = NORMAL; > PRAGMA journal_mode = OFF; > PRAGMA auto_vacuum = 0; > > > In windows process monitor ,it show 2.5M writing ,but the database file > increase only 10k or so. > So those IO writing is very abnormal IMO. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
My database connection option:
PRAGMA synchronous = NORMAL;
PRAGMA journal_mode = OFF;
PRAGMA auto_vacuum = 0;
In windows process monitor ,it show 2.5M writing ,but the database file
increase only 10k or so.
So those IO writing is very abnormal IMO.
At 2013-04-22 03:24:43,"Stephen Chrzanowski" wrote:
>You know, this kind of "smells" like a full sync, non transactioned, single
>inserts problem. Basically, you open the connection to the database, start
>throwing inserts at it without a transaction around each insert. To make
>matters worse (For time anyways) you've probably got full sync on, and
>you're not using prepared statements. I think this because of the volume
>of data you're writing versus the time it takes and the final size of the
>file. 2 meg isn't even a snack by todays standards. Depending on the
>wrapper you're using, synchronous can be turned on to FULL, or it could be
>set to normal. The wrapper of my wrapper always turns Synchronous off.
>
>http://www.sqlite.org/pragma.html#pragma_fullfsync
>http://www.sqlite.org/pragma.html#pragma_synchronous
>
>If this is the production method, since you're rebuilding the data every
>time, I think you could pull all the safeties off in this case. Right
>after you open the connection, execute these two PRAGMAs:
>**
>PRAGMA synchronous = 0
>PRAGMA fullfsync = 0 (Only if being used on a Mac)
>
>When you start inserting data, put them within a transaction. Personally,
>I haven't figured out how to do prepared statements with the wrapper I
>have, so I basically re-do my query every time. It IS a performance hit.
>I get the concept, but the methodology seems to be odd. *shrugs*.
>Basically, from what I've gathered, you write your SQL statement like:
>
>insert into TempTable (Field1, Field2) values (:x,:y)
>
>Then you tell SQLite to substitute the :x for one value, and ;y for
>another. Then you loop through the bulk inserts updating only the fields
>you substituted, not the entire query.
>
>The actual METHOD to do this depends on the wrapper and language you're
>using, so a bit of homework is going to be needed.
>
>The other thing you could do is create a new database in memory. What you
>do is create a second database connection but instead of opening a file,
>open it to memory via *OPEN(':MEMORY:)* with the colons, and use the backup
>API SQLite provides, work off the data in memory, then reverse the backup
>and put it back to the disk. This, of course, is going to depend entirely
>on how much memory you have to play with. The backup API copies the ENTIRE
>database to the destination specified. So if you have a 12gig database in
>a 4gig machine... .. well
>
>
>**
>
>
>On Sun, Apr 21, 2013 at 2:31 PM, Paolo Bolzoni <
>paolo.bolzoni.br...@gmail.com> wrote:
>
>> FAQ 19 applies also to SAVEPOINT/RELEASE, not only to BEGIN/COMMIT, right?
>>
>> On Sun, Apr 21, 2013 at 9:35 AM, Kees Nuyt wrote:
>> > On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ??
>> > wrote:
>> >>
>> >> Ok,I do not make my means clearly. I mean 60 seconds after my
>> >> program started,not token 60 seconds to load database file.
>> >>
>> >> Now, I got the reason of sqlite abnormal IO writing,it about
>> >> batch insert. Here is my usecase: One table about 4 column and
>> >> 500 row,the content of every row no exceed 100 byte, every time
>> >> I update the whole table using batch query.
>> >>
>> >> It should take about one second and 100k IO writing on
>> >> estimate,BUT it sustained about 20 second and wrote about 2.5M
>> >> actually.
>> >>
>> >> Now,I modify the implement of batch query, it take about one
>> >> second and 70k IO writing.So there are abnormal something in
>> >> batch query indeed,Sqlite or Qt SQL module.
>> >
>> > Is this still about bulk INSERT or about a SEELCT query?
>> > In which way did you modify it?
>> > Perhaps http://sqlite.org/faq.html#q19 helps?
>> >
>> > --
>> > Groet, Cordialement, Pozdrawiam, Regards,
>> >
>> > Kees Nuyt
>> >
>> > ___
>> > 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
Here is the schema of table: CREATE TABLE statdata ( typeINT, seq INT, uid VARCHAR( 36 ), counter INT, PRIMARY KEY ( type, seq ASC ) ); before my modify: 500 rows at maximum, query:insert or replace (...) in batch mode take 10-20seconds and 2.5M IO writing after my modify: 100 rows at maximum, query:delete from statdata; insert (...) in batch mode, take about second and 70k IO writing So IO writing is unacceptable before my modify. 在 2013-04-22 06:16:49，"Klaas V" 写道： >>On Sun, 21 Apr 2013 11:15:23 +0800 (CST), 刘运杰 >>wrote: >>Ok,I do not make my means clearly. I mean 60 seconds after my >>program started,not token 60 seconds to load database file. >... >>Now,I modify the implement of batch query, it take about one >>second and 70k IO writing.So there are abnormal something in >>batch query indeed,Sqlite or Qt SQL module. > >>>From: Kees Nuyt >>>Date: 21 Apr 2013 09:35:53 GMT+02:00 >>>Is this still about bulk INSERT > >It looks like Yunjie reorganized the database in a way that the specific table >is contiguous. >This action can make a huge difference in I/O time. >___ >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] sqlite abnormal IO writing
>On Sun, 21 Apr 2013 11:15:23 +0800 (CST), 刘运杰 >wrote: >Ok,I do not make my means clearly. I mean 60 seconds after my >program started,not token 60 seconds to load database file. ... >Now,I modify the implement of batch query, it take about one >second and 70k IO writing.So there are abnormal something in >batch query indeed,Sqlite or Qt SQL module. >>From: Kees Nuyt >>Date: 21 Apr 2013 09:35:53 GMT+02:00 >>Is this still about bulk INSERT It looks like Yunjie reorganized the database in a way that the specific table is contiguous. This action can make a huge difference in I/O time. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
You know, this kind of "smells" like a full sync, non transactioned, single
inserts problem. Basically, you open the connection to the database, start
throwing inserts at it without a transaction around each insert. To make
matters worse (For time anyways) you've probably got full sync on, and
you're not using prepared statements. I think this because of the volume
of data you're writing versus the time it takes and the final size of the
file. 2 meg isn't even a snack by todays standards. Depending on the
wrapper you're using, synchronous can be turned on to FULL, or it could be
set to normal. The wrapper of my wrapper always turns Synchronous off.
http://www.sqlite.org/pragma.html#pragma_fullfsync
http://www.sqlite.org/pragma.html#pragma_synchronous
If this is the production method, since you're rebuilding the data every
time, I think you could pull all the safeties off in this case. Right
after you open the connection, execute these two PRAGMAs:
**
PRAGMA synchronous = 0
PRAGMA fullfsync = 0 (Only if being used on a Mac)
When you start inserting data, put them within a transaction. Personally,
I haven't figured out how to do prepared statements with the wrapper I
have, so I basically re-do my query every time. It IS a performance hit.
I get the concept, but the methodology seems to be odd. *shrugs*.
Basically, from what I've gathered, you write your SQL statement like:
insert into TempTable (Field1, Field2) values (:x,:y)
Then you tell SQLite to substitute the :x for one value, and ;y for
another. Then you loop through the bulk inserts updating only the fields
you substituted, not the entire query.
The actual METHOD to do this depends on the wrapper and language you're
using, so a bit of homework is going to be needed.
The other thing you could do is create a new database in memory. What you
do is create a second database connection but instead of opening a file,
open it to memory via *OPEN(':MEMORY:)* with the colons, and use the backup
API SQLite provides, work off the data in memory, then reverse the backup
and put it back to the disk. This, of course, is going to depend entirely
on how much memory you have to play with. The backup API copies the ENTIRE
database to the destination specified. So if you have a 12gig database in
a 4gig machine... .. well
**
On Sun, Apr 21, 2013 at 2:31 PM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:
> FAQ 19 applies also to SAVEPOINT/RELEASE, not only to BEGIN/COMMIT, right?
>
> On Sun, Apr 21, 2013 at 9:35 AM, Kees Nuyt wrote:
> > On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ??
> > wrote:
> >>
> >> Ok,I do not make my means clearly. I mean 60 seconds after my
> >> program started,not token 60 seconds to load database file.
> >>
> >> Now, I got the reason of sqlite abnormal IO writing,it about
> >> batch insert. Here is my usecase: One table about 4 column and
> >> 500 row,the content of every row no exceed 100 byte, every time
> >> I update the whole table using batch query.
> >>
> >> It should take about one second and 100k IO writing on
> >> estimate,BUT it sustained about 20 second and wrote about 2.5M
> >> actually.
> >>
> >> Now,I modify the implement of batch query, it take about one
> >> second and 70k IO writing.So there are abnormal something in
> >> batch query indeed,Sqlite or Qt SQL module.
> >
> > Is this still about bulk INSERT or about a SEELCT query?
> > In which way did you modify it?
> > Perhaps http://sqlite.org/faq.html#q19 helps?
> >
> > --
> > Groet, Cordialement, Pozdrawiam, Regards,
> >
> > Kees Nuyt
> >
> > ___
> > 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] sqlite abnormal IO writing
FAQ 19 applies also to SAVEPOINT/RELEASE, not only to BEGIN/COMMIT, right? On Sun, Apr 21, 2013 at 9:35 AM, Kees Nuyt wrote: > On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?? > wrote: >> >> Ok,I do not make my means clearly. I mean 60 seconds after my >> program started,not token 60 seconds to load database file. >> >> Now, I got the reason of sqlite abnormal IO writing,it about >> batch insert. Here is my usecase: One table about 4 column and >> 500 row,the content of every row no exceed 100 byte, every time >> I update the whole table using batch query. >> >> It should take about one second and 100k IO writing on >> estimate,BUT it sustained about 20 second and wrote about 2.5M >> actually. >> >> Now,I modify the implement of batch query, it take about one >> second and 70k IO writing.So there are abnormal something in >> batch query indeed,Sqlite or Qt SQL module. > > Is this still about bulk INSERT or about a SEELCT query? > In which way did you modify it? > Perhaps http://sqlite.org/faq.html#q19 helps? > > -- > Groet, Cordialement, Pozdrawiam, Regards, > > Kees Nuyt > > ___ > 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] sqlite abnormal IO writing
On 21.04.2013 05:15, 刘运杰 wrote: Ok,I do not make my means clearly. I mean 60 seconds after my program started,not token 60 seconds to load database file. Now, I got the reason of sqlite abnormal IO writing,it about batch insert. Here is my usecase: One table about 4 column and 500 row,the content of every row no exceed 100 byte, every time I update the whole table using batch query. It should take about one second and 100k IO writing on estimate,BUT it sustained about 20 second and wrote about 2.5M actually. Now,I modify the implement of batch query, it take about one second and 70k IO writing.So there are abnormal something in batch query indeed,Sqlite or Qt SQL module. Can you give a table schema ? It's important to know what kind of primary key and indexes you define (if present). The amount of data written leads to the assumption that you are using a primary key not of type integer primary key and you have a few indexes perhaps to make searching faster. If you have indexes and another primary key than integer primary key than you can improve the performance by delaying index creation until after a insert / update or by dropping a index before a bulk update and recreating the index afterwards. Otherwise each raw can lead to more than x page inserts updates. Your scheme would be of help ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
刘运杰 wrote: >One table about 4 column and 500 row,the content of every row no exceed >100 byte, every time I update the whole table using batch query. > >It should take about one second and 100k IO writing on estimate,BUT it >sustained about 20 second and wrote about 2.5M actually. Please show that query. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?? wrote: > > Ok,I do not make my means clearly. I mean 60 seconds after my > program started,not token 60 seconds to load database file. > > Now, I got the reason of sqlite abnormal IO writing,it about > batch insert. Here is my usecase: One table about 4 column and > 500 row,the content of every row no exceed 100 byte, every time > I update the whole table using batch query. > > It should take about one second and 100k IO writing on > estimate,BUT it sustained about 20 second and wrote about 2.5M > actually. > > Now,I modify the implement of batch query, it take about one > second and 70k IO writing.So there are abnormal something in > batch query indeed,Sqlite or Qt SQL module. Is this still about bulk INSERT or about a SEELCT query? In which way did you modify it? Perhaps http://sqlite.org/faq.html#q19 helps? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
Ok,I do not make my means clearly. I mean 60 seconds after my program started,not token 60 seconds to load database file. Now, I got the reason of sqlite abnormal IO writing,it about batch insert. Here is my usecase: One table about 4 column and 500 row,the content of every row no exceed 100 byte, every time I update the whole table using batch query. It should take about one second and 100k IO writing on estimate,BUT it sustained about 20 second and wrote about 2.5M actually. Now,I modify the implement of batch query, it take about one second and 70k IO writing.So there are abnormal something in batch query indeed,Sqlite or Qt SQL module. At 2013-04-21 01:36:16,"Stephen Chrzanowski" wrote: >Attachments cannot be put into this mailing list. Please either upload to >a service like PasteBin or put a copy in your PUBLIC directory in DropBox >and provide us with a link. > >Try using a different application, like the Command Line Interface (CLI) >and open it through there and see if you get a hit. Although I've seen 1.5 >second delays on an open when the file is in a READ ONLY state (Either by >file system permissions, or, just the file attribute) under the Windows >environment, 60 seconds seems to be too much unless you're hammering it >with open and closes. > > >On Sat, Apr 20, 2013 at 12:12 PM, 刘运杰 wrote: > >> Sqlite version : "3.7.16" (Qt 4.8.2 ) >> OS: Windows XP >> Databse PRAGMA: PRAGMA synchronous = NORMAL; PRAGMA journal_mode = OFF; >> PRAGMA auto_vacuum = 0; >> >> the process: >> I wrote one application using Qt 4.8.2 and Sqlite "3.7.16". Every time >> after my program start up 60 second, the sqlite database file will rebuild >> undergroud, >> it sustained about 20 second and write about 2.5M, this process can be >> monitor by Windows Process Monitor utility. The attachmet is the procmon >> log file with csv format. "YodaNote.exe" is my program , >> "F:\release\YodaNote\a4.yns" is the sqlite database file. >> To use "Process Monitor" , you can follow this article : How can I monitor >> I/O activity on a specific file or folder in Windows >> http://www.veryant.com/support/phpkb/question.php?ID=136 >> >> >> ___ >> 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] sqlite abnormal IO writing
Attachments cannot be put into this mailing list. Please either upload to a service like PasteBin or put a copy in your PUBLIC directory in DropBox and provide us with a link. Try using a different application, like the Command Line Interface (CLI) and open it through there and see if you get a hit. Although I've seen 1.5 second delays on an open when the file is in a READ ONLY state (Either by file system permissions, or, just the file attribute) under the Windows environment, 60 seconds seems to be too much unless you're hammering it with open and closes. On Sat, Apr 20, 2013 at 12:12 PM, 刘运杰 wrote: > Sqlite version : "3.7.16" (Qt 4.8.2 ) > OS: Windows XP > Databse PRAGMA: PRAGMA synchronous = NORMAL; PRAGMA journal_mode = OFF; > PRAGMA auto_vacuum = 0; > > the process: > I wrote one application using Qt 4.8.2 and Sqlite "3.7.16". Every time > after my program start up 60 second, the sqlite database file will rebuild > undergroud, > it sustained about 20 second and write about 2.5M, this process can be > monitor by Windows Process Monitor utility. The attachmet is the procmon > log file with csv format. "YodaNote.exe" is my program , > "F:\release\YodaNote\a4.yns" is the sqlite database file. > To use "Process Monitor" , you can follow this article : How can I monitor > I/O activity on a specific file or folder in Windows > http://www.veryant.com/support/phpkb/question.php?ID=136 > > > ___ > 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] sqlite abnormal IO writing
Sqlite version : "3.7.16" (Qt 4.8.2 ) OS: Windows XP Databse PRAGMA: PRAGMA synchronous = NORMAL; PRAGMA journal_mode = OFF; PRAGMA auto_vacuum = 0; the process: I wrote one application using Qt 4.8.2 and Sqlite "3.7.16". Every time after my program start up 60 second, the sqlite database file will rebuild undergroud, it sustained about 20 second and write about 2.5M, this process can be monitor by Windows Process Monitor utility. The attachmet is the procmon log file with csv format. "YodaNote.exe" is my program , "F:\release\YodaNote\a4.yns" is the sqlite database file. To use "Process Monitor" , you can follow this article : How can I monitor I/O activity on a specific file or folder in Windows http://www.veryant.com/support/phpkb/question.php?ID=136 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
