Re: fio tool to size BW, throughput and latency Oracle, Postgres , Mysql workloads

Thank you Tim for your inputs , especially the one about IOPS. And when to consider SLOB 👍
Thanks Mark for your feedback. Sure, I will learn SLOB usage and see how it correlates for other RDBMS's results.

On Mon, Jan 2, 2023 at 10:04 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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
> *To:* kunwar singh
> *Cc:* Mark W. Farnham; ORACLE-L
> *Subject:* Re: fio tool to size BW, throughput and latency Oracle,
> Postgres , Mysql workloads
>
>
>
> 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...
>
> - latency
> - low concurrency, smaller I/O requests
> - throughput
> - high concurrency, larger I/O requests
> - IOPS
> - high concurrency, smaller I/O requests
>
>
> 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
> *To:* ORACLE-L
> *Subject:* fio tool to size BW, throughput and latency Oracle, Postgres ,
> Mysql workloads
>
>
>
> 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.
>
>
>
> 1. OLTP
>
> iops ,bw, latency are important to size correctly to avoid latency. but
> rate= parameter doesn't seem to work.
>
>
>
> 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:
>
> - Do I have to ensure all the disks are created in the target before I run
> the tests?
>
> - Can you please share your examples of fio commands for 1,2,3 above
>
> - How to set the numjobs correctly?
>
>
>
> --
>
> Cheers,
> Kunwar
>
>
>
>
> --
>
> Cheers,
> Kunwar
>
>
>
>
>
>
> --
>
> Cheers,
> Kunwar
>
>
>

-- 
Cheers,
Kunwar

--
http://www.freelists.org/webpage/oracle-l