Estimating MySQL TCP Connections for 100 Writes/sec Over 5 Seconds

During a 2020 Tencent interview, a candidate was asked: if data is written to MySQL at a rate of 100 rows per second for 5 seconds, how many TCP connections will the program and MySQL establish?

Parameter Analysis

From a programming perspective, the answer depends on two main factors: MySQL's processing capability and the connection‑pool settings.

1. MySQL Processing Capability

If the load is normal, MySQL can handle 100 requests per second.

If the load is high, we assume it can only handle 50 requests per second.

Note: Even a modest MySQL instance (e.g., 1 CPU + 1 GB RAM) can comfortably process 100 inserts per second; only abnormal conditions or resource contention would reduce this capacity.

2. Connection Pool

A connection pool enables connection reuse. Without a pool, each MySQL interaction creates a short‑lived connection that is closed immediately. In high‑concurrency scenarios, repeatedly creating connections is costly, so long‑lived connections are kept in a pool for reuse.

The pool size is controlled by several parameters, typically supported by most client drivers:

maxConnections : Upper limit of simultaneous connections.

maxIdleConnections : Maximum number of idle (unused) connections kept in the pool.

maxIdleTime : How long an idle connection remains in the pool before being closed (commonly 10‑20 seconds).

minIdleConnections : Minimum number of idle connections; the pool creates new ones when the count falls below this value.

initialSize : Number of connections created at pool startup (often set to minIdleConnections).

The three parameters highlighted in the interview—maxConnections, maxIdleConnections, and maxIdleTime—determine the pool’s behavior and the total number of TCP connections.

Orthogonal Parameter Analysis

Assuming maxConnections and maxIdleTime are sufficiently large, the remaining variables are MySQL’s processing capacity and the maxIdleConnections (i.e., the number of long‑lived connections). We consider four scenarios:

Sufficient processing and full reuse : With 100 req/s, maxIdleConnections set to 100, and MySQL handling 100 req/s, exactly 100 connections are established.

Insufficient processing, large idle pool : With 100 req/s, maxIdleConnections = 100, but MySQL only processes 50 req/s. The pool reuses 50 idle connections each second, illustrated in the diagram below.

Sufficient processing, small idle pool : With 100 req/s and maxIdleConnections = 50, MySQL can handle all requests, but only 50 connections can stay idle. The total connections become 100 + 4 × 50 (accounting for short‑lived connections).

Insufficient processing, small idle pool : With 100 req/s, maxIdleConnections = 50, and MySQL processing only 50 req/s, the situation mirrors scenario 2; analysis follows the same pattern.

Detailed per‑second breakdown (first 5 seconds) shows how the pool fills, reuses, and creates new connections, illustrated with GIFs.

Conclusion

This seemingly simple interview question actually probes the candidate’s understanding of MySQL connection‑pool mechanics, including how processing capacity and pool parameters interact to determine the number of TCP connections.

It tests both clear logical thinking in a real‑world scenario and knowledge of fundamental database connection concepts.