CODEWITHSUNDEEP
mysqlspring-bootspring-data-jpa
santocielo99
santocielo99

Reputation: 113

Spring Data JPA Too many connections

me and my collegues are working on a SpringBoot project, we work simultaneously and we all connect to the same mysql database. Trouble is that after a while some of us will no longer be able to connect, error is Too many connections, now I've spoken to the db administrator and he raised the number of max connections to 500 (it was something like 150 before this), but we still get the error, how can I fix this? the only configuration properties we use are these:

spring.datasource.url= ...
spring.datasource.username= ...
spring.datasource.password= ...
spring.datasource.hikari.maximum-pool-size=25

Maybe jpa opens a new connection every time he does a query but doesn't close it? I don't know I'm clueless here

EDIT: I've been asked to show some code regarding the interactions with the database so here it is:

@Autowired
private EmployeeDAO employeeDAO;
@Autowired
private LogDAO logDAO;
@Autowired
private ContrattoLavoroDAO contrattoLavoroDAO;

    @Override
    public void deleteEmployeeById(Long idEmployee, String username) {
        contrattoLavoroDAO.deleteContrattoByEmpId(idEmployee);
        employeeDAO.deleteById(idEmployee);
        LogEntity log = new LogEntity();
        LocalDateTime date = LocalDateTime.now();
        log.setData(date);
        log.setUser(username);
        log.setCrud("delete");
        log.setTabella("Employees");
        log.setDescrizione("l'utente " + username + " ha rimosso il dipendente con matricola " + idEmployee);
        logDAO.save(log);
    }

and here's the model for a DAO:

public interface ContrattoLavoroDAO extends JpaRepository<ContrattoLavoroEntity, Long> {

    @Modifying
    @Query(value = "DELETE contratto_lavoro, employee_contratto FROM contratto_lavoro" + " INNER JOIN"
            + " employee_contratto ON employee_contratto.id_contratto = contratto_lavoro.id_contratto" + " WHERE"
            + " contratto_lavoro.id_contratto = ?1", nativeQuery = true)
    public void deleteContrattoByEmpId(Long empId);
}

Upvotes: 0

Views: 6130

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81998

You set the Hikari maximum pool size to 25. Which is pretty high for a development environment.

But this shouldn't be a problem, because it's only the maximum, right?

Well Hikaris documentation says:

🔢minimumIdle This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize

🔢maximumPoolSize This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out. Please read about pool sizing. Default: 10

If I'm reading this correct it means each developer on your team opens 25 connections when they start the application. Another 25 when they start an integration test which starts a new application context. If the tests of the test suite have different configuration each set of configurations will have their own set of 25 connections in use.

The quick solution is to reduce the maximumPoolSize significantly for your development environment. I'd recommend 2. This is enough to allow for one normal transaction and one background process. It will throw exceptions if the application requires more connections, which is probably a good thing since in most cases it shouldn't.

Above that you might want to set the minimumIdle to 1 or 0, so the application doesn't consume shared resources if an application is just running because no one shut it down yet.

Mid term you probably want to get rid of having a central database for development. With the availability of TestContainers there really isn't a reason anymore to not have a local database for each developer. As a nice side effect it will ensure that all the schema update scripts work properly.

Upvotes: 2

Related Questions