ProxySQL Cheatsheet

proxysql-cheatsheet.md

ProxySQL Cheatsheet

Useful links

• https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
• https://www.slideshare.net/YoungHeonKim1/proxysql-pxcquery-routing-and-failover-test
• https://severalnines.com/database-blog/how-performant-is-your-proxysql-node

Common commands

• Check backend servers status (online,shunned etc)

select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;

• Check hostgroup configuration

select * from mysql_galera_hostgroups;

• Get a rundown of global variables

select * from global_variables;

• List proxysql servers

select * from proxysql_servers;

• List backend mysql servers

select * from mysql_servers;

• List mysql users

select * from mysql_users;

Query rules

Troubleshooting commands

• show 10 most executed queries, sorted by time

select count_star,sum_time,(sum_time/count_star)/1000 as average_time_ms,digest_text 
from stats_mysql_query_digest 
where count_star > 100 
order by average_time_ms desc limit 10;

• show 5 most executed commands of type $TYPE (e.g. SELECT, INSERT) etc

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time 
FROM stats_mysql_query_digest 
WHERE digest_text 
LIKE 'UPDATE%' 
ORDER BY sum_time DESC LIMIT 5;

Benchmarking

• read benchmark

sysbench /usr/share/sysbench/oltp_read_only.lua \
  --threads=64 \
  --tables=10 \
  --table-size=40000000 \
  --report-interval=5 \
  --rand-type=pareto \
  --forced-shutdown=1 \
  --time=300 \
  --events=0 \
  --percentile=95  \
  --mysql-user=mysqluser \
  --mysql-password=mysqlpass \
  --mysql-db=mysql-db \
  --mysql-storage-engine=INNODB \
  --mysql-host=127.0.0.1 \
  --mysql-port=6033 \
  --point-selects=25 \
  --range_size=5 \
  --skip_trx=on \
  {prepare|run|cleanup}

• write benchmark

sysbench /usr/share/sysbench/oltp_read_write.lua \
  --threads=64 \
  --tables=10 \
  --table-size=40000000 \
  --report-interval=5 \
  --rand-type=pareto \
  --forced-shutdown=1 \
  --time=300 \
  --events=0 \
  --percentile=95 \
  --mysql-user=mysqluser \
  --mysql-password=mysqluser \
  --mysql-db=randomdb \
  --mysql-storage-engine=INNODB \
  --mysql-host=127.0.0.1 \
  --mysql-port=6033 \
  --point-selects=25 \
  --range_size=5 \
  {prepare|run|cleanup}