1. What is database connection?
Each time Rails app need to make a query to database, it'll use TCP protocol to make a connection to the database. TCP handshake happen and the result is a database connection.
2. Database connection problem
- One of the most simple way to use database connection is whenever app want to query to database it'll create a connection, then the connection will be destroyed after the query executed.
- The time each connection is created and destroyed adds up is quite large. So the engineers come up with the ideal the connection will not be destroy after use instead N connection will be created when the app start and this connection will be held waiting for some query to deliver.
- The time consume is reduced. But how many connection is the appropriate. Because some app need to handle large amount of queries in some certain time, the another time it only handle few requests each day (course registration software for example). If we create too much connections then it is waste of resources. In order to solve this problems, the engineers set a idle_timeout for each connection. If after idle_timeout the connection hasn't been used it will be destroyed.
- In case app need to handle large amount of requests (require more connection than it currently hold). The request will be queued and has to wait until a connection available. The default time is 5 seconds. After 5 seconds, it will raise Exception Timeout. There're 2 solutions for this, the first is increase number of connection in connection pool (recommend) the other is increase the Timeout (not recommend).
3. Config database connection in Rails
We can config number of connection in database.yml file
- pool: Number of max connection
- idle_timeout: The maximum time a connection can stay in connection pool without serving any request.
- checkout_timeout: The time out time that a request can be held, its default value is 5 seconds. After this time the request will be aborted and the app will raise Timeout error.