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.

[Former Member]

It's really not a matter of which is responsible.  They all are.

What is the connection speed, latency, etc from the server machine to the NAS? How is it connected to the NAS?  NFS?  CIFS?  iSCSI? 

What is the maximum sustained rate at which the NAS can write to the disks?

Which platform is the IQ server on?  Have you tuned the os, network and any file system options? If so, how?

Have you tuned the NAS?  If so, how?

Are you current on the patches/firmware for all components?

jason

[markmumy]

Adding to this....  If you want to see how much of an impact NAS has on your system and the process, change the output name of the TEMP_EXTRACT_NAMEx options to /dev/null, then rerun your extract.

Also, is NAS using the same network that the other users are?  That could be an issue since there is a finite bandwidth to share.

What do the extractions look like?  Are there where clauses and search arguments?  Joins?  These will impact the degree of parallelism that the extraction can use to resolve the rows.  You could end up with a high degree of parallelism on your extracts that is taking resources from other jobs and users.

Mark

[Former Member]

I would opt for writing to a local file system instead of /dev/null because it is a better representation of how much writing to the NAS system impacts performance on the IQ system.

On some NAS systems, performance will go down as the file(s) become larger.  a dd to the NAS system from the IQ box (and other boxes) of similar sized files may give a little more indication as to where the bottleneck may be.   Be warned that depending on the storage mechanism used by the NAS, reading from /dev/zero will be masked since block 8000 is the same as every other block so it may not actually write all blocks to physical disk.

On a side note, benchmarking tools may be worthwhile to run against the NAS system to obtain the optimal block size, packet size, etc.  There are dozens both commercial and open source.  One example would be Bonnie++ (http://en.wikipedia.org/wiki/Bonnie%2B%2B). 

jason

[jin-up_shim]

Thanks for reply, Jason

Regardless the connection speed, latency, etc of NAS and how they connect to their NAS, the fact is their NAS system is slow.

In fact, Why their NAS system is slow is not my concern.

My Major concern on this issue is how extracting operation on slow NAS can infulence other IQ operations's performance except simple infulence as an additional sql

For example, as follows.

Slow I/O of NAS system increases cpu's wait time and other operations cannot use cpu as much as normal situation. That's why other operations are getting slow.

Actually, When extracted a file on NAS, cpu wait and sys usage were high and user usage was low. And disk(SAN Storage for dbspaces) read/write decreased

It's just one example. Actually, another reason may cause this issue.

Tuning the os, network and any file system options is next step.

Thank you

[jin-up_shim]

Thanks for reply, Mark.

When I tried to extract the file on SAN Storage instead of /dev/null, no issue was occurred.

The extract query was very simple.

It has just one where condition and no join as follows.

select * from kfb_gct_mmly_dat

where bse_yymm like '2013%'

Like you said, their NAS is using the same network that other users are. But, lt looks not a issue.

Because, other operations that were slowed were server-side operations such as insert ~ select~, delete, etc

Please read my answer to the Jason.

Thank you.