ProxySQL 읽기/쓰기 분할
읽기/쓰기 분할은 가장 일반적으로 사용되는 쿼리 라우팅 유형 중 하나이며 가장 일반적으로 사용되는 다른 쿼리 라우팅 구현은 샤딩입니다. 보고 쿼리를 특정 복제본으로 라우팅하는 것과 같은 쿼리 라우팅에 대한 다른 많은 사용 사례가 있지만 읽기/쓰기 분할 및 샤딩이 가장 일반적입니다.
다른 포트를 사용하여 읽기/쓰기 분할
MySQL용 표준 TCP 로드 밸런서를 사용하는 경우 일반적으로 두 개의 개별 포트에서 수신 대기하도록 구성합니다. 각 포트는 엔드포인트 역할을 하며 하나는 쓰기용이고 다른 하나는 읽기용입니다. 유사한 접근 방식을 사용하여 ProxySQL을 구성하는 것이 일반적이며, 특히 이러한 메커니즘을 이미 구현한 다른 TCP 로드 밸런서를 위해 마이그레이션할 때 그렇습니다. 이 구성에서 쿼리 라우팅 기준은 들어오는 포트 입니다.
다음은 ProxySQL Admin에서 들어오는 포트를 기반으로 쿼리 라우팅을 구현하는 방법의 예입니다. 이것은 올바른 호스트 그룹(호스트 그룹 1의 MySQL 작성기 및 호스트 그룹 2의 MySQL 판독기)에 기본 및 복제본이 이미 구성되어 있다고 가정합니다. Galera 또는 그룹 복제를 사용하는 경우 유사한 접근 방식이 적용됩니다. 단계는 다음과 같습니다.
- 두 개의 포트에서 수신 대기하도록 ProxySQL을 구성하고 다시 시작: mysql-interfaces런타임에 변경할 수 없고 다시 시작해야 하는 몇 가지 변수 중 하나
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; - 수신 포트 를 기반으로 라우팅 추가 :
INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,1,1), (2,1,6402,2,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent
이제 포트 6401로 들어오는 모든 쿼리는 호스트 그룹 1의 MySQL 서버로 전송되고 포트 6402로 들어오는 모든 쿼리는 호스트 그룹 2의 MySQL 서버 중 하나로 전송됩니다.
수신 포트에 따른 읽기/쓰기 분할 제한
들어오는 포트를 기반으로 하는 라우팅에는 주요 단점이 있습니다. 읽기와 쓰기를 구분하기 위해 응용 프로그램에 읽기/쓰기 분할 기능이 내장되어 있어야 합니다. 종종 이것은 사실이 아니며 응용 프로그램은 물론 MySQL 기본으로 밝혀진 단일 연결 끝점만 사용합니다.
ProxySQL은 트래픽을 분석합니다. 단일 포트에서 허용되는 모든 트래픽은 쿼리 유형 또는 기타 기준에 따라 다시 라우팅됩니다.
이는 애플리케이션 변경이 필요하지 않기 때문에 매우 편리합니다. 그러나 ProxySQL의 주요 이점은 애플리케이션 변경 없이 트래픽을 라우팅하는 기능이 아니라 데이터베이스로 전송되는 트래픽을 제어하는 데 사용할 수 있는 시스템이 있다는 것입니다. 운영 팀은 개발 팀의 개입 없이 프로덕션 문제를 해결해야 합니다. ProxySQL은 DB 트래픽을 완전히 제어할 수 있도록 하여 이를 가능하게 합니다.
특정 쿼리 또는 쿼리 유형에 대한 지능형 라우팅을 구현하려면 고급 ProxySQL 쿼리 규칙을 활용하는 것이 좋습니다.
정규식을 사용한 일반 읽기/쓰기 분할
이전에 만든 쿼리 규칙을 지우는 것으로 시작합니다.
DELETE FROM mysql_query_rules;
그런 다음 다음과 같이 읽기/쓰기 분할을 위해 모든 SELECT를 복제본으로 라우팅하는 일반 규칙을 만듭니다.
UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG1
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$',1,1),
(2,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent
이제 라우팅은 다음과 같이 작동합니다.
- 모든 SELECT FOR UPDATE명세서는 HG1로 전송됩니다.
- 다른 모든 SELECT명세서는 HG2로 전송됩니다.
- 다른 모든 것은 HG1(기본값)로 전송됩니다.
프로덕션 환경에서 이러한 규칙을 사용하지 마십시오. 일반적인 읽기/쓰기 규칙은 문제를 일으킵니다.
더 나은 접근 방식은 데이터베이스 워크로드를 적절하게 분석하고 효율적인 규칙을 구현하고 이전 규칙을 다시 한 번 지우는 것입니다 .
DELETE FROM mysql_query_rules;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent
정규식과 다이제스트를 사용한 지능적인 읽기/쓰기 분할
읽기/쓰기 분할을 보다 효율적으로 설정하기 위한 구성 절차는 다음과 같습니다.
- 먼저 모든 트래픽을 기본(쓰기 및 읽기 모두)인 하나의 MySQL 노드로만 보내도록 ProxySQL을 구성합니다.
- stats_mysql_query_digest가장 비싼 SELECT명세서 를 식별하기 위해 분석
- 이러한 명령문 중 어느 것이 판독기 노드를 라우팅하는 데 안전 한지 결정 합니다(즉, 복제 지연에 민감하지 않음, 복제 하드웨어가 쿼리 지원에 적합함 등).
- mysql_query_rules복제본에서 이러한 비용이 많이 드는 명령문만 선택적으로 라우팅 하도록 구성
다음을 사용하여 비용이 많이 드는 쿼리 찾기 stats_mysql_query_digest
다음은 읽기/쓰기 분할에 대한 잠재적인 쿼리를 식별하는 방법에 대한 예제 목록입니다. ProxySQL은 stats데이터베이스 테이블의 내부 메트릭을 노출하며 표준 SQL 쿼리를 실행하여 CLI를 통해 라이브 인스턴스에서 이 정보를 수집할 수 있습니다.
이러한 예제의 이러한 결과는 몇 달 동안 실행되어 지금까지 이미 약 천억 개의 쿼리를 처리한 매우 바쁜 ProxySQL 인스턴스를 기반으로 합니다.
- 총 실행 시간을 기준으로 상위 5개 쿼리를 찾는 방법:
Admin> 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; +--------------------+--------------------------+------------+---------------+ | digest | SUBSTR(digest_text,0,25) | count_star | sum_time | +--------------------+--------------------------+------------+---------------+ | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 | | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 | | 0x38BE36BDFFDBE638 | SELECT instance.name as | 59343662 | 1096236803754 | | 0xB4233552504E43B8 | SELECT ir.type as type, | 1362897166 | 488971769571 | | 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293 | 475253770301 | +--------------------+--------------------------+------------+---------------+ 5 rows in set (0.01 sec) - 개수를 기준으로 상위 5개 검색어를 찾는 방법:
Admin> 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; +--------------------+--------------------------+------------+---------------+ | digest | SUBSTR(digest_text,0,25) | count_star | sum_time | +--------------------+--------------------------+------------+---------------+ | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 | | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 | | 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609 | | 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867 | | 0xB4233552504E43B8 | SELECT ir.type as type, | 1362906755 | 488974931108 | +--------------------+--------------------------+------------+---------------+ 5 rows in set (0.00 sec) - 최대 실행 시간을 기준으로 상위 5개 쿼리를 찾는 방법:
Admin> 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; +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ | digest | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | min_time | max_time | +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ | 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390 | 185951894994 | 1270249 | 445 | 237344243 | | 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130 | 24842335265 | 562935 | 494 | 231395575 | | 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194 | 1356742749 | 1136300 | 624 | 216677507 | | 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796 | 748804483 | 156131 | 607 | 197881845 | | 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid | 592196 | 40209254260 | 67898 | 416 | 118055372 | +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ 5 rows in set (0.01 sec) - 총 실행 시간 및 최소 실행 시간이 1밀리초 이상인 상위 5개 쿼리를 찾는 방법:
Admin> 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; +--------------------+--------------------------+------------+-------------+----------+----------+----------+ | digest | SUBSTR(digest_text,0,20) | count_star | sum_time | avg_time | min_time | max_time | +--------------------+--------------------------+------------+-------------+----------+----------+----------+ | 0x9EED412C6E63E477 | SELECT a.id as acco | 961733 | 24115349801 | 25074 | 10994 | 7046628 | | 0x8DDD43A9EA37750D | Select ( Coalesce(( | 107069 | 3156179256 | 29477 | 1069 | 24600674 | | 0x9EED412C6E63E477 | SELECT a.id as acco | 91996 | 1883354396 | 20472 | 10095 | 497877 | | 0x08B23A268C35C08E | SELECT id as reward | 49401 | 244088592 | 4940 | 1237 | 1483791 | | 0x437C846F935344F8 | SELECT Distinct i.e | 164 | 163873101 | 999226 | 1383 | 7905811 | +--------------------+--------------------------+------------+-------------+----------+----------+----------+ 5 rows in set (0.01 sec) - 평균 실행 시간이 1초 이상인 총 실행 시간을 기준으로 상위 5개 쿼리를 찾는 방법입니다. 총 실행 시간의 백분율도 표시합니다.
Admin> 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; +--------------------+--------------------------+------------+--------------+----------+-------+ | digest | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | pct | +--------------------+--------------------------+------------+--------------+----------+-------+ | 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390 | 185951894994 | 1270249 | 2.11 | | 0xD38895B4F4D2A4B3 | SELECT instance.name as | 9783 | 12409642528 | 1268490 | 0.141 | | 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194 | 1356742749 | 1136300 | 0.015 | +--------------------+--------------------------+------------+--------------+----------+-------+ 3 rows in set (0.00 sec) - 평균 실행 시간이 최소 15밀리초인 총 실행 시간을 기준으로 정렬된 상위 5개 쿼리를 찾고 총 실행 시간의 백분율도 표시하는 방법:
Admin> 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 | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | pct | +--------------------+--------------------------+------------+---------------+----------+--------+ | 0x38BE36BDFFDBE638 | SELECT instance.name as | 59360371 | 1096562204931 | 18472 | 13.006 | | 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390 | 185951894994 | 1270249 | 2.205 | | 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid | 592281 | 40215136635 | 67898 | 0.477 | | 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130 | 24842335265 | 562935 | 0.295 | | 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768 | 24116011513 | 25074 | 0.286 | +--------------------+--------------------------+------------+---------------+----------+--------+ 5 rows in set (0.00 sec)
이러한 모든 쿼리를 기본에서 실행해야 합니까? 한 쿼리의 평균 실행 시간이 1초 이상인 경우 답은 '아니오'일 수 있습니다. 이는 애플리케이션 요구 사항에 따라 달라집니다. 일부 애플리케이션의 경우 평균 실행 시간이 15ms인 쿼리도 자주 업데이트되지 않는 데이터를 쿼리하기 때문에 아마도 슬레이브로 갈 수 있습니다.
결과에서 일부 쿼리는 최대 실행 시간이 매우 긴 반면 최소 실행 시간은 매우 짧고 평균 시간도 상당히 느립니다. 구체적으로 다이제스트 0x36CE5295726DB5B4가 포함된 쿼리의 평균 실행 시간은 1.27초, 최소 실행 시간은 0.4ms, 최대 실행 시간은 237.34초입니다. 실행 시간이 왜 그렇게 임의적인지 조사할 가치가 있으며 인프라 문제의 징후일 수 있습니다.
견고한 분석을 수행한 후 다이제스트 0x38BE36BDFFDBE638가 포함된 쿼리가 복제본으로 이동하도록 결정할 수 있습니다.
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',20,1);
마찬가지로 이 명령문의 출력을 확인하여 명령문을 평가한 후 SELECT COUNT(*):
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;
복제본 으로 시작하는 모든 쿼리를 이동하고 다음 SELECT COUNT(*)과 같은 ProxySQL 쿼리 규칙을 생성하는 것이 합리적일 수 있습니다.
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; # if you want this change to be permanent마지막으로 변경 사항을 적용한 후 작업 부하를 재평가해야 합니다. 작업 부하가 반드시 변경되고 최적화할 수 있는 새 쿼리를 찾을 수 있기 때문입니다. 여기에 설명된 쿼리 분석 방법은 ProxySQL 배포에서 최대한의 가치를 얻을 수 있도록 지속적인 활동이어야 합니다.
'2023' 카테고리의 다른 글
| ProxySQL - Query Cache (0) | 2023.01.19 |
|---|---|
| ProxySQL - Sharding (0) | 2023.01.19 |
| ProxySQL - 백엔드 서버 구성 (0) | 2023.01.19 |
| ProxySQL 초기 설정 (0) | 2023.01.18 |
| ProxySQL - MySQL 8.0 지원 (0) | 2023.01.18 |