ProxySQL — Performance & Connection Tuning

-- Number of ProxySQL worker threads (usually = CPU cores)
-- Change requires ProxySQL restart
UPDATE global_variables SET variable_value='8'
WHERE variable_name='mysql-threads';

-- Stack size per thread (bytes)
UPDATE global_variables SET variable_value='1048576'
WHERE variable_name='mysql-stacksize';

-- Poll timeout (ms) — how often threads check for new events
UPDATE global_variables SET variable_value='2000'
WHERE variable_name='mysql-poll_timeout';

-- Poll timeout when there are no clients (ms)
UPDATE global_variables SET variable_value='100'
WHERE variable_name='mysql-poll_timeout_on_failure';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Max frontend connections (app → ProxySQL)
UPDATE global_variables SET variable_value='65000'
WHERE variable_name='mysql-max_connections';

-- Timeout for client waiting for backend connection (ms)
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-default_query_timeout';

-- Backend connect timeout (ms)
UPDATE global_variables SET variable_value='3000'
WHERE variable_name='mysql-connect_timeout_server';

-- Max backend connect timeout (ms)
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-connect_timeout_server_max';

-- Retries when backend connect fails
UPDATE global_variables SET variable_value='5'
WHERE variable_name='mysql-connect_retries_on_failure';

-- Delay between retries (ms)
UPDATE global_variables SET variable_value='10'
WHERE variable_name='mysql-connect_retries_delay';

-- Max age of a backend connection (ms) — 0=no limit, 28800000=8hrs
UPDATE global_variables SET variable_value='28800000'
WHERE variable_name='mysql-connection_lifetime_ms';

-- Keep unused connections warm (ms delay before closing)
UPDATE global_variables SET variable_value='0'
WHERE variable_name='mysql-connection_delay_multiplex_ms';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Default query timeout (ms) — 1 hour
UPDATE global_variables SET variable_value='3600000'
WHERE variable_name='mysql-default_query_timeout';

-- Delay all queries by this many ms (0=disabled, useful for throttling)
UPDATE global_variables SET variable_value='0'
WHERE variable_name='mysql-default_query_delay';

-- Max query size ProxySQL will accept (bytes)
UPDATE global_variables SET variable_value='67108864'
WHERE variable_name='mysql-max_allowed_packet';

-- Handle LOAD DATA LOCAL INFILE
UPDATE global_variables SET variable_value='1'
WHERE variable_name='mysql-local_infile';

-- Forward client character set changes to backend
UPDATE global_variables SET variable_value='1'
WHERE variable_name='mysql-set_query_lock_on_hostgroup';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Set max backend connections per server
UPDATE mysql_servers SET max_connections=500
WHERE hostgroup_id=10 AND hostname='192.168.1.100';

UPDATE mysql_servers SET max_connections=1000
WHERE hostgroup_id=20;

-- Set max latency — server shunned if ping exceeds this (microseconds)
UPDATE mysql_servers SET max_latency_ms=1000
WHERE hostgroup_id=20;

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Check current connection usage
SELECT hostgroup, srv_host, srv_port,
       ConnUsed, ConnFree, ConnOK, ConnERR,
       MaxConnUsed, Latency_us
FROM stats.stats_mysql_connection_pool
ORDER BY ConnUsed DESC;

-- Give a more powerful replica more traffic
UPDATE mysql_servers SET weight=3 WHERE hostname='192.168.1.101' AND hostgroup_id=20;
UPDATE mysql_servers SET weight=2 WHERE hostname='192.168.1.102' AND hostgroup_id=20;
UPDATE mysql_servers SET weight=1 WHERE hostname='192.168.1.103' AND hostgroup_id=20;

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Verify weighted distribution in practice
SELECT hostgroup, srv_host, Queries
FROM stats.stats_mysql_connection_pool
WHERE hostgroup=20
ORDER BY Queries DESC;

-- Enable compression between ProxySQL and backend MySQL
UPDATE mysql_servers SET compression=1
WHERE hostgroup_id=20;

-- Enable compression support in ProxySQL
UPDATE global_variables SET variable_value='true'
WHERE variable_name='mysql-have_compress';

LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;