tanelpoder

In the spirit of the Yo app, does anyone want to invest in bonfire.app?

The Threadripper Pro 5000-series is available for retail purchases [1].

When 3000-series was launched, it was first OEM only (Lenovo) but later became available in retail too.

[1] https://www.amazon.com/AMD-ThreadripperTM-PRO-5975WX-64-Thre...

AMD definitely has a recent high end desktop/workstation platform with its ThreadRipper Pro WX CPUs (EPYC server CPUs) that have 128 PCIe 4.0 lanes and 8 memory channels. And enough PCIe slots to actually use this throughput with the right kind of hardware, like a bunnch of PCIe4.0 NVMe SSDs [1].

Yep, the platform is 2 years old, but supports both the Zen2 and current Zen3 CPUs (but not PCIe5.0 & DDR5 as Zen 4 requires a different CPU socket and is only announced right now, not available from AMD until the end this month).

However, 8 x DDR4 channels and 128 x PCIe4.0 lanes still give you better total bandwidth than 2 x DDR5 and 24 x PCIe5.0 like the few currently available DDR5/PCIe5 platforms support.

[1] https://tanelpoder.com/posts/11m-iops-with-10-ssds-on-amd-th...

So, just as I had written a few articles [1] about Optane persistent memory use for databases, Intel seems to be shutting down the whole thing (and moves on to CXL-accessible memory, which is somewhat like remote memory access over PCIe 5.0, memory boards disaggregated from CPUs, etc. The writing was on the wall actually, there were some successful niche use cases (for example Oracle's Exadata database appliance), but no widespread mainstream use. Terrible product naming too, Intel named regular NAND SSDs with a little bit of 3DXpoint/Optane cache in front of them "Optane Memory" - solid state disks that were called memory for some reason.

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

Edit: I'm still keeping my Optane DIMMs (called DCPMMS - data center persistent memory modules), I just love the tech adventure of having persistent RAM. Should try to write an experimental OS or something (but I also know that's not gonna happen :)

I wonder if there's any article keeping track of how much money the billionaires actually donated vs. the promise & pledge competition between them when the topic was still hot?

The macOS Monterey 12.5 update shows this as one of the fixed items:

* "Fixes an issue in Safari where a tab may revert back to a previous page"

Maybe related?

Yeah, for similar reasons, the /proc/PID/wchan now shows just "0" (for other users' processes) on newer kernels, unless you run as root. Same with /proc/PID/stack, but it's implemented in a different way, I can open() that file successfully, but the read() syscall on the opened file descriptor returns EACCESS error...

  $ ls -l /proc/$$/stack
  -r-------- 1 tanel tanel 0 May 26 21:52 /proc/967141/stack
  $ 
  $ cat /proc/$$/stack
  cat: /proc/967141/stack: Permission denied
  $ 
  $ sudo cat /proc/$$/stack
  [<0>] do_wait+0x1c3/0x230
  [<0>] kernel_wait4+0xaf/0x150
  [<0>] __do_sys_wait4+0x85/0x90
  [<0>] __x64_sys_wait4+0x1e/0x20
  [<0>] do_syscall_64+0x49/0xc0
  [<0>] entry_SYSCALL_64_after_hwframe+0x44/0xa9

Edit: Adding one more comment - my impression has been that the "no userland-visible changes" promise applies to system calls - how procfs presents data as human-readable text in the /proc files has changed every now and then before (I recall the sar command showing wrong numbers after a kernel update, for example).

But they still allow using 2FA via SMS too, even if a yubikey is configured. And there’s no way to turn the SMS auth off (last time I checked).

Also, KVM, VMWare (and maybe Xen?) allow PMU access too (but not VirtualBox). Both VMWare ESXi and VMware Server/Workstation/Fusion allow PMU access, you just need to make sure it's enabled in the settings.

A quick way to check if PMU access is enabled is this:

  dmesg | grep "Performance Events"

Edit: Oh, unfortunately VMware Fusion 12 (on Mac OSX Intel) does not expose the performance counters to the VM anymore, as it's using the OSX Hypervisor Framework instead of its own kernel module.

You have a good point that the app should track the difference between I/O submission times and I/O completion times. Can't speak for MySQL, but Oracle nowadays has two "wait events" for the DB Writer flow (that syncs dirty buffers from its buffer cache to disk):

1. db file async I/O submit

2. db file parallel write

(the 2nd one has such a name for historical reasons, but it's the I/O reaping/completion check timing, not the submission).

Yep I do agree that it's a reasonable caveat and your point that async I/O allows the app to move on and do other stuff (in whatever thread state) and come back later.

But I'm talking about the cases where the app is done with the other work and needs to ensure that the previous write is persisted (for example to a write ahead log), before moving on? It will have to deliberately wait for I/O completion now, thus will run io_getevents() with the "willing to wait" mode enabled. I see this in Oracle database world all the time and MySQL world occasionally too:

- Not seeing a high number of threads in D state or PSI IO figure does not mean that there are no threads waiting for I/O

- Seeing a high number of threads in D state and high PSI IO figure means that there are threads waiting for I/O (but possibly even more, if you'd look into the ones in S state, but in io_getevents syscall, with WCHAN=read_events.

The reason why I'm aware of this is that Oracle databases use libaio for some reads and most of the writes on Linux, including transaction log writes. There are plenty of cases where you don't exhaust the block device I/O queues - io_submit() finishes quickly without blocking, but then the I/O issuing process wants to wait for the asynchronously submitted I/O completion, before moving on. It will use io_getevents() with the "timeout > 0" argument, so it will sleep (in S mode), waiting for I/O completion. If it had submitted a synchronous I/O, it would have increased Linux load, but if configured to use libaio, it won't.

So, the I/O component of Linux system load and PSI do not include the async I/O waiting threads that decide to synchronoysly wait for asynchronously submitted I/O requests :-)

I see MySQL is using libaio in some places too (the io_submit/io_getevents show up in syscalls), but I haven't looked deeper into whether the getevents are "willing to wait" or not. But any application using libaio, that at some point uses io_getevents() in "willing to wait" mode, would be affected by this discrepancy.

Yep, one thing that helps in this context is to only look into threads in R or D state (and ignore the hundreds or sometimes thousands of kernel threads that are just sleeping or waiting for more work to do in idle (I) state).

But yeah, even that doesn't clearly tell you what amount of active threads is normal for your workload/configuration. Or you can just save the thread count info (grouped by thread state) and save/graph it over time, to have a better idea of what's normal. The difference of this approach vs just graphing Linux-reported load is that you'll get a breakdown of how much of it was CPU demand vs sync I/O demand. Or you could even break the the thread states down further, by syscall and WCHAN, for example. This will give you a much more detailed idea of why the load is high, not just that it is high.

(Like I mentioned in the article), I even have a tool for sampling /proc at regular intervals (1 Hz by default) and saving it into hourly CSV files, so you can easily go "back in time" and zoom into short spikes and see what was going on:

  $ xcapture
  
  0xTools xcapture v1.0 by Tanel Poder [https://0x.tools]
  
  Sampling /proc...
  
  DATE       TIME             PID     TID USERNAME   ST COMMAND        SYSCALL    WCHAN                    
  2020-10-17 12:01:50.583    6404    7524 mysql      R  (mysqld)       fsync      wait_on_page_bit          
  2020-10-17 12:01:50.583    6404    8944 mysql      D  (mysqld)       fsync      wait_on_page_bit          
  2020-10-17 12:01:50.583    6404    8946 mysql      D  (mysqld)       fsync      wait_on_page_bit          
  2020-10-17 12:01:50.583    6404   76046 mysql      D  (mysqld)       fsync      wait_on_page_bit          
  2020-10-17 12:01:50.583    6404   76811 mysql      D  (mysqld)       fdatasync  xfs_log_force_lsn         
  2020-10-17 12:01:50.583    6404   76815 mysql      D  (mysqld)       fsync      blkdev_issue_flush        
  2020-10-17 12:01:50.583    8803    8803 root       R  (md10_resync)  [running]  0                         

  DATE       TIME             PID     TID USERNAME   ST COMMAND        SYSCALL    WCHAN                    
  2020-10-17 12:01:51.623    6404    7521 mysql      D  (mysqld)       pwrite64   xfs_file_buffered_aio_write 
  2020-10-17 12:01:51.623    6404    7524 mysql      D  (mysqld)       fsync      xfs_log_force_lsn         
  ...

By the way, PSI has the same shortcoming of not accounting the time of threads waiting for async I/O completion as I/O pressure.

Perf sched shows you scheduling latency (time spent waiting on CPU runqueue in Runnable state), but not "demand" for (some of) the system resources like the load figure tries to estimate.

I think the author didn't realize that the unit of load is just "number of threads" doing _something_ (wanting to be on CPU, running on CPU or waiting for I/O in D state on Linux, just CPU stuff on other Unixes).

Load average is just "average number of threads" doing something over last 1,5,15 minutes.

So if just the single-number averaged over multiple minutes is not good enough for drilling down into your load spikes, then you just go look into the data source (not necessarily source code) yourself. Just use ps or /proc filesystem to list the _number of threads_ that are currently either in R or D state. That's your system load at the current moment. If you want some summary/average over 10 seconds, run the same commant 100x in a row (and sleep a bit in the between) and count all threads in R & D state (and then divide by 100 to normalize it to an average).

It's basically sampling-based profiling of Linux thread states.

There's also one interesting addition that people may not be aware of: Synchronous I/O that blocks (like pread64/pwrite64) will contribute to Linux system load (threads in D state).

Asynchronous I/O completion checks (libaio's io_getevents) that are willing to wait for I/O completion, will not contribute to Linux system load (threads in S state).

Asynchronous I/O submissions (libaio's io_submit) either quickly submit their I/O (a small amount of time in R state) OR get stuck in io_submit() if the underlying block device I/O queue is full. When io_submit() gets stuck, then you're sleeping in D mode, thus contributing to system load again.

When I was looking into this, I found that just running ps (with -L option to see all threads, not just processes/thread group leaders) with some grep/sort/uniq was the easiest way to break down where does the “too high Linux system load” come from. No need to compile C code or have root access to drill down into load. And you could drill down further by sampling some additional /proc/PID/task/TID/ files, like “syscall”, “stack” to see which (blocked) syscall is contributing to the load and where in the kernel is it stuck. Knowing what kind of process/thread level /proc files are available and reading/sampling them with a shell one-liner is a powerful entry point for performance troubleshooting and may allow you to delay writing advanced kernel tracing scripts further.

For those that are interested:

https://tanelpoder.com/posts/high-system-load-low-cpu-utiliz...

In my experience (a 25+ year Oracle database geek), MySQL error messages confuse me more and Oracle's are better. But Oracle also has some error messages where you have to know how parsers (and semantic/typecheckers) work, in order to understand why the message was worded like that.

But Oracle usually does point out the exact character/token in the SQL statement where the error originated from.

By the way, JVMs also set thread names for their own housekeeping threads (on Linux at least, not sure from which version) and allow applications to name them too. I wrote an example here:

https://tanelpoder.com/posts/measuring-java-jvm-thread-level...

Have fun with in-memory columnar databases or SQL engines and see how fast they are (the ones that use CPU-efficient data structures and data element access, plus SIMD processing). For example Apache Arrow datafusion [1]

Edit: Also, run a cluster of anything (in VMs or containers) and muck around killing individual cluster nodes or just suspending them/throttling them to be extremely slow to simulate a brownout/straggling cluster node.

[1] https://github.com/apache/arrow-datafusion

Yep, when the app code and the schema/partitioning/index design works _with_ the database as intended, not _against_ the database engine’s intended use, a whole class of SQL performance and plan stability problems tends to go away.

Yep, agreed, hints should be temporary workarounds (or just experimentation aid when developing/testing SQL performance to validate some hypothesis). But many of these temporary workarounds end up being permanent temporary workarounds :-) That’s why Oracle now has hints and parameters to make the optimizer ignore all other hints in the SQL code :-)

All the commercial enterprise database performance consultants are busy spending the money they make from fixing these critical apps and expensive problems ;-)

Edit: Can’t speak much for MSSQL and DB2, but there are lots of similar problems in the Oracle world too, sometimes due to an app design that works against the intended use of the DB/optimizer, sometimes due to a database optimizer bug and sometimes just due to the complexity of estimating the optimal plan based on limited statistical summaries of the “shape of your data” and expectation that the optimization won’t take longer than a few milliseconds.

Other databases have optimizer hints for that, but apparently there’s resistance to adding hints into Postgres [1]. I find hints very useful (in Oracle world) for quickly working around some optimizer glitch (and figure out the long term solution later).

There actually is a “pg_hint_plan” [2] Postgres module that adds some hints to Postgres optimizer (but it’s not part of Postgres core as I understand):

[1] https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

[2] https://github.com/ossc-db/pg_hint_plan

In addition to what others have said:

- eBPF script: https://www.brendangregg.com/blog/2016-10-08/linux-bcc-runql...

- perf sched: https://www.brendangregg.com/blog/2017-03-16/perf-sched.html

- schedlat: https://tanelpoder.com/posts/schedlat-low-tech-script-for-me...

The last one (written by me) is using /proc/PID/schedstat pseudofile for monitoring % time spent in the CPU runqueue of a single process (not systemwide). I don't always have (root) access for running perf or eBPF scripts (or the old Linux/kernel version don't allow that). But I can still measure how much time a process on a request's critical path (like a RDBMS transaction log writer on commit) spent waiting to get back onto CPU before doing any work.

Like others have already said, the OS CPU queuing/scheduling latency is just one component contributing to the total responsiveness/reaction latency of your app.

I sometimes print out papers that I want to thoroughly understand. Read on the couch when feeling chilled out enough - reading the same papers on a computer or iPad always has more intensity, feeling like I have to finish the reading as quickly as possible, so that I could switch back to checking email or twitter…

It's worth mentioning that Finland and Sweden are not NATO member states.

FYI I got 66 GiB/s using 10 Samsung 980 Pro PCIe 4.0 SSDs on a ThreadRipper Pro workstation:

https://news.ycombinator.com/item?id=25956670

Had to use direct I/O and use random large IO patterns to avoid various caching, buffering and prefetching effects.

I tend to disable all the fancy transparency and animation options (reduce motion) in the preferences & accessibility settings and that made Monterey visually more tolerable for me. Also, disabled the "show color in tab bar" option on both OSX and iOS devices...

Good to see that YugaByte folks get it - compatibility with a database X (Postgres in this case) is not only about building a compatible syntax-parser, there's so much more (wire protocol/database drivers, transaction semantics, execution engine capabilities, etc).

MacRumors summary of the paywalled FT article: https://www.ft.com/content/69396795-f6e1-4624-95d8-121e4e5d7...