본문 바로가기
DB

ProxySQL 초기 설정

by 세계정보ㄱ 2023. 1. 18.
728x90
반응형

초기 구성

이 가이드에서는 ProxySQL의 기본 구성 요소를 단계별로 구성하는 방법을 설명합니다. 이 가이드에서는 사용자가 ProxySQL의 전체 아키텍처를 이해하고 있고 ProxySQL이 기본 구성으로 운영 체제에 이미 설치되어 있다고 가정합니다. ProxySQL의 내부는 모든 MySQL 명령줄 클라이언트를 통해 액세스할 수 있는 표준 SQL ProxySQL Admin 인터페이스를 사용하여 재구성할 수 있습니다(기본 포트 6032 사용 가능).:

$ mysql -u admin -padmin -h 10.10.0.1 -P6032 --prompt 'ProxySQL Admin> '

mysql_servers, mysql_replication_hostgroups 과 mysql_query_rules 테이블에 항목 구성이 비어 있는지 확인합니다.

ProxySQL Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

ProxySQL Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)

ProxySQL Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)

백엔드 추가

 

이 가이드의 목적을 위해, 3x MySQL 서버 백엔드가 구성됩니다. mysql_servers 테이블에 추가합니다. 원격 백엔드 MySQL 서버를 구성할 때 기본값이 아닌 경우(즉, 포트 넘버가 3306이 아닌 경우 )만 지정 하면 됩니다.:

ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.1',3306);
Query OK, 1 row affected (0.01 sec)

ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.2',3306);
Query OK, 1 row affected (0.01 sec)

ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.3',3306);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

참고: 백엔드 MySQL 서버는 read_only = 1 의 설정이 모든 복제본에서 구성되었습니다. ProxySQL은 read_only = 0으로 설정된 백엔드 인스턴스를 WRITER 인스턴스로 간주하므로 primary MySQL 서버(또는 다중 기본 복제의 경우 모든 primaries 서버)에만 설정해야 합니다. my.cnf 파일에 set global read_only = 1 로 올바르게 설정되었는지 확인해야 합니다. ProxySQL이 이 값을 기반으로 라우팅 결정을 합니다.

모니터링 구성

ProxySQL은 상태를 식별하도록 구성된 MySQL 서버 백엔드를 지속적으로 모니터링합니다. 백엔드를 모니터링하기 위한 자격 증명은 MySQL에서 생성되어야 하며 환경별 확인 간격과 함께 ProxySQL에서도 구성되어야 합니다.

 

MySQL에서 사용자를 생성하려면 PRIMARY에 연결하고 다음을 실행합니다.

mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

그런 다음 모니터 사용자의 자격 증명을 ProxySQL에 추가합니다.

ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

그런 다음 다양한 모니터링 간격을 구성합니다.

ProxySQL Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)

ProxySQL Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name                          | variable_value                                    |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history                  | 600000                                            |
| mysql-monitor_connect_interval         | 2000                                              |
| mysql-monitor_connect_timeout          | 200                                               |
| mysql-monitor_ping_interval            | 2000                                              |
| mysql-monitor_ping_timeout             | 100                                               |
| mysql-monitor_read_only_interval       | 2000                                              |
| mysql-monitor_read_only_timeout        | 100                                               |
| mysql-monitor_replication_lag_interval | 10000                                             |
| mysql-monitor_replication_lag_timeout  | 1000                                              |
| mysql-monitor_username                 | monitor                                           |
| mysql-monitor_password                 | monitor                                           |
| mysql-monitor_query_variables          | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status             | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS    |
| mysql-monitor_query_interval           | 60000                                             |
| mysql-monitor_query_timeout            | 100                                               |
| mysql-monitor_timer_cached             | true                                              |
| mysql-monitor_writer_is_also_reader    | true                                              |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)

테이블의 은 명령문 global_variables 테이블에 MySQL 모니터에 대한 변경 사항을 적용하기 위해 LOAD MYSQL VARIABLES TO RUNTIME 명령을 실행합니다. 다시 시작해도 구성 변경 사항을 유지하려면 SAVE MYSQL VARIABLES TO DISK도 실행해야 합니다.

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)

백엔드의 상태 확인

 

monitor구성이 활성화되면 ProxySQL Admin 의 데이터베이스 테이블에서 MySQL 백엔드의 상태를 확인합니다 .

ProxySQL Admin> SHOW TABLES FROM monitor;
+----------------------------------+
| tables                           |
+----------------------------------+
| mysql_server_connect             |
| mysql_server_connect_log         |
| mysql_server_ping                |
| mysql_server_ping_log            |
| mysql_server_read_only_log       |
| mysql_server_replication_lag_log |
+----------------------------------+
6 rows in set (0.00 sec)

각 검사 유형에는 전용 로깅 테이블이 있으며 각각 개별적으로 검사해야 합니다.

ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+----------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time | connect_error |
+-----------+------+------------------+----------------------+---------------+
| 10.10.0.1 | 3306 | 1456968814253432 | 562                  | NULL          |
| 10.10.0.2 | 3306 | 1456968814253432 | 309                  | NULL          |
| 10.10.0.3 | 3306 | 1456968814253432 | 154                  | NULL          |
+-----------+------+------------------+----------------------+---------------+
3 rows in set (0.00 sec)

ProxySQL Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+-------------------+------------+
| hostname  | port | time_start_us    | ping_success_time | ping_error |
+-----------+------+------------------+-------------------+------------+
| 10.10.0.1 | 3306 | 1456968828686787 | 124               | NULL       |
| 10.10.0.2 | 3306 | 1456968828686787 | 62                | NULL       |
| 10.10.0.3 | 3306 | 1456968828686787 | 57                | NULL       |
+-----------+------+------------------+-------------------+------------+
3 rows in set (0.01 sec)

여기서 주의할 점은 이것이 RUNTIME에 로드되기 전에도 구성된 mysql_servers 기준으로 connect 와 ping 모니터링이 이루어진다는 것 입니다. 이 접근 방식은 의도적입니다. 이러한 방식으로 프로덕션에 노드를 추가하기 전에 기본 상태 검사를 수행할 수 있습니다.

 

서버가 올바르게 모니터링되고 있고 정상인지 확인한 후 구성을 활성화합니다.

ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.2 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.3 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

MySQL 복제 호스트 그룹

 

 

클러스터 토폴로지 변경 사항은 ProxySQL에 구성된 MySQL 복제 호스트 그룹을 기반으로 모니터링됩니다. ProxySQL은 mysql_replication_hostgroups에 설정된 hostgroups 내의 서버의 read_only 값을 모니터링하는 것으로 복제 토폴로지를 이해합니다.

 

이 테이블은 기본적으로 비어 있으며 READER 및 WRITER 호스트 그룹 쌍을 지정하여 구성해야 합니다. 예를 들면 다음과 같습니다.

Admin> SHOW CREATE TABLE mysql_replication_hostgroupsG
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)

이제 호스트 그룹 1 또는 2에 구성된 모든 MySQL 백엔드 서버는 read_only 값에 따라 해당 호스트 그룹에 배치됩니다.

 

  • read_only=0 있는 경우,  호스트 그룹 1로 이동됩니다.
  • read_only=1 있는 경우, 호스트 그룹 2로 이동됩니다.

mysql_replication_hostgroups 에 설정한 복제 호스트 그룹 rumtime에 적재하기 위해서는 MySQL 서버에 사용 되는 것과 동일한 LOAD 명령을 사용합니다.  LOAD MYSQL SERVERS TO RUNTIME 명령은 mysql_servers 테이블  mysql_replication_hostgroups  테이블을 모두 처리합니다. 

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

read_only검사 결과는 monitor 데이터베이스  mysql_servers_read_only_log테이블에 기록됩니다.

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+-------+------------------+--------------+-----------+-------+
| hostname  | port  | time_start_us    | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 10.10.0.1 | 3306  | 1456969634783579 | 762          | 0         | NULL  |
| 10.10.0.2 | 3306  | 1456969634783579 | 378          | 1         | NULL  |
| 10.10.0.3 | 3306  | 1456969634783579 | 317          | 1         | NULL  |
+-----------+-------+------------------+--------------+-----------+-------+
3 rows in set (0.01 sec)

ProxySQL이 read_only서버의 값을 모니터링하고 있습니다.

또한 hostgroup1에서 read_only=1(독자)가 있는 서버를 이동한 위치에 hostgroup2를 생성했습니다.

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 2            | 10.10.0.2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 2            | 10.10.0.3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

마지막 단계로 구성을 디스크에 유지합니다.

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.00 sec)

MySQL 사용자

 

MySQL 서버 백엔드를 구성한 후 mysql_servers다음 단계는 mysql 사용자를 구성하는 것입니다.

이는 mysql_users테이블에 항목을 추가하여 수행됩니다.

Admin> SHOW CREATE TABLE mysql_usersG
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)

테이블은 처음에 비어 있으며 사용자를 추가하려면 를 지정하고 username기본 password구성 default_hostgroup을 위해 다음을 수행합니다.

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('stnduser','stnduser',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_users;                                                                                                                      
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root     |          | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
| stnduser | stnduser | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

 default_hostgroup 를 정의함으로써 우리는 사용자가 기본적으로 연결해야 하는 백엔드 서버를 지정합니다(즉, 이것은 특정 사용자로부터 오는 트래픽의 기본 경로가 됩니다. 경로를 다시 지정하도록 추가 규칙을 구성할 수 있지만 부재 시 모든 쿼리는 특정 호스트 그룹 으로 이동합니다. ).

 

By defining the default_hostgroup we are specifying which backend servers a user should connect to BY DEFAULT (i.e. this will be the default route for traffic coming from the specific user, additional rules can be configured to re-route however in their absence all queries will go to the specific hostgroup).

 

ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

ProxySQL은 이제 포트 6033(기본값)에서 트래픽을 처리할 준비가 되었습니다.


mysql -u stnduser -p stnduser -h 10.10.0.1 -P6033 -e"SELECT @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  3306  |
+--------+

이 쿼리는 기본인 포트 3306에서 수신 대기 중인 서버로 전송되었습니다. 이는 hostgroup1에 구성된 서버이고 사용자의 기본값이기 때문 stnduser입니다.

기능 테스트

Sysbench는 ProxySQL이 작동하는지 확인하고 시스템 성능을 벤치마크하는 데 유용한 도구입니다.

sysbench를 이미 구성했다고 가정하면( 여기에서 Sysbench 정보 참조 ) 다음 명령을 사용하여 로컬에서 ProxySQL에 대한 부하 테스트를 실행할 수 있습니다.

sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run

[ output omitted ]

참고: 이전 버전의 sysbench의 report-interval경우 제거하고 --db-ps-mode=disable추가해야 합니다.

sysbench --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=10.10.0.1 --mysql-port=6033 --db-ps-mode=disable run

[ output omitted ]

쿼리 규칙의 기반인 ProxySQL 통계

 

ProxySQL은 스키마에서 많은 실시간 통계를 수집하며 stats, 각 테이블은 ProxySQL의 동작 및 처리 중인 워크로드에 대한 특정 정보를 제공합니다.

ProxySQL Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| stats_mysql_query_rules        |
| stats_mysql_commands_counters  |
| stats_mysql_processlist        |
| stats_mysql_connection_pool    |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_global             |
+--------------------------------+
7 rows in set (0.00 sec)

stats_mysql_connection_pool

ProxySQL Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| hostgroup | srv_host  | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| 1         | 10.10.0.1 | 3306     | ONLINE       | 0        | 4        | 5      | 0       | 144982  | 7865186         | 278734683       |
| 2         | 10.10.0.1 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.2 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.3 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
4 rows in set (0.00 sec)

테이블 에는 stats_mysql_connection_poolMySQL 백엔드와 연결 및 전체 트래픽과 관련된 정보가 표시됩니다. 헬스 체크 결과에 따라 각 서버의 상태를 추적합니다. 정상 서버는 의 상태를 가지며 서버는 ONLINE일시적으로 제거되며 SHUNNED서버가 제거되면(완전히 제거되거나 호스트 그룹에서 이동됨) 내부적으로 OFFLINE_HARD로 표시됩니다.

 

 

stats_mysql_commands_counters

 

테이블 은 stats_mysql_commands_counters실행된 문의 유형 및 실행 시간 분포에 대한 자세한 정보를 반환합니다.

ProxySQL Admin> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN   | 1921940       | 7249      | 4214      | 2106      | 570     | 340     | 14       | 5        | 0         | 0         | 0      | 0      | 0       | 0        |
| COMMIT  | 5986400       | 7249      | 119       | 3301      | 1912    | 1864    | 44       | 8        | 1         | 0         | 0      | 0      | 0       | 0        |
| DELETE  | 2428829       | 7249      | 325       | 5856      | 585     | 475     | 5        | 3        | 0         | 0         | 0      | 0      | 0       | 0        |
| INSERT  | 2260129       | 7249      | 356       | 5948      | 529     | 408     | 6        | 2        | 0         | 0         | 0      | 0      | 0       | 0        |
| SELECT  | 40461204      | 101490    | 12667     | 69530     | 11919   | 6943    | 268      | 149      | 13        | 1         | 0      | 0      | 0       | 0        |
| UPDATE  | 6635032       | 14498     | 333       | 11149     | 1597    | 1361    | 42       | 16       | 0         | 0         | 0      | 0      | 0       | 0        |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)

 

stats_mysql_query_digest

stats_mysql_query_digest쿼리 정보는 백엔드당 쿼리 수, 쿼리당 응답 시간, 실제 쿼리 텍스트 및 모든 쿼리 유형의 고유 식별자인 쿼리 다이제스트를 제공하는 에서 추적됩니다 .

ProxySQL Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                                                          | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | sbtest             | stnduser | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=?                                     | 72490      | 1456971810 | 1456971830 | 17732590 | 23       | 58935    |
| 1         | sbtest             | stnduser | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249       | 1456971810 | 1456971830 | 9629225  | 20       | 121604   |
| 1         | sbtest             | stnduser | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          | 7249       | 1456971810 | 1456971830 | 6650716  | 26       | 76159    |
| 1         | sbtest             | stnduser | 0x5DBEB0DD695FBF25 | COMMIT                                                               | 7249       | 1456971810 | 1456971830 | 5986400  | 64       | 59229    |
| 1         | sbtest             | stnduser | 0xCCB481C7C198E52B | UPDATE sbtest1 SET k=k+? WHERE id=?                                  | 7249       | 1456971810 | 1456971830 | 3948930  | 44       | 47860    |
| 1         | sbtest             | stnduser | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     | 7249       | 1456971810 | 1456971830 | 3235986  | 22       | 24624    |
| 1         | sbtest             | stnduser | 0xE75DB8313E268CF3 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                | 7249       | 1456971810 | 1456971830 | 3211197  | 51       | 29569    |
| 1         | sbtest             | stnduser | 0x5A23CA36FB239BC9 | UPDATE sbtest1 SET c=? WHERE id=?                                    | 7249       | 1456971810 | 1456971830 | 2686102  | 23       | 27779    |
| 1         | sbtest             | stnduser | 0x55319B9EE365BEB5 | DELETE FROM sbtest1 WHERE id=?                                       | 7249       | 1456971810 | 1456971830 | 2428829  | 29       | 11676    |
| 1         | sbtest             | stnduser | 0x10634DACE52A0A02 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              | 7249       | 1456971810 | 1456971830 | 2260129  | 61       | 13711    |
| 1         | sbtest             | stnduser | 0x4760CBDEFAD1519E | BEGIN                                                                | 7249       | 1456971810 | 1456971830 | 1921940  | 30       | 39871    |
| 1         | information_schema | stnduser | 0x9DD5A40E1C46AE52 | SELECT ?                                                             | 1          | 1456970758 | 1456970758 | 1217     | 1217     | 1217     |
| 1         | information_schema | stnduser | 0xA90D80E5831B091B | SELECT @@port                                                        | 1          | 1456970769 | 1456970769 | 273      | 273      | 273      |
| 1         | information_schema | stnduser | 0x52A2BA0B226CD90D | select @@version_comment limit ?                                     | 2          | 1456970758 | 1456970769 | 0        | 0        | 0        |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
14 rows in set (0.00 sec)

주요 쿼리 정보를 필터링하여 간단한 쿼리로 핵심 트래픽 워크로드를 분석할 수 있습니다.

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 1  | 17732590 | 72490      | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 9629225  | 7249       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 6650716  | 7249       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 5986400  | 7249       | COMMIT                                                               |
| 1  | 3948930  | 7249       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 3235986  | 7249       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 3211197  | 7249       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 2686102  | 7249       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 2428829  | 7249       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 2260129  | 7249       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 1921940  | 7249       | BEGIN                                                                |
| 1  | 1217     | 1          | SELECT ?                                                             |
| 1  | 273      | 1          | SELECT @@port                                                        |
| 1  | 0        | 2          | select @@version_comment limit ?                                     |
+----+----------+------------+----------------------------------------------------------------------+
14 rows in set (0.00 sec)

제공된 정보에서 모든 트래픽이 hostgroup1의 기본 인스턴스로 전송된다는 것이 분명합니다. 이 워크로드를 hostgroup2의 복제본으로 다시 라우팅하려면 쿼리 규칙이 필요합니다.

 

MySQL 쿼리 규칙

쿼리 규칙은 ProxySQL을 통과하는 트래픽을 제어하는 ​​매우 강력한 수단이며 다음 mysql_query_rules표에 구성됩니다.

Admin> SHOW CREATE TABLE mysql_query_rules\G
*************************** 1. row ***************************
       table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT,
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED,
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED,
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR)
1 row in set (0.00 sec)

상위 2개 쿼리를 복제본 hostgroup2로 보내고 나머지 모든 쿼리를 기본으로 보내도록 ProxySQL을 구성하려면 다음 규칙이 필요합니다.

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=?,2,1);
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)

이러한 쿼리 규칙(및 일반적인 쿼리 규칙)에 대한 핵심 사항:

  • 쿼리 규칙은 rule_id 순서대로 처리됩니다.
  • active=1 만 있는 규칙 처리
  • 첫 번째 규칙 예는 캐럿( ^) 및 달러( $)를 사용합니다. 이들은 패턴의 시작과 끝을 표시하는 특수 정규식 문자입니다. 즉, 이 경우 match_digest또는 match_pattern쿼리와 완전히 일치해야 합니다.
  • 예제의 두 번째 규칙은 캐럿이나 달러를 사용하지 않습니다. 일치 항목은 쿼리의 어느 위치에나 있을 수 있습니다.
  • 물음표는 정규식에서 특별한 의미를 갖기 때문에 이스케이프됩니다.
  • apply=1 현재 규칙이 일치하는 경우 더 이상 규칙을 평가하지 않아야 함을 의미합니다.

현재 규칙 구성은 에서 확인할 수 있습니다 mysql_query_rules. 참고: 이 구성은 아직 활성화되지 않았습니다.

 

ProxySQL Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='stnduser' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest                        | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=?$ | 2                     |
| DISTINCT c FROM sbtest1             | 2                     |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)

 

이 두 가지 특정 규칙의 경우 쿼리가 슬레이브로 전송됩니다. 쿼리와 일치하는 규칙이 없으면 default_hostgroup사용자에 대해 구성된 규칙이 적용됩니다(즉, 사용자 stnduser의 경우 1).

 

stats_mysql_query_digest_reset 은 이전 워크로드를 검색하고 stats_mysql_query_digest 테이블 의 내용을 지우고 자르도록 쿼리할 수 있습니다. 쿼리 규칙을 활성화하기 전에 변경 사항을 쉽게 검토하는 것이 좋습니다.

쿼리 규칙을 런타임에 로드하여 변경 사항을 활성화합니다.

 

ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

트래픽이 새 구성을 통과하면 stats_mysql_query_digest쿼리당 라우팅의 변경 사항이 반영됩니다.

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 2  | 14520738 | 50041      | SELECT c FROM sbtest1 WHERE id=?                                     |
| 2  | 3203582  | 5001       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 3142041  | 5001       | COMMIT                                                               |
| 1  | 2270931  | 5001       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 2021320  | 5003       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 1768748  | 5001       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 1697175  | 5003       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 1346791  | 5001       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 1263259  | 5001       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 1191760  | 5001       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 875343   | 5005       | BEGIN                                                                |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)

식별된 상위 2개 쿼리는 hostgroup2 복제본으로 전송됩니다.

집계된 결과는 표에서도 볼 수 있습니다.  stats_mysql_query_digest예를 들면 다음과 같습니다.

ProxySQL Admin> SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
+----+---------------+-----------------+
| hg | SUM(sum_time) | SUM(count_star) |
+----+---------------+-----------------+
| 1  | 21523008      | 59256           |
| 2  | 23915965      | 72424           |
+----+---------------+-----------------+
2 rows in set (0.00 sec)

쿼리 캐싱

 

ProxySQL의 일반적인 사용 사례는 쿼리 캐시 역할을 하는 것입니다. 기본적으로 쿼리는 캐시되지 않으며 mysql_query_rules에 정의된 cache_ttl 규칙을 설정(밀리초 단위)하여 활성화됩니다 .

 

복제본으로 전송된 모든 쿼리를 5초 동안 캐시하려면 이전 예에서 정의한 쿼리 규칙에서 cache_ttl을 업데이트합니다.

ProxySQL Admin> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
Query OK, 2 rows affected (0.00 sec)

ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQL Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; -- we reset the counters
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

트래픽이 새 구성을 통과한 후 stats_mysql_query_digest호스트 그룹 값이 "-1"인 캐시된 쿼리가 표시됩니다.

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 1  | 7457441  | 5963       | COMMIT                                                               |
| 1  | 6767681  | 5963       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 2  | 4891464  | 8369       | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 4573513  | 5963       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 4531319  | 5963       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 3993283  | 5963       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 3482242  | 5963       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 3209088  | 5963       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 2959358  | 5963       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 2415318  | 5963       | BEGIN                                                                |
| 2  | 2266662  | 1881       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0        | 4082       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0        | 51261      | SELECT c FROM sbtest1 WHERE id=?                                     |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)

쿼리 재작성

 

쿼리 텍스트를 일치시키기 위해 ProxySQL은 2가지 메커니즘을 제공합니다.

  • match_digest: SQL 쿼리 데이터(예: stats_mysql_query_digest.query_digest 에 표시된 대로 `SELECT c FROM sbtest1 WHERE id=?`)를 제거하는 쿼리의 다이제스트와 정규식을 일치시킵니다.
  • match_pattern: 쿼리의 실제 텍스트와 정규 표현식을 일치시킵니다(예: `SELECT c FROM sbtest1 WHERE id=2`

다이제스트는 항상 쿼리 자체보다 작습니다. 더 작은 문자열에 대해 정규식을 실행하는 것이 더 빠르며 (성능을 위해) match_digest 를 사용하는 것이 좋습니다 . 쿼리를 다시 작성하거나 쿼리 텍스트 자체와 일치시키려면 match_pattern을 사용합니다.

예를 들어:

 

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'stnduser','DISTINCT(.*)ORDER BY c','DISTINCT1',1);
Query OK, 1 row affected (0.00 sec)


ProxySQL Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=?$  | NULL                   | NULL            | 5000      | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 1     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT1       | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

이 구성으로 인해 다음 동작이 발생합니다.

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits  | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10      | ^SELECT c FROM sbtest1 WHERE id=?   | NULL                   | NULL            | 5000      | 1     |
| 4856  | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 0     |
| 4856  | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT1       | NULL      | 1     |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
728x90
반응형