wso2 / product-apim

Welcome to the WSO2 API Manager source code! For info on working with the WSO2 API Manager repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
845 stars 785 forks source link

[Devportal] JDBC transaction lock exhausts pool and won't load APIs #11998

Open TomasTokaMrazek opened 2 years ago

TomasTokaMrazek commented 2 years ago

Description:

Our Devportal contains 56 published APIs. When a user with permission to all APIs logs into devportal and displays them at once (100 per page), devprotal stops working due to pool exhaustion to shared_db. Default size of 50 connections is instantaneously depleted. Connections wait idle in transaction on SELECT UM_ID FROM UM_PERMISSION WHERE UM_RESOURCE_ID=$1 AND UM_ACTION=$2 AND UM_TENANT_ID=$3.

My guess is that the transaction is released after the whole process completes. In other worlds for every API there is at least one transaction called from devportal, which is released after the whole operation is done. This appears to be after maxWait 60 seconds, when the rest of JDBC calls fail. Full stack trace is below.

Affected Product Version:

4.0.0

Environment details (with versions):

Stack Trace

[2021-11-14 18:14:01,649] ERROR - ApisApiServiceImpl Error while retrieving ratings for API 1de80a58-dd11-418c-bc17-f4ff85e73e8f
org.wso2.carbon.apimgt.api.APIManagementException: Failed to get API
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5896) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID(APIConsumerImpl.java:5866) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.UserAwareAPIConsumer.getAPIorAPIProductByUUID_aroundBody24(UserAwareAPIConsumer.java:126) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.UserAwareAPIConsumer.getAPIorAPIProductByUUID(UserAwareAPIConsumer.java:124) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.rest.api.store.v1.impl.ApisApiServiceImpl.apisApiIdRatingsGet(ApisApiServiceImpl.java:636) [classes/:?]
    at org.wso2.carbon.apimgt.rest.api.store.v1.ApisApi.apisApiIdRatingsGet(ApisApi.java:208) [classes/:?]
    at jdk.internal.reflect.GeneratedMethodAccessor396.invoke(Unknown Source) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201) [cxf-rt-frontend-jaxrs-3.3.7.jar:3.3.7]
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104) [cxf-rt-frontend-jaxrs-3.3.7.jar:3.3.7]
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) [cxf-core-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:296) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:220) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) [tomcat-servlet-api_9.0.34.wso2v1.jar:?]
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:271) [cxf-rt-transports-http-3.3.7.jar:3.3.7]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat_9.0.34.wso2v1.jar:?]
    at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:107) [org.wso2.carbon.identity.context.rewrite.valve_1.4.25.jar:?]
    at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:110) [org.wso2.carbon.identity.authz.valve_1.4.25.jar:?]
    at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:102) [org.wso2.carbon.identity.auth.valve_1.4.25.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:101) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:145) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690) [tomcat_9.0.34.wso2v1.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:57) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:126) [org.wso2.carbon.tomcat.ext_4.6.2.jar:?]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) [tomcat_9.0.34.wso2v1.jar:?]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat_9.0.34.wso2v1.jar:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat_9.0.34.wso2v1.jar:?]
    at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: org.wso2.carbon.apimgt.persistence.exceptions.APIPersistenceException: Failed to get API
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3026) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: org.wso2.carbon.apimgt.api.APIManagementException: Error while adding role permissions to API
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:493) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error! [https-jsse-nio-9443-exec-366] Timeout: Pool empty. Unable to fetch a connection in 60 seconds, none available[size:50; busy:50; idle:0; lastwait:60000].
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.callSecure(JDBCAuthorizationManager.java:1495) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:563) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:480) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: java.security.PrivilegedActionException
    at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.callSecure(JDBCAuthorizationManager.java:1485) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:563) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:480) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: java.lang.reflect.InvocationTargetException
    at jdk.internal.reflect.GeneratedMethodAccessor239.invoke(Unknown Source) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager$2.run(JDBCAuthorizationManager.java:1488) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.callSecure(JDBCAuthorizationManager.java:1485) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:563) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:480) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error! [https-jsse-nio-9443-exec-366] Timeout: Pool empty. Unable to fetch a connection in 60 seconds, none available[size:50; busy:50; idle:0; lastwait:60000].
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.addAuthorizationForRole(JDBCAuthorizationManager.java:1140) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:579) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at jdk.internal.reflect.GeneratedMethodAccessor239.invoke(Unknown Source) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager$2.run(JDBCAuthorizationManager.java:1488) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.callSecure(JDBCAuthorizationManager.java:1485) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:563) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:480) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [https-jsse-nio-9443-exec-366] Timeout: Pool empty. Unable to fetch a connection in 60 seconds, none available[size:50; busy:50; idle:0; lastwait:60000].
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:717) ~[jdbc-pool_9.0.35.wso2v1.jar:?]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:198) ~[jdbc-pool_9.0.35.wso2v1.jar:?]
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:132) ~[jdbc-pool_9.0.35.wso2v1.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.getDBConnection(JDBCAuthorizationManager.java:1349) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.addAuthorizationForRole(JDBCAuthorizationManager.java:1053) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:579) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.registry.core.secure.AuthorizeRoleListener.authorizeRole(AuthorizeRoleListener.java:156) ~[org.wso2.carbon.registry.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:570) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at jdk.internal.reflect.GeneratedMethodAccessor239.invoke(Unknown Source) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager$2.run(JDBCAuthorizationManager.java:1488) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.callSecure(JDBCAuthorizationManager.java:1485) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.user.core.authorization.JDBCAuthorizationManager.authorizeRole(JDBCAuthorizationManager.java:563) ~[org.wso2.carbon.user.core_4.6.2.jar:?]
    at org.wso2.carbon.apimgt.persistence.utils.RegistryPersistenceUtil.loadloadTenantAPIRXT(RegistryPersistenceUtil.java:480) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getRegistry(RegistryPersistenceImpl.java:3008) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.persistence.RegistryPersistenceImpl.getDevPortalAPI(RegistryPersistenceImpl.java:700) ~[org.wso2.carbon.apimgt.persistence_9.0.174.jar:?]
    at org.wso2.carbon.apimgt.impl.APIConsumerImpl.getAPIorAPIProductByUUID_aroundBody266(APIConsumerImpl.java:5870) ~[org.wso2.carbon.apimgt.impl_9.0.174.jar:?]
    ... 56 more
tharindu1st commented 2 years ago

can you share the data source configuration in deployment.toml file hiding sensitive information.

TomasTokaMrazek commented 2 years ago

PG driver version: 42.2.23

There is nothing interesting, no custom values are used.

    [database.apim_db]
    type = "postgre"
    url = "jdbc:postgresql://{{ .Values.wso2.deployment.am.database.hostname }}:{{ .Values.wso2.deployment.am.database.port }}/wso2_am400_apim_db?ssl=true&sslmode=require"
    username = "{{ .Values.wso2.deployment.am.database.username }}"
    password = "{{ .Values.wso2.deployment.am.database.password }}"

    [database.shared_db]
    type = "postgre"
    url = "jdbc:postgresql://{{ .Values.wso2.deployment.am.database.hostname }}:{{ .Values.wso2.deployment.am.database.port }}/wso2_am400_shared_db?ssl=true&sslmode=require"
    username = "{{ .Values.wso2.deployment.am.database.username }}"
    password = "{{ .Values.wso2.deployment.am.database.password }}"

Here's master-datasources.xml file.

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>

        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:./repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <maxActive>50</maxActive>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2_SHARED_DB</name>
            <description>Shared Database for user and registry data</description>
            <jndiConfig>
                <name>jdbc/SHARED_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://redacted.postgres.database.azure.com:5432/wso2_am400_shared_db?ssl=true&amp;sslmode=require</url>
                    <username>redacted</username>
                    <password>redacted</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <validationQuery>SELECT 1; COMMIT</validationQuery>
                    <testOnBorrow>true</testOnBorrow>
                    <maxWait>60000</maxWait>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <validationInterval>30000</validationInterval>
                    <maxActive>50</maxActive>
                 </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2AM_DB</name>
            <description>The datasource used for API Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://redacted.postgres.database.azure.com:5432/wso2_am400_apim_db?ssl=true&amp;sslmode=require</url>
                    <username>redacted</username>
                    <password>redacted</password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <validationQuery>SELECT 1; COMMIT</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>true</defaultAutoCommit>
                    <testOnBorrow>true</testOnBorrow>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
            </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2_MB_STORE_DB</name>
            <description>The datasource used for message broker database</description>
            <jndiConfig>
                <name>WSO2MBStoreDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:./repository/database/WSO2MB_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;MVCC=TRUE</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <maxWait>60000</maxWait>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <testOnBorrow>true</testOnBorrow>
                    <maxActive>50</maxActive>
                </configuration>
            </definition>
        </datasource>
    </datasources>

</datasources-configuration>

I tried to set maxActive to 200 for shared db, just to see how many connections are actually there. It spawned new connections gradually for about 5 seconds, the final number was 134 connections at 60 visible APIs. Eventually everything cleared up all at once in 30 - 60 seconds, might be related to maxWait.

Here's a csv from pg_stat_activity table. pg_stat_activity_202111161112.csv

I can simulate it every time, so feel free to ask whatever you need.