[IQUG] Rwee E: Slow fetching between two identical IQ servers

Louie, David David.Louie at blackrock.com
Thu Nov 16 09:08:46 MST 2017


This will allow us to deploy into production on our standard port and port the application users.  ( avoids app api changes )

From: Mei, Richard [mailto:Richard.Mei at bmo.com]
Sent: Thursday, November 16, 2017 10:50 AM
To: Louie, David <David.Louie at blackrock.com>; Baker, Chris <c.baker at sap.com>; Bhandari, Shashikant <shashikant.bhandari at sap.com>; Mark Mumy <markdmumy at gmail.com>
Cc: IQ Users Group <iqug at iqug.org>
Subject: RE: [IQUG] Slow fetching between two identical IQ servers

Never knew that we could start one IQ instance on multiple ports. Any reason for that?

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Louie, David
Sent: November-16-17 10:46 AM
To: Baker, Chris; Bhandari, Shashikant; Mark Mumy
Cc: IQ Users Group
Subject: Re: [IQUG] Slow fetching between two identical IQ servers

We will check.

We have the QA slower server up on multiple ports while the DEV (faster) server is not.

### Port
-x tcpip{port=4100;port=14110}


Would that be causing this slowness?

From: Baker, Chris [mailto:c.baker at sap.com]
Sent: Thursday, November 16, 2017 10:39 AM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>; Bhandari, Shashikant <shashikant.bhandari at sap.com<mailto:shashikant.bhandari at sap.com>>; Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: RE: [IQUG] Slow fetching between two identical IQ servers

Along with my other comments, if you are generating the flat file on the client side, check the NIC for errors and dropped packets.  Maybe there is a problem with the server’s network interface.

How many hops are reported by ping/traceroute?  Do the servers use the same DNS and can they see your client?  dbisql is a Java process and sometimes java will perform a reverse DNS lookup to resolve an address and may not get the same address if the client and server have different DNS resolutions (e.g. /etc/hosts) where the entries may have different FQDNs.

Chris

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sap.com_&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=t2UYjI9rr97NRM6djIaKuQmZ_SqY3EEa0Vx-aYV6c38&s=MB0qCzzI5EbzFN7AnDtUQS3eb22c4lh4m8dB9ipwt_k&e=>

https://sap.na.pgiconnect.com/I826572<https://urldefense.proofpoint.com/v2/url?u=https-3A__sap.na.pgiconnect.com_I826572&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=t2UYjI9rr97NRM6djIaKuQmZ_SqY3EEa0Vx-aYV6c38&s=vxbl4cfETQGS6JoJcUKcuppOaEDbonpFYnJ01SYlp2M&e=>
Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Louie, David
Sent: Thursday, November 16, 2017 10:02 AM
To: Bhandari, Shashikant <shashikant.bhandari at sap.com<mailto:shashikant.bhandari at sap.com>>; Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: Re: [IQUG] Slow fetching between two identical IQ servers

Will do.   Thanks Shashi.

From: Bhandari, Shashikant [mailto:shashikant.bhandari at sap.com]
Sent: Thursday, November 16, 2017 9:52 AM
To: Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>; Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: RE: [IQUG] Slow fetching between two identical IQ servers

Hi David,

   Did you checked the I/O speed on both the machines? Do you have detail “iostat”, “vmstat” outputs to check?

Regards

Shashikant Bhandari
Shashi.Bhandari at sapns2.com<mailto:Shashi.Bhandari at sapns2.com>
http://www.sapns2.com<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sapns2.com_&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=_6Qq6PIGijvdvmJiNRJRLDZXNkiSMEk-DVhoCas2DTo&s=G19EP4l5AdjbT1pop38aoInLU5EW0rcwoTX8SRpcpig&e=>
     Office: +1 301 896 1427
   Please consider the impact on the environment before printing this e-mail.

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Mark Mumy
Sent: Thursday, November 16, 2017 9:45 AM
To: David Louie <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: Re: [IQUG] Slow fetching between two identical IQ servers

So I checked and the packet size change came in with 16.0.  The max packet size in 15 is 16000.  The max packet size in 16 is 65536.

Can you also give more details on your process?  "16X longer to fetch data from IQ to a flat file” doesn’t tell much about what the query is, what tools are being used, how the data is being extracted and how it is being written to disk.

Mark

On Nov 16, 2017, at 08:33, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:

So I should change my packet size from –p 15872  to –p 65024?

From: Louie, David
Sent: Thursday, November 16, 2017 9:22 AM
To: 'Mark Mumy' <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: RE: [IQUG] Slow fetching between two identical IQ servers

Thanks Mark.

However the packet sizes are identical on both Dev and QA servers which does not explain the slow fetching.

I don’t think these options are the issues but saw them set in the slow QA server and not in the DEV.

I opened a case with SAP.


From: Mark Mumy [mailto:markdmumy at gmail.com]
Sent: Thursday, November 16, 2017 9:10 AM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: Re: [IQUG] Slow fetching between two identical IQ servers

The max packet size should be 64k now.  Thought that was in 15.4 too.

The options and errors make perfect sense.  Those are not IQ options but rather for dbisql and it’s behavior.
Mark

========================

Sent from my mobile device

On Nov 16, 2017, at 07:17, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
<image001.png>


It takes 16X longer to fetch data from IQ to a flat file in our QA server vs our DEV server.

Hardware same, nic card, same, RAM and CPU same.  Table is the same.

My packet size is 16000 ( max).

However I see that these two isql configs are set in the slower server.

Suspecting this may be the issue I want to turn them off or rest to default however cannot


QTREWD_QA_IQ.dlouie..2010.1> set option public.ISQL_SHOW_MULTIPLE_RESULT_SETS='OFF'
QTREWD_QA_IQ.dlouie..2010.2> go
SQL Anywhere Warning 120: 'ISQL_SHOW_MULTIPLE_RESULT_SETS' is an unknown option


QTREWD_QA_IQ.dlouie..2012.1> set option public.ISQL_PRINT_RESULT_SET='LAST';
SQL Anywhere Warning 120: 'ISQL_PRINT_RESULT_SET' is an unknown option
QTREWD_QA_IQ.dlouie..2013.1>

This is IQ 15.4.

Thanks
David



This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information.  Please refer tohttp://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2017 BlackRock, Inc. All rights reserved.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug<https://urldefense.proofpoint.com/v2/url?u=http-3A__iqug.org_mailman_listinfo_iqug&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=JK4-1yujrvHzaEGMP6nvOWLrZmT7atlaKS7m7voGi0M&s=W2XMzus00NyOwaAGZ7C4Jo1ZOWMJBrBoG93sn1rqpKE&e=>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171116/b10adc5b/attachment-0001.html>


More information about the IQUG mailing list