Is creating multiple MySQL database connections wasteful?

Question

MySQL

I generate a large number of data from the database. Formation occurs cyclically. The synchronous version runs for about 6 seconds, after parallelization and improvements, it achieved only 1 second. But because of this, for each iteration, you have to open a new connection to the database, and then close it. Because reading from the database by several threads from one connector is impossible, an error is thrown. Should I change something or can I use this option that I came up with?

Answer 1

All solved this problem. It turned out that MySQL implicitly creates a pool of threads and keeps the connectors when it closes. Therefore, after closing and the next opening of the connector, time and resources are not wasted. It takes more time for the first discoveries, and then everything goes very, very well. The background thread then closes the pool for three minutes if it is not in use. If you want to have at least one of the threads in the pool, you can specify a minimum number of threads in the pool. For more information, see the MySQL technical documentation. Thanks to everyone who suggested, especially those who advised to look towards the connection pool.

Answer 2

Given the execution time of 1-6 seconds, the overhead of opening and closing connections can be neglected.

Answer 3

The pool of connections to the database and that's it. You take a connection for a request, complete the request, return the connection to the pool, that's all.

Answer 4

You can make a pool of threads (each thread has its own connection). After the thread is executed, do not terminate it, but wait until it is needed again. But at the same time, do not forget that the database server will not be able to process more in parallel than it has resources for it.