tanelpoder

I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. If the optimizer knows that some column never has NULLs or is guaranteed to be unique, all kinds of transformation & shortcuts become possible.

(There are plenty of large big-vendor ERP/CRM/etc apps out there that do not use DB constraints for the sake of "portability"... not fun to work with these).

And few people tend to remove hints after DB version upgrades, where the optimizer magic (or your table stats) have improved. Now you're limiting optimizer's choices. That's been a problem (by now) for decades in the Oracle world with lots of legacy SQL code full of random hints where some of them aren't even valid anymore, but others still are - and limit optimizer's choices.

At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right.

I do regularly use hints in SQL tuning and troubleshooting experiments, just to verify and prove that a better plan is theoretically and physically possible - and if yes, then go from there. When the (deliberately placed) hints make the query faster, the next step is to check the hinted, faster plan's optimizer cost estimate and drill down from there: Why did optimizer think that the other plan was cheaper or why did the optimizer think that the faster plan was more expensive. Then you end up with measuring row-count misestimates, etc...

But yes, plenty of people (including myself) have made legacy Oracle apps run much more efficiently and faster just by globally telling the DB to stop paying attention to all the old random hints lingering on and gathering object stats using the modern settings & defaults (doesn't always work though).

[1] https://docs.oracle.com/en/database/oracle/oracle-database/1...

I’d also recommend the detailed pytorch optimization case studies by Paul Bridger:

https://paulbridger.com/

Transient & disposable memory structures like keeping track who's logged in or compiled SQL execution plans that facilitate access to the persistent "business data", much of that stuff will need to be in RAM/HBM/CPU cache anyway, for performance reasons and as these things do not necessarily need to persist across a crash/reboot. The data (and likely indexes, etc) need to. But you won't need a buffer cache manager that copies entire blocks around from storage to different places in memory and vice versa. Your giant index or graph could rely just on direct memory pointers instead of physical disk block addresses that need to get read to somewhere in memory and then are accessed via various hashtable lookups & indirect pointers. And you don't have to ship entire 512B-8kB blocks around just to access the next index/graph pointer, just access only the relevant cache line, etc.

With proper design, you'd still have layers of code that take care of coherency, consistency and recovery...

And in the later Xeon architecture (Xeon Scalable 3rd gen, I think), intel expanded the persistence domain to CPU caches too. So, you didn't even have to bother with CLFLUSH and CLWB instructions to manually ensure that some cache lines (not 512B blocks, but 64B cache lines) get persisted. You could operate in the CPU cache and in the event of power loss, the CPU/mem controllers/and the capacitors on Optane DCPMMs ensured that the dirty cache lines got persisted to Optane before the CPU lights went off. But all this coolness a bit too late...

Another note: Intel's marketing had terrible naming for Optane stuff. Optane DCPMMs are the ones that go into DIMM slots and have all the cool features. Optane Memory SSDs (like Optane H10) are just NAND SSDs with some Optane cache in front of them. These are flash disks, installed in PCIe slots but Intel decided to call these disks "Optane Memory" ...

Intel discontinued/deprecated Optane, before I could do anything really cool with it. But Intel can probably still reuse lots of their cache coherency logic for external CXL.mem device access.

One serious early adaptor and enterprise user of Optane tech was Oracle. More specifically Oracle's Exadata clusters (where database compute nodes are disaggregated from storage nodes that contained Optane) and connected via InfiniBand or RoCE. And since Optane is memory (memory addressable), they could skip OS involvement and some of the interrupt handling when doing RDMA ops directly to/from Optane memory located inside different nodes of the Exadata cluster. I think they could do 19 microsecond 8kB block reads from storage cells and WAL sync/commit times were also measurable in microseconds (if you didn't hit other bottlenecks). They could send concurrent RDMA write ops (to remote Optane memory) for WAL writes into multiple different storage nodes, so you could get very short commit times (of course when you need sync commits for disaster recovery, you'd have to pay higher latency).

With my Optane kit I tested out Oracle's Optane use in a single-node DB mode for local commit latency (measured in a handful of microseconds, where the actual log file write "I/O" writes were sometimes even sub-microsecond). But again, if you need sync commits across buildings/regions for disaster recovery and have to pay 1+ ms latency anyway, then the local commit latency advantage somewhat diminishes. I have written a couple of articles about this, if anyone cares: [1][2].

Fast forward a few years, Oracle is not selling Exadata clusters with PMEM anymore, but with XMEM, which is just RAM in remote cluster nodes and by using syscall-less RDMA ops, you can access it with even lower latency.

[1] - https://tanelpoder.com/posts/testing-oracles-use-of-optane-p... [2] - https://tanelpoder.com/posts/testing-oracles-use-of-optane-p...

- https://access.redhat.com/solutions/197643

Also, there's a PDF doc (from a conference presentation), covering many layers of potential alternatives for ZFS features:

- https://news.ycombinator.com/item?id=38484598

But "just" slapping all these layers on each other (configuring each individually and then hoping for the best) doesn't seem like the best idea for production systems either.

I guess back in the Sun/Solaris days, people still had the luxury of releasing (major) features in major versions, every few years instead of weeks or months... and as far as storage, backup, security solutions go - can't really do this right with the move fast & break things culture...

(or do we need eBay-like "seller ratings" and customer reviews for ransomware groups?)

One problem that DTrace has is that the "pid" provider that you use for userspace app tracing only works on processes that are already running. So, if more processes with the executable of interest launch after you've started DTrace, its pid provider won't catch the new ones. Then you end up doing some tricks like tracking exec-s of the binary and restarting your DTrace script...

Initially I wrote a python program for flexible querying & summarizing of what the threads of interest are doing (psn) and then wrote a C version to capture & save a sampled history of thread activity (xcapture) [1]. I ended up spending too much time optimizing the C code - as just formatting strings taken from /proc pseudofiles and printing them out took very little time compared to the kernel-dives when extracting things like WCHAN and kernel stack via the proc intereface.

That's why I've since built an eBPF prototype for sampling the OS thread activity. The old approach still works even on RHEL5 machines with 2.6.x kernels without root access too :-)

[1] https://0x.tools/#usage--example-output

https://discourse.ubuntu.com/t/sshd-now-uses-socket-based-ac...

https://oxtp.com

https://en.wikipedia.org/wiki/Soviet_deportations_from_Eston...

But yeah, USSR didn't manage to break our culture (and our own language is a noteworthy part of that). Although there was corruption in various government institutions after USSR collapse, systemic open corruption wasn't part of our culture.

Most (many?) people are ok to work for their own success instead of cheating others as the first choice, that helps the startup culture. And Skype is the most notheworthy company affecting Estonian startup culture. Although not founded/funded by Estonians, the tech & business was built in Estonia from scratch, by mostly Estonians. And now there are multiple generations of ex-Skypers who've successfully built startups & businesses more than once.

Culture & neighbors - that's what I usually tell other people, when they ask about our quick recovery after the USSR occupation.

Not in real life concurrent systems where latency matters. In addition to the queuing/random request arrival rate reasons, all kinds of funky latency hiccups start happening both at the DB and OS level when you run your CPU average utilization near 100%. Spinlocks, priority inversion, etc. Some bugs show up that don’t manifest when running with lower CPU utilization etc.

Mostly about systematic troubleshooting of Linux and SQL tuning (mostly Oracle stuff, but the concepts are universal)

https://mohit.io/blog/fs_usage-trace-file-system-calls-on-ma...