Size I/O block on storage array

[RogerVI]

Hi,
My SO is HP-UX 11iV1
I have a database with block size = 2K. The database is Informix with raw device.
This database is on lvm. The PE size is 4M
The database is on a SAN storage disk array.
I want to know the IOPS and the MB/s for the disk. Is 2K? In which cases it is different ? What is the optimum ?
Thanks in advance.

[Bill Hassell]

> The database is on a SAN storage disk array.
> I want to know the IOPS and the MB/s for the disk.

Not very useful. Is the the SAN made by HP? EMC? Hitachi? Fujitsu? What does the manufacturer report as the speed of the array? Most disk arrays support large block sizes (dozens of KB to MB), but ask your SAN administrator how the disk has been configured.

By the way, 2K is extremely small for a database. Did the Informix recommendations specify 2K? Do you have a database with very small records?

Generally, small block sizes will severely limit disk performance. Have you run sar -d to check disk activity? There is just not enough information here.

[RogerVI]

The array is an EVA 5000
The data is on a disk group. This disk group have 28 disks of 36 GB (15rpm)
I attach the output of sar
My question is about the operating system. When the operating system does a request to storage, what is the block size of such request ?
Thanks in advance.

[RogerVI]

I forget tell you that in Informix, the page size is depends of the platform. In the case of HP-UX, the page size is 2K

[Dennis Handly]

>In the case of HP-UX, the page size is 2K

For the kernel, the default size is 4Kb.

[Hein van den Heuvel · Accepted solution]

The optimum IO size is just enough to read all the data needed to gather the data to full fill the SQL request. So 'it depends'. It depends on the work done often roughly categorized as OTLP style or Warehouse style.
There is no single optimum size for all. That would be too easy.
Any DB worth being called a DB will try to do the right thing making the base page size less critical. This holds for Informix.

Since the DB is on raw storage, the IO size can be any number of blocks. Ditto for direct IO which Informix also supports.

Where did you find 'block size'? onstat -b ?
Do you mean "database server page size"

The IO size may be several pages, notably blobpages. Many factors play a role, like lightweigth IO (60KB chunks?), extentsizes, chunks, logbuff,...

The sar output shows lots of disk.
You could have helped some by identifying DB disks.

There are 2 extra busy disk. They seem to run at an average of 16+ (512 byte) block/IO = 8KB+ (29027/1772, 25932/1493)

What Informix version? 10.0 11?

I suggest you spend some quality time with the IBM documentation first. Perhaps start wit the "IBM Informix Dynamic Server Performance Guide"

( http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.gsg.doc/gsg140.htm )

Good luck!
Hein van den Heuvel
HvdH Performance Consulting.

[RogerVI]

Thanks Dennis, Hein

The output of onstat -b shows 2048 buffer size

The sar output shows several disks. Almost all of disk are from DB (all of c20 and c22)
My informix version is 11.50

It means that, despite that Informix page is 2K, the sar sometimes shows 16K? What is the cause of this behavior ?

[Hein van den Heuvel]

>> the sar sometimes shows 16K? What is the cause of this behavior ?

Correct.

Informix know how to fill large buffers for teh log file writes, and it knows how to read in larger portions for tablescans, blobs and or a few more. Sounds reasonable and expected to me. Being more of an Oracle expert myself i can not offer up details, but the Documentation certainly indicates opportunities.

It is great to use the SAR an other systems stats to have a direction to investigate, but you really want to rely on your database statistics to exactly see why resources are consumed, and what to do about it, if need be.

Cheers,
Hein

[RogerVI]

Thanks for all