Find out how many times does SQLite hit the disk?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Find out how many times does SQLite hit the disk?

Deepak
Dear SQLite Community,

On windows, is there a way to find out how many times does my SQLite DB hit
the disk?

This will help me in deciding if moving to In-memory SQLite will improve my
application performance. I am done with adding indexes to my tables in
SQLite DB.

If possible, please point me URLs that will help me.

Thank you,
_______________________________________________
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: Find out how many times does SQLite hit the disk?

Simon Slavin-3

On 30 Jan 2016, at 9:31pm, dpb <[hidden email]> wrote:

> On windows, is there a way to find out how many times does my SQLite DB hit
> the disk?
>
> This will help me in deciding if moving to In-memory SQLite will improve my
> application performance.

You will find that tools which examine process statistics will tell you how many read and write operations the process does.

To do it in SQLite you might add your own code to the standard VFS for your OS just to total up accesses to disk.

<https://www.sqlite.org/vfs.html>

However, I am not certain that counting disk accesses is actually going to help you figure anything one.

> I am done with adding indexes to my tables in
> SQLite DB.

Are you sure you're adding the /right/ indexes ?  I see many people adding indexes to commonly-used columns without making up a index designed specifically to help a particular SELECT command.

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
|

Re: Find out how many times does SQLite hit the disk?

J Decker
could use a tool like ProcMon and filter to disk activity on a
specified file to see...
https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255&MSPPError=-2147217396

On Sat, Jan 30, 2016 at 3:21 PM, Simon Slavin <[hidden email]> wrote:

>
> On 30 Jan 2016, at 9:31pm, dpb <[hidden email]> wrote:
>
>> On windows, is there a way to find out how many times does my SQLite DB hit
>> the disk?
>>
>> This will help me in deciding if moving to In-memory SQLite will improve my
>> application performance.
>
> You will find that tools which examine process statistics will tell you how many read and write operations the process does.
>
> To do it in SQLite you might add your own code to the standard VFS for your OS just to total up accesses to disk.
>
> <https://www.sqlite.org/vfs.html>
>
> However, I am not certain that counting disk accesses is actually going to help you figure anything one.
>
>> I am done with adding indexes to my tables in
>> SQLite DB.
>
> Are you sure you're adding the /right/ indexes ?  I see many people adding indexes to commonly-used columns without making up a index designed specifically to help a particular SELECT command.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Find out how many times does SQLite hit the disk?

J Decker
On Sat, Jan 30, 2016 at 8:09 PM, J Decker <[hidden email]> wrote:
> could use a tool like ProcMon and filter to disk activity on a
> specified file to see...
> https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255&MSPPError=-2147217396

Might not actually be useful though; if the file is memory mapped (WAL
Journal?) then you won't see those accesses...

then you'd end up having to hook into VFS stuff...

>
> On Sat, Jan 30, 2016 at 3:21 PM, Simon Slavin <[hidden email]> wrote:
>>
>> On 30 Jan 2016, at 9:31pm, dpb <[hidden email]> wrote:
>>
>>> On windows, is there a way to find out how many times does my SQLite DB hit
>>> the disk?
>>>
>>> This will help me in deciding if moving to In-memory SQLite will improve my
>>> application performance.
>>
>> You will find that tools which examine process statistics will tell you how many read and write operations the process does.
>>
>> To do it in SQLite you might add your own code to the standard VFS for your OS just to total up accesses to disk.
>>
>> <https://www.sqlite.org/vfs.html>
>>
>> However, I am not certain that counting disk accesses is actually going to help you figure anything one.
>>
>>> I am done with adding indexes to my tables in
>>> SQLite DB.
>>
>> Are you sure you're adding the /right/ indexes ?  I see many people adding indexes to commonly-used columns without making up a index designed specifically to help a particular SELECT command.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Find out how many times does SQLite hit the disk?

Yannick Duchêne
On Sat, 30 Jan 2016 20:36:55 -0800
J Decker <[hidden email]> wrote:

> On Sat, Jan 30, 2016 at 8:09 PM, J Decker <[hidden email]> wrote:
> > could use a tool like ProcMon and filter to disk activity on a
> > specified file to see...
> > https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx?f=255&MSPPError=-2147217396
>
> Might not actually be useful though; if the file is memory mapped (WAL
> Journal?) then you won't see those accesses...
>
> then you'd end up having to hook into VFS stuff...

If it's memory mapped, it's less an efficiency issue, which was the original concern if I remember well. If he wants to know about physical accesses, this may be what he needs.

--
Yannick Duchêne
_______________________________________________
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: Find out how many times does SQLite hit the disk?

Dominique Pellé
In reply to this post by Deepak
dpb <[hidden email]> wrote:
> Dear
 SQLite Community,
>
> On windows, is there a way to find out how many times does my SQLite DB hit
> the disk?
>
> This will help me in deciding if moving to In-memory SQLite will improve my
> application performance. I am done with adding indexes to my tables in
> SQLite DB.
>
> If possible, please point me URLs that will help me.

You could print the number of SQLite page misses.
Each miss causes an I/O of page size bytes.
See sqlite3_db_status():

https://www.sqlite.org/c3ref/db_status.html
https://www.sqlite.org/c3ref/c_dbstatus_options.html

On Linux, I would also use strace to see I/Os. I suppose
that procmon on Windows can also show I/Os.

Regards
Dominique
_______________________________________________
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: Find out how many times does SQLite hit the disk?

James K. Lowden
In reply to this post by Deepak
On Sun, 31 Jan 2016 03:01:30 +0530
dpb <[hidden email]> wrote:

> This will help me in deciding if moving to In-memory SQLite will
> improve my application performance. I am done with adding indexes to
> my tables in SQLite DB.

I second Simon's question.  If SQLite isn't fast enough, a good
starting assumption is that it's not being used as efficiently as
possible.  

Remember that problems at the SQL level can introduce delays that are
orders of magnitude greater than the difference in speed between memory
and disk.  That's especially true if your database is small enough to
consider switching to in-memory.  

--jkl
_______________________________________________
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: Find out how many times does SQLite hit the disk?

Rowan Worth
In reply to this post by Yannick Duchêne
On 31 January 2016 at 15:09, Yannick Duchêne <[hidden email]>
wrote:

> If it's memory mapped, it's less an efficiency issue,
>

Hm, can you elaborate on this assertion? I don't think I agree.

Lets say sqlite wants to access a page in the DB/journal. In the case of
normal file access this is a call to pread/ReadFile, and in the
memory-mapped case a call to memcpy.

Now, the data in question may or may not alreaby be in the OS's disk cache.
If it is pread/ReadFile/memcpy proceeds without delay. If the data is not
in the cache, pread/ReadFile blocks until the i/o is complete. Similarly,
memcpy will encounter a page fault and the process will block until the OS
completes the i/o required to fill the page in memory. I'll grant there's
an extra syscall per i/o in the normal file access mode, but this cost is
_vanishingly_ small compared to the time required to load data from disk.

Have I misunderstood the mechanism behind memory-mapped file access?
-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users