Re: [sqlite] Is there a way to measure query time in sqlite at runtime?
On 16 Nov 2012, at 11:26pm, 杨苏立 Yang Su Li wrote: > 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? While you can use the built-in functions to do this, as Kyan showed you, it is more accurate to do it in your own programming language, since those times will include the overhead for calling the SQLite functions and handling memory. If you are calling the raw C API functions that overhead is low, but if you are using an object-oriented library it can be high. If your language has a 'get time of day as a number' function then you can just use that before and after each SQLite call and subtract one from another. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
> The more money you pay for your storage, the less likely this is to be an > issue (high end SSD's, enterprise class arrays, etc don't have volatile write > caches and most SAS drives perform reasonably well with the write cache > disabled). "Performance" without a write cache is a physical property. It varies according to very simple principles related to arial density, rotational speed, actuator speed (stepping -- momentum, acceleration and deceleration and settling of the read/write heads). "Performance", without a write cache, has absolutely nothing whatsoever to do with the external data transfer method or whether the bus is parallel, serial, or via hyperspace, just as it has nothing to do with whether the moon is made of green of purple cheese. Statements such as "most SAS drives perform reasonably well with the write cache disabled" demonstrate a very deep seated ignorance of "the way things work" that ought to indicate that anything said should be taken as highly likely to be incorrect. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to measure query time in sqlite at runtime?
On Sat, Nov 17, 2012 at 1:26 AM, 杨苏立 Yang Su Li wrote: > I want to measure the execution time of each sql statement in sqlite. Use sqlite3_profile() to register a profiler callback for a database connection that will be called for every SQL statement you execute on that connection. http://www.sqlite.org/c3ref/profile.html HTH. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
Chris Friesen, on 11/15/2012 05:35 PM wrote: The easiest way to implement this fsync would involve three things: 1. Schedule writes for all dirty pages in the fs cache that belong to the affected file, wait for the device to report success, issue a cache flush to the device (or request ordering commands, if available) to make it tell the truth, and wait for the device to report success. AFAIK this already happens, but without taking advantage of any request ordering commands. 2. The requesting thread returns as soon as the kernel has identified all data that will be written back. This is new, but pretty similar to what AIO already does. 3. No write is allowed to enqueue any requests at the device that involve the same file, until all outstanding fsync complete [3]. This is new. This sounds interesting as a way to expose some useful semantics to userspace. I assume we'd need to come up with a new syscall or something since it doesn't match the behaviour of posix fsync(). This is how I would export cache sync and requests ordering abstractions to the user space: For async IO (io_submit() and friends) I would extend struct iocb by flags, which would allow to set the required capabilities, i.e. if this request is FUA, or full cache sync, immediate [1] or not, ORDERED or not, or all at the same time, per each iocb. For the regular read()/write() I would add to "flags" parameter of sync_file_range() one more flag: if this sync is immediate or not. To enforce ordering rules I would add one more command to fcntl(). It would make the latest submitted write in this fd ORDERED. All together those should provide the requested functionality in a simple, effective, unambiguous and backward compatible manner. Vlad 1. See my other today's e-mail about what is immediate cache sync. ___ 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 11/15/2012 11:14 AM 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. Exactly! However, logically they are two distinctive functionalities Exactly! Those two points are exactly why concept of barriers must be forgotten for sake of productivity and be replaced by a finer grained abstractions as well as why they where removed from the Linux kernel Vlad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
David Lang, on 11/15/2012 07:07 AM wrote: 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. Not correct. When people are talking about barriers, they are meaning different things. For instance, Alan Cox few e-mails ago was meaning cache flush. That's the problem with the barriers concept: barriers are ambiguous. There's no barrier which can fit all requirements. the hardware capabilities are not directly accessable from userspace (and they probably shouldn't be) The discussion is not about to directly provide storage hardware capabilities to the user space. The discussion is to replace fully inadequate barriers abstractions to a set of other, adequate abstractions. For instance: 1. Cache flush primitives: 1.1. FUA 1.2. Non-immediate cache flush, i.e. don't return until all data hit non-volatile media 1.3. Immediate cache flush, i.e. return ASAP after the cache sync started, possibly before all data hit non-volatile media. 2. ORDERED attribute for requests. It provides the following behavior rules: A. All requests without this attribute can be executed in parallel and be freely reordered. B. No ORDERED command can be completed before any previous not-ORDERED or ORDERED command completed. Those abstractions can naturally fit all storage capabilities. For instance: - On simple WT cache hardware not supporting ordering commands, (1) translates to NOP and (2) to queue draining. - On full features HW, both (1) and (2) translates to the appropriate storage capabilities. On FTL storage (B) can be further optimized by doing data transfers for ORDERED commands in parallel, but commit them in the requested order. barriers keep getting mentioned because they are a easy concept to understand. Well, concept of flat Earth and Sun rotating around it is also easy to understand. So, why isn't it used? Vlad ___ 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?
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
On 11/16/2012 10:54 AM, Howard Chu wrote: Ric Wheeler wrote: On 11/16/2012 10:06 AM, Howard Chu wrote: David Lang wrote: 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. Users readily accept that if the system crashes, they will loose the most recent stuff that they did, *some* users may accept that. *None* should. 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 wait. The user doesn't really care if the changes hit disk immediately, or several seconds (or even 10s of seconds) later, as long as there is not any possibility of the rename hitting disk before the file contents. The fact that this could be implemented in multiple ways in the existing hardware does not mean that there need to be multiple ways exposed to userspace, it just means that the cost of doing the operation will vary depending on the hardware that you have. This also means that if new hardware introduces a new way of implementing this, that improvement can be passed on to the users without needing application changes. There are a couple industry failures here: 1) the drive manufacturers sell drives that lie, and consumers accept it because they don't know better. We programmers, who know better, have failed to raise a stink and demand that this be fixed. A) Drives should not lose data on power failure. If a drive accepts a write request and says "OK, done" then that data should get written to stable storage, period. Whether it requires capacitors or some other onboard power supply, or whatever, they should just do it. Keep in mind that today, most of the difference between enterprise drives and consumer desktop drives is just a firmware change, that hardware is already identical. Nobody should accept a product that doesn't offer this guarantee. It's inexcusable. B) it should go without saying - drives should reliably report back to the host, when something goes wrong. E.g., if a write request has been accepted, cached, and reported complete, but then during the actual write an ECC failure is detected in the cacheline, the drive needs to tell the host "oh by the way, block XXX didn't actually make it to disk like I told you it did 10ms ago." If the entire software industry were to simply state "your shit stinks and we're not going to take it any more" the hard drive industry would have no choice but to fix it. And in most cases it would be a zero-cost fix for them. Once you have drives that are actually trustworthy, actually reliable (which doesn't mean they never fail, it only means they tell the truth about successes or failures), most of these other issues disappear. Most of the need for barriers disappear. I think that you are arguing a fairly silly point. Seems to me that you're arguing that we should accept inferior technology. Who's really being silly? No, just suggesting that you either pay for the expensive stuff or learn how to use cost effective, high capacity storage like the rest of the world. I don't disagree that having non-volatile write caches would be nice, but everyone has learned how to deal with volatile write caches at the low end of market. If you want that behaviour, you have had it for more than a decade - simply disable the write cache on your drive and you are done. You seem to believe it's nonsensical for someone to want both fast and reliable writes, or that it's unreasonable for a storage device to offer the same, cheaply. And yet it is clearly trivial to provide all of the above. I look forward to seeing your products in the market. Until you have more than "I want" and "I think" on your storage system design resume, I suggest you spend the money to get the parts with non-volatile write caches or fix your code. Ric If you - as a user - want to run faster and use applications that are coded to handle data integrity properly (fsync, fdatasync, etc), leave the write cache enabled and use file system barriers. Applications aren't supposed to need to worry about such details, that's why we have operating systems. Drives should tell the truth. In event of an error detected after the fact, the drive should report the error back to the host. There's nothing nonsensical there. When a drive's cache is enabled, the host should maintain a queue of written pages, of a length equal to the size of the dr
Re: [sqlite] light weight write barriers
On 11/16/2012 10:06 AM, Howard Chu wrote: David Lang wrote: 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. Users readily accept that if the system crashes, they will loose the most recent stuff that they did, *some* users may accept that. *None* should. 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 wait. The user doesn't really care if the changes hit disk immediately, or several seconds (or even 10s of seconds) later, as long as there is not any possibility of the rename hitting disk before the file contents. The fact that this could be implemented in multiple ways in the existing hardware does not mean that there need to be multiple ways exposed to userspace, it just means that the cost of doing the operation will vary depending on the hardware that you have. This also means that if new hardware introduces a new way of implementing this, that improvement can be passed on to the users without needing application changes. There are a couple industry failures here: 1) the drive manufacturers sell drives that lie, and consumers accept it because they don't know better. We programmers, who know better, have failed to raise a stink and demand that this be fixed. A) Drives should not lose data on power failure. If a drive accepts a write request and says "OK, done" then that data should get written to stable storage, period. Whether it requires capacitors or some other onboard power supply, or whatever, they should just do it. Keep in mind that today, most of the difference between enterprise drives and consumer desktop drives is just a firmware change, that hardware is already identical. Nobody should accept a product that doesn't offer this guarantee. It's inexcusable. B) it should go without saying - drives should reliably report back to the host, when something goes wrong. E.g., if a write request has been accepted, cached, and reported complete, but then during the actual write an ECC failure is detected in the cacheline, the drive needs to tell the host "oh by the way, block XXX didn't actually make it to disk like I told you it did 10ms ago." If the entire software industry were to simply state "your shit stinks and we're not going to take it any more" the hard drive industry would have no choice but to fix it. And in most cases it would be a zero-cost fix for them. Once you have drives that are actually trustworthy, actually reliable (which doesn't mean they never fail, it only means they tell the truth about successes or failures), most of these other issues disappear. Most of the need for barriers disappear. I think that you are arguing a fairly silly point. If you want that behaviour, you have had it for more than a decade - simply disable the write cache on your drive and you are done. If you - as a user - want to run faster and use applications that are coded to handle data integrity properly (fsync, fdatasync, etc), leave the write cache enabled and use file system barriers. Everyone has to trade off cost versus something else and this is a very, very long standing trade off that drive manufacturers have made. The more money you pay for your storage, the less likely this is to be an issue (high end SSD's, enterprise class arrays, etc don't have volatile write caches and most SAS drives perform reasonably well with the write cache disabled). Regards, Ric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users