Recent content on Tanel Poder Blog
Here’s a little PL/SQL function create_partition_high_value_func.sql that allows you to write SQL queries against data dictionary for partition maintenance automation and examination operations, by converting the cumbersome LONG-datatype to VARCHAR2 on the fly. This approach works also on Oracle 19c and earlier versions. It runs a nested SQL query inside PL/SQL for every (owner, table,…
Datadog recently released a free online service explain.datadoghq.com where you can paste in your explained SQL execution plans with metrics as JSON text and it visualizes it for you. They support multiple different databases. Hopefully the interactive UI features help you determine where the problem is, without having to mentally parse and scroll through lots of text. This is a topic close to my…
Here are two scripts for showing GV$SYSMETRIC_HISTORY contents in a simple tabular way: vstat.sql - show up to one hour of current history at 1-minute granularity from memory awr/dstat.sql - show 1-minute granularity sysmetric history across your entire AWR history The database metrics infrastructure was introduced back in Oracle 10g, so you (and built-in tuning advisors) could use typically…
Here’s part 3 of my xb.sql & xbi.sql article series that I started writing back in 2019 :-) Part 1 - Show and format execution plans by querying V$SQL_PLAN data directly Part 2 - Show plan line level “self-values” for A-Time and logical, physical I/Os Part 3 - Show plan line level cardinality misestimate factors (this article) The importance of row-count (cardinality) estimates…
I have uploaded an early version of my Oracle hybrid histogram examination script that shows you the estimated row-count (cardinality) numbers when looking up a single value with a simple WHERE col = X condition from a table. You can pull the latest tpt-oracle repo or see it here: https://github.com/tanelpoder/tpt-oracle/blob/master/tabhisthybrid.sql When troubleshooting optimizer’s…
As my cat woke me up way too early for a Saturday morning, I decided to put all this extra time (and my ChatGPT Pro subscription) into good use and generated a QR-code generator for PostgreSQL, written as a single pure SQL statement . No external libraries or Postgres extension installation needed! The pqr.sql example usage is simple: $ psql -qf pqr.sql -v payload='Hello, World!' There’s…
Update: I just released xcapture v3.0.3 at P99 CONF , the first proper release of this whole concept! The 20-minute launch video is available for streaming on P99 CONF website. Repo: github.com/tanelpoder/0xtools The title and update say it all, I think it’s time to call the xcapture and xtop toolset ready for use now. I plan to announce the production release (or at least a release…
I have added a static version of my CatVector demo app for visualizing embedding vectors: tanelpoder.com/catvector/ Previously you had to clone the CatBench repo and run the little Python webserver locally, but now it’s just a publicly accessible website. It loads a few multi-megabyte JSON files into arrays in your browser and then dynamically looks up a specific pet photo that you hover…
I just added another tool into my 0x.tools toolset: With xstack , you can passively sample both kernel stacks and user stacks (as long as framepointers are present) with a pretty minimalistic tool, with no direct impact to your critical application processes! While xstack itself is just a data extraction tool, you probably want to summarize/profile all those thread states and stack profiles.…
If you are wondering how the dimensional eBPF data collection works under the hood, check out my earlier post about xcapture: eBPF performance analysis and the 0x.tools repo Summary xtop is like the Linux top tool, but extended with x-ray vision and ability to view your performance data from any chosen angle (that eBPF allows to access). This enables dimensional performance analysis on Linux and…
TL;DR: I’m doing a CatBench Vector Search on AlloyDB webinar (hacking session style!) with Google on Wednesday, July 23th at 9am EDT . I was having some chats with Kiran Tailor (of Google) and one thing led to another: We are announcing a webinar/hacking session where I stress-test the latest (Postgres-compatible) Google AlloyDB Omni release using my CatBench (cat benchmarking) vector search…
Today, my blog turned 18 years old! I published my first blog entry on June 18, 2007 and have since published over 300 posts . The first blog entry, like most of my others, was about systematic troubleshooting and performance optimization of various (Oracle) database workloads and especially their touchpoint with underlying Unix/Linux operating systems, storage and network I/O. The articles…
CatBench is a Postgres + PgVector demo application that uses 25k pet photos from a Kaggle dataset for demonstrating how the similarity search features work together with the rest of your application code & schema in the backend (HammerDB Order Entry). [ Jump to the new stress test + monitoring section ] CatBench Interactive UI You can navigate around the recommendation engine for Cat Purchases UI,…
Here’s a useful tool for quickly testing whether a disk (or a cloud block store volume) is a good candidate for your database WAL/redo logs and any other files that require low latency writes. The pg_test_fsync tool is bundled with standard Postgres packages, so no extra installation is needed. You don’t actually have to use Postgres as your database, this tool’s output is…
Oracle’s SQL*Net break/reset to client wait event shows up whenever Oracle (SQL, PL/SQL call, etc) returns an ORA- error back to the client instead of the normal expected results (and ORA-0 under the hood): SQL> @oerr 0 ORA-0000: normal, successful completion I explained this behavior a bit more in my 2008 post about Oracle’s SQL*Net break/reset to client post. Despite the wait event…
When dealing with disks and I/O things on Linux, you’d regularly run commands like lsblk , lsscsi , nvme list , etc. All of them tend to report a different set of information, so I ended up running multiple commands and correlating their output based on the device name or number. And then I had to run commands like these, to get extra info about the current OS-level configuration settings…
In this post I will introduce a much more efficient method for accounting block I/O latencies with eBPF on Linux. In my stress test, the “new biolatency” accounting method had 59x lower CPU and probe latency overhead compared to the current biolatency approach. So I couldn’t help it and ended up putting 21 NVMe SSDs into one of my homelab servers. 8 of them are PCIe5 and the…
To capture your attention, this is what you get: Table of contents What is xCapture? xCapture with modern eBPF Modular and decoupled at multiple levels Dimensional performance data analysis with DuckDB Top wall-clock time used by all threads Regular expressions in the filter Syscalls against network socket file descriptors What is the performance overhead? Performance overhead of active tracking…
TLDR; Starting from Linux kernel version 6.9 on x86_64, there’s a new config option CONFIG_X86_FRED enabled and it adds 16 bytes to the starting point of a task’s kernel stack area, so you’ll need to account for this extra padding in your “raw” kernel stack & pt_regs lookup code. Update: It turns out that there’s a bpf_task_pt_regs() helper function available in…
This should be something useful for Linux kernel explorers and eBPF nerds! Last year I released a tool called syscallargs that walked through the Linux /sys/kernel/debug/tracing/events/syscalls directory tree and allowed you to query and list available system calls in your current system from the command line. And it printed out syscall argument names and their datatypes, so it has helped me avoid…
Last year, I published a simple demo app that visualizes embedding vectors of a bunch of cat, dog and airplane photos as heatmaps. It’s a simple concept and does not have much practical use. I built it mostly for fun, but also for some “learning by doing” too. Today I’m releasing a completely new version of my CatBench Vector Search Playground app!…
Last year I wrote an article about visualizing embedding vectors of a variety of different pictures as heatmaps. I used TorchVision with ImageNet1K_V1 model for encoding a bunch of different cat, dog and plane images into their embeddings, 1000 floating-point values in each resulting vector. I used the generate_embeddings.py script for that. This whole exercise is just for my own learning (and…
Back in 2018 I wrote about how to apply Brendan Gregg’s FlameGraphs for visualizing SQL plan execution time 1 , using the plan node-level context and timing data that some database engines provide. This approach is not designed to replace the traditional plan display methods that are packed with lots of additional metrics and details. Nevertheless, in case of large execution plans, SQL plan…
Disclaimer: I’m not an ML expert and not even a serious ML specialist (yet?), so feel free to let me know if I’m wrong! It seems to me that we have hit a bit of an “on-premises” vs. “on-premise” situation in the ML/AI and vector search terminology space. The majority of product announcements, blog articles and even some papers I’ve read use the term vector…
Here’s a 12-minute video of our chat with Silk VP of Product Tom O’Neill about my recent testing of the Silk Platform in Google Cloud. In this interview we cover some high level points, conclusions and talk a little bit about the future. If you want to read all the technical details and some interesting references, see the links below:
Hands-on technical analysis of a novel data platform for high-performance block I/O in the cloud , tested by Tanel Poder , a database consultant and a long-time computer performance nerd. Index Background and motivation Scalable Architecture by Design Enterprise Features Testing Results I/O Latency 1.3 Million IOPS Lessons Learned Summary Background and Motivation Back in 2021, my old friend Chris…
Following my earlier Visualizing and Comparing Embedding Vectors as Heatmaps article, here are the launch & demo videos. You might want to read this article first, these short demo videos should make more sense then! Visualizing Vision Transformer Embedding Vectors as HeatMaps In this video I’ll peek around cat vectors and then find outliers from both the cat photo dataset and aircraft…
An interactive demo video is here: Part 2 . If you already know what embeddings are, you can jump right to: Vector Heatmap Visualization Background I am currently testing various RDBMS vector search options and their usability & performance. Next week I will publish an early version of my CatBench toolset that will eventually evolve into a full end-to-end test (and fun!) suite for high performance…
Here’s my talk from eBPF Summit 2024 . Video (10 minutes) The 0x.tools Extended Task State Sampling approach provides a new observability signal - wall-clock time of all active threads’ activity in your system. You can see both total workload demand of your apps and also drill down deep into individual threads’ activity when needed. This method and toolset give you a single data…
Starting from Python 3.12, you can instruct Python to enable direct perf profiling support for your Python code. This feature currently works only on Linux , as it communicates the relationships between Python-level interpreted function calls and machine-code level return pointers (for stack unwinding) via perf map files. The Linux perf tool knows how to read these files and maps the return…
Yes! Various traps like page faults may cause your user process to be switched into kernel codepath even when the process is minding its own business in the userspace. It just needs to do something that causes such a trap, like touch a new page of memory in its virtual memory address space, that hasn’t been “fully materialized” yet in the kernel memory structures. For example,…
I recently published what I have been working on for the past 5 years. No, this is not about AI or a yet another startup - I’m still a big fan of NI ( natural intelligence ) and what I reveal is open source and free for everyone to use. I’m not gonna sell you any products, only ideas! This is a new approach and a supporting toolset for understanding system behavior and its performance…
This blog post is not about tracing program system calls, but about programmatically extracting all system calls and their accepted arguments in your current system. If you want to read more about tracing system calls, here’s my tutorial using strace for troubleshooting a real life scenario: Troubleshooting Linux SSH Login Delay - Why does logging in always take 10 seconds? 1 Update : I have…
One evening back in January I finally had enough of thermal issues within my homelab server. You know, every time the computer fans make more noise than I think they should, I can’t help but investigate ! Also, the RTX4090 is so thick that it takes 3 PCIe slots worth of space on a typical motherboard - and you also need space for the airflow! I was using the same machine that achieved 11M…
Thanks to Tomasz Sroka ’s pull request for my hint.sql in the TPT repo and blog entries by Sayan 1 and Jonathan 2 , we now have a HINT_SCOPE field in my hint.sql script too! SQL> @hint merge NAME VERSION VERSION_OUTLINE INVERSE HINT_SCOPE ----------------------- ----------- --------------- -------------- -------------- MERGE_CONST_ON 8.0.0 STATEMENT MERGE_AJ 8.1.0 8.1.7 QBLOCK MERGE_SJ 8.1.0…
I switched my blog’s commenting system to use Giscus . It’s still backed by Github (Discussions) as “storage”, but it doesn’t request wide Github permissions from commenters (like Utterances used to do). I didn’t migrate old comments over, but they’re still available here: https://github.com/tanelpoder/blog-comments/issues/ For current/latest comments,…
Over time, Oracle has been adding more contextual information into each allocated heap memory chunk, to make it easier to see what for your heap memory is used. This instrumentation is used for private (PGA,UGA,etc) heaps too, but this article focuses only on shared pool heaps. A few examples from past are: Library cache object hash values added to allocated shared pool chunk names (2010) The…
Back in 2007, my third blog entry was about a parallel index building gotcha , a warning about building & rebuilding indexes in parallel. After completing the build, Oracle left the parallel degree in place for that index in data dictionary. Thus, after building a great new index over some weekend, many of your OLTP queries could have picked a parallel plan afterwards! Whenever creating/rebuilding…
You probably are already familiar with my ash_wait_chains scripts that use CONNECT BY for walking through the blocking_session fields in ASH and report “wait chains”, signatures of complex waits involving other database sessions. Since ASH is “just” a history of V$SESSION, it’s sensible to assume that one could sample the current wait chains just by querying the…
About 14 years ago I published the OstackProf script that ran on Windows sqlplus clients (the Oracle server could run on any platform that supported ORADEBUG SHORT_STACK). I don’t use Windows almost at all nowadays, so it’s about time to publish the same functionality for MacOSX, Linux, Unix sqlplus clients. This is mostly useful when hacking dev systems, troubleshooting test systems…
Update: Just as I started blogging about the Optane technology, it turns out that Intel is shutting down the Optane business (after bringing the latest-gen “Crow Pass” Optane memory to market - probably just for satisfying existing commitments). While not exactly the same thing, keep an eye on the new CXL standard developments to see where the data center/cloud infrastructure world is…
Update: Just as I started blogging about the Optane technology, it turns out that Intel is shutting down the Optane business (after bringing the latest-gen “Crow Pass” Optane memory to market - probably just for satisfying existing commitments). While not exactly the same thing, keep an eye on the new CXL standard developments to see where the data center/cloud infrastructure world is…
Starting from v8.0.27, the MySQL daemon shows its thread names at the OS level too, for better troubleshooting. The MySQL 8.0.27 release notes say this: To assist monitoring and troubleshooting, the Performance Schema instrumentation is now used to export names of instrumented threads to the operating system. This enables utilities that display thread names, such as debuggers and the Unix ps…
In the previous post in this series, I showed how my scripts xb.sql and xbi.sql ( eXplain Better and eXplain Better, by sqlId ) help you see Oracle execution plan details in a more complete way. Here are the features that I currently plan to cover: More detailed access/filter predicate info next to plan tree data ( Part 1 ) More intelligent row-source level execution statistics presentation (Part…
Tanel at Mount Denali in Alaska (2021) In the beginning of every year I travel somewhere and take some time to think about the future and what I should be working on next. The 2020/2021 years were full of all kinds of distractions for me (and many of you). I got somewhat sick a couple of times too (although never tested positive for COVID) and the full recovery to 100% took time. This made…
You may remember my previous article about high-performance I/O for cloud databases, where I tested out the Silk Platform and got a single Azure VM to do over 5 GB/s (gigaBytes/s) of I/O for my large database query workload. The magic was in using iSCSI over the switched cloud compute network to multiple “data VMs” at the virtualized storage layer for high I/O throughput and…
Update: 3 years after writing this article, I tested the latest Silk Platform in 2024 , on Google Cloud Platform, with impressive results. In my tests, I got to over 20 GiB/s with large reads, 1.3M IOPS with small 8kB random reads and 10 GiB/s sustained write rate! Both articles are worth a read. Hands-on technical analysis of a novel data platform for high-performance block I/O in the cloud ,…
Over the years of troubleshooting performance problems in the Unix/Linux world, I have seen multiple cases where a regularly used command line tool in a customer server just stops working for some reason. The tool just returns immediately, doing absolutely nothing. No output printed, no coredumps and the exit code is zero (success!). This article walks you through a couple of such incidents and in…
In this video I will present a case study of a complex performance issue where the initial symptoms pointed somewhere else than the root cause. Only when systematically following through the troubleshooting evidence, we get to the root cause of the problem. This session aims to help you understand (and reason about) the Oracle’s multi-process & multi-layer system behavior, preparing you for…
TL;DR: Some Linux distros and even different kernel versions within a distro have disabled IRQ time accounting. In such case, your monitoring tools will report zero time spent in hardware interrupt handlers (shown as %hi , %irq , hiq , etc). It’s easy to check how your kernel is behaving by looking at /proc/stat and you can still measure interrupt CPU usage using perf and a little creativity…