[sqlite] SQL statements not captured by sqlite_trace or sqlite_profile?

[杨苏立 Yang Su Li]

Hi,

I am trying to use sqlite_trace and sqlite_profile to trace what SQL
statements have been executed.

In the callback function of sqlite_trace/profile I simply print out the SQL
statement which triggers the callback. And in sqlite_open_v2() I have:
sqlite3_trace(*ppDb, print_sql_callback, *ppDb);
sqlite3_profile(*ppDb, print_sql_callback, *ppDb);


However, it seems like not every SQL statement is captured. As I also
instrumented sqlite_step(), sqlite_bind_*(), and sqlite_clear_binding(),
sqlite_reset() to print out log. And some sql statements which are shown in
these logs do not seem to be printed out by sqlite_trace or sqlite_profile.

Does anyone have an idea about what went wrong here?

Thanks a lot!

Suli

-- 
Suli Yang

Department of Physics
University of Wisconsin Madison

4257 Chamberlin Hall
Madison WI 53703
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there a way to measure query time in sqlite at runtime?

[杨苏立 Yang Su Li]

I want to measure the execution time of each sql statement in sqlite.

I understand in sqlite shell you could just do .timer on, but I am
wondering how to do it in a pogrammerable way so that I could know how much
time a sql statement takes when applications are accessing the database at
real time, not just by replaying that statement in a shell afterwards?

Could I just provide a plugin function for sqlite_profile?

Thanks a lot


Suli

-- 
Suli Yang

Department of Physics
University of Wisconsin Madison

4257 Chamberlin Hall
Madison WI 53703
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[杨苏立 Yang Su Li]

On Thu, Nov 15, 2012 at 10:29 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 15 Nov 2012, at 4:14pm, 杨苏立 Yang Su Li <s...@cs.wisc.edu> wrote:
>
> > 1. fsync actually does two things at the same time: ordering writes (in a
> > barrier-like manner), and forcing cached writes to disk. This makes it
> very
> > difficult to implement fsync efficiently. However, logically they are two
> > distinctive functionalities, and user might want one but not the other.
> > Particularly, users might want a barrier, but doesn't care about
> durability
> > (much). I have no idea why ordering and durability, which seem quite
> > different, ended up being bundled together in a single fsync call.
> >
> > 2. fsync semantic in POSIX is a bit vague (at least to me) in a
> concurrent
> > setting. What is the expected behavior when more than one thread write to
> > the same file descriptor, or different file descriptor associated with
> the
> > same file?
>
> And, as has been posted many times here and elsewhere, on many systems
> fsync does nothing at all.  It is literally implemented as a 'noop'.  So
> you cannot use it as a basis for barriers.
>

I think it is because it's so difficult to implement fsync efficiently,
some systems just stop trying.

>
> > In modern file system we do all kind of stuff to ensure ordering, and I
> > think I can see how leveraging ordered commands (when it is available
> from
> > hardware) could potentially boost performance.
>
> Similarly, on many hard disk subsystems (the circuit board and firmware
> provided with the hard disk), the 'wait until cache has been written'
> operation does nothing.  So even if you /could/ depend on fsync you still
> couldn't depend on the hardware.  Read the manufactuer's documentation:
> they don't hide it, they boast about it because it makes the hard drive far
> faster.  If you really want this feature to work you have to buy expensive
> server-quality hard drives and set the jumpers in the right positions.
>

When you cannot trust hardware, there are still somethings you can do to
ensure durability and consistency. There are some work from UW-Madsion,
talks about how do you do that without trusting the hardware, say, using
coerced cache eviction. Of course, this is expensive, thus we want to
decouple ordering and durability even more.

Suli

>
> Simon.
> ___
> 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] light weight write barriers

[杨苏立 Yang Su Li]

On Thu, Nov 15, 2012 at 6:07 AM, David Lang  wrote:

> On Wed, 14 Nov 2012, Vladislav Bolkhovitin wrote:
>
>  Nico Williams, on 11/13/2012 02:13 PM wrote:
>>
>>> declaring groups of internally-unordered writes where the groups are
>>> ordered with respect to each other... is practically the same as
>>> barriers.
>>>
>>
>> Which barriers? Barriers meaning cache flush or barriers meaning commands
>> order, or barriers meaning both?
>>
>> There's no such thing as "barrier". It is fully artificial abstraction.
>> After all, at the bottom of your stack, you will have to translate it
>> either to cache flush, or commands order enforcement, or both.
>>
>
> When people talk about barriers, they are talking about order enforcement.
>
>
>  Your mistake is that you are considering barriers as something real,
>> which can do something real for you, while it is just a artificial
>> abstraction apparently invented by people with limited knowledge how
>> storage works, hence having very foggy vision how barriers supposed to be
>> processed by it. A simple wrong answer.
>>
>> Generally, you can invent any abstraction convenient for you, but farther
>> your abstractions from reality of your hardware => less you will get from
>> it with bigger effort.
>>
>> There are no barriers in Linux and not going to be. Accept it. And start
>> instead thinking about offload capabilities your storage can offer to you.
>>
>
> the hardware capabilities are not directly accessable from userspace (and
> they probably shouldn't be)
>
> barriers keep getting mentioned because they are a easy concept to
> understand. "do this set of stuff before doing any of this other set of
> stuff, but I don't care when any of this gets done" and they fit well with
> the requirements of the users.
>

Well, I think there are two questions to be answered here: what primitive
should be offered to the user by the file system (currently we have fsync);
and what primitive should be offered by the lower level and used by the
file system (currently we have barrier, or flushing and FUA).

I do agree that we should keep what is accessible from user-space simple
and stupid. However if you look into fsync semantics a bit closer, I think
there are two things to be noted:

1. fsync actually does two things at the same time: ordering writes (in a
barrier-like manner), and forcing cached writes to disk. This makes it very
difficult to implement fsync efficiently. However, logically they are two
distinctive functionalities, and user might want one but not the other.
Particularly, users might want a barrier, but doesn't care about durability
(much). I have no idea why ordering and durability, which seem quite
different, ended up being bundled together in a single fsync call.

2. fsync semantic in POSIX is a bit vague (at least to me) in a concurrent
setting. What is the expected behavior when more than one thread write to
the same file descriptor, or different file descriptor associated with the
same file?

So I do think in the user space, we need some kind of barrier (or other)
primitive which is not tied to durability guarantees; and hopefully this
primitive could be implemented more efficiently than fsync. And of course,
this primitive should be simple and intuitive, abstracting the complexity
out.


On the other hand, we have the questions of what should file system use.
Traditionally block layer provides barrier primitive, and now I think they
are moving to flushing and FUA, or even ordered commands. (
http://lwn.net/Articles/400541/).

In terms of whether file system should be exposed with the hardware
capability, in this case, ordered commands. I personally think it should.
In modern file system we do all kind of stuff to ensure ordering, and I
think I can see how leveraging ordered commands (when it is available from
hardware) could potentially boost performance. And all the complexity of,
say, topological order, is dealt within the file system, and is not visible
to the user.

Of course, there are challenges in when you want to do ordered writes in
file system. As Ts'o mentioned, *when you have entagled metadata updates,
i.e., *you update file A, and file B, and file A and B might share
metadata, it could be difficult to get the ordering right without
sacrificing performance. But I personally think it is worth exploring.

Suli


>
> Users readily accept that if the system crashes, they will loose the most
> recent stuff that they did, but they get annoyed when things get corrupted
> to the point that they loose the entire file.
>
> this includes things like modifying one option and a crash resulting in
> the config file being blank. Yes, you can do the 'write to temp file, sync
> file, sync directory, rename file" dance, but the fact that to do so the
> user must sit and wait for the syncs to take place can be a problem. It
> would be far better to be able to say "write to temp file, and after it's
> on disk, rename the file" and not have the user 

Re: [sqlite] light weight write barriers

[杨苏立 Yang Su Li]

On Fri, Oct 26, 2012 at 8:54 PM, Vladislav Bolkhovitin  wrote:

>
> Theodore Ts'o, on 10/25/2012 01:14 AM wrote:
>
>> On Tue, Oct 23, 2012 at 03:53:11PM -0400, Vladislav Bolkhovitin wrote:
>>
>>> Yes, SCSI has full support for ordered/simple commands designed
>>> exactly for that task: to have steady flow of commands even in case
>>> when some of them are ordered.
>>>
>>
>> SCSI does, yes --- *if* the device actually implements Tagged Command
>> Queuing (TCQ).  Not all devices do.
>>
>> More importantly, SATA drives do *not* have this capability, and when
>> you compare the price of SATA drives to uber-expensive "enterprise
>> drives", it's not surprising that most people don't actually use
>> SCSI/SAS drives that have implemented TCQ.
>>
>
> What different in our positions is that you are considering storage as
> something you can connect to your desktop, while in my view storage is
> something, which stores data and serves them the best possible way with the
> best performance.
>
> Hence, for you the least common denominator of all storage features is the
> most important, while for me to get the best of what possible from storage
> is the most important.
>
> In my view storage should offload from the host system as much as
> possible: data movements, ordered operations requirements, atomic
> operations, deduplication, snapshots, reliability measures (eg RAIDs), load
> balancing, etc.
>
> It's the same as with 2D/3D video acceleration hardware. If you want the
> best performance from your system, you should offload from it as much as
> possible. In case of video - to the video hardware, in case of storage - to
> the storage. The same as with video, for storage better offload - better
> performance. On hundreds of thousands IOPS it's clearly visible.
>
> Price doesn't matter here, because it's completely different topic.
>
>
>  SATA's Native Command
>> Queuing (NCQ) is not equivalent; this allows the drive to reorder
>> requests (in particular read requests) so they can be serviced more
>> efficiently, but it does *not* allow the OS to specify a partial,
>> relative ordering of requests.
>>
>
> And so? If SATA can't do it, does it mean that nobody else can't do it
> too? I know a plenty of non-SATA devices, which can do the ordering
> requirements you need.
>

I would be very much interested in what kind of device support this kind of
"topological order", and in what settings they are typically used.

Does modern flash/SSD (esp. which are used on smartphones) support this?

If you could point me to some information about this, that would be very
much appreciated.

Thanks a lot!

Suli

>
> Vlad
>
> --
> To unsubscribe from this list: send the line "unsubscribe linux-fsdevel" in
> the body of a message to majord...@vger.kernel.org
> More majordomo info at  
> http://vger.kernel.org/**majordomo-info.html
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[杨苏立 Yang Su Li]

I am not quite whether I should ask this question here, but in terms
of light weight barrier/fsync, could anyone tell me why the device
driver / OS provide the barrier interface other than some other
abstractions anyway? I am sorry if this sounds like a stupid questions
or it has been discussed before

I mean, most of the time, we only need some ordering in writes; not
complete order, but partial,very simple topological order. And a
barrier seems to be a heavy weighted solution to achieve this anyway:
you have to finish all writes before the barrier, then start all
writes issued after the barrier. That is some ordering which is much
stronger than what we need, isn't it?

As most of the time the order we need do not involve too many blocks
(certainly a lot less than all the cached blocks in the system or in
the disk's cache), that topological order isn't likely to be very
complicated, and I image it could be implemented efficiently in a
modern device, which already has complicated caching/garbage
collection/whatever going on internally. Particularly, it seems not
too hard to be implemented on top of SCSI's ordered/simple task mode?
(I believe Windows does this to an extent, but not quite sure).

Thanks a lot

Suli


On Wed, Oct 10, 2012 at 12:17 PM, Andi Kleen  wrote:
> Richard Hipp writes:
>>
>> We would really, really love to have some kind of write-barrier that is
>> lighter than fsync().  If there is some method other than fsync() for
>> forcing a write-barrier on Linux that we don't know about, please enlighten
>> us.
>
> Could you list the requirements of such a light weight barrier?
> i.e. what would it need to do minimally, what's different from
> fsync/fdatasync ?
>
> -Andi
>
> --
> a...@linux.intel.com -- Speaking for myself only
> ___
> 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