Network

I have a query that we are running between 2 LPAR's on the same P6. Both systems are IDS 11.70.fc3x9 and AIX 6.1. The table contains about 3.2 million rows... database eisds01@dss_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_data with no log; 3+ minutes database entods@ods_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_junk with no log; 8 seconds If I do a similar query where both instances are on the same LPAR, the time is about 1.5 minutes. My development team doesn't think the cross server times are reasonable. Both of these LPARS have dual fiber channel adapters (1gb). I guess the biggest question is this a network issue, or is it informix related...and then what if any suggestions to make it faster... Thanks in advance ... Peter Logan Senior Database Administrator Phone: 616/878-8309

sounds to me like a resource sharing issue. i.e if one lpar has very little activity then this allows for other busier lpars on the frame to 'borrow' it's share of the cpu and memory resources. Usually the resource sharing will diminish once activity on the quite lpar picks up with respect to the other lpars. Alternatively, the slow lpar may be capped too low with respect to cpu and memory resources.

Both of these servers are configured similaryly .. it doesn't matter which way I go .. speed is the same .. It just seems to me that the network is the bottleneck ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> To: ids@iiug.org Date: 01/12/2012 01:41 PM Subject: Re: Network [25909] Sent by: ids-bounces@iiug.org sounds to me like a resource sharing issue. i.e if one lpar has very little activity then this allows for other busier lpars on the frame to 'borrow' it's share of the cpu and memory resources. Usually the resource sharing will diminish once activity on the quite lpar picks up with respect to the other lpars. Alternatively, the slow lpar may be capped too low with respect to cpu and memory resources. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

original post: have a query that we are running between 2 LPAR's on the same P6. Both systems are IDS 11.70.fc3x9 and AIX 6.1. The table contains about 3.2 million rows... database eisds01@dss_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_data with no log; 3+ minutes database entods@ods_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_junk with no log; 8 seconds If I do a similar query where both instances are on the same LPAR, the time is about 1.5 minutes. My development team doesn't think the cross server times are reasonable. Both of these LPARS have dual fiber channel adapters (1gb). I guess the biggest question is this a network issue, or is it informix related...and then what if any suggestions to make it faster... Thanks in advance ... Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: Well you could set the env variable SRV_FET_BUF_SIZE to 32767 (which should be the max) and that should help as it should reduce the number of messages that need to go between the 2 servers to ship the rows back and forth. Also I think you would want to use the b=32767 sqlhosts option as well to increase the size of the network buffs, otherwise it could possibly still be broken up into multiple sends. Then on the AIX side assuming you are using VIO servers to virtualize the IO I think there have been some AIX APARS for network traffic that would be small packet sizes, which could also come into play here. I don't have the APAR number/s off the top of my head. However, I think the fastest method if you need to do cross server communication on LPARS is to have direct connects between the network adapters to take the VIO servers out of it. But without hardware changes/hardware config changes I'd try setting SRV_FET_BUF_SIZE and the b option of the sqlhosts and see how much performance that buys you. You'll need to bounce the servers for those changes to take affect, and you'd want the SRV_FET_BUF_SIZE set in the environment of the session bringing the engine online. Jacques Renaut IBM Informix Advanced Support APD Team

Any negatives to making these modifications? Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "JACQUES RENAUT" <jrenaut@us.ibm.com> To: ids@iiug.org Date: 01/12/2012 04:07 PM Subject: Re: Network [25913] Sent by: ids-bounces@iiug.org original post: have a query that we are running between 2 LPAR's on the same P6. Both systems are IDS 11.70.fc3x9 and AIX 6.1. The table contains about 3.2 million rows... database eisds01@dss_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_data with no log; 3+ minutes database entods@ods_lnk; select * from c3ods@ods_lnk:temp_event_item into temp test_junk with no log; 8 seconds If I do a similar query where both instances are on the same LPAR, the time is about 1.5 minutes. My development team doesn't think the cross server times are reasonable. Both of these LPARS have dual fiber channel adapters (1gb). I guess the biggest question is this a network issue, or is it informix related...and then what if any suggestions to make it faster... Thanks in advance ... Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: Well you could set the env variable SRV_FET_BUF_SIZE to 32767 (which should be the max) and that should help as it should reduce the number of messages that need to go between the 2 servers to ship the rows back and forth. Also I think you would want to use the b=32767 sqlhosts option as well to increase the size of the network buffs, otherwise it could possibly still be broken up into multiple sends. Then on the AIX side assuming you are using VIO servers to virtualize the IO I think there have been some AIX APARS for network traffic that would be small packet sizes, which could also come into play here. I don't have the APAR number/s off the top of my head. However, I think the fastest method if you need to do cross server communication on LPARS is to have direct connects between the network adapters to take the VIO servers out of it. But without hardware changes/hardware config changes I'd try setting SRV_FET_BUF_SIZE and the b option of the sqlhosts and see how much performance that buys you. You'll need to bounce the servers for those changes to take affect, and you'd want the SRV_FET_BUF_SIZE set in the environment of the session bringing the engine online. Jacques Renaut IBM Informix Advanced Support APD Team ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

original post Any negatives to making these modifications? Peter Logan Senior Database Administrator Phone: 616/878-8309 Response: There shouldn't be any for SRV_FET_BUF_SIZE. All that controls is how big the SQLI layer tries to pack together for cross server results. The b= in the sqlhosts option would make each session that connects via the network protocol use more more virtual memory for the network buffers it uses. Since the default size of the network buffers is 4k, but that would increase them to 32k, and I think each session allocates 10 buffers. If you don't use the b=, the server to server would still send 32767 bytes worth of rows on each fetch, but that 32767 bytes would get split up into 4k messages. So you still reduce the number of times the request more rows message would go from server to server, which should help. But best improvement would be increasing both. Jacques Renaut IBM Informix Advanced Support APD Team

The network? Why? What are you exchanging using the network? On Thu, Jan 12, 2012 at 6:50 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Both of these servers are configured similaryly .. it doesn't matter which > way I go .. speed is the same .. It just seems to me that the network is > the bottleneck ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> > To: ids@iiug.org > Date: 01/12/2012 01:41 PM > Subject: Re: Network [25909] > Sent by: ids-bounces@iiug.org > > sounds to me like a resource sharing issue. i.e if one lpar has very > little > activity then this allows for other busier lpars on the frame to 'borrow' > it's > share of the cpu and memory resources. Usually the resource sharing will > diminish once activity on the quite lpar picks up with respect to the > other > lpars. > > Alternatively, the slow lpar may be capped too low with respect to cpu and > > memory resources. > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf303b40ff858b7a04b66ac290

Please ignore this email. I re-checked your post and I understand... On Fri, Jan 13, 2012 at 3:45 PM, Fernando Nunes <domusonline@gmail.com>wrote: > The network? Why? What are you exchanging using the network? > > On Thu, Jan 12, 2012 at 6:50 PM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > Both of these servers are configured similaryly .. it doesn't matter > which > > way I go .. speed is the same .. It just seems to me that the network is > > the bottleneck ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > From: "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> > > To: ids@iiug.org > > Date: 01/12/2012 01:41 PM > > Subject: Re: Network [25909] > > Sent by: ids-bounces@iiug.org > > > > sounds to me like a resource sharing issue. i.e if one lpar has very > > little > > activity then this allows for other busier lpars on the frame to 'borrow' > > it's > > share of the cpu and memory resources. Usually the resource sharing will > > diminish once activity on the quite lpar picks up with respect to the > > other > > lpars. > > > > Alternatively, the slow lpar may be capped too low with respect to cpu > and > > > > memory resources. > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf303b40ff858b7a04b66ac290 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3063e33744f16f04b66ac992