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 o low concurrency, smaller I/O requests
• throughput o high concurrency, larger I/O requests
• IOPS o 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

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