Using fio to mimicking Oracle workload pattern

Using fio to mimicking Oracle workload pattern

[Neto, Antonio Jose Rodrigues]

Hi All,


This is neto from Brazil


How are you?


I want to simulate some Oracle workload pattern using fio. I would like to do the following:


Archive LOGS: I would like to create x number of different file sizes. I have tried the following, but I am not sure if this is right about filesize option.

directory=/archive
size=10g
ioengine=libaio
nrfiles=5
iodepth=4
rw=write
filesize=500m
direct=1
threads=4
numjobs=1

Redo LOGS:  I have tried the following: sync I/O and block split (to simulate partial writes) - 60% 512 bytes and 40% 4096.

directory=/redo
size=1g
ioengine=libaio
nrfiles=5
iodepth=4
sync=1
rw=write
filesize=500m
bssplit=512/60:4k/40
direct=1
numjobs=1

FULL Table Scan: I have tried creating 3 files and read from them (randomly). I used ba=4096 to have block aligned and 1MB of block size (to simulate db_scattered_read). 

[tablescan]
directory=/u109/oradata/fio
nrfiles=3
openfiles=3
file_service_type=roundrobin
size=1g
iodepth=32
ba=4k
bs=1M
rw=randread
numjobs=2


Could you please help to check if the above files are correct? If they are, how can I double check if everything is working as expected (on fio output)?

Thank you very much

neto

Re: Using fio to mimicking Oracle workload pattern

[Kyle Hailey]

Why are you trying to reproduce the I/O patterns of Oracle? Is it to
test the performance of the back end storage for Oracle? If so what
criteria are you using to judge the performance?

I've been using fio to test the back end storage for Oracle. I've been
interested in the limits of the 3 main axis of Oracle I/O (and less in
the mix of the three)

   1.  8k random read  (index access etc)
   2.  1M sequential read (full table scans etc)
   3. 1-128K sequential write (redo writes)

The other types of I/O are

   *  8k random write which is done by DBWR but users don't typically
wait for these I/Os
   *  direct path write by users but this isn't a typical operation more ET

I put together tests for the main 3 types of I/Os each isolated (ie
not mixed at the same time). The tests are on github at:

https://github.com/khailey/fio_scripts

See the README.md for more info.

The tests are run by a shell script that runs fio in an array of
tests. There is a script to parse the data and summarize it as well as
an R script to graph the data.

I have new set of R scripts to post soon.

- Kyle

On Sun, Sep 2, 2012 at 7:50 AM, Neto, Antonio Jose Rodrigues
<Antonio.Jose.Rodrigues.Neto@netapp.com> wrote:
>
> Hi All,
>
>
> This is neto from Brazil
>
>
> How are you?
>
>
> I want to simulate some Oracle workload pattern using fio. I would like to
> do the following:
>
>
> Archive LOGS: I would like to create x number of different file sizes. I
> have tried the following, but I am not sure if this is right about filesize
> option.
>
> directory=/archive
> size=10g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> rw=write
> filesize=500m
> direct=1
> threads=4
> numjobs=1
>
> Redo LOGS:  I have tried the following: sync I/O and block split (to
> simulate partial writes) - 60% 512 bytes and 40% 4096.
>
> directory=/redo
> size=1g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> sync=1
> rw=write
> filesize=500m
> bssplit=512/60:4k/40
> direct=1
> numjobs=1
>
> FULL Table Scan: I have tried creating 3 files and read from them
> (randomly). I used ba=4096 to have block aligned and 1MB of block size (to
> simulate db_scattered_read).
>
> [tablescan]
> directory=/u109/oradata/fio
> nrfiles=3
> openfiles=3
> file_service_type=roundrobin
> size=1g
> iodepth=32
> ba=4k
> bs=1M
> rw=randread
> numjobs=2
>
>
> Could you please help to check if the above files are correct? If they
> are, how can I double check if everything is working as expected (on fio
> output)?
>
> Thank you very much
>
> neto--
> To unsubscribe from this list: send the line "unsubscribe fio" in
> the body of a message to majordomo@vger.kernel.org
> More majordomo info at  http://vger.kernel.org/majordomo-info.html




--
- Kyle

O: +1.415.341.3430
F: +1.650.494.1676
275 Middlefield Road, Suite 50
Menlo Park, CA 94025
http://www.delphix.com

Re: Using fio to mimicking Oracle workload pattern

[Kyle Hailey]

My suggestion for pounding Oracle I/O is to use SLOB - Silly Little
Oracle Benchmark.
For an example of dNFS testing with SLOB see
http://dboptimizer.com/2012/07/16/nfs-versus-dnfs/
SLOB will bang on random reads and redo writes. It's not setup for
sequential reads.

For the fio scripts, I/O for sequential writes is set at certain sizes
- 1K, 8K, 128K. A set of tests is run with each block size. Each set
of tests runs up 1, 4, 16 uses as if there were 1, 4,16 databases. The
write job file use SYNC and DIRECT (except on ZFS where direct isn't
supported)

Example job file ( the job files are generated by a script called
fio.sh which iterates though different combinations)

[global]
size=8192m
filename=/dev/rdsk/c4t3d0p0:/dev/rdsk/c4t4d0p0
directory=/
direct=1
runtime=5
randrepeat=0
end_fsync=1
group_reporting=1
ioengine=psync
fadvise_hint=0
time_based=1
[job1]
rw=write
bs=0008k
numjobs=1
offset=0
sync=1

>>How can I split the block size for I/O?
I haven't tried this. Looks like you are doing this already. I change
block sizes by running different tests at different sizes.

>>How can I create different size of files?
Why would you want too do that? Why not just test all the same file size?

On Wed, Sep 5, 2012 at 3:42 PM, Neto, Antonio Jose Rodrigues
<Antonio.Jose.Rodrigues.Neto@netapp.com> wrote:
>
> Hi Kyle
>
>
>
> This is neto from Brazil
>
>
>
> How are you?
>
>
>
> Thanks for your email.
>
>
>
> Yes I am trying to reproduce Oracle I/O patterns because I need to tests different storage controllers here and I don�t like to use ORION for that. (Orion doesn�t support DIRECT_IO for NFS or mounted filesystem)
>
>
>
> Anyway�
>
>
>
> How can I split the block size for I/O?
>
> How can I create different size of files?
>
>
>
> How are you doing this?
>
>
>
>  1-128K sequential write (redo writes)
>
>
>
>
>
>
>
> I will take a look on your scripts
>
>
>
> All the best
>
>
>
> neto
>
>
>
> From: Kyle Hailey [mailto:kyle.hailey@delphix.com]
> Sent: Wednesday, September 05, 2012 3:48 PM
>
>
> To: Neto, Antonio Jose Rodrigues
> Cc: fio@vger.kernel.org
>
> Subject: Re: Using fio to mimicking Oracle workload pattern
>
>
>
>
>
> Why are you trying to reproduce the I/O patterns of Oracle? Is it to test the performance of the back end storage for Oracle? If so what criteria are you using to judge the performance.
>
>
>
> I've been using fio to test the back end storage for Oracle. I've been interested in the limits of the 3 main axis of Oracle I/O (and less in the mix of the three)
>
>  8k random read  (index access etc)
>  1M sequential read (full table scans etc)
>  1-128K sequential write (redo writes)
>
> The other types of I/O are
>
> 8k random write which is done by DBWR but users don't typically wait for these I/Os
> ldirect path write by users but this isn't a typical operation more ET
>
> I put together tests to test the main 3 types of I/Os each isolated (ie not mixed at the same time). The tests are on github at:
>
> https://github.com/khailey/fio_scripts
>
>
>
> See the README.md for more info.
>
>
>
> The tests are run by a shell script that runs fio in an array of tests. There is a script to parse the data and summarize it as well as an R script to graph the data.
>
>
>
> I have new set of R scripts to post soon.
>
>
>
> - Kyle
>
>
>
>
>
> On Sun, Sep 2, 2012 at 7:50 AM, Neto, Antonio Jose Rodrigues <Antonio.Jose.Rodrigues.Neto@netapp.com> wrote:
>
> Hi All,
>
>
> This is neto from Brazil
>
>
> How are you?
>
>
> I want to simulate some Oracle workload pattern using fio. I would like to do the following:
>
>
> Archive LOGS: I would like to create x number of different file sizes. I have tried the following, but I am not sure if this is right about filesize option.
>
> directory=/archive
> size=10g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> rw=write
> filesize=500m
> direct=1
> threads=4
> numjobs=1
>
> Redo LOGS:  I have tried the following: sync I/O and block split (to simulate partial writes) - 60% 512 bytes and 40% 4096.
>
> directory=/redo
> size=1g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> sync=1
> rw=write
> filesize=500m
> bssplit=512/60:4k/40
> direct=1
> numjobs=1
>
> FULL Table Scan: I have tried creating 3 files and read from them (randomly). I used ba=4096 to have block aligned and 1MB of block size (to simulate db_scattered_read).
>
> [tablescan]
> directory=/u109/oradata/fio
> nrfiles=3
> openfiles=3
> file_service_type=roundrobin
> size=1g
> iodepth=32
> ba=4k
> bs=1M
> rw=randread
> numjobs=2
>
>
> Could you please help to check if the above files are correct? If they are, how can I double check if everything is working as expected (on fio output)?
>
> Thank you very much
>
> neto--
> To unsubscribe from this list: send the line "unsubscribe fio" in
> the body of a message to majordomo@vger.kernel.org
> More majordomo info at  http://vger.kernel.org/majordomo-info.html
>
>
>
>
>
> --
>
> - Kyle
>
>
> O: +1.415.341.3430
> F: +1.650.494.1676
> 275 Middlefield Road, Suite 50
> Menlo Park, CA 94025
> http://www.delphix.com




--
- Kyle

O: +1.415.341.3430
F: +1.650.494.1676
275 Middlefield Road, Suite 50
Menlo Park, CA 94025
http://www.delphix.com

RE: Using fio to mimicking Oracle workload pattern

[Neto, Antonio Jose Rodrigues]

Hi Kyle

This is neto from Brazil

How are you?

Thanks for your email. I know very well SLOB (Kevin Closson). At this time, I don't SLOB because I cannot specify a working set on it. That won't work for me.

I saw your script and I really liked. I just need a simple example how did you configure fio for the following scenarios:

How did you configure "users" ? Threads?

Why are you doing psync and not libaio (to simulate async IO)?

Could you please do me a favor?

Could you please double check if I have the right configuration for the following files?

My doubt are:

1) In the Oracle performance characterization, what is the "correct" ioengine on linux? How many iodepth? I am assuming 1 job only (8K Random Read)
2) What is the correct for 1M sequential? libaio as well? And iodepth?
3) 1-128K redo writes, assuming that I need to use sync=1 right? And the iodepth? Direct 1 or not?

If you could help me with that, I really appreciate.

Thank you very much

neto

[8Krandomread]
directory=/oradata/fio
nrfiles=50
openfiles=10
size=100g
bs=8k
ioengine=libaio
iodepth=32
rw=randr
direct=1
numjobs=1

[1Msequentialread]
directory=/oradata/fio
nrfiles=50
openfiles=10
size=100g
bs=1M
ioengine=libaio
iodepth=4
rw=randr
direct=1
numjobs=1


[1-128Ksequentialwrite]
directory=/redolog/fio
filename=redo01.log
size=1g
ioengine=libaio
iodepth=4
rw=write
fsync=1
bssplit=512/60:4k/40
direct=1
numjobs=1

-----Original Message-----
From: Kyle Hailey [mailto:kyle.hailey@delphix.com] 
Sent: Wednesday, September 05, 2012 6:56 PM
To: Neto, Antonio Jose Rodrigues
Cc: fio@vger.kernel.org
Subject: Re: Using fio to mimicking Oracle workload pattern

My suggestion for pounding Oracle I/O is to use SLOB - Silly Little Oracle Benchmark.
For an example of dNFS testing with SLOB see http://dboptimizer.com/2012/07/16/nfs-versus-dnfs/
SLOB will bang on random reads and redo writes. It's not setup for sequential reads.

For the fio scripts, I/O for sequential writes is set at certain sizes
- 1K, 8K, 128K. A set of tests is run with each block size. Each set of tests runs up 1, 4, 16 uses as if there were 1, 4,16 databases. The write job file use SYNC and DIRECT (except on ZFS where direct isn't
supported)

Example job file ( the job files are generated by a script called fio.sh which iterates though different combinations)

[global]
size=8192m
filename=/dev/rdsk/c4t3d0p0:/dev/rdsk/c4t4d0p0
directory=/
direct=1
runtime=5
randrepeat=0
end_fsync=1
group_reporting=1
ioengine=psync
fadvise_hint=0
time_based=1
[job1]
rw=write
bs=0008k
numjobs=1
offset=0
sync=1

>>How can I split the block size for I/O?
I haven't tried this. Looks like you are doing this already. I change block sizes by running different tests at different sizes.

>>How can I create different size of files?
Why would you want too do that? Why not just test all the same file size?

On Wed, Sep 5, 2012 at 3:42 PM, Neto, Antonio Jose Rodrigues <Antonio.Jose.Rodrigues.Neto@netapp.com> wrote:
>
> Hi Kyle
>
>
>
> This is neto from Brazil
>
>
>
> How are you?
>
>
>
> Thanks for your email.
>
>
>
> Yes I am trying to reproduce Oracle I/O patterns because I need to 
> tests different storage controllers here and I don't like to use ORION 
> for that. (Orion doesn't support DIRECT_IO for NFS or mounted 
> filesystem)
>
>
>
> Anyway...
>
>
>
> How can I split the block size for I/O?
>
> How can I create different size of files?
>
>
>
> How are you doing this?
>
>
>
>  1-128K sequential write (redo writes)
>
>
>
>
>
>
>
> I will take a look on your scripts
>
>
>
> All the best
>
>
>
> neto
>
>
>
> From: Kyle Hailey [mailto:kyle.hailey@delphix.com]
> Sent: Wednesday, September 05, 2012 3:48 PM
>
>
> To: Neto, Antonio Jose Rodrigues
> Cc: fio@vger.kernel.org
>
> Subject: Re: Using fio to mimicking Oracle workload pattern
>
>
>
>
>
> Why are you trying to reproduce the I/O patterns of Oracle? Is it to test the performance of the back end storage for Oracle? If so what criteria are you using to judge the performance.
>
>
>
> I've been using fio to test the back end storage for Oracle. I've been 
> interested in the limits of the 3 main axis of Oracle I/O (and less in 
> the mix of the three)
>
>  8k random read  (index access etc)
>  1M sequential read (full table scans etc)  1-128K sequential write 
> (redo writes)
>
> The other types of I/O are
>
> 8k random write which is done by DBWR but users don't typically wait 
> for these I/Os ldirect path write by users but this isn't a typical 
> operation more ET
>
> I put together tests to test the main 3 types of I/Os each isolated (ie not mixed at the same time). The tests are on github at:
>
> https://github.com/khailey/fio_scripts
>
>
>
> See the README.md for more info.
>
>
>
> The tests are run by a shell script that runs fio in an array of tests. There is a script to parse the data and summarize it as well as an R script to graph the data.
>
>
>
> I have new set of R scripts to post soon.
>
>
>
> - Kyle
>
>
>
>
>
> On Sun, Sep 2, 2012 at 7:50 AM, Neto, Antonio Jose Rodrigues <Antonio.Jose.Rodrigues.Neto@netapp.com> wrote:
>
> Hi All,
>
>
> This is neto from Brazil
>
>
> How are you?
>
>
> I want to simulate some Oracle workload pattern using fio. I would like to do the following:
>
>
> Archive LOGS: I would like to create x number of different file sizes. I have tried the following, but I am not sure if this is right about filesize option.
>
> directory=/archive
> size=10g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> rw=write
> filesize=500m
> direct=1
> threads=4
> numjobs=1
>
> Redo LOGS:  I have tried the following: sync I/O and block split (to simulate partial writes) - 60% 512 bytes and 40% 4096.
>
> directory=/redo
> size=1g
> ioengine=libaio
> nrfiles=5
> iodepth=4
> sync=1
> rw=write
> filesize=500m
> bssplit=512/60:4k/40
> direct=1
> numjobs=1
>
> FULL Table Scan: I have tried creating 3 files and read from them (randomly). I used ba=4096 to have block aligned and 1MB of block size (to simulate db_scattered_read).
>
> [tablescan]
> directory=/u109/oradata/fio
> nrfiles=3
> openfiles=3
> file_service_type=roundrobin
> size=1g
> iodepth=32
> ba=4k
> bs=1M
> rw=randread
> numjobs=2
>
>
> Could you please help to check if the above files are correct? If they are, how can I double check if everything is working as expected (on fio output)?
>
> Thank you very much
>
> neto--
> To unsubscribe from this list: send the line "unsubscribe fio" in the 
> body of a message to majordomo@vger.kernel.org More majordomo info at  
> http://vger.kernel.org/majordomo-info.html
>
>
>
>
>
> --
>
> - Kyle
>
>
> O: +1.415.341.3430
> F: +1.650.494.1676
> 275 Middlefield Road, Suite 50
> Menlo Park, CA 94025
> http://www.delphix.com




--
- Kyle

O: +1.415.341.3430
F: +1.650.494.1676
275 Middlefield Road, Suite 50
Menlo Park, CA 94025
http://www.delphix.com