Mysql. Should I keep him alive or start a new connection before each transaction?

Question

I am making my first foray with mysql, and I have a doubt about how to handle the connections (s) that my applications have.

Now I open the connection and maintain it until I finish my program. I do mysql_ping () from time to time, and the connection starts with MYSQL_OPT_RECONNECT.

Another option (I can think of) would be to start a new connection before doing anything that requires my connecting to the database and closing it after I have finished with it.

What are the pros and cons of these two approaches? What are the “side effects” of a long-term relationship? What is the most used method for handling this?

Greetings;)

---

additional information

At this point, I keep the connection alive, and from time to time I check its status and reconnect if necessary.

Despite this, when there is some concurrency sequence with fast requests, I get the message "Server is gone", and after a while the connection is restored.

I have to wonder if this is a side effect of a long connection or if this is just a case of a poor mysql server configuration.

Any ideas?

Answer 1

In the general case, when opening a connection, a lot of overhead occurs. Depending on how often you expect this to happen, this might be fine, but if you are writing an application that executes more than just a few commands to run a program, I would recommend a connection pool (for server type applications ) or at least one or more connections from your stand-alone application, which should remain open for some time and be reused for several transactions.

Thus, you better control how many connections open at the application level, even before the database server is involved. This is a service that the application server offers, but you can also copy it quite easily if you want to reduce it.

In addition to efficiency considerations, the pool is also a good idea to prepare for demand peaks. When a lot of requests come in, and each of them tries to open a separate database connection - or, as you suggested even more (per transaction), you will quickly run out of resources. Keep in mind that each connection consumes memory inside MySQL!

You also want a non-root user to log in, because if you haven’t done this (I think that it is tied to the MySQL SUPER privilege), you may be blocked. MySQL reserves at least one connection for the administrator to troubleshoot, but if your application connects to this privilege, all connections will already be used when trying to manually turn off the fire.

Answer 2

If you are not worried that too many connections are open (i.e. more than 1000), you will leave this connection open. Connecting / reconnecting has overhead that slows down the work. If you know that you need a connection to stay open for a while, run this query instead of periodically ping:

 SET SESSION wait_timeout=# 

Where # is the number of seconds that idle connections open.

Answer 3

What application are you writing? If this is a website: keep it open. If it is an executable file, combine your connections (if necessary, most of the time when one singleton will be executed).