MaxScale Multiplexing and Caching – Part 4

Part 4 – Tuning MaxScale for Real Workloads

This multi-part series breaks down each section into easy logical steps.

Part 1 – Complete Guide for High-Concurrency Workloads

Part 2 – Understanding MaxScale Thread Architecture

Part 3 – Backend Sizing and Connection Pooling

Part 4 – Tuning MaxScale for Real Workloads

Part 5 – MaxScale Multiplexing

Part 6 – MaxScale Caching

Part 7 – Warnings & Caveats

Part 8 – Key Takeaways

---

Tuning MaxScale ensures it efficiently handles the expected workload while minimizing latency and resource consumption. Each parameter impacts how client connections are routed, processed, and pooled.

Variable	Description	Recommendation / Tuning Guidance
threads	Number of worker threads handling client connections	Align with number of CPU cores (or v-cores) to avoid contention. (MariaDB)
router_options	Options for routing logic (e.g., read/write split)	Enable when using read/write split; ensures queries route correctly. (MariaDB)
max_sescmd_history	How many session state–modifying commands (e.g., SET ...) MaxScale keeps in its history	Default is 50, but increase if your application does many state commands or uses many prepared statements. (MariaDB)
prune_sescmd_history	Whether to prune session command history when it exceeds max_sescmd_history	Leave true (default) in many cases — it reduces memory use but still allows reconnections. (MariaDB)
multiplex_timeout	When multiplexing, how long a client waits for a pooled backend connection	Default is 60 s; increase if clients are timing out waiting for a shared connection. (MariaDB)
idle_session_pool_time	How long a session must be idle before its backend connections can be pooled for reuse	Default -1s (pooling disabled). To enable connection sharing, set to 0ms or more. Be mindful: reusing connections needs session replay (sescmd history). (MariaDB)
persistpoolmax	Maximum number of pooled backend connections per routing thread	Set this based on expected concurrency. If using connection sharing, ensure this is large enough to hold idle connections. (MariaDB)
persistmaxtime	How long to keep an unused (pooled) backend connection alive before closing it	Default is 0s (disabled). Tune to balance keeping connections for reuse vs freeing idle ones. (MariaDB)
wait_timeout	Idle timeout for client sessions (MaxScale will drop them)	Default is 28800s (8 hours) in recent MaxScale versions. Lower it if you want MaxScale to close idle sessions earlier than the backend. (MariaDB)
net_write_timeout	Maximum duration a write to a client can stay buffered before closing the connection	Default 0s (disabled). Useful if client network is slow and you want to avoid unbounded buffering. (MariaDB)
query_classifier_cache_size	Max memory for MaxScale’s internal query-classifier cache	Increase this if you have many unique SQL statement patterns. Defaults to ~15% of system memory. (MariaDB)

Additional Tuning Tips:

• Start with these baseline values, then monitor MaxScale metrics (threads, connection pool usage, query latency).
• Use idle_session_pool_time to control how long idle connections are kept for reuse, balancing reuse vs. backend saturation.
• Adjust backend connections (persistpoolmax) per thread based on expected concurrency and server resources.

---

Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 | Page 8