Agroal Sorry, acquisition timeout

[Taysay Shaguy]

Hello All, 

Please I have the following settings as my datasource settings  which is pointing to an Agroal implementation.

quarkus.datasource.url = jdbc:mysql://127.0.0.1:3306/****serverTimezone=UTC

quarkus.datasource.driver = com.mysql.cj.jdbc.Driver

quarkus.datasource.username = *******

quarkus.datasource.password = ********

quarkus.hibernate-orm.dialect=org.hibernate.dialect.MySQL5Dialect

quarkus.datasource.initial-size = 10

quarkus.datasource.min-size=5

quarkus.datasource.max-size=40

quarkus.datasource.acquisition-timeout=30

But I keep getting 

Sorry, acquisition timeout!

Please what am I doing wrong?

[Paul Carter-Brown]

Is your database definitely available on localhost port 3306?

What happens if you telnet 127.0.0.1 3306

Paul

--
You received this message because you are subscribed to the Google Groups "Quarkus Development mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to quarkus-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/quarkus-dev/464ea94e-3294-43cb-8cd4-1095cca0f92do%40googlegroups.com.

[Sanne Grinovero]

I would suggest first to check if you have a firewall running, and if
it's allowing connections. Do other tools connect to the same database
without issues?

Second step, double-check that you are using the right credentials in
the Quarkus configuration. Some databases will just let you go in
timeout rather than explicitly stating that you have the wrong
permissions / wrong password / etc...

[Taysay Shaguy]

Thank you so much Team,

Yes it actually works, I get this error at run time and unannounced. So it can work for a whole week and then Boom.

[Paul Carter-Brown]

Aha I think this is actually an issue with the connection pool being exhausted as opposed to not being able to create a connection to the DB. You have the pool set at max 40 and you could be either facing high load with many concurrent connections, or you could be leaking connections if you are getting them using an EMF.

What does your MySQL db show when you run this SQL:

show processlist;

It will return a list of connections. How many are there? Could it be over 40 when this happens?

If you have no leaks then I suggest increasing quarkus.datasource.max-size or even better find out why the concurrency is so high... perhaps poorly tuned SQL running slowly and causing large thread pools to deal with the traffic all waiting on the DB. Or could be lock contention on the DB. A thread dump at the time this happens would help diagnose.

Paul

On Mon, Jul 20, 2020 at 3:34 PM Taysay Shaguy <taysay...@gmail.com> wrote:

Thank you so much Team,

Yes it actually works, I get this error at run time and unannounced. So it can work for a whole week and then Boom.

--
You received this message because you are subscribed to the Google Groups "Quarkus Development mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to quarkus-dev...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/quarkus-dev/21ffc692-aebd-4fb3-9a44-7959abd0e0f0o%40googlegroups.com.

[Taysay Shaguy]

@Paul, Thank you very much. I am now reviewing my connections on the code level with the insights from the sleeping queries. funny I know the command but didn't not use it

On Monday, July 20, 2020 at 2:41:39 PM UTC+1, Paul Carter-Brown wrote:

Aha I think this is actually an issue with the connection pool being exhausted as opposed to not being able to create a connection to the DB. You have the pool set at max 40 and you could be either facing high load with many concurrent connections, or you could be leaking connections if you are getting them using an EMF.

What does your MySQL db show when you run this SQL:

show processlist;

It will return a list of connections. How many are there? Could it be over 40 when this happens?

If you have no leaks then I suggest increasing quarkus.datasource.max-size or even better find out why the concurrency is so high... perhaps poorly tuned SQL running slowly and causing large thread pools to deal with the traffic all waiting on the DB. Or could be lock contention on the DB. A thread dump at the time this happens would help diagnose.

Paul

On Mon, Jul 20, 2020 at 3:34 PM Taysay Shaguy <taysay...@gmail.com> wrote:

Thank you so much Team,

Yes it actually works, I get this error at run time and unannounced. So it can work for a whole week and then Boom.

--
You received this message because you are subscribed to the Google Groups "Quarkus Development mailing list" group.

To unsubscribe from this group and stop receiving emails from it, send an email to quark...@googlegroups.com.

[Taysay Shaguy]

@Paul and all. 

Thank you very much,  the show process list showed a couple of sleeping connections due to enclosed connections when I did this it looks like peace has come. 

I appreciate your support team. 

[Roman]

Hi all,

I have a similar issue with my quarkus application, but with a Postgres (as a service) database (azure). I'm not sure if it's exactly the same but I do recognize the "So it can work for a whole week and then Boom." 

Typical errors that I can randomly get are:

org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection 

ClusterManager: Error managing cluster: Failed to obtain DB connection from data source 'QUARKUS_QUARTZ_DEFAULT_DATASOURCE': java.sql.SQLException: Acquisition timeout while waiting for new connection: org.quartz.JobPersistenceException

Followed by:

Caused by: java.sql.SQLException: Acquisition timeout while waiting for new connection.

Do I understand correctly that a solution could be going into the database, listing processes and clearing out closed/sleeping connections ?

Kind regards,

Roman

Op woensdag 22 juli 2020 om 04:15:59 UTC+2 schreef taysay...@gmail.com: