1

I'm using a pool to make queires to my db within a class , while i run this class in 16 instances (with limit of 100 connections per each - I don't know if this high number has anything to do with the error)

And suddenly after some time of running the application crashes with the error "Pool Closed" While the only place is use pool.end() is at the end of the apllciation.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Sw Sx
  • 77
  • 1
  • 10

1 Answers1

0

It's a rare MySQL server that can handle anything close to 1600 connections. If you have one of those servers, you know it: you're paying a small fortune for the hardware, and you probably have a team of database administrators looking after it.

There's a bit of a paradox here: more connections to production MySQL servers usually slow things down. Why? When many connections present similar queries to the server, they often contend with each other.

Vast pools also can conceal application problems like very slow queries and pool-connection leaks.

Edit Another application problem, your problem, is reusing a connection while a query is still active on it. MySQL reports this using Packets out of order messages. If you have a nested query setup like this pseudocode

  for each item in SELECT some query
     for each detail in SELECT some query WHERE something = (item value from outer query)

each query needs its own connection. If you put a new query onto your first connection while it's still delivering results from a prior, MySQL gets confused and says Packets out of order.

Pro tip Use each connection for just one query, then .release() it to the pool.

Your sudden Pool Closed may be the result of some kind of server fault. (You didn't give us many details, so that's only a guess.)

Try running your application with a pool maximum size of 5 on each instance. Make sure you set your pool options appropriately: waitForConnections must be true, and you should allow a high (or 0) poolLimit.

If you had the time and the appetite for complexity you could write a little nest of pool event handlers to monitor how long pool requests stay in their queue. If it turned out to be too long you could make the pool maximum size a little larger (6 rather than 5, for example).

Pro tip Debug with a very small number of connections in your pool. You'll find your bugs faster.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thank you for your answer! I've tried to decrease the connections , and now the error i get is "Packets out of order . Got 1 Expected 0" maybe i can increase the connection limit in the xammp config to solve that? My machine is 4core 16gb vps – Sw Sx Jan 06 '20 at 13:44
  • I don't have a defined connection I just use pool.query() , also there is no nested queries – Sw Sx Jan 06 '20 at 14:26
  • Ask another question. Show the code that makes the query and reads the result set. Yours is an application error that cannot be solved by bigger pools or servers. – O. Jones Jan 06 '20 at 14:31
  • 1
    Your tips and setting higher max packet helped me , thank you :) – Sw Sx Jan 06 '20 at 15:11