[sqlite] IS a SQLite db of small size as good as reliable cache?
On 2016/02/04 3:30 PM, Keith Medcalf wrote: > You conclusion about the Windows design goals are correct. > > Hardware Destroyer (power saving) was invented for the same reason (to > maximize the rate of hardware failure through imposition of unnecessary > thermal and mechanical stresses on all system components, causing early > failure and increasing profits due to replacement by consumers). > > Microsoft optimizes for "maximum busyness" of the CPU and I/O channels. and > "minimum busyness" of RAM and other cache's. > > This is, of course, 100% opposite of the correct way to design a computer > system. However, it is Microsoft and no one (except a fanbois) ever claimed > they possessed any common (or other) sense whatsoever. Keith, it's very unlike me to get into opinion-based parlance but I feel I have to say that while I agree with you mostly, this continuous MS hate-speech is getting somewhat repetitive and, dare I say, rather boring (in stark contrast to the other things you write). I mean any time an arguer has to resort to calling opposing views "fanboyism", perhaps a blind-spot has appeared? I'm not even disagreeing with you, excepting to say that the value that Linux has added to my life is immense, but so is the value MS and Apple gui-jungles added to the lives of my Mother and the artists at our offices for whom a CLI represents an immeasurable abyss. Sure they do stuff wrong and sometimes downright laughable under the auspices of "improving the user-experience", but their explicit goal has never been to destroy hardware. (One does of course see some of what they do mess with hardware [or efficient computing in general], but that is a long way off suggesting intent/malevolence). I'm not suggesting you stop either (since it isn't my place nor my goal), but may I simply intimate that, were you to dial it down a tad from "Evangelist" to simply "Educator", it would render all your posts brilliant and unboring to me.
[sqlite] IS a SQLite db of small size as good as reliable cache?
I don't really care about all the MS hate, but will add mine[1]. I am not a participant of that question, but it shows the same problem. I had over 50% CPU usage for hours when the computer was "idle" because Windows was indexing my stuff, forever. A friend of mine had the same problem and after contacting MS the "solution" was to disable it. There were no patches, no bugs being tracked, nothing. Just "disable the feature and it will be fixed". After all, it is simpler and easier to disable Windows as a whole and replace it by something that shuts down in less than 5 seconds and does not make its files unmodifiable when turned off by default. [1] https://goo.gl/05EllC
[sqlite] IS a SQLite db of small size as good as reliable cache?
On Wed, Feb 3, 2016 at 3:53 PM, Howard Chu wrote: > No. Windows will toss clean pages out even in the total absence of memory > pressure. It is moronic, but true. You can repeat the steps I outlined for > yourself and see. > > https://groups.google.com/d/msg/comp.os.ms-windows.nt.misc/449tdNYPoX0/it4cx8Gvv2AJ I did repeat something similar. I tried running md5sum on a source tree containing many files. I'm not using this source tree for anything else right now, nor have I in many days. The first time I ran the script, it took 2 minutes. The second time I ran it, 30 minutes later, the same script took 30 seconds, implying some amount of data is still in the filesystem cache. I've been using this system for other things in those 30 minutes, including compiling code in another source tree. Indeed, if I run RAMMap ( https://technet.microsoft.com/en-us/sysinternals/ff700229.aspx ), I can see many of the source files from my md5sum test still in the cache. That same tool claims the majority of my non-process memory is being used as standby for mapped files, aka, being used by the file system cache. No doubt Windows isn't perfect here, but I do wonder if something else wasn't at play with the GCC test you did.
[sqlite] IS a SQLite db of small size as good as reliable cache?
You conclusion about the Windows design goals are correct. Hardware Destroyer (power saving) was invented for the same reason (to maximize the rate of hardware failure through imposition of unnecessary thermal and mechanical stresses on all system components, causing early failure and increasing profits due to replacement by consumers). Microsoft optimizes for "maximum busyness" of the CPU and I/O channels. and "minimum busyness" of RAM and other cache's. This is, of course, 100% opposite of the correct way to design a computer system. However, it is Microsoft and no one (except a fanbois) ever claimed they possessed any common (or other) sense whatsoever. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Howard Chu > Sent: Wednesday, 3 February, 2016 16:53 > To: SQLite mailing list > Subject: Re: [sqlite] IS a SQLite db of small size as good as reliable > cache? > > GB wrote: > > So where have you got this wisdom from? It's just plain Bullshit! > > > > Just as most cache managers do, Windows cache manager uses some sort of > LRU > > caching scheme. So all data once read from file is kept in memory until > either > > some memory pressure occurs or it is simply pushed out by newer data. > > No. Windows will toss clean pages out even in the total absence of memory > pressure. It is moronic, but true. You can repeat the steps I outlined for > yourself and see. > > https://groups.google.com/d/msg/comp.os.ms- > windows.nt.misc/449tdNYPoX0/it4cx8Gvv2AJ > > > Actually, from what I've experienced, I'd recommend to size sqlite's > memory to > > fit for your largest transactions and leave most of the caching to the > OS. > > > > And yes, unlike in many *NIX implementations, Windows' > FlushFileBuffers() call > > DOES issue according write commands to the hardware, so setting PRAGMA > > synchronous ON/OFF makes a big difference in write performance on > Windows > > systems. > > > > Howard Chu schrieb am 30.01.2016 um 23:23: > >> dpb wrote: > >>> Dear Community, > >>> > >>> Assuming that SQLite would arrange the database table contents on page > >>> boundary and > >>> > >>> 1) if db is small let us say maximum 5MB, > >>> > >>> 2 further if such a db is part of windows service / application > running > >>> continuously then may be pages will remain in the memory under normal > >>> conditions. > >>> > >>> Q1) Operating System like Windows would cache the series of pages, is > this > >>> behavior not almost same as that of Cache? > >> The Windows cache manager is quite aggressive at evicting cached pages > from > >> RAM. It used to be tunable back in Win2000, but none of those tuning > knobs > >> survived past WinXP. Generally, if you access some data, leave it for > more > >> than 5-10 seconds, don't expect to be able to reference it again > without > >> incurring a hard page fault. > >> > >> Note that the cache eviction runs quite frequently - once every 5 > seconds or > >> so, and evicts pages regardless of whether there's any memory pressure > in the > >> system. It's quite possibly the stupidest cache manager ever written. > > -- >-- Howard Chu >CTO, Symas Corp. http://www.symas.com >Director, Highland Sun http://highlandsun.com/hyc/ >Chief Architect, OpenLDAP http://www.openldap.org/project/ > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IS a SQLite db of small size as good as reliable cache?
GB wrote: > So where have you got this wisdom from? It's just plain Bullshit! > > Just as most cache managers do, Windows cache manager uses some sort of LRU > caching scheme. So all data once read from file is kept in memory until either > some memory pressure occurs or it is simply pushed out by newer data. No. Windows will toss clean pages out even in the total absence of memory pressure. It is moronic, but true. You can repeat the steps I outlined for yourself and see. https://groups.google.com/d/msg/comp.os.ms-windows.nt.misc/449tdNYPoX0/it4cx8Gvv2AJ > Actually, from what I've experienced, I'd recommend to size sqlite's memory to > fit for your largest transactions and leave most of the caching to the OS. > > And yes, unlike in many *NIX implementations, Windows' FlushFileBuffers() call > DOES issue according write commands to the hardware, so setting PRAGMA > synchronous ON/OFF makes a big difference in write performance on Windows > systems. > > Howard Chu schrieb am 30.01.2016 um 23:23: >> dpb wrote: >>> Dear Community, >>> >>> Assuming that SQLite would arrange the database table contents on page >>> boundary and >>> >>> 1) if db is small let us say maximum 5MB, >>> >>> 2 further if such a db is part of windows service / application running >>> continuously then may be pages will remain in the memory under normal >>> conditions. >>> >>> Q1) Operating System like Windows would cache the series of pages, is this >>> behavior not almost same as that of Cache? >> The Windows cache manager is quite aggressive at evicting cached pages from >> RAM. It used to be tunable back in Win2000, but none of those tuning knobs >> survived past WinXP. Generally, if you access some data, leave it for more >> than 5-10 seconds, don't expect to be able to reference it again without >> incurring a hard page fault. >> >> Note that the cache eviction runs quite frequently - once every 5 seconds or >> so, and evicts pages regardless of whether there's any memory pressure in the >> system. It's quite possibly the stupidest cache manager ever written. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] IS a SQLite db of small size as good as reliable cache?
On Wed, 3 Feb 2016 23:25:33 +0100 GB wrote: > So where have you got this wisdom from? It's just plain Bullshit! > > Just as most cache managers do, Windows cache manager uses some sort of > LRU caching scheme. So all data once read from file is kept in memory > until either some memory pressure occurs or it is simply pushed out by > newer data. Actually, from what I've experienced, I'd recommend to size > sqlite's memory to fit for your largest transactions and leave most of > the caching to the OS. > > And yes, unlike in many *NIX implementations, Windows' > FlushFileBuffers() call DOES issue according write commands to the > hardware, so setting PRAGMA synchronous ON/OFF makes a big difference in > write performance on Windows systems. > I agree there are too much incorrect statements about Windows's behaviour, however, on a Linux system, I could check (just for testing it) `PRAGMA synchronous ON/OFF` makes a difference with at least small transactions, while it's less perceptible with big transactions (small and big is vague, that's just to keep the wording simple). I guess on Windows too, when the transactions are big, using synchronous mode or not, will make less difference than with small transactions, understandably. -- Yannick Duch?ne
[sqlite] IS a SQLite db of small size as good as reliable cache?
So where have you got this wisdom from? It's just plain Bullshit! Just as most cache managers do, Windows cache manager uses some sort of LRU caching scheme. So all data once read from file is kept in memory until either some memory pressure occurs or it is simply pushed out by newer data. Actually, from what I've experienced, I'd recommend to size sqlite's memory to fit for your largest transactions and leave most of the caching to the OS. And yes, unlike in many *NIX implementations, Windows' FlushFileBuffers() call DOES issue according write commands to the hardware, so setting PRAGMA synchronous ON/OFF makes a big difference in write performance on Windows systems. Howard Chu schrieb am 30.01.2016 um 23:23: > dpb wrote: >> Dear Community, >> >> Assuming that SQLite would arrange the database table contents on page >> boundary and >> >> 1) if db is small let us say maximum 5MB, >> >> 2 further if such a db is part of windows service / application running >> continuously then may be pages will remain in the memory under normal >> conditions. >> >> Q1) Operating System like Windows would cache the series of pages, is this >> behavior not almost same as that of Cache? > The Windows cache manager is quite aggressive at evicting cached pages from > RAM. It used to be tunable back in Win2000, but none of those tuning knobs > survived past WinXP. Generally, if you access some data, leave it for more > than 5-10 seconds, don't expect to be able to reference it again without > incurring a hard page fault. > > Note that the cache eviction runs quite frequently - once every 5 seconds or > so, and evicts pages regardless of whether there's any memory pressure in the > system. It's quite possibly the stupidest cache manager ever written. >
[sqlite] IS a SQLite db of small size as good as reliable cache?
On Mon, 01 Feb 2016 06:39:05 -0700 "Keith Medcalf" wrote: > OS/2 had IBM cache technology in it which worked properly. I remember OS/2. I remember that, like VMS, you could back up the whole OS to ... well, floppies, I suppose, and later restore them to brand new drive, with nothing else required. I couldn't believe my eyes when the equivalent "system restore" process on NT started with 1. Install the OS. Twenty-five years on, that's still SOP on NT, except that nowadays no one expects to restore a backup anyway! But IIRC, NTFS was a Microsoft invention, not connected to OS/2, and borrowed quite heaviily from VMS, because David Cutler. So I'm inclined toward your low-wattage theory, because hardly anything Cutler originally provided remains unsmudged. --jkl
[sqlite] IS a SQLite db of small size as good as reliable cache?
For cache eviction, below are you referring to writing dirty pages to disk? If you are talking about cached reads, the evidence seems to point to many such pages remaining in some kind of cache. I perform a recursive grep (Cygwin) on a large directory tree. It takes 4.5m. I wait five minutes and do it again, it takes 0.6m. It is possible that I am measuring something else (caching at the disk-driver level, or lower), but for the OP's question, that may be good enough. Win7, 1tb Seagate scsi drive, 5500 rpm. System has 32gb of ram (c: drive is a 256gb ssd). Regards, Bill -Original Message- From: Howard Chu [mailto:h...@symas.com] Sent: Saturday, January 30, 2016 4:23 PM To: SQLite mailing list Subject: Re: [sqlite] IS a SQLite db of small size as good as reliable cache? The Windows cache manager is quite aggressive at evicting cached pages from RAM. It used to be tunable back in Win2000, but none of those tuning knobs survived past WinXP. Generally, if you access some data, leave it for more than 5-10 seconds, don't expect to be able to reference it again without incurring a hard page fault. Note that the cache eviction runs quite frequently - once every 5 seconds or so, and evicts pages regardless of whether there's any memory pressure in the system. It's quite possibly the stupidest cache manager ever written. ** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **
[sqlite] IS a SQLite db of small size as good as reliable cache?
> > Any insight into what they were thinking? Back when I used Windows > > daily, it used to annoy me that every morning the machine had to warm > > up again, to revive the state I'd left it in the night before. In > > NetBSD I learned that unused memory is unused, so why not use it? The file caching algorithms in DOS and OS/2 were patented technology of IBM. Microsoft was not permitted to use them (that is why Microsoft *never* had any sort of disk cache that ever worked, even in DOS), plus Microsoft is a Low Wattage company. OS/2 had IBM cache technology in it which worked properly. However, when Microsoft broke the Joint Application Development Agreement with OS/2 New Technology 2.0 (which they renamed to just "NT") Microsoft had to remove all of IBMs code and write their own. As I said previously, Microsoft is pretty low wattage when it comes to understanding how and why things work and how to make computers work efficiently. After all, they do stupid shit like optimize for maximum CPU usage, and then put that in their documentation as a crowning achievement.
[sqlite] IS a SQLite db of small size as good as reliable cache?
James K. Lowden wrote: > On Sat, 30 Jan 2016 22:23:24 + > Howard Chu wrote: > >> Note that the cache eviction runs quite frequently - once every 5 >> seconds or so, and evicts pages regardless of whether there's any >> memory pressure in the system. It's quite possibly the stupidest >> cache manager ever written. > > Any insight into what they were thinking? Back when I used Windows > daily, it used to annoy me that every morning the machine had to warm > up again, to revive the state I'd left it in the night before. In > NetBSD I learned that unused memory is unused, so why not use it? As I understand it, the main rationale is reliability - they don't trust their OS to stay up longer than 3 seconds after any particular write operation. There's a lot more information here https://groups.google.com/forum/#!topic/comp.os.ms-windows.nt.misc/449tdNYPoX0 That's from 2005 and unfortunately the Windows kernel variables for cache tuning no longer exist. A lot of the relevant info no longer exists on the original websites either, but I was able to pull one up from the web archive: https://web.archive.org/web/20010825042328/http://www.sysinternals.com/ntw2k/source/cacheman.shtml > I have a feeling that "fast app launching" is the reason, as though > Windows users were excitedly punching the Start button with a > stopwatch. But maybe there's more to it than that? It may well be a user-oriented philosophy. It is certainly not a developer-oriented approach. It was my frustration with slow build times on Windows that led me to investigate this in the first place. https://bugzilla.mozilla.org/show_bug.cgi?id=294122#c69 A lengthier explanation of how it works is online here https://msdn.microsoft.com/en-us/library/bb742613.aspx but there's pretty much no information there that's actionable - aside from LargeSystemCache there's no tuning knobs left. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] IS a SQLite db of small size as good as reliable cache?
> On 31 Jan 2016, at 11:24pm, James K. Lowden > wrote: > > Howard Chu wrote: > >> Note that the cache eviction runs quite frequently - once every 5 >> seconds or so, and evicts pages regardless of whether there's any >> memory pressure in the system. It's quite possibly the stupidest >> cache manager ever written. > > Any insight into what they were thinking? Only a cynical one. It's really good for demo sessions. When you're giving a demo you log in, immediately start an App, do a few things, spend a few seconds talking, start a second App, talk about that, start another App, etc.. Having memory cleared while you're taking means you have plenty free to start another App. Simon
[sqlite] IS a SQLite db of small size as good as reliable cache?
On Sat, 30 Jan 2016 22:23:24 + Howard Chu wrote: > Note that the cache eviction runs quite frequently - once every 5 > seconds or so, and evicts pages regardless of whether there's any > memory pressure in the system. It's quite possibly the stupidest > cache manager ever written. Any insight into what they were thinking? Back when I used Windows daily, it used to annoy me that every morning the machine had to warm up again, to revive the state I'd left it in the night before. In NetBSD I learned that unused memory is unused, so why not use it? I have a feeling that "fast app launching" is the reason, as though Windows users were excitedly punching the Start button with a stopwatch. But maybe there's more to it than that? --jkl
[sqlite] IS a SQLite db of small size as good as reliable cache?
Dear Community, Assuming that SQLite would arrange the database table contents on page boundary and 1) if db is small let us say maximum 5MB, 2 further if such a db is part of windows service / application running continuously then may be pages will remain in the memory under normal conditions. Q1) Operating System like Windows would cache the series of pages, is this behavior not almost same as that of Cache? Q2) Does it make sense to use In-memory SQLite for small size db upto 5MB average size 2MB? Thank you
[sqlite] IS a SQLite db of small size as good as reliable cache?
At 22:57 30/01/2016, you wrote: >2 further if such a db is part of windows service / application running >continuously then may be pages will remain in the memory under normal >conditions. > >Q1) Operating System like Windows would cache the series of pages, is this >behavior not almost same as that of Cache? > >Q2) Does it make sense to use In-memory SQLite for small size db upto 5MB > average size 2MB? Howard answered the Windows cache part pretty well: don't rely on it. Now for Q2, I'd use the built-in SQLite backup API to load the DB from disk to memory at launch of the service, use it again to save the DB to disk at reasonable intervals (depends on how the service is sensitive to the D of ACID [durability]) and back again to disk at service shutdown. 2 to 5 Mb is nothing now. Then you don't have to setup a connection SQLite cache of max possible size, in-memory DB is all ou need. Obviously if the machine may be shutdown without notice (e.g. power going off unexpectedly) and the context is sensible to durability of transactions, then all bets are off for in-memory DB.
[sqlite] IS a SQLite db of small size as good as reliable cache?
dpb wrote: > Dear Community, > > Assuming that SQLite would arrange the database table contents on page > boundary and > > 1) if db is small let us say maximum 5MB, > > 2 further if such a db is part of windows service / application running > continuously then may be pages will remain in the memory under normal > conditions. > > Q1) Operating System like Windows would cache the series of pages, is this > behavior not almost same as that of Cache? The Windows cache manager is quite aggressive at evicting cached pages from RAM. It used to be tunable back in Win2000, but none of those tuning knobs survived past WinXP. Generally, if you access some data, leave it for more than 5-10 seconds, don't expect to be able to reference it again without incurring a hard page fault. Note that the cache eviction runs quite frequently - once every 5 seconds or so, and evicts pages regardless of whether there's any memory pressure in the system. It's quite possibly the stupidest cache manager ever written. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
