When extract files on NAS storage, other operations slow down significantly

[jin-up_shim]

http://me2.do/5toO17tq

Hi

One of our customers have an performance issue on extracting big files ( about bigger than 20 Giga bytes ) on NAS storage.

They are not using NAS storage for dbspaces. They only use it for extracting files with temp_extract_name1 option.

They know using NAS storage gives them poor performance too.

They don't expect to extract files more rapidly, but extracting files affect on performance of other operations.

In spite of only one additional extracting operation compared to normal, other operations take 5 ~ 10 times longer.

I have two sp_iqsysmon's results and two nmon's monitoring reports.

One is for an issued day(4/23), the other is for a normal day(4/24)

From 6 P.M. to 8 P.M.

Click a headline URL to download files.

Which is responsible for this issue? IQ, OS or Storage?

Thank you.

[Gisung]

Hi,

Have you ever tested with named pipe when extracting files?

Please let me know it it's same as before.

==

Gi-Sung Jang

[jin-up_shim]

Hi,

I didn''t tried to with named pipe.

But I tried to on SAN storage. It didn't make an issue.

Thank you.

[markmumy]

I wouldn't expect a named pipe to increase performance given how small a buffer it has and how it generally stops and starts the data movement process.

Based on what you describe, Jason's comments are likely spot on.  When IQ is writing out to slow storage, OS level issues creep in.  IQ will open up the output file(s) for write.  During that process, the OS will handle all IO.  When the OS has to wait on storage, it goes into a wait state.  On some systems, that 'wait' may block the CPU core from doing any other processing.  This would then manifest itself in an IQ system that was able to use fewer resources because those CPU resources are all waiting on storage now.

Mark

[jin-up_shim]

You said, on some systems, the 'wait' may block the CPU core from doing any other processing.

How about on AIX?

Their IQ server's on AIX 6.1.4.3.

I have read some articles that even if cpu resources are in wait state, they can be used by any other process's request on AIX.

If so, in this case, why couldn't IQ use the waiting cpu resources?

Was it because the process (extract sql) causing wait and other requests( other sqls ) were runned by same process, iqsrv15?

[markmumy]

The issue is completely independent of IQ.  The multithreaded cores that are in the market today (p-series, Intel, SunOracle, etc) typically leverage the wait states for compute resources.  If a thread is waiting on IO, it will put another thread on the core for CPU processing.  Should that thread need IO, it will wait.  You will only get so many processor threads that can wait.  On IBM p-series it is 2, 4, or 8 threads per core (SMT2, SMT4, or SMT8) depending on the machine and chip type.

The one thing that no hardware will do is have a near infinite number of threads waiting on IO.  With the slowdown in extractions it will ripple into all other IQ activity because the hardware (this is way below the OS even) only has so many IO waiting slots. As you use those, the wait time increases.  And will continue to increase until you have no more IO wait slots and the CPUs are spinning constantly checking on IOs.

OK, that's a gross generalization, but it goes to illustrate the point that if you have slow enough storage it will ripple into every process on the  host, even those that should be on the CPU all the time.

I would look tp having a dedicate network for NAS, use another network protocol that is more efficient for large IO, use another filesystem that will do larger IO, extract data locally then copy it to the NAS.  Something that allows IQ to continue processing efficiently.

Mark