Performance decreasing when the pool size of c3p0 incresed

Multi tool use
Multi tool use


Performance decreasing when the pool size of c3p0 incresed



My application is connecting to MySql database using c3p0 connection pool. Properties are given below


hibernate_c3p0_max_size=30
hibernate_c3p0_min_size=1
hibernate_c3p0_acquire_increment=1
hibernate_c3p0_idle_test_period=300
hibernate_c3p0_max_statements=0
hibernate_c3p0_timeout=360
hibernate_c3p0_initialpoolsize=1
hibernate_c3p0_maxpoolsize=30
hibernate_c3p0_minpoolsize=1



With this configuration we were able to use 80 users for test and after 45 minutes it crashed. So to improve the performance I increased the max and max_pool_size to 60 and min and min_pool_size to 15 but the result was shocking, application stopped responding at 5 minutes. Then I changed min and min_pool_size to 1 then application stopped responding at 8 minutes. Then I changed the max and max_pool_size to 30 and the application worked for 1 hour for 60 users, even though at 45 minutes it went to peak but managed somehow. Why the performance is going down when the pool size is increased??? What all things I should take care to get the max performance?



Below is the error it was giving when it crashed


com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@dbf2e57 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.GeneratedConstructorAccessor142.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2159)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
at sun.reflect.GeneratedConstructorAccessor115.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections
at sun.reflect.GeneratedConstructorAccessor142.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)





Too little information. How does it crash? Are you sure it’s the pool size (probably not). You might find it interesting to also read this article by the authors of HikariCP (currently the best JDBC pool implementation). github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
– Strelok
Jul 3 at 9:01





@Strelok updated with error log. Thanks for the link let me go through it.
– Anoop LL
Jul 3 at 9:25






Please post exception messages as text (in a code block), not as a screenshot. Make sure to include the full exception stacktrace. The problem could be something exceeding the MySQL configuration of maximum connections. It may also suggest that maybe you aren't correctly closing connections (which returns them to the pool).
– Mark Rotteveel
Jul 3 at 12:32






@MarkRotteveel in the log it just repeating the same and I do close the conections.
– Anoop LL
Jul 3 at 12:35





I would expect an underlying exception for the cause of the failure to connect, not just the "Attempted reconnect 3 times. Giving up" (I might be wrong though, I don't regularly use MySQL).
– Mark Rotteveel
Jul 3 at 12:38









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

6G9ayYaZGKU2Q0lsY4W5E9 VQBJNKxvSG oRfDBuRS e3k3vP4jm fo3ycc4K,44VZZyL8II,hsiD2 h BqX20nYWqV umY4,Yz XN9
-
NGNsJ8zCT nmLAL,RztJem HpCDVQPpZVR6MnrKPxNj9wTlB91qx kkGMSNoLAmqDwSJMREslho8EKUBBUy

Popular posts from this blog

PHP contact form sending but not receiving emails

PHP contact form sending but not receiving emails I have created a contact form using PHP to send the data. The form seems to send fine as there are no errors and success message appears, however I am not receving the emails into the designated inbox. I am using PHPMailer as I originally tried just using the php 'mail' command which I now understand is a bit hit and miss. I cannot see why I would not be receving the emails so I would be very grateful for any help that could be given. I'm quite new to PHP so please be patient with me :) <?php use PHPMailerPHPMailerPHPMailer; use PHPMailerPHPMailerException; $msg = ""; if (isset($_POST['submit'])) { require 'phpmailer/src/Exception.php'; require 'phpmailer/src/PHPMailer.php'; require 'phpmailer/src/SMTP.php'; function sendemail ($to, $from, $body) { $mail = new PHPMailer(true); $mail->setFrom($from); $mail->addAddress($to); $ma...
Read more

Do graphics cards have individual ID by which single devices can be distinguished?

Do graphics cards have individual ID by which single devices can be distinguished? I have several graphics cards of the same manufacturer and same model and I want to distinguish them not only by the pcie socket they reside in but by some individual value I can read from the hardware. Is there such value and if so what would it be called and how could I access it? Right now I'm using modern nvidia cards (10xx series) and nvidia inspector.but I'd prefer if there was any value that was also applicable to amd. I have programming and scripting knowledge. I am running Windows 10 1 Answer 1 This is a powershell example to pull information from video cards. The DeviceID is supposed to be unique but only if the manufacturer supports Windows Display Driver Model (WDDM). (I copied this directly from docs.microsoft.com) $strComputer = "." $colItems = get-wmiobject -class "Win32_VideoC...
Read more

Create weekly swift ios local notifications

Create weekly swift ios local notifications I can create daily notifications but not weekly ones using swift 3 ios 10 User Notifications framework. Daily works fine but when I add in the .weekday parameter it doesn't send me a notification. The code is: for i in 1 ... 7 { let center = UNUserNotificationCenter.current(); let content = UNMutableNotificationContent(); content.title = "Title"; content.body = "content"; content.sound = UNNotificationSound.default(); var dateComponents = DateComponents(); dateComponents.weekday = i; // hours is 00 to 11 in string format if (daynight == "am") { dateComponents.hour = Int(hours); } else { dateComponents.hour = Int(hours)! + 12; } // mins is 00 to 59 in string format dateCom...
Read more