How can I optimize IQ for a better I/O performance?

[corona_yao]

1) IQ version 16.0Sp0820, cluster contains two nodes, shared dbspace contains 4 dbfile, each dbfile is a independent raid6 (8+2), the size is 21T. memory is 400G.

2. use command like " "dd if=/dev/dm-1* of=/dev/null bs=512k skip=xxx count=10240" to test the max theoretical value of disk-read bandwidth

of each file is about 900MB/s.

3.run a join query using hash algorithm.  a query plan is generated. IQ 's read bandwidth only reached 200Mb/s.

  1).collect cpu usage using  "sar -P ALL 2 >> cpu.log".

  2)collect iostat using "iostat -x -m 2 | grep dm-1[3678]>>io.log".

  3)dm-13 , dm-16, dm-17, dm-18 are db files of a dbspace.

  4)test bandwidth of dm-13 , dm-16, dm-17, dm-18 : "dd if=/dev/dm-1* of=/dev/null bs=512k skip=xxx count=10240" 。

    bandwidth of each file is about 900MB/S.

  5)storage is ddn, infiniband.

  wonder why IQ only read 200MB/s each db file?

4.run a simple query against the first 100million rows of which table has1.5billion rows . Returned one column. the query results are redirected to the virtual disk. The disk read bandwidth reached about 300MB/s.

My question is how can we optimize the I/O performance from IQ side ? and Is that reasonable to compare with dd ?

If CUP and memory is not the bottleneck, then what caused the big iowait? why can't IQ reach higher r/w bandwidth?

Attached sar output(cpu.txt) and iostat output(io.txt) while the query with a hash algorithm. Also a query plan (please change xml to html)attached.

Thanks a lot.

Corona

cpu.txt.zip

io.txt.zip

DBA_L_21__20140901-175847_4.xml.zip

[former_member333181 · Accepted solution]

Hi Corona,

   First when customer uses "dd" command they are using /dev/null as output file, which is always misleading for testing the throughput.  In this "dd" test you are really testing reading of device and not writing to the device. IQ will do read as well as write operations. How many fibre channels are available to these 4 disks you mentioned for IQ? You should also check the IQ config as well as threads and other data along with what you collected. vmstat output will be also useful here.

Regards

Shashi

[markmumy · Accepted solution]

It is important to keep in mind I/O mechanics, too.  'dd' will do only sequential operations. IQ does mostly random operations.  Depending on the storage, it is quite common to see such a vast difference between random and sequential I/O operations.

I would go back to the storage team/vendor and ask what the theoretical limit is for an I/O pattern that is 80-95% random.

Based on the IO output, you have fast service times.  All in the 1-2 ms range which is really good.  The problem is that you have one drive, dm18, that seems to be waiting 100 ms or more between each IO operation.  My guess is that the random nature of IQ disk operations is causing major issues with the storage and that it is likely not tuned for random IO.

Mark

[corona_yao · Accepted solution]

Hi Shashi,

Thanks for your reply, I got the configuration file and create database statement from ct as below :

Configuration file:

-iqtc 120000   
-iqmc 120000
-iqlm 120000

-gp 32768

-ch 4096m
-cl 1024m   

-x tcpip{port=9999}

-o /sydata/hist1/hist1.svrlog
-oe /sydata/hist1/hist1.stderr

-iqmsgsz  100
-iqmsgnum 5

-on 100m

-gm 300
-gl all

-sf -external_procedure_v3
-n mpx_node_w3

Create database:

CREATE DATABASE '/sydata/hist1/hist1.db'

TRANSACTION LOG ON 'hist1.log'

CASE IGNORE

COLLATION 'UTF8BIN' PAGE SIZE 32768

IQ PATH 'SysMainDBF01.iq'

--IQ SIZE 40000

IQ PAGE SIZE 524288

--IQ RESERVE 12000

TEMPORARY PATH 'tmp.iqtmp'

TEMPORARY SIZE 100;

--TEMPORARY RESERVE 300

Thanks.

Best regards,

Corona

[former_member333181 · Accepted solution]

IQ config and create database command will not be useful here.

As Mark also pointed out IQ is not doing all sequential read as command like "dd" does. Along with randomness of I/O pattern IQ will also do write operation which are typically much slower than read operations as seen with "dd" command.

As per youe earlier iostat data, there seem to be only few disks and out of those one disk is getting heavy I/O processing time

Regards

Shashi

[corona_yao · Accepted solution]

Hi Mark,

Thanks a lot for your advice. It's really helpful.

Do you mean there is no choice to tune the I/O processing time for dm18 because of random nature of disk operations?

Do we have any documents to tune the I/O of IQ as you know?

Thanks.

Best regards,

Corona

[Former Member]

Which platform and storage system?  There are things you can tune 'em.  For example, your storage may have an upper limit on the number of outstanding IOPs allowed.  It's not just about throughput. 

jason