RE: fio tool to size BW, throughput and latency Oracle, Postgres , Mysql workloads
Date: Mon, 2 Jan 2023 10:03:58 -0500
Message-ID: <471801d91ebb$712c3320$53849960$_at_rsiz.com>
Tim’s approach is valid.
Here is the difference:
Leap to SLOB flow:
if the set-up is verified by SLOB as good, you’re done for Oracle environments and probably good for other RDBMS environments.
If it is not actually for an Oracle environment you might have some software to remove and you might need a measurement for your actual target if it is not covered by SLOB and the target is not known to be consistently good if there is a good SLOB result.
if the set-up is verified by SLOB as disappointing, you indeed need to drop back and do all work with fio or similar to know whether or not the Oracle install is indicted as the problem.
Tim’s approach:
verify substract/infrastructure
use something (SLOB or other)
IF most of the situations you face are essentially pre-configured “appliance” or “service” installations, I think SLOB first is less total effort.
IF you’re cooking up something custom from scratch or testing the first of n “appliance” or “service” installations, Tim’s approach is probably less total effort and more information.
I’m usually handed a poorly performing already running something, so SLOB ends up being a shortcut to rule out poorly configured io subsystems. With separation of duties and security, I then usually have to kick it back to a team with access to run the tests I used to run myself in the previous century.
From your initial question, probably Tim’s approach is better for you.
It would be interesting to know if SLOB results correlate with performance requirements for your other io needs.
Good luck!
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Sunday, January 01, 2023 6:52 PM
Kunwar,
I use it for Oracle and other application workloads, including other databases, but primarily for Oracle.
The FIO test in the script breaks down three ways...
Each type of FIO test can be performed on reads, writes, and both reads and writes. Also, each type of FIO test can be performed on sequential or random-access operations.
Latency (measured in milliseconds or microseconds) and throughput (usually measured as megabytes per second) are absolute metrics. A millisecond is a millisecond and a MB/s is a MB/s, so latency and throughput are useful metrics with any application, including Oracle. But IOPS also requires a fixed and known I/O request size, and Oracle databases uses any size I/O request between 512 bytes and 1 MB. So IOPS is a useless metric with Oracle database, because 10000 IOPS measured at one time is not comparable to 10000 IOPS measured at another time or on another environment.
Usually when setting up an Oracle environment I'll test with FIO prior to installing Oracle, to ensure that everything is configured right at the OS level. If FIO testing doesn't meet expectations, then we need to fix it then and there before progressing to installing Oracle. Only then, afterwards, will I consider testing Oracle I/O performance with something like SLOB. Skip FIO and just use SLOB, then any problems detected might well be at either the OS or DB level. Problems detected by SLOB after favorable FIO testing is usually a problem in the database, less likely in the OS.
Hope this helps...
-Tim
On 1/1/2023 2:27 PM, kunwar singh wrote:
Thanks Tim! It is indeed helpful. I tried a lot of git repos and just kept discovering new issues with those.
Your simple script is what I was going to write :) , saved me some time.
I assume you tested it for Oracle workloads. Any tips on Postgre or Mysql workloads ? Are there some aspects specific to those database engines that will lead to any change in the parameter values in this shell script?
I am planning migrations for mysql , postgre onprem systems to cloud and I am very new to these databases.
On Sun, Jan 1, 2023 at 4:16 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:
Kunwar,
The Oracle documentation page <https://docs.oracle.com/en-us/iaas/Content/Block/References/samplefiocommandslinux.htm> about FIO is pretty awesome and you might already be working from it?
I wrote the attached shell script based on that page, and I use it to test and compare I/O latency and I/O throughput (and IOPS) from the Linux guest OS across all different kinds of configurations. As usual, the ".txt" file-extension is intended just to get past email servers filters, so please rename it?
Hope this helps,
-Tim
On 1/1/2023 7:59 AM, kunwar singh wrote:
Thanks Mark. I ruled out SLOB earlier as I want to size for Mysql and Postgres workloads as well. Still I will go over the SLOB documentation again , it has been a few years since using SLOB.
Yes I am aware of the OCI documentation , but couldn't get to making it work for sizing our needs.
On Sun, Jan 1, 2023 at 9:52 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
I suppose you could create your own test from the system up, but most likely you can learn what you need to know from SLOB, and I’d bet a dozen donuts that https://kevinclosson.net/slob/ will provide you with better information than anything you cobble together with system tools. (Individual systems tools might help you find what is misconfigured if SLOB shows poor results compared to what you think the resources you have deployed.)
Good luck.
This: https://docs.oracle.com/en-us/iaas/Content/Block/References/samplefiocommandslinux.htm
probably has commands you can pattern match to your case if you really want to do it yourself. Even if you proceed because you want to learn a bit, I would check your answer with SLOB.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kunwar singh
Sent: Sunday, January 01, 2023 9:17 AM
Happy new year Listers!
I am trying to run fio <https://fio.readthedocs.io/en/latest/fio_doc.html> and I cannot get the right combination of commands for below systems I am sizing for.
2. DSS
Should I just focus on iops and bw here
3. Mixed
How do I play with fio to get the right set of commands using rwmixread/write?
Few questions:
To: kunwar singh
Cc: Mark W. Farnham; ORACLE-L
Subject: Re: fio tool to size BW, throughput and latency Oracle, Postgres , Mysql workloads
To: ORACLE-L
Subject: fio tool to size BW, throughput and latency Oracle, Postgres , Mysql workloads
iops ,bw, latency are important to size correctly to avoid latency. but rate= parameter doesn't seem to work.
-- Cheers, Kunwar -- Cheers, Kunwar -- Cheers, Kunwar -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 02 2023 - 16:03:58 CET