Filesystem, handling files/records

[poteto]

On @helios the second quote, I am not wrong that this is how the CPU handles caches. But I am wrong about seeking the disk for the new value, it only checks the l3 cache which is still kinda fast (which is why it isn't important enough to matter, this was mostly an analogy).

Yea the rest of the top half of my post were pretty stupid, I forgot to do some research, and it is common knowledge that sql servers automatically use 100% of your ram and uses it for caching, so you don't need to worry about it, so the answer is it uses both ram and volume memory. I was overthinking it.

[Ganado]

I think helios is saying that the CPU doesn't have enough information to do this, you need to go to a higher level (OS/filesystem/process) to do locking/setting complicated objects as stale.

potato wrote:

it is common knowledge that sql servers automatically use 100% of your ram

Huh? Source? If that were the case, boy would my software be throwing a bunch of allocation errors.

volang wrote:

Do you think their db is prepared in RAM or do they search on disc every time?

It's not black and white. There are tiers to loading data efficiently. I don't know much about the innards of database management, but a normal rule of thumb is that the stuff accessed more often is put in a place where it can be accessed faster. For example, in DNS lookup, you don't need to get information from the root DNS server every time. A server more directly connected to you stores frequently used information its cache.

[helios]

But I am wrong about seeking the disk for the new value, it only checks the l3 cache which is still kinda fast

I think you're confusing the CPU cache, which:
* Caches the contents of RAM and nothing else.
* Is very tiny compared to RAM, typically a few megabytes at most.
* Has a fixed size.
* Exists physically either on the CPU itself or on the motherboard but close to the CPU and separately from RAM.
* Its contents are maintained by the CPU's logic.

with the disk cache, which:
* Caches the contents of all storage devices (hard disks, floppies, SSDs, etc.) that have been configured to be cached.
* Is tiny compared to the total sum storage in the system. Usually a few gigabytes at most (depending on available memory and competing allocation pressures).
* Has a variable (although bounded) size.
* Doesn't exist physically, or even necessarily. It's a software feature.
* Its contents are maintained by the OS logic.

> it is common knowledge that sql servers automatically use 100% of your ram
Huh? Source? If that were the case, boy would my software be throwing a bunch of allocation errors.

No, that's actually true, somewhat. A typical DBMS such as MySQL or PostgreSQL will try to cache as much of the DB as possible in a breadth-first sort of fashion, starting with the indexes (which are B-trees) and finally with the data, until the DB behaves as if it lives in RAM, or all RAM has been allocated. This is a similar allocation philosophy as used by the disk cache. "Free RAM is wasted RAM".
I don't know what happens if the DBMS has to shared the system with a RAM-intensive application, whether they both start to thrash or whether the DBMS is able to detect this condition and throw bits of the cache away, again, similar to how the system's disk cache does things.