Re: Clarification on behaviour after pool exhaustion happen in tomcat jdbc pool 9.0.16
Sampath, On 8/9/21 01:45, Sampath Rajapakshe wrote: In our case, we know the reason for the pool exhausted behaviour, there are slow queries and also due to high TPS where pool is not enough. So we are expected to get pool exhaustion with current configurations. Ok. What we wanted to verify was the behaviour after pool exhaustion. Do the current executing connections continue their executions during pool exhausted duration? I would not expect the connection pool to actively kill connections unless explicitly configured to do so. Usually, connections that are "orphaned" will stay that way, "just in case". If you aren't seeing exceptions being thrown due to "connection is closed" or some such thing, you are probably okay as far as the long-running queries are concerned. Do you know in advance which queries will take a "long time"? Perhaps you'd like to use a different connection pool for those long-running queries -- one where the timeout is significantly higher. As per our observations, they do not, and connections are stuck without executing any queries until maxWait. is that the expected behaviour after pool exhaustion? Let's be clear what we mean when we say "connection". The only "connection" here that is relevant is the "connection to the database." It sounds like you mean "incoming HTTP connection" whose thread will stall if a DB connection is not immediately available from the pool. That may be true, but the "(HTTP) connection" isn't waiting for a DB connection; the request-processing thread is waiting for a DB connection. Do you mean "behavior of connections checked-out and used long-term" or do you mean "behavior of the pool when all connections are checked-out and we need a NEW one?" I assume the second question is what you are asking. When all the connections are being used, the pool usually stalls, meaning that your code will just sit there a wait (possibly forever) for a connection. To fix that, you'd have to adjust the configuration of the pool (e.g. add more possible connections, increase maxWait to avoid errors). You can also usually configure the pool to allow connections which are checked-out and not returned after a certain period of time ("abandoned" to use the Commons-Pool terminology) to be allowed to "leak" and replenish the pool. You didn't say which pool you were using, so I will assume you are using the default DB connection pool based upon Apache commons-dbcp2. Here is the documentation for that pool; you can use these configuration settings on your element in your web application's META-INF/context.xml file: https://commons.apache.org/proper/commons-dbcp/configuration.html I recommend looking at the "abandoned"-related configuration options. -chris On Sat, Aug 7, 2021 at 3:43 AM Christopher Schultz < ch...@christopherschultz.net> wrote: Sampath, On 8/6/21 08:37, Sampath Rajapakshe wrote: Hi All, In my local setup before pool exhaustion exception is thrown, all the connections seem to be in freezed and when checking processList in mysql, those connections are in sleep state and doesn't execute any queries. After waiting for maxWait period the pool exhausted exception gets thrown and seems to reset the connections and then the queries are getting processed as normally. > So, my question is, with pool exhausted scenarios, doesn't existing connections execute their queries during that time(maxWait) and try to resolve the exhausted behaviour by releasing those connections to idle queue automatically? When checking the JMX matrix during this pool exhausted time all the connections are in the active queue. https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/ If not, what i am experiencing is as expected behaviour where the system is stuck after pool exhaustion for the best case of maxWait? Most of the time I've seen this kind of behavior it's due to sloppy resource-management. -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: [OT] Other connection may not see updated date immediately
W, On 8/9/21 12:04, W wrote: Hi,I have a web application. It is a java-jsp-tomcat-mysql. It is working, but sometimes, it is slow. For each data update statement, it is not slow: the next jsp page shows promptly. But the next page does not see updated data. I wait a coupe seconds, refresh the page, the updated date shows. Each connection is from tomcat.DataSource, so I believe each jsp page may use a different database connection. So, mysql inside is slow. Anything I can do? I checked server cpu and memory. Cpu is 20%, memory is lower than 50%. Everything is running on one server machine, one tomcat, one mysql, runs on Ubuntu: 4.15.0-54-generic, Tomcat/9.0.16, mysql: 8.0.21. Any information would be appreciated. Thanks in advance. This is certainly an application issue, so I have marked this thread as "off-topic". That doesn't mean we can't help. Please confirm the following: 1. An initial request arrives on the server which executes a SQL DML query, which changes some data in your database. The transaction is committed. The response is sent to the client. 2. A second request arrives *after the completion of the first request* and queries the data which was changed in #1 above. The data retrieved from the database appears to have values which are "old" -- that is, the value that was expected before #1 above occurred. Some questions: * Are you sure that #2 happens fully after #1? How are you sure? * Are you absolutely sure you are querying the *same* data from the database? * Are you absolutely sure that there is no "step 1.5" where the data are changed *back* to their original values? How are you sure? * What is the "transaction isolation level" of your database connections? Do you execute your query in #2 within a transaction? -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Connector Port Issue
Anyone happen to know where in the server.xml file the attributes go for clientAuth and sslEnabledProtocols? I'm running Tomcat 10. Seems like I tried putting them under everything (Connector, SSLHostConfig, Certificate) but still get the warning in the logs: failed to set property. Thanks,ChrisOn Thursday, August 5, 2021, 03:37:54 PM EDT, Peter Kreuser wrote: Chris, > Am 05.08.2021 um 18:32 schrieb Rob Sargent : > > >> Caused by: java.lang.IllegalArgumentException: No SSLHostConfig >>element was found with the hostName [_default_] to match the >>defaultSSLHostConfigName for the connector [https-jsse-nio-9443] >> > The ssl-Options are not attributes on the connector, but the SSLHostConfig http://tomcat.apache.org/tomcat-10.0-doc/config/http.html#Common_Attributes http://tomcat.apache.org/tomcat-10.0-doc/config/http.html#SSL_Support Peter > Isn’t that the real issue? > > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org >
Re: [OT] Other connection may not see updated date immediately
On Wednesday, August 11, 2021, 07:00:22 AM PDT, Christopher Schultz wrote: W, On 8/9/21 12:04, W wrote:>> Hi,I have a web application. It is a java-jsp-tomcat-mysql. It is working, but sometimes, it is slow. For each data update statement, it is not slow: the next jsp page shows promptly. But the next page does not see updated data. I wait a coupe seconds, refresh the page, the updated date shows. Each connection is from tomcat.DataSource, so I believe each jsp page may use a different database connection. So, mysql inside is slow. Anything I can do?>> I checked server cpu and memory. Cpu is 20%, memory is lower than 50%.> Everything is running on one server machine, one tomcat, one mysql, runs on Ubuntu: 4.15.0-54-generic, Tomcat/9.0.16, mysql: 8.0.21.>> Any information would be appreciated. Thanks in advance. >This is certainly an application issue, so I have marked this thread >as>"off-topic". That doesn't mean we can't help. >Please confirm the following: >1. An initial request arrives on the server which executes a SQL DML>query, >which changes some data in your database. The transaction is>committed. The >response is sent to the client. The query is commited, otherwise I would not see updated data after I refresh the next page. >2. A second request arrives *after the completion of the first request*>and >queries the data which was changed in #1 above. The data retrieved>from the >database appears to have values which are "old" -- that is, the>value that was >expected before #1 above occurred. Yes. >Some questions: >* Are you sure that #2 happens fully after #1? How are you sure?Yes. Otherwise >the second page would not appear. >* Are you absolutely sure you are querying the *same* data from >the>database?Yes. >* Are you absolutely sure that there is no "step 1.5" where the data >are>changed *back* to their original values? How are you sure?Yes. >* What is the "transaction isolation level" of your database>connections? Do >you execute your query in #2 within a transaction?I did not set, it should be >default. REPEATABLE READ? I believe, correct me if I am wrong:tomcat.connection.commit() calls mysql.connection.commit(), Is it possible thatactually operating-system-file-writing not finished when the calls return? or there is a delay, or updating query haslower priority, either tomcat or mysql? The following connectioncall is a 'select' statement without delay, so 'select' statement executed before operating-system-file-writing finished. It sounds that you did not hear similar issues. Thanks. -To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.orgFor additional commands, e-mail: users-h...@tomcat.apache.org
Re: Connector Port Issue
Chris, > Am 11.08.2021 um 16:40 schrieb Chris Strickland > : > > > Anyone happen to know where in the server.xml file the attributes go for > clientAuth and sslEnabledProtocols? They goes in "protocols" and "certificateVerification" attributes of SSLHostConfig. Like this: > I'm running Tomcat 10. Seems like I tried putting them under everything > (Connector, SSLHostConfig, Certificate) but still get the warning in the > logs: failed to set property. > Thanks,ChrisOn Thursday, August 5, 2021, 03:37:54 PM EDT, Peter Kreuser > wrote: > > Chris, > >> Am 05.08.2021 um 18:32 schrieb Rob Sargent : >> >> >>> Caused by: java.lang.IllegalArgumentException: No SSLHostConfig >>> element was found with the hostName [_default_] to match the >>> defaultSSLHostConfigName for the connector [https-jsse-nio-9443] >>> >> > > The ssl-Options are not attributes on the connector, but the SSLHostConfig > > http://tomcat.apache.org/tomcat-10.0-doc/config/http.html#Common_Attributes > > http://tomcat.apache.org/tomcat-10.0-doc/config/http.html#SSL_Support > > Peter > >> Isn’t that the real issue? >> >> >> - >> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org >> For additional commands, e-mail: users-h...@tomcat.apache.org >>