Re: [sqlite] Performance issue in different versions

[Nick]

I realized that the amount of memory used for the page cache is different.
And I found that is the root cause.
Sorry for my careless mistake.

Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in different versions

[Nick]

Yup, absolutely you are right.
I just ran a new test using the same upper bound on the amount of memory
used for the page cache, then I found a reasonable result.

Thank you, Dan.
I did notice the cache_size change before but you made me realize it.

Thanks a lot.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in different versions

[Dan Kennedy]

On 02/01/2018 04:05 PM, Nick wrote:

I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.
And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096
(changed since 3.12.0).
I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I
use speedtest1.c to test it.

There are many test cases in speedtest1.c and case 270 is a DELETE case
which is the most time-consuming one.
There is a result.  (different version + different page_size)

  3.16.2+4096 3.16.2+1024  3.9.2+4096
3.9.2+1024
Case 270:   5.695s 5.908s   2.307s
6.130s
TOTAL  75.182s79.811s 58.723s
81.732s

It is easy to find 3.9.2+4096 is extremely faster than others.
And page_size has great effect on 3.9.2 but has only a small effect on
3.16.2.
But why?


I think 3.16.2 should faster than 3.9.2 according to the measurements of
sqlite (https://www.sqlite.org/cpu.html) but I guess 3.9.2 used 1024 at that
time.

Could someone give me any explanations? The result is strange but I think it
is reproducible if we set SQLITE_DEFAULT_PAGE_SIZE=4096 in 3.9.2.


Are you using the same sized cache in all tests?

For 3.9.2, the default cache was 2000 pages - 2MB with 1024 byte pages 
and 8MB with 4096 byte pages. But for 3.16.2 the default cache was 
changed to "-2000" - which means 2MB of cache regardless of page size. 
See the third paragraph of this:


  http://sqlite.org/pgszchng2016.html

Dan.


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

Re: [sqlite] Performance issue in different versions

[Richard Hipp]

On 2/1/18, Nick  wrote:
> I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.
>
> There are many test cases in speedtest1.c and case 270 is a DELETE case
> which is the most time-consuming one.
> There is a result.  (different version + different page_size)
>
>  3.16.2+4096 3.16.2+1024  3.9.2+4096
> 3.9.2+1024
> Case 270:   5.695s 5.908s   2.307s
>

We get very different results when comparing the performance of 3.9.2
to 3.16.2.  See the graph at https://www.sqlite.org/cpu.html for
example.

Since in your case, the 3.9.2 was compiled by Google and you compiled
3.16.2 yourself, I suggest that there might be some difference in your
compiler or compiler settings.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in different versions

[Nick]

Um, I am a OS application developer and we just upgraded the source code on
our developing engine.

I am sure I used the same compile-options. 
SQLITE_SECURE_DELETE is not set.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in different versions

[Clemens Ladisch]

Nick wrote:
> I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2.

How?  Your own copy, or the system one?
Did you use the same configuration?  Especially SQLITE_SECURE_DELETE?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users