Quantcast

Difference between min and max time, especially for sys

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Difference between min and max time, especially for sys

Cecil Westerhof-5
I have several queries which I time with ‘.timer on’. I repeat the queries
350 times. I see a big difference between the minimum and maximum time
needed to run the queries. Especially for sys. Like:
Number of iterations 350
Timing OR version
real: 120.86, min: 0.2710, max: 0.5540, max/min:  2.04
user: 110.10, min: 0.2360, max: 0.4760, max/min:  2.02
sys :   9.87, min: 0.0040, max: 0.0640, max/min: 16.00


Timing ABS version
real: 108.96, min: 0.2730, max: 0.4850, max/min:  1.78
user:  97.67, min: 0.2320, max: 0.3720, max/min:  1.60
sys :  10.67, min: 0.0080, max: 0.0680, max/min:  8.50


Timing NOT BETWEEN version
GOT A ZERO VALUE FOR SYS!!!
real: 106.51, min: 0.2610, max: 0.4830, max/min:  1.85
user:  95.64, min: 0.2160, max: 0.4160, max/min:  1.93
sys :  10.14, min: 0.0040, max: 0.0760, max/min: 19.00


Timing NOT BETWEEN version 2
GOT A ZERO VALUE FOR SYS!!!
real: 113.77, min: 0.2770, max: 0.5370, max/min:  1.94
user: 101.76, min: 0.2320, max: 0.4520, max/min:  1.95
sys :  11.24, min: 0.0040, max: 0.0680, max/min: 17.00



So the difference between minimum and maximum is about a factor two. What
seems reasonable to me. But the difference between sys can be almost a
factor twenty. What seems very big to me.

Also it looks like SQLite suggests a precision it does not have. It shows
four digits after the point, but it looks like that the smallest value
greater as zero is 0.004. So why does SQLite not show three digits after
the point?



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between min and max time, especially for sys

Simon Slavin-3

On 15 Feb 2017, at 7:47pm, Cecil Westerhof <[hidden email]> wrote:

> So the difference between minimum and maximum is about a factor two. What
> seems reasonable to me. But the difference between sys can be almost a
> factor twenty. What seems very big to me.

Caching affects sys.  Might be difference between time taken to fetch from disk and time taken when already in memory.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between min and max time, especially for sys

Jens Alfke-2
In reply to this post by Cecil Westerhof-5

> On Feb 15, 2017, at 11:47 AM, Cecil Westerhof <[hidden email]> wrote:
>
> But the difference between sys can be almost a factor twenty. What seems very big to me.

Yup. There is a huge speed difference between the kernel going to the disk/SSD to read from a file, vs. the kernel reading the data out of its buffer cache. That’s why the kernel has buffer caches :)

(And those disk reads can be hugely variable. A hard disk can spend 10ms or more seeking, and any medium has bandwidth limits, so other processes’ I/O can greatly slow down your own. I’ve seen filesystem slowdowns of 20x or more shortly after a user logs in when dozens of processes are launching at once.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Difference between min and max time, especially for sys

Cecil Westerhof-5
2017-02-16 19:31 GMT+01:00 Jens Alfke <[hidden email]>:

>
> > On Feb 15, 2017, at 11:47 AM, Cecil Westerhof <[hidden email]>
> wrote:
> >
> > But the difference between sys can be almost a factor twenty. What seems
> very big to me.
>
> Yup. There is a huge speed difference between the kernel going to the
> disk/SSD to read from a file, vs. the kernel reading the data out of its
> buffer cache. That’s why the kernel has buffer caches :)
>
> (And those disk reads can be hugely variable. A hard disk can spend 10ms
> or more seeking, and any medium has bandwidth limits, so other processes’
> I/O can greatly slow down your own. I’ve seen filesystem slowdowns of 20x
> or more shortly after a user logs in when dozens of processes are launching
> at once.)
>

​Well user/sys is mostly around 10, so it does not have a to big influence
on the measurements. ;-)

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...