Robert Young
Storage Architect
What is Transactional IO and why is this important? To understand what IO throughput and latency should be in each situation makes us better at troubleshooting and grows us in design capability. We become the go-to person in a myriad of circumstances increasing our worth to the mission and our teammates.
This piece serves several purposes. Here in numbered form are the things I set out to prove or demonstrate. Stick around for the follow-on to this as it all comes together there with maybe a surprise. To do this in one piece would be far too long. I hope to convince you via real-world examples or firm up (if you are already mostly in agreement) the following:
- Transactional IO is understandable in size, use-case, and reality.
- There are certain protocols that are friends of Transactional IO.
- There are protocols that really don’t work well with Transactional IO.
- Block-level access of data must be around for a very long time.
First thing let’s define what we mean by Transactional IO. Simply put, smaller IOs sized typically found in databases. Banking records, patient records, point-of-sale, inventory systems make use of smaller block sizes and therefore smaller IOs. Here is a small table that reflects IO sizes we are referring to – this isn’t all inclusive but representative.
Great IO Expectations
Transactional IO sizing involves understanding how slow or fast various sized IOs can be and why size plays a significant role in retrieval times (latency.) Secondly, once we have a handle on size and impact on retrieval times, we will look at a few things to confirm that premise. In a VMWare support article1 we read this:
“If your goal is to get the minimum latency, you must choose a small IO size (ideally smaller than 64K).”
I want to state that differently. I will say: “If you desire performant DB transactional IO, you need IO request sizes tuned accordingly.” The following shows how slow/fast an IO can be to SSD. We will come back to some of this in the follow-on to this piece. Keep in mind, this is just a talking point on these screenshots here to further our understanding as we draw conclusions. Don’t hang your hat on these numbers or what is being measured. “But..but we are faster/slower”, etc. Typical fio2 to capture these numbers:
Weekend Slowdown
We had a customer come to us and they were technical enough to show us latency numbers on 4K IO. The issue they were trying to solve was slower weekend runs on a massive SQL DB on the new SSD array versus performance on the former SSD array. Here is what they were seeing for IO times in their benchmarking – latency is shown in milliseconds. Their IO testing showed this:
As it turned out, the top two volumes were created (and used in production) in a certain way that impacted performance. 1.4 milliseconds is not good performance for 4K random IO. 0.5 ms to array based SSD is a decent number. The point here is we know what good transactional IO timings should be in 2023, and so do you!
64K IO – Throttles PostgreSQL Performance
Frank Pachot3 does an excellent job tracking down a PostgreSQL performance issue in AWS.
Frank states:
“Here is the problem. iostats shows the exact picture [iostat shows 64K reads] I think I’m doing random reads of 8k, which is the expected OLTP workload for PostgreSQL, but reads are actually performed with a 64k size. “
How/why? The Red Hat 8.4 Arm-based box uses 64K memory pages, therefore all IO requests are a minimum of 64K compared to an expected 8K IO request. His execution times increased dramatically on the Red Hat 8.4 runs. We need to be careful here, the larger 64K IO is causing a bigger slowdown than we would expect from the first review of the article. After careful reading of the article, we see multiple re-runs and the “true” 4K IO of the AWS Linux shows low microsecond IO rates – cache hits. If we review our table above, we expect 50 % to 80%+ slowdown based on 8K latency versus 64K latency if this was true random IO to a drive. Large IO is the problem, that we can agree and “most likely” IO cache issues via misaligned cache with all 64K IO going to the drive. Review the link for yourself.
A short summary here is we’ve touched on transactional IO sizes, and we’ve observed that transactional IO is assumed to be block-based. What we will explore in Part 2 is how 64K IO is not just Frank’s issue but can knee-cap us in other transactional situations and we will also turn our attention to protocols in Part 2, continue exploring the bullet points at the top and further drill down on latency and bandwidth.
Enterprise Storage and Data Solutions
Mainline offers cyber storage solutions for today’s data intensive enterprises. Working with all the major data storage technology vendors, and having deep knowledge across the entire spectrum of enterprise storage solutions, we help our clients select and acquire the best offerings to suit their business needs and budget. Our experts can assist you from the initial assessment through post-implementation support to include services that can help you maximize and sustain business efficiencies. Contact your Mainline Account Representative directly or reach out to us here with any questions.
Note or References:
[1] https://kb.vmware.com/s/article/2150019
[2] fio –filename=/dev/sda2 –rw=randread –random_generator=lfsr –direct=1 –bs=[8k/64k/1024k] –ioengine=libaio –iodepth=1 –runtime=10 –numjobs=1 –time_based –group_reporting –name=iops_test_job –eta-newline=1 –readonly
[3] https://dev.to/aws-heroes/postgresql-on-arm-default-page-size-matters-2n7a Foot note to the reference. Frank says this: “But ARM processors can have 64k pages, and some distributions, like CentOS, RedHat, or Oracle Linux, set the default page size to 64k.” That doesn’t appear to be accurate, see this Red Hat article link below – default Red Hat page size is 4K regardless of architecture. You can choose to set page size to 64K as outlined in this link. https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9/html/managing_monitoring_and_updating_the_kernel/what-is-kernel-64k_managing-monitoring-and-updating-the-kernel