MCPDBWizard

Documentation  ·  MariaDB

Sessions and pooling on MariaDB

A generated MariaDB server is always pooled. Each tool call is one transaction, and after it the session is reset, so nothing one caller leaves behind reaches the next. This page is the MariaDB counterpart of Session management and Connection pooling, which are written for Oracle.

Finding the connections

A generated server’s connections do not carry a name. MariaDB has nothing a session can name itself with and change later: Oracle’s DBMS_APPLICATION_INFO has no counterpart, and a connection attribute is fixed when the connection opens. So a server’s connections are known by their login, their host and their database:

SELECT id, user, host, db, command, time, state, left(info, 80) AS statement
FROM   information_schema.PROCESSLIST
WHERE  user = 'your_login'
ORDER  BY time DESC;

Give each generated server a login of its own if you need to tell them apart, or to tell them from other applications. It is also the better security arrangement, since the login’s grants are what the server can reach.

The generator’s own connection, which reads your catalog, does send a connection attribute, program_name = MCPDBWizard. It is visible in performance_schema.session_connect_attrs only when performance_schema is on, and it is off by default.

statement is the statement running now, with ? where values are bound: a generated server never puts a value into SQL text. An idle pooled connection shows command = 'Sleep'.

To stop a call, KILL QUERY <id> stops its statement and the call fails; KILL <id> ends the connection, and the pool replaces it.

Pooling is always on

The console’s pooling switch is fixed on for MariaDB. Each concurrent call borrows its own DAO factory, with its own connection and its own prepared statements. The pool settings are the same as on Oracle, and each can be overridden by an environment variable of the same name without regenerating:

KeyMeaning
DAO_POOL_MAX_SIZECeiling. Also your effective concurrency limit.
DAO_POOL_MIN_IDLEHow many stay connected when idle.
DAO_POOL_MAX_WAIT_MSHow long a call waits for a free factory before “Server busy”.
DAO_POOL_IDLE_TIMEOUT_MSHow long an idle factory survives before it is closed.
DAO_POOL_ON_RETURNCOMMIT or ROLLBACK after a call that succeeded.

Sizing

Size the maximum against the server’s max_connections, not against how many CPUs you have. Each factory in the pool is one MariaDB connection, and so one server thread. max_connections is 151 by default. If you run several generated servers against one database, it is the total across them, plus everything else that connects, that has to fit.

MariaDB has no counterpart of Oracle’s per-session OPEN_CURSORS limit to run into. Its nearest is max_prepared_stmt_count (16,382 by default), which counts server-side prepared statements across every connection to the server. A generated server uses none by default: the driver prepares its statements on the client. If you add useServerPrepStmts=true to the connection URL, each statement in use takes one, but the reset after every call releases them and the driver prepares them again when they are next used, so a server holds only those of the calls in progress.

One call, one transaction

When a call succeeds, its transaction is committed (or rolled back, with DAO_POOL_ON_RETURN=ROLLBACK). When it fails, it is rolled back. A COMMIT inside a procedure commits the call’s work up to that point, and DDL commits implicitly, so a later failure cannot undo what came before it.

Session state lasts one call

A MariaDB session carries state that a commit or rollback does not clear: a package’s variables, user variables (@x) and temporary tables. On a pooled connection that state would pass from one caller to the next. So after every call the server resets the session (COM_RESET_CONNECTION), which clears all three, and the next caller starts from nothing. The reset costs about half a millisecond.

The consequence to know about: package state lasts one tool call, not the life of a connection. A package’s initialisation section runs again in each call that uses the package, and a package variable set in one call is not there in the next. A routine that relies on state carried between calls has to keep it in a table instead. (Before 2.0.39 the state did carry over, to whichever caller next got the same connection.)

Context Pinning values are user variables (@mcp_...), set at the start of each call, so they too last only for that call.

Runaway calls

DAO_QUERY_TIMEOUT_SECONDS caps how long one statement may run. Past it, MariaDB stops the statement with error 1969 (the wording differs: 11.x says “max_statement_time exceeded”, 12.3 and later “execution time limit … exceeded”), the call fails, and the connection goes back to the pool. A rate limit bounds how often calls start, not how long one runs, so this is the setting that protects the database.

One exception, measured on 13.0: a SLEEP() or BENCHMARK() inside a routine is cut short at the time limit without an error. The routine carries on from there and the call can succeed. A query the routine runs is still stopped with 1969.

Watching the pool

A pooled server logs a POOL-STATS line every 15 seconds, which the Runtime page reads. The same numbers are Prometheus gauges under mcpdbwizard_mcp_pool_* if you have metrics on. Server busy: all database connections are in use means the pool is at its ceiling: load, not a fault.