[IQUG] Slow fetching between two identical IQ servers

Mark Mumy markdmumy at gmail.com
Thu Nov 16 08:56:22 MST 2017


I usually use this for application segregation.  Usually, we would use a different IP, thus network path, for different uses like public vs private networks.  By default, IQ will open the port on all network interfaces so that it is visible everywhere.

Mark

> On Nov 16, 2017, at 09:50, Mei, Richard <Richard.Mei at bmo.com> wrote:
> 
> 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] 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 <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 <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 <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 <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 <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 <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 <http://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 <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/d33970f3/attachment-0001.html>


More information about the IQUG mailing list