proxy sql

sql 서버 3대를 가지고 있는데 1대가 죽어도 서비스에 영향이 없게 하기 위해서 작업한 내용.

install

registry 설치

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

proxysql 설치 및 설정

yum install proxysql  -y

systemctl start proxysql-initial
systemctl stop proxysql-initial

# 상태 확인
systemctl enable proxysql

systemctl status proxysql
systemctl start proxysql

# systemctl stop proxysql
# systemctl restart proxysql

접속해서 설정 포트번호 주의

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
 
Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
 
Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)

DB ADD

hostgroup_id 1번에 3개의 노드를 모두 추가

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.0.11',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.0.12',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.0.13',3306);

SELECT * FROM mysql_servers;

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

접속 설정

편의상 루트로 했으나 어카운트를 따로 만드는게 좋다.

UPDATE global_variables SET variable_value='root' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='password' WHERE variable_name='mysql-monitor_password';
-- 주기저긍로 확인하는것을 2초로 함.
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

-- 관련 옵션들을 다 확인하자.
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

설정을 Runtime에 적용하고 disk에 백업하자.

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

3개의 서버에 health check를 하자.

SHOW TABLES FROM monitor;

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

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

에러가 나면 여기서 볼수 있다. 문제가 없을경우 다음으로 넘어간다.

실제 코드에서 사용할 유저를 추가하자.

password와 default_hostgroup이 중요하다.

편의상 root를 사용햇으나 다른 이름이어야한다.

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','password',1);
SELECT * FROM mysql_users;        

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
exit

proxysql에 접속해서 확인을 해보자.

포트가 바뀌였다. 위에서 사용한 포트는 설정시 사용 여기서는 실제로 사용되는 포트이다.

관리자 커맨드에서 나와서 다음처럼 해보자.

mysql -u root -ppassword -h 127.0.0.1 -P6033 -e "SELECT 1"; -- 포트번호 주의
mysql -u root -ppassword -h 127.0.0.1 -P6033 -e "SELECT @@hostname";

계속 시도하면 자꾸 바뀐다.

+------------+
| @@hostname |
+------------+
| node09     |
+------------+

+------------+
| @@hostname |
+------------+
| node13     |
+------------+

이제 코드에서 디비를 바라보던 아이피를 proxysql 아이피를 보게 바꾸면 된다. 그러면 이제 1대가 죽어도 문제없이 서비스가 지속된다.

Exporting configuration into file

설정을 cfg파일로 저장해보자.

SELECT CONFIG INTO OUTFILE /tmp/backup.cfg

SAVE CONFIG TO FILE  /tmp/backup.cfg

cat /tmp/filename.cfg

SELECT CONFIG FILE #/etc/proxysql.cnf 을 보여줌

mysql -padmin -uadmin -h127.0.0.1 -P6032 -e 'select config file' #/etc/proxysql.cnf 을 보여줌

Read/Write를 구분해보자.

현 프로젝트는 MMM 구조라 의미가 없지만 Read/Write를 구분해야할 경우가 일반적이다.

아래와 같은 방식이 있다.

  1. 포트로 read/write를 구분해서 트래픽을 나눠서 보내가
  2. regex로 트래픽을 나눠서 처리하기
  3. performace를 확인해서 특정 트래픽만 특정 서버로 보내기

Port를 이용

6401 6402 포트를 mysql-interface 를 만든다.

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
## save it on disk and restart proxysql
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;

add routing based on incoming port:

destination_hostgroup 이 10이나 20 두개로 나눠서 서버들을 등록하고 각 포트마다 한쪽 destination_hostgroup으로 보내준다.

select * from mysql_query_rules;

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,10,1), (2,1,6402,20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6401은 호스트 group id가 10 6402는 호스트 groupid 가 20

write는 10번으로 모으고 read 는 20번에 모아두고 나눠서 처리할수 있다. 근데 이건 별로같은데?

Limitation of read/write split based on incoming port
In the previous paragraph I wrote that people have often asked how to configure ProxySQL to use routing based on incoming port.
Although this is a valid approach sometimes, in my opinion it has a big drawback: the application needs to have built-in read/write split capabilities in order to distinguish between reads and writes.
Often this is not the case. Often an application only uses a connection endpoint, that of course turns out to be the MySQL master. If ProxySQL is used, this can accept all the traffic in a single port and can analyze the traffic to perform a read/write split based on the query type.
This is very convenient because it doesn’t requires any application changes.
Nonetheless, the main advantage is not the ability to route traffic without application changes. The main advantage is that the DBA has now the tool to control the traffic sent to the database. The DBA is the one that gets paged in the middle of the night because the DB server is overloaded, when there are no developers around, and making changes in the application is not an option: he now has the option to control the traffic.

Basic read/write split using regex

정규식으로 이걸 이용해서 처리해보자.

  • 기본트래픽은 노드그룹 10번으로 (write)
  • select for update 는 노드그룹 10번으로 (write)
  • selelct는 노드그룹 20번으로 보내자.(read)
DELETE FROM mysql_query_rules;
UPDATE mysql_users SET default_hostgroup=10; # by default, all goes to HG10

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; # if you want this change to be permanent

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
  (1,1,'^SELECT.*FOR UPDATE$',10,1),
  (2,1,'^SELECT',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

select update가 먼저 넣는게 의미가 있을라나?

그런데 문서에 DO NOT USE THE ABOVE EXAMPLE IN PRODUCTION 이렇게 잇네..왜그럴가?

Read/write split using regex and digest

mysql 트래픽을 한곳으로 몰아서 내용을 다 적은후 통계를 내서 필요한 내용만 다른 노드그룹으로 보낼수 있음

  1. configure ProxySQL to send all the traffic to only one MySQL node, the master (both writes and reads)
  2. check in stats_mysql_query_digest which are the most expensive SELECT statements
  3. determine which expensive statements should be moved to reader nodes;
  4. configure mysql_query_rules (create rules) to send only expensive SELECT statements to the readers
-- Find the top 5 queries based on total execution time:
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

-- Find the top 5 queries based on count:
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

-- Find the top 5 queries based on maximum execution time:
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

-- Find the top 5 queries ordered by total execution time, and with a minimum execution time of at least 1 millisecond:
SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;

-- Find the top 5 queries ordered by total execution time, with an average execution time of at least 1 second. Also show the percentage of the total execution time
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;

-- Find the top 5 queries ordered by total execution time, with an average execution time of at least 15 milliseconds. Also show the percentage of the total execution time:
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

결론이 나면 digest를 찾아서 20번 그룹에 넣는다.

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',20,1);

-- select count는 20번 그룹으로 

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT COUNT%' ORDER BY sum_time DESC;

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT COUNT\(\*\)',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

이러면 특정한것만 다른 노드그룹으로 보낼수 있다.

teamsmiley's profile image

teamsmiley

2020-11-09 00:00

Read more posts by this author