IO Bound System tuning

Database
Enthusiast

IO Bound System tuning

Hi,

 

Our system is IO bound system.We have so many instance where io % goes beyond 100% in IO heatmap.Can somebody help we to tune this system.Where to start.Is dbql totalIO count is column i can consider and if this is high mean query has taken high IO and needs tuning.What is considered hih in a 4 node system.Is it 100000bytes?Please guide

6 REPLIES 6
Enthusiast

Re: IO Bound System tuning

Any inputs highly appriciated 

Teradata Employee

Re: IO Bound System tuning

TotalIOCount is "logical" requests. I would focus on ReqPhysIOKB and ReqPhysIO.

And even on an I/O bound system, tuning for CPU usage can be valuable. (Generally, doing more I/O  results in an increase in CPU as well.)

Enthusiast

Re: IO Bound System tuning

Thank you so much for the reply Fred..So here is what i see for 2 of main users 

username   ReqPhysIOKB    ReqPhysIO

SHAHSAI 4,983,797,636.00 11,534,642.00
SMITHJ7 4,719,400,788.00 6,714,072.00

What is significance of these columns.Looks similar but not able to relate numbers

If I want to have an alert check to catch users and send mail for tuning who using high IO what should be alert in viewpoint,what should be IO threshold.

 

 

Highlighted
Teradata Employee

Re: IO Bound System tuning

So the second user made a lot fewer requests, but the data blocks read/written totaled nearly as many KB.

It's hard to give a specific threshold. Usually I would look at recent history and pick something that would have sent a "reasonable" number of alerts for action (depending on resources available for tuning), then consider lowering the threshold as the number of alerts declines noticeably.

Enthusiast

Re: IO Bound System tuning

so ReqPhysIO is number of requests for IO?

One user is showing ReqPhysIOKB as 50,014,220,016.00 so he took 50TB?How can it be..

we are 50TB capacity and 4 nodes,some guidelines might be there as such where to start and how.Please help

Tags (2)
Teradata Employee

Re: IO Bound System tuning

Yes. ReqPhysIO is the physical count and TotalIOCount is the logical count (some of which may have been satisfied by file system cache without physical I/O).

That I/O volume is not impossible. For example,if a query requires multiple large spool files, the I/O can add up very quickly.

Also note that even for direct access to a single row, you still have to read the whole block; and if even one byte changes you must rewrite the entire data block. Of course, if there are multiple accesses to a particular block for the same SQL request, the database can optimize to only read & write the block once. But high-volume single-row operations can be expensive. Using techniques such as row partitioning (PPI) and column partitioning can be useful in reducing the number of blocks that must be read or written for certain queries.

 

Maybe someone else has a guideline or calculation to offer.