Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Dan Kennedy]

On 03/29/2013 11:57 AM, Rob Turpin wrote:

Dan,

Thanks for looking into this.  So I get the notion of explicit scheduling
with lsm_work, but as I currently understand the sqlite4 API, explicit
scheduling can't be done with a sqlite4 database object.  Is that correct?


Technically I think you could just open the database with an lsm
connection and use it for the lsm_work() and lsm_checkpoint()
calls. But no, it can't really be done using the sqlite4 API.

Perhaps we need some super-easy way to delegate all that to a
background thread like Leveldb does.

Dan.





Rob


On Thu, Mar 28, 2013 at 2:02 PM, Dan Kennedy  wrote:


On 03/19/2013 01:18 AM, Rob Turpin wrote:


It's a simple table, primary key is an integer, with another column which
is integer.  Although I do begin a transaction, do 1000 updates and then
commit.

On Mon, Mar 18, 2013 at 11:36 AM, Dan Kennedy 
wrote:

  On 03/19/2013 12:28 AM, Rob Turpin wrote:


  I was running some performance tests for both sqlite3 and sqlite4 and

have
a question about a difference in my  numbers.

I'm running these on a Linux machine, so I enabled fdatasync for the
sqlite3 build.

I'm measuring the number of updates I can perform (updates per second),
here are the numbers.

sqlite3:
Updates (CPU): 156250
Updates (Clock): 27733.6

sqlite4:
Updates (CPU): 46729
Updates (Clock): 33132.8


With sqlite3 there's a large difference between the CPU time and wall
clock
time.  No big deal, that's the I/O to disk.  But then I'm wondering why
the
difference with sqlite4 is so small?




So I tried a test using db schema:

   CREATE TABLE t1(k INTEGER PRIMARY KEY, v INTEGER);

Initially the db is populated with 100,000 rows. Keys are
contiguous integers starting with 1 and values are pseudo-random
positive integers smaller than 2^32.

Then run 100,000 statements of the form:

   UPDATE t1 SET v = randomval() WHERE k = ?

with a BEGIN/COMMIT block around each 1000 UPDATEs.

With sqlite3, I get:

   real0m16.190s
   user0m0.620s
   sys 0m0.552s

And with sqlite4:

   real0m1.966s
   user0m1.424s
   sys 0m0.000s

On a 64-bit Linux host with synchronous=NORMAL and journal_mode=WAL.
sqlite3 built with -Dfdatasync=fdatasync.

So sqlite4 is using more CPU. But in this particular case is faster
overall because the way the database file is written is more efficient
(i.e. more sequential). It's probably writing a bit less too.

One way to reduce the (perceived) CPU usage with sqlite4 is to move
segment merges (database work) and database syncs (database checkpoints)
to a background thread or process. Maybe we should have some way to
make doing that easy for applications. More detail in the following,
especially 6.1 and 6.3:

   http://www.sqlite.org/src4/**doc/trunk/www/lsmusr.wiki#**
performance_tuning

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

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Simon Slavin]

On 29 Mar 2013, at 4:57am, Rob Turpin  wrote:

> Thanks for looking into this.  So I get the notion of explicit scheduling
> with lsm_work, but as I currently understand the sqlite4 API, explicit
> scheduling can't be done with a sqlite4 database object.  Is that correct?

Can you explain what it is that you care about the scheduling of ?  You can 
schedule whatever calls the SQLite API versus any other things your program is 
doing, but you can't schedule how SQLite works internally.

Changes to a SQLite database are performed under the SQL definition of a 
change: one transaction is one alteration; one transaction can involve 
creating/destroying/changing any number of rows of any number of tables; a 
number of transactions must change the database in the order that those 
transactions are committed.  SQLite does these things internally because ACID 
requires it.  I don't know of any reason to mess with SQLite internally since 
you signal everything you need to it by when you execute "COMMIT".

Something about ism_work can be found here:



but I don't know whether that addresses your problem.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Rob Turpin]

Dan,

Thanks for looking into this.  So I get the notion of explicit scheduling
with lsm_work, but as I currently understand the sqlite4 API, explicit
scheduling can't be done with a sqlite4 database object.  Is that correct?

Rob


On Thu, Mar 28, 2013 at 2:02 PM, Dan Kennedy  wrote:

> On 03/19/2013 01:18 AM, Rob Turpin wrote:
>
>> It's a simple table, primary key is an integer, with another column which
>> is integer.  Although I do begin a transaction, do 1000 updates and then
>> commit.
>>
>> On Mon, Mar 18, 2013 at 11:36 AM, Dan Kennedy 
>> wrote:
>>
>>  On 03/19/2013 12:28 AM, Rob Turpin wrote:
>>>
>>>  I was running some performance tests for both sqlite3 and sqlite4 and
 have
 a question about a difference in my  numbers.

 I'm running these on a Linux machine, so I enabled fdatasync for the
 sqlite3 build.

 I'm measuring the number of updates I can perform (updates per second),
 here are the numbers.

 sqlite3:
 Updates (CPU): 156250
 Updates (Clock): 27733.6

 sqlite4:
 Updates (CPU): 46729
 Updates (Clock): 33132.8


 With sqlite3 there's a large difference between the CPU time and wall
 clock
 time.  No big deal, that's the I/O to disk.  But then I'm wondering why
 the
 difference with sqlite4 is so small?

>>>
> So I tried a test using db schema:
>
>   CREATE TABLE t1(k INTEGER PRIMARY KEY, v INTEGER);
>
> Initially the db is populated with 100,000 rows. Keys are
> contiguous integers starting with 1 and values are pseudo-random
> positive integers smaller than 2^32.
>
> Then run 100,000 statements of the form:
>
>   UPDATE t1 SET v = randomval() WHERE k = ?
>
> with a BEGIN/COMMIT block around each 1000 UPDATEs.
>
> With sqlite3, I get:
>
>   real0m16.190s
>   user0m0.620s
>   sys 0m0.552s
>
> And with sqlite4:
>
>   real0m1.966s
>   user0m1.424s
>   sys 0m0.000s
>
> On a 64-bit Linux host with synchronous=NORMAL and journal_mode=WAL.
> sqlite3 built with -Dfdatasync=fdatasync.
>
> So sqlite4 is using more CPU. But in this particular case is faster
> overall because the way the database file is written is more efficient
> (i.e. more sequential). It's probably writing a bit less too.
>
> One way to reduce the (perceived) CPU usage with sqlite4 is to move
> segment merges (database work) and database syncs (database checkpoints)
> to a background thread or process. Maybe we should have some way to
> make doing that easy for applications. More detail in the following,
> especially 6.1 and 6.3:
>
>   http://www.sqlite.org/src4/**doc/trunk/www/lsmusr.wiki#**
> performance_tuning
>
> 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

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Nico Williams]

On Thu, Mar 28, 2013 at 6:06 PM, Simon Slavin  wrote:
> On 28 Mar 2013, at 9:54pm, Nico Williams  wrote:
>
>> Question: would it be better to optimize key and value encodings more
>> for CPU and less for space?
>
> Don't forget that SQLite is not solely intended for running on a desktop 
> computer.  Almost all smartphones use SQLite for many purposes.  My TV 
> recorder uses SQLite for keeping lists of channels and recorded programmes.  
> SQLite is used inside many GPS units to maintain tracks and Points Of 
> Interest.

There the disparity in favor of optimizing for speed might well be
bigger: using more CPU == using more power, shorter battery life.
Storage size has no impact on power consumption, though I/Os do, of
course, and larger encodings generally lead to more I/Os, but the
difference should still favor optimizing for speed.

The CPUs-get-no-faster-at-serial-work while
storage-and-network-gets-bigger-and-faster-all-the-time dynamic
applies all the more to mobile devices than to desktops.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Simon Slavin]

On 28 Mar 2013, at 9:54pm, Nico Williams  wrote:

> Question: would it be better to optimize key and value encodings more
> for CPU and less for space?

Don't forget that SQLite is not solely intended for running on a desktop 
computer.  Almost all smartphones use SQLite for many purposes.  My TV recorder 
uses SQLite for keeping lists of channels and recorded programmes.  SQLite is 
used inside many GPS units to maintain tracks and Points Of Interest.

The above use CPUs of very different designs.  They're so different that 
optimizing for one sort of CPU is almost the opposite of optimizing for another 
sort.  Some CPUs have big fat 64-bit databusses, others need to do everything 
in 8-bit.  (I don't know of anything that still works in 4-bit, but that's just 
a reason someone will post a follow-up telling me of one.)  Some CPUs are 
optimized for tiny die and minimum power-usage and concentrate on single-stream 
RISC.  Others are optimized for speed and do pipelining and speculative 
branch-processing (don't ask, it's horrible).

So while it's easy to optimize for the size of a database file, optimizing for 
unknown CPU is mostly just a question of keeping things as simple as possible.  
Which is where SQLite wins out anyway, because that's an advantage of its 
'lite' design.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Nico Williams]

On Thu, Mar 28, 2013 at 3:02 PM, Dan Kennedy  wrote:
> So sqlite4 is using more CPU. But in this particular case is faster
> overall because the way the database file is written is more efficient
> (i.e. more sequential). It's probably writing a bit less too.
>
> One way to reduce the (perceived) CPU usage with sqlite4 is to move
> segment merges (database work) and database syncs (database checkpoints)
> to a background thread or process. Maybe we should have some way to
> make doing that easy for applications. More detail in the following,
> especially 6.1 and 6.3:
>
>   http://www.sqlite.org/src4/doc/trunk/www/lsmusr.wiki#performance_tuning

Question: would it be better to optimize key and value encodings more
for CPU and less for space?

Space is still trending cheaper, but key and value encoding are very
serial[0] and serial CPU performance is not likely to improve much
more.  Therefore it seems better to optimize for speed than for space.
 And I'd say this is true even for mobile devices, where storage
capacities will probably keep increasing (at no additional cost in
power) while CPUs are particularly bound by power consumption.  Of
course, mobile apps aren't likely to suffer much from key/value
encoding being slow (lots of other things will be slower), but still.
A counter-argument would be that optimizing for space is optimizing
for I/O, but with random I/O getting faster (SSDs) I'm not sure that
the trade-off is justified.

Which brings me to: have you done enough profiling?  I don't mean to
presume that you haven't!  If you can share your results that'd be
great.

[0] Well, for each column value the encoding is very serial.  For
multiple columns the whole encoding may be highly parallelizable in
theory, but I'm betting that in practice it's not, so let's score the
whole thing as embarrassingly serial.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 and sqlite4 disk I/O

[Dan Kennedy]

On 03/19/2013 01:18 AM, Rob Turpin wrote:

It's a simple table, primary key is an integer, with another column which
is integer.  Although I do begin a transaction, do 1000 updates and then
commit.

On Mon, Mar 18, 2013 at 11:36 AM, Dan Kennedy  wrote:


On 03/19/2013 12:28 AM, Rob Turpin wrote:


I was running some performance tests for both sqlite3 and sqlite4 and have
a question about a difference in my  numbers.

I'm running these on a Linux machine, so I enabled fdatasync for the
sqlite3 build.

I'm measuring the number of updates I can perform (updates per second),
here are the numbers.

sqlite3:
Updates (CPU): 156250
Updates (Clock): 27733.6

sqlite4:
Updates (CPU): 46729
Updates (Clock): 33132.8


With sqlite3 there's a large difference between the CPU time and wall
clock
time.  No big deal, that's the I/O to disk.  But then I'm wondering why
the
difference with sqlite4 is so small?


So I tried a test using db schema:

  CREATE TABLE t1(k INTEGER PRIMARY KEY, v INTEGER);

Initially the db is populated with 100,000 rows. Keys are
contiguous integers starting with 1 and values are pseudo-random
positive integers smaller than 2^32.

Then run 100,000 statements of the form:

  UPDATE t1 SET v = randomval() WHERE k = ?

with a BEGIN/COMMIT block around each 1000 UPDATEs.

With sqlite3, I get:

  real0m16.190s
  user0m0.620s
  sys 0m0.552s

And with sqlite4:

  real0m1.966s
  user0m1.424s
  sys 0m0.000s

On a 64-bit Linux host with synchronous=NORMAL and journal_mode=WAL.
sqlite3 built with -Dfdatasync=fdatasync.

So sqlite4 is using more CPU. But in this particular case is faster
overall because the way the database file is written is more efficient
(i.e. more sequential). It's probably writing a bit less too.

One way to reduce the (perceived) CPU usage with sqlite4 is to move
segment merges (database work) and database syncs (database checkpoints)
to a background thread or process. Maybe we should have some way to
make doing that easy for applications. More detail in the following,
especially 6.1 and 6.3:

  http://www.sqlite.org/src4/doc/trunk/www/lsmusr.wiki#performance_tuning

Dan.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no mention of even number of digits for BLOB types

[James K. Lowden]

On Thu, 28 Mar 2013 17:55:00 +0100
Stephan Beal  wrote:

> On Thu, Mar 28, 2013 at 11:33 AM, Francesco Montorsi <
> francesco.monto...@embit.it> wrote:
> > The docs only say:
> > BLOB literals are string literals containing hexadecimal data
> > and preceded by a single "x" or "X" character
> >
> 
> The encoding is not (or shouldn't be!) done by the client, but by the
> sqlite internals, so there is generally no need to concern oneself
> with the number of bytes in the blob's encoded representation. Each
> byte of input to sqlite3_bind_blob() is internally converted to two
> hex bytes, but clients still work with normal bytes, not the encoded
> form.

sqlite> select x'40';
x'40' 
--
@ 

sqlite> select x'400';
SQL error: unrecognized token: "x'400'"

Hexadecimal string literals must consist of pairs of digits only.
I believe Francesco's point is that that fact is not expressed in the
documentation.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Timeline for full ALTER TABLE support?

[Tim Gustafson]

> Referring to a page at
> http://web.archive.org/web/20020926232103/http://www.sqlite.org/omitted.html,
> clearly dated mid-2002, Tim Gustafson effluviated:

Incorrect.  I was referring to:

http://www.sqlite.org/omitted.html

> The word "lie" is ill-chosen.  Of the top 3 items in that 10-item list, two
> have been implemented.  The "likely to be added" statement is arguable true,
> even in retrospect.  To suggest that any deceit was intended or that the
> statement was known to be false when made is unsupported by the evidence and
> indicates either ignorance of what the word "lie" means or bad faith.

Yes, "lie" was a poor choice of words.  How about "confusing"?

Poor choice of words aside, my point still stands.  If none of the
features listed on the "omitted" page are likely to be implemented any
time soon, the page should say so, rather than leading people to
believe that the ones at the top of the list might be implemented
soon.

And, indeed, it seems that someone has already removed the offending
sentence.  So, thanks to whoever did that!

-- 

Tim Gustafson
t...@ucsc.edu
831-459-5354
Baskin Engineering, Room 313A
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[James K. Lowden]

On Thu, 28 Mar 2013 08:43:50 -0400
Jeff Archer  wrote:

> > When you turn off journalling, you save something; when you
> > consolidate the activity into a single transaction, you save
> > something else.  What you're seeing is that the saving
> > achieved with reference to your base-line measurement by using a
> > single transaction exceeds that achieved by disabling journalling.
> >
> > /Niall
> >
> 
> Agreed.  But the question is: Why?  Without journaling only half as
> much writing to disk should occur so why would it take longer?

Jeff, you seem to working from the assumption that turning off
journalling should be the secret sauce because I/O is (or should be)
the dominant factor.  Your experiment is telling you
different: transaction control costs more than I/O.  

There are two ways you can think about it.  

1.  Look at the code path through SQLite to perform an insert.  If
you're like me, you'll give up before you get all the way through.  I
don't see any wasted motion, but there's a lot of work.  

2.  Consider the analogy to write(2).  Many small writes take longer
than one large one (up to some value of "large").  Without testing on
your system, I can tell you that writing 1 byte 5764 times, will take
more than 2X the time to write 5764 bytes one time.  

The OS has some overhead to handling a write operation, yet makes
no ACID promise.  (Not even durability!)  SQLite makes a higher
promise, if you will, at a higher price.  Per transaction.  

HTH.  

--jkl


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no mention of even number of digits for BLOB types

[Stephan Beal]

On Thu, Mar 28, 2013 at 11:33 AM, Francesco Montorsi <
francesco.monto...@embit.it> wrote:

> The docs only say:
> BLOB literals are string literals containing hexadecimal data and
> preceded by a single "x" or "X" character
>

The encoding is not (or shouldn't be!) done by the client, but by the
sqlite internals, so there is generally no need to concern oneself with the
number of bytes in the blob's encoded representation. Each byte of input to
sqlite3_bind_blob() is internally converted to two hex bytes, but clients
still work with normal bytes, not the encoded form.

http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Simon Slavin]

On 28 Mar 2013, at 12:43pm, Jeff Archer  wrote:

> But the question is: Why?  Without journaling only half as
> much writing to disk should occur so why would it take longer?

But you are still doing 5764 sets of writing.  One per transaction (which, 
since you are not defining transactions yourself is actually one per SQL 
command).  When you define a single transaction you're actually doing just one 
write: the only thing that requires a write to disk is your COMMIT command.  So 
naturally doing one write takes less time than doing 5764 write.

Journalling isn't relevant to the above.  Journals just add extra to the above, 
no matter whether you're doing a single transaction or many transactions.  But 
they don't make anything like as much difference as the difference between 1 
transaction and 5764 transactions.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TCL Test failures on ARM

[Dan Kennedy]

On 03/28/2013 02:18 PM, Bk wrote:

Hi,

The osFtruncate is not returning zero value.


And what is it setting errno to? At line 23440, when rc is non-zero,
what does perror("ftruncate") print?

Dan.






i have put breakpoint at

ts_ftruncate abd robust_ftruncate


(gdb) break robust_ftruncate
Breakpoint 2 at 0x636ec: file sqlite3.c, line 23439.
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$1 = 1
(gdb) step

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273
273  if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=8, sz=0) at sqlite3.c:23439
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$2 = 449088
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=8, n=284060) at ./src/test_syscall.c:273
273  if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$3 = 365128
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273
273  if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=5, sz=24576) at sqlite3.c:23439
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb)




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67936.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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] Performance with journal_mode = off

[Richard Hipp]

On Thu, Mar 28, 2013 at 9:03 AM, Jeff Archer <
jsarc...@nanotronicsimaging.com> wrote:

>
> Yes, I have read this.  (And now re-read it)
>
> So, since much more work must be done when using journal file, why
> does it take longer to do the inserts when there is NO journal file?


Much work must also be done to commit, with our without a journal.  So if
you only commit once, that runs faster than committing 5764 times.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Thu, Mar 28, 2013 at 8:24 AM, ibrahim  wrote:
> On 28.03.2013 13:09, Jeff Archer wrote:
>>
>> On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin 
>> wrote:
>>>
>>> Reasonable figures.  With 5764 writes to the disk in separates
>>> transactions you have quite a lot of reading of data plus 5764 attempts to
>>> update the database file.  The updates have to be done in the right order,
>>> and each update has to wait for the disk to be rotated into the right
>>> position, though each update changes only a small amount of data (probably
>>> two sectors).
>>
>> But my most basic question remains.  Why is single transaction faster
>> than PRAGMA journal_mode = off?
>>
>> Seems to me that with no journal there should only be single set of
>> writes to the actual db and that journaling would double the number of
>> writes because data has to be written to journal file as well.
>>
>> 2.5 sec with journal
>> 5.5 sec without journal   <= seems like this sould be the smaller number
>>
> You should read the sections 3 forward.
>
> http://www.sqlite.org/atomiccommit.html
>
> A single transaction happens mostly in memory then is flushed to the OS
> Cache in one step. The Journal file (the amount of pages that will be
> changed) is small while inserting new data into a database and the OS File
> Cache is usually large enough ...
>

Yes, I have read this.  (And now re-read it)

So, since much more work must be done when using journal file, why
does it take longer to do the inserts when there is NO journal file?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 28.03.2013 13:09, Jeff Archer wrote:

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin  wrote:

Reasonable figures.  With 5764 writes to the disk in separates transactions you 
have quite a lot of reading of data plus 5764 attempts to update the database 
file.  The updates have to be done in the right order, and each update has to 
wait for the disk to be rotated into the right position, though each update 
changes only a small amount of data (probably two sectors).

But my most basic question remains.  Why is single transaction faster
than PRAGMA journal_mode = off?

Seems to me that with no journal there should only be single set of
writes to the actual db and that journaling would double the number of
writes because data has to be written to journal file as well.

2.5 sec with journal
5.5 sec without journal   <= seems like this sould be the smaller number


You should read the sections 3 forward.

http://www.sqlite.org/atomiccommit.html

A single transaction happens mostly in memory then is flushed to the OS 
Cache in one step. The Journal file (the amount of pages that will be 
changed) is small while inserting new data into a database and the OS 
File Cache is usually large enough ...



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] no mention of even number of digits for BLOB types

[Francesco Montorsi]

Dear SQLite developers,
just a small "documentation bug": at the page
   http://www.sqlite.org/lang_expr.html#litvalue
there is no mention that, BLOB literals should have an even number of digits in order to 
be correctly recognized.


The docs only say:
BLOB literals are string literals containing hexadecimal data and preceded by a 
single "x" or "X" character



Thanks for this great software!
Keep up the good work,


--
Ing. Francesco Montorsi, Ph.D
Embedded System Engineer

Embit s.r.l.
Embedded & Wireless Solutions
Via Emilia Est, 911 41122 Modena - Italy
tel: +39 059 371714
fax: +39 059 3680498
email: francesco.monto...@embit.it
web: www.embit.eu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[ibrahim]

On 27.03.2013 22:55, Jeff Archer wrote:

On Wed, Mar 27, 2013 at 5:46 PM, David King  wrote:

I am populating a database with 5764 records using the exact same data set
each time into a newly created file.
When I use no explicit transactions (default atomic commit) it takes 17.7
seconds.
When I set journal_mode = off, same operation takes 5.5 seconds.
If I do all 5764 inserts within a single transaction only 2.5 seconds.


That sounds about right, yeah. With journalling, most disk writes have to be 
done twice (once to the journal and once to the data file).

Which is why I expected journal_mode = off to make it faster.  But it
is 3 seconds faster when I leave journaling enabled and do all writes
within a single transaction.

Seems like you have defined some sort of index. You will get faster bulk 
inserts when you create your indexes afterwards.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TCL Test failures on ARM

[Bk]

Hi, 

The osFtruncate is not returning zero value. i have put breakpoint at
ts_ftruncate abd robust_ftruncate 


(gdb) break robust_ftruncate 
Breakpoint 2 at 0x636ec: file sqlite3.c, line 23439. 
(gdb) continue 
Continuing. 

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439 
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR ); 
(gdb) p rc 
$1 = 1 
(gdb) step 

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273 
273  if( tsIsFailErrno("ftruncate") ){ 
(gdb) continue 
Continuing. 

Breakpoint 2, robust_ftruncate (h=8, sz=0) at sqlite3.c:23439 
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR ); 
(gdb) p rc 
$2 = 449088 
(gdb) continue 
Continuing. 

Breakpoint 1, ts_ftruncate (fd=8, n=284060) at ./src/test_syscall.c:273 
273  if( tsIsFailErrno("ftruncate") ){ 
(gdb) continue 
Continuing. 

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439 
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR ); 
(gdb) p rc 
$3 = 365128 
(gdb) continue 
Continuing. 

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273 
273  if( tsIsFailErrno("ftruncate") ){ 
(gdb) continue 
Continuing. 

Breakpoint 2, robust_ftruncate (h=5, sz=24576) at sqlite3.c:23439 
23439  do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR ); 
(gdb) 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67936.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TCL Test failures on ARM

[Bk]

Hi, 

The osFtruncate is not returning non zero value. i have put breakpoint at
ts_ftruncate abd robust_ftruncate


(gdb) break robust_ftruncate
Breakpoint 2 at 0x636ec: file sqlite3.c, line 23439.
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439
23439 do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$1 = 1
(gdb) step

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=8, sz=0) at sqlite3.c:23439
23439 do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$2 = 449088
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=8, n=284060) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23439
23439 do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) p rc
$3 = 365128
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=7, n=284060) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=5, sz=24576) at sqlite3.c:23439
23439 do{ rc = osFtruncate(h,sz); }while( rc<0 && errno==EINTR );
(gdb) 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67935.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Thu, Mar 28, 2013 at 8:35 AM, Niall O'Reilly  wrote:
>
> On 28 Mar 2013, at 12:09, Jeff Archer wrote:
>
>> But my most basic question remains.  Why is single transaction faster
>> than PRAGMA journal_mode = off?
>>
>> Seems to me that with no journal there should only be single set of
>> writes to the actual db and that journaling would double the number of
>> writes because data has to be written to journal file as well.
>>
>> 2.5 sec with journal
>> 5.5 sec without journal   <= seems like this sould be the smaller number
>
> Your base-line for comparison is the case of multiple transactions
> with journalling.
>
> When you turn off journalling, you save something; when you
> consolidate the activity into a single transaction, you save
> something else.  What you're seeing is that the saving achieved
> with reference to your base-line measurement by using a single
> transaction exceeds that achieved by disabling journalling.
>
> /Niall
>

Agreed.  But the question is: Why?  Without journaling only half as
much writing to disk should occur so why would it take longer?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Niall O'Reilly]

On 28 Mar 2013, at 12:09, Jeff Archer wrote:

> But my most basic question remains.  Why is single transaction faster
> than PRAGMA journal_mode = off?
> 
> Seems to me that with no journal there should only be single set of
> writes to the actual db and that journaling would double the number of
> writes because data has to be written to journal file as well.
> 
> 2.5 sec with journal
> 5.5 sec without journal   <= seems like this sould be the smaller number

Your base-line for comparison is the case of multiple transactions
with journalling.

When you turn off journalling, you save something; when you
consolidate the activity into a single transaction, you save
something else.  What you're seeing is that the saving achieved
with reference to your base-line measurement by using a single 
transaction exceeds that achieved by disabling journalling.

/Niall

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin  wrote:
>
> Reasonable figures.  With 5764 writes to the disk in separates transactions 
> you have quite a lot of reading of data plus 5764 attempts to update the 
> database file.  The updates have to be done in the right order, and each 
> update has to wait for the disk to be rotated into the right position, though 
> each update changes only a small amount of data (probably two sectors).

But my most basic question remains.  Why is single transaction faster
than PRAGMA journal_mode = off?

Seems to me that with no journal there should only be single set of
writes to the actual db and that journaling would double the number of
writes because data has to be written to journal file as well.

2.5 sec with journal
5.5 sec without journal   <= seems like this sould be the smaller number
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users