One of the nice features is the throttling mechanism for queries to the backends.
Imagine you have a very active system and applications are executing queries at very high rate, which is not so unusual nowadays. If just one of the queries slows down you could easily end up with many active sessions running the same query. Just one problematic query could cause high resource usage and general slowness.
Usually, DBA is called but DBA cannot modify a query, disable problematic application, or change database model without detailed analysis.
But ProxySQL could help.
Using ProxySQL we could delay execution of the problematic queries.
Yes, specific application request would still have a problem, but we would avoid general problem/downtime and "buy" some time for the fix.
Let's simulate such a situation in the test environment.
Run benchmark test using sysbench.
NUM_THREADS=1
TEST_DIR=/usr/share/sysbench/tests/include/oltp_legacy
sysbench \
--test=${TEST_DIR}/oltp_simple.lua \
--oltp-table-size=2000000 \
--time=300 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=sbtest \
--mysql-password=sbtest \
--mysql-port=3307 \
--mysql-host=192.168.56.25 \
--mysql-engine-trx=yes \
--num-threads=$NUM_THREADS \
prepare
sysbench \
--test=${TEST_DIR}/oltp_simple.lua \
--oltp-table-size=2000000 \
--time=180 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=sbtest \
--mysql-password=sbtest \
--mysql-port=3307 \
--mysql-host=192.168.56.25 \
--mysql-engine-trx=yes \
--num-threads=$NUM_THREADS \
run
Enable throttling mechanism and delay execution for all queries globally setting "mysql-default_query_delay=100".
ProxySQLServer> set mysql-default_query_delay=100; ProxySQLServer> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQLServer> SAVE MYSQL VARIABLES TO DISK;
Run test again and Check latency(ms).
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1774
write: 0
other: 0
total: 1774
transactions: 1774 (9.85 per sec.)
queries: 1774 (9.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.0942s
total number of events: 1774
Latency (ms):
min: 100.76 <<<<<<<<<<<<<<<<<
avg: 101.51 <<<<< Throttling
max: 129.17 <<<<<<<<<<<<<<<<<
95th percentile: 102.97
sum: 180083.66
Threads fairness:
events (avg/stddev): 1774.0000/0.00
execution time (avg/stddev): 180.0837/0.00
Disable throttling and reset ProxySQL counters.
ProxySQLServer> set mysql-default_query_delay=0; ProxySQLServer> LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; ProxySQLServer> select * from stats_mysql_query_digest_reset;
Check latency(ms).
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 641413
write: 0
other: 0
total: 641413
transactions: 641413 (3563.38 per sec.)
queries: 641413 (3563.38 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.0004s
total number of events: 641413
Latency (ms):
min: 0.19
avg: 0.28
max: 44.45
95th percentile: 0.43
sum: 179252.76
Threads fairness:
events (avg/stddev): 641413.0000/0.00
execution time (avg/stddev): 179.2528/0.00
Enable throttling for just a specific query using ProxySQL mysql query rules.
-- Find problematic query ProxySQLServer> select hostgroup,username,count_star, (count_star/(select Variable_Value from stats_mysql_global where Variable_Name='ProxySQL_Uptime')) as avg_per_sec, digest, digest_text from stats_mysql_query_digest order by count_star desc limit 10; +-----------+----------+------------+-------------+--------------------+----------------------------------+ | hostgroup | username | count_star | avg_per_sec | digest | digest_text | +-----------+----------+------------+-------------+--------------------+----------------------------------+ | 2 | sbtest | 641413 | 78 | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=? | +-----------+----------+------------+-------------+--------------------+----------------------------------+ 1 row in set (0.00 sec) -- Reset counters ProxySQLServer> select * from stats_mysql_query_digest_reset; +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ | hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ | 2 | sbtest | sbtest | | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=? | 641413 | 1601934890 | 1601935070 | 153023170 | 159 | 44349 | 0 | 214399 | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ 1 row in set (0.00 sec)
Insert mysql query rule and enable throttling just for a specific query.
ProxySQLServer> insert into mysql_query_rules(rule_id,active,digest,delay,apply) values (1,1,'0xBF001A0C13781C1D',100,1); Query OK, 1 row affected (0.00 sec) LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
Compare "min_time" between executions.
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1773
write: 0
other: 0
total: 1773
transactions: 1773 (9.85 per sec.)
queries: 1773 (9.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.0325s
total number of events: 1773
Latency (ms):
min: 100.78 <<<<<<<<<<<<<<<<<
avg: 101.53 <<<<< Throttling
max: 104.77 <<<<<<<<<<<<<<<<<
95th percentile: 102.97
sum: 180021.34
Threads fairness:
events (avg/stddev): 1773.0000/0.00
execution time (avg/stddev): 180.0213/0.00
ProxySQLServer> select * from stats_mysql_query_digest_reset; +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ | hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ | 2 | sbtest | sbtest | | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=? | 1773 | 1601935408 | 1601935588 | 179697522 | 100681 | 104195 | 0 | 594 | +-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+-----------+----------+----------+-------------------+---------------+ 1 row in set (0.01 sec)
0 Comments:
Post a Comment