DX Application Performance Management

Expand all | Collapse all

APMSQL - Broken connections

Jump to Best Answer
  • 1.  APMSQL - Broken connections

    Broadcom Employee
    Posted 08-17-2017 03:21 PM

    Hi Team,


    we are using APMSQL to make some querys and sometimes the connection between client JDBC (Squirrel, for example) and APMSQL it was broken.
    This broken connections stay in background execution and I can't make new querys because the clamp was reached.

    Has any way of kill this connections?

     

    Thanks,

    Vinícius Campelo



  • 2.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-17-2017 03:39 PM

    Dear APM Admins & Partners

      Have you experienced an issue like this? What sort of things did you do to resolve this? What do you recommend as a next step?

     

    Thanks

    Hal German

     

    If we do not hear back in several hours , APM Support and others may respond.



  • 3.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-18-2017 12:57 PM

    Dear Vinicus:

    There is an older  thread on this error What does this Error mean? I also seem to recall a recent KD but unable to find. I suggest opening a case to pursue the answeer. Then post back here the resolution to help others. You may also ask follow-up questions as needed

     

    Thanks

    Hal German



  • 4.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-21-2017 07:18 AM

    Hello Vinicius,
    Please use some tools like “netstat ano” (all systems) or https://docs.microsoft.com/en-us/sysinternals/downloads/tcpview (windows) to investigate open connections, their states (wait_close, established, ...) etc. As it will help to see the state of networking system before looking into problem – it may be configuration problem of system not products etc.
     
    If squirrel client detects the closed connection I will bet on not properly closed connection from initiating side (APMSql server) than sql rest api – it will keep squirrel waiting for close etc. It is easier to process to close open connections than binded connections. So checking log of APMSql server for anything unusual (exception etc.) can also help.

     

    If this is a test environment, you can try enable DEBUG logging in the APMSqlServer.properties

     

    log4j.rootLogger=DEBUG,console, logfile
    log4j.logger.org.teiid=DEBUG,teiidlog
    log4j.logger.com.ca.apm.server=DEBUG,console,logfile

     

    Increasing verbosity can help, but I will expect that disconnection should be in warn or at least info level, so maybe debug is too much for this kind of issue, also, I am not sure if teiid will not generate huge log in debug in your environment.

     

    I hope this helps,

    Regards,
    Sergio



  • 5.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-21-2017 02:08 PM

    Hi Team,
    Lynn_Williams and SergioMorales Thanks for your advices.
    I verified some TCP/IP Connections on S.O. .

    I used the "tcpkill" to finalize this connections and it's work fine.

    I will verify If in this case the problem is timeout on "Squirrel" or on configuration of S.O.

     

    Thank you very much.

     

    Regards,

    Vinícius Campelo



  • 6.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-17-2017 08:14 PM

    Hi Vinicius,

    It sounds like you could be hitting a problem between APMSQL and the MOM where the restapi sql connection limit/clamp is being hit . The MOM log would contain "AGCSmartstoreControllerImpl: Maximum number of connections reached: 5". This KB TEC1219476 has more details on how to change that value to be unlimited from default of 5:

    Using the new 10.5 APMSQL Server feature the MOM log shows "AGCSmartstoreControllerImpl: Maximum number of connections r… 

     

    Hope that helps

     

    Regards,

     

    Lynn



  • 7.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-17-2017 09:07 PM

    Hi Lynn, thanks for your answer.

    But in this case, my problem not is the clamp, my problem is the "zoombie connections".

    This connections makes my APM cluster more slow.

     

     If possible, I need kill this "zoombie connections".



  • 8.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-17-2017 10:22 PM

    Hi Vinicius,

    So when a SQL client -> APMSQL connection is broken the APMSQL still keeps the corresponding (zombie) restapi sql connection to the MOM and it never closes?

    There is the APMSQL HTTP connection read timeout value property com.ca.apm.sqlserver.ws.client.receiveTimeout (defaults to 300000 = 5 minutes) but that maybe just for the initial connection.

     

    Thanks

     

    Lynn



  • 9.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-18-2017 08:31 AM

    Hi Lynn, this is it.
    I tried to restart the APMSQL but when I verify in metric "*SuperDomain*|Custom Metric Host (Virtual)|Custom Metric Process (Virtual)|Custom Metric Agent (Virtual)|Enterprise Manager|Data Store|SQL API:Concurrent Invocations" the connections remain.

    I know that has some timeout because after hours, this connections close, but this timeout not is 5 minutes.

    In my opinion, the problem is in Rest SQL API of side of MOM.

     

    Thanks,

    Vinícius



  • 10.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-18-2017 09:18 AM

    Are you seeing any exceptions in the MOM log?



  • 11.  Re: APMSQL - Broken connections

    Broadcom Employee
    Posted 08-18-2017 09:29 AM

    Hi Matthew,
    When I have 5 zombie connections and I try connection, the message of connections reached appear:


    8/17/17 03:38:35.357 PM BRT [INFO] [btpool0-6662] [Manager.AppMap] AGCSmartstoreControllerImpl: Maximum number of connections reached: 5

     

    And I have many Error messages, but I think that is related to ATC, not to this problem...

     

    8/17/17 06:03:51.906 PM BRT [ERROR] [btpool0-6716] [Manager.AppMap] Internal Server Error happend during controller invocation!
    org.mortbay.jetty.EofException
    at org.mortbay.jetty.HttpGenerator.flush(HttpGenerator.java:789)
    at org.mortbay.jetty.AbstractGenerator$Output.flush(AbstractGenerator.java:568)
    at org.mortbay.jetty.HttpConnection$Output.flush(HttpConnection.java:1010)
    at org.mortbay.jetty.AbstractGenerator$Output.write(AbstractGenerator.java:650)
    at org.mortbay.jetty.AbstractGenerator$Output.write(AbstractGenerator.java:579)
    at com.fasterxml.jackson.core.json.UTF8JsonGenerator._flushBuffer(UTF8JsonGenerator.java:1982)
    at com.fasterxml.jackson.core.json.UTF8JsonGenerator.writeString(UTF8JsonGenerator.java:422)
    at com.fasterxml.jackson.databind.ser.impl.StringCollectionSerializer.serializeContents(StringCollectionSerializer.java:125)
    at com.fasterxml.jackson.databind.ser.impl.StringCollectionSerializer.serialize(StringCollectionSerializer.java:81)
    at com.fasterxml.jackson.databind.ser.impl.StringCollectionSerializer.serialize(StringCollectionSerializer.java:21)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:672)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:678)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:157)
    at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serializeContents(CollectionSerializer.java:149)
    at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:112)
    at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:25)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:672)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:678)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:157)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:672)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:678)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:157)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:130)
    at com.fasterxml.jackson.databind.ObjectMapper.writeValue(ObjectMapper.java:2444)
    at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:231)
    at org.springframework.http.converter.AbstractHttpMessageConverter.write(AbstractHttpMessageConverter.java:208)
    at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:205)
    at org.springframework.web.servlet.mvc.method.annotation.HttpEntityMethodProcessor.handleReturnValue(HttpEntityMethodProcessor.java:146)
    at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue(HandlerMethodReturnValueHandlerComposite.java:71)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:126)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:775)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:965)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:867)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:841)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)

    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:440)
    at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:326)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
    at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:943)
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
    at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
    at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:451)
    Caused by: java.net.SocketException: Broken pipe (Write failed)
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at org.mortbay.io.ByteArrayBuffer.writeTo(ByteArrayBuffer.java:368)
    at org.mortbay.io.bio.StreamEndPoint.flush(StreamEndPoint.java:122)
    at org.mortbay.jetty.HttpGenerator.flush(HttpGenerator.java:723)
    ... 59 more

     

    Thanks,

    Vinícius



  • 12.  Re: APMSQL - Broken connections
    Best Answer

    Broadcom Employee
    Posted 08-20-2017 05:01 PM

    Hi Vinicius,

    The KB TEC1219476 I mentioned above covers the "AGCSmartstoreControllerImpl: Maximum number of connections reached: 5" message:

    Using the new 10.5 APMSQL Server feature the MOM log shows "AGCSmartstoreControllerImpl: Maximum number of connections r… 

     

    I suspect the zombie connection timeout may be controlled by the tcp/ip stack settings on the OS. If you would like us to investigate that further and whether the connection build-up could be negatively impacting the overall performance of the cluster creating a support case is probably the best option.

    In the meantime what does this command show on the APMSQL server machine (assuming you are using default port 54321): netstat -na|grep 54321

     

    Hal's advice below covers the ATC related errors,

     

    Thanks

     

    Lynn