DB connections - what's the best practice?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’m new to databases in web environment and my questions are about managing DB connections:

  1. should I keep DB connection open till session end? the pro is performance, while the con is that I may loose data due to buffers etc.
  2. In a multi sessions environment, how DB conncetions are handled? I expect to have many sessions doing little work: about 1000 concurrent sessions, each doing a query every 5 min. Is there recommended mySQL configuration I should do?

Many thanks
Yuval

2

When managing connections, you want to try to establish the smallest number of connections to satisfy your needs and keep them open for as long as possible. There are two main patterns for handling database connections: thread local storage and connection pooling.

Thread local storage requires you to open a connection for each program thread you create and store the connection on that thread. The connection remains open for the lifetime of the thread. This makes sense when 1) your threads have a long lifetime, 2) operations performed by these threads almost always need to access the database. This pattern can also be applied to multiple processes each using a single thread.

Connection pooling establishes a fixed number of connections for use by multiple threads. Threads check out a connection, use it and then check it back in to the pool for another thread to use. The pool ensures that no two threads use the same connection at the same time. This makes sense when 1) your threads can access some shared memory (the pool) and 2) they do not need a database connection frequently.

Given that you only need to query very rarely, the connection pool is probably your best bet. Depending on the language/framework you’re using to write your web app, this may or may not be possible.