Connections and pooling

MySQL Course · lesson 12 of 21 · 4 min read

Why a Java app opens ten connections and not one per request.

Open this lesson in the learning hub

Key points

  • Opening a MySQL connection costs a TCP handshake, authentication and session setup - milliseconds each.
  • A pool keeps a small set open and lends them out, so the cost is paid once at startup.
  • Pool size should be small: a few per CPU core usually beats a large pool.
  • A bigger pool does not make the database faster - it just moves the queue from your app into MySQL.
  • A connection held open by a long transaction is a connection nobody else can use.

Example

spring:
  datasource:
    url: jdbc:mysql://db:3306/shop
    hikari:
      maximum-pool-size: 10      # small on purpose
      connection-timeout: 3000   # fail fast rather than pile up
      max-lifetime: 1800000      # recycle before the DB times them out

A small pool is faster than a large one: it queues work where waiting is cheap.

Reading EXPLAIN NULL: the value that breaks comparisons

This is a reading copy. The full lesson — with the visual explainer, the interactive lab and a Run button for the code — lives in the MySQL Course course, and every lesson in it is listed on the MySQL Course contents page.