On Wednesday, 17 May, 2017 23:18, Gabriele Lanaro <gabriele.lan...@gmail.com> wrote:
> Hi, I'm trying to assess if the performance of my application is dependent > on disk access from sqlite. Of course it is. Depending on what your application is doing. > To rule this out I wanted to make sure that the SQLite DB is completely > accessed from memory and there are no disk accesses. You still haven't stated why you think disk access may be the bottleneck and what sort of operations that you are doing that you think are problematic. > Is it possible to obtain this effect by using pragmas such as cache_size? Yes and No. It depends what kind of operations you are doing. For example, assuming you have enough RAM then once pre-charged even a modern crappy filesystem cache will short-circuit I/O reads so that they do not hit the disk. Nothing can be done for writes though (except that the old timer adage that the fastest way to do I/O is not to do it, applies in spades). > Another solution is to copy the existing db to a :memory: db but I'd like > to achieve the same effect without doing so (because it will require > substantial modification of the application). For the sake of argument, > let's image that using :memory: db is not an option. Ok. > Also using a ramdisk is not an option because I don't have root access to > the machine. That would imply that the machine has a concept of "root", which means that it is not Windows, MVS, or another operating system that does not call that level of access "root" access. Perhaps you can provide some actually useful information such as: the OS you are using, what type of operations you think are impacted (made slow) by I/O, whether or not the I/O channel "gets full" (ie, is the bottleneck), whether the CPU is fully consumed. Etc. And yes, Disk I/O makes a huge difference. I have things that run on a "real computer (my laptop)" with an SSD hard drive that does I/O at 2.5 GB/s (yes, those are big-B Bytes and big G american billions) and has a significant sized "block" cache (as opposed to the modern preponderance of mostly useless filesystem caches) which can run a workload in 1/250th of the time taken compared to running the exact same workload on a typical "server class" machine configured with a high-speed hardware raid attached spinning rust. The "server class" hardware has significantly better CPU, but the bottleneck (in this particular case) is apparently I/O. Have you identified the "parts you think are slow" and instrumented them to see what is happening? Although the SQLite query optimizer is very good, it does sometimes make incorrect or unnecessary optimizations that can severely impact performance. Not to mention that there are a lot of other factors in play such as OS, filesystem, CPU, RAM, Virtualization, etc. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
