Performance issue in different versions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance issue in different versions

Nick
This post was updated on .
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.182s          79.811s           58.723s         81.732s
                        (I use WAL mode and zDbName = "dbSpeedTest")

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.

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
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance issue in different versions

Richard Hipp-3
In reply to this post by Nick
On 2/1/18, Nick <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance issue in different versions

Dan Kennedy-4
In reply to this post by Nick
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.182s                79.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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance issue in different versions

Nick
In reply to this post by Richard Hipp-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users