MySQL & Maria
MRTE ( MySQL Real Traffic Emulator )
- -
반응형
MRTE2 ( MySQL Real Traffic Emulator )는 카카오에서 효율적인 MySQL 운영을 위해 개발한 opensource tool입니다.
MRTE는 실서비스용 MySQL 서버의 트래픽을 수집하는 MRTE-Collector와, 수집한 데이터를 테스트용 MySQL 서버에서 재현하는 MRTE-Player 두 개의 툴로 구성되어 있습니다.
특히 MRTE-Collector는 Go로 작성되어 Go로 네트웍 프로그래밍을 하려는 개발자들에게 유용할 것입니다.
■ MRTE 실행 순서
- MongoDB 시작
- MRTECollector 시작
- MRTEPlayer 시작
각 컴포넌트 재시작 시
- MRTEPlayer만 재시작하는 경우, MRTEPlayer가 재시작되는 경우 마지막 실행했던 Event를 기억하지 못한다. 그래서 MRTEPlayer가 재시작되는 경우에는 MongoDB의 Capped collection에 저장된 모든 Event를 한번씩 더 실행하게 된다.
- MRTECollector만 재지작하는 경우, MRTECollector는 시작될 때 Capped Collection을 삭제하고 새로 생성하게 되는데 이 과정에서 MRTEPlayer의 Taiable Cursor의 데이터 읽기가 실패하게 된다.
- 가능하면 MRTECollector또는 MRTEPlayer만 재시작하는 작업은 하지 않도록 하자. 현재 구현에는 이런 경우에 대한 핸들링이 포함되어 있지 않기 때문이다.
■ Mongo 설치
- MongoDB 서버는 MRTECollector에서 수집된 MySQL 명령을 MRTEPlayer로 전달하는 매개 역할을 하게 된다. 그래서 MongoDB 서버가 저장되는 데이터를 영구적으로 보관(Durable)할 필요가 없다. 그래서 MRTE에서 사용되는 MongoDB는 아래와 같이 "Percona Server for MongoDB"를 이용해서 메모리 엔진으로 Wiredtiger를 기동 하는 것이 좋다(물론 꼭 메모리 스토리지 엔진을 사용해야 하는 것은 아님).
[root@lee1 MRTE2]# sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@lee1 MRTE2]# percona-release enable psmdb-44 release
[root@lee1 MRTE2]# yum install percona-server-mongodb
[root@lee1 MRTE2]# mongo --port 30000
Percona Server for MongoDB shell version v4.4.10-11
connecting to: mongodb://127.0.0.1:30000/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("bd3e963b-1be8-4125-b2c1-45efa4cc2db3") }
Percona Server for MongoDB server version: v4.4.10-11
Welcome to the Percona Server for MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
https://www.percona.com/doc/percona-server-for-mongodb
Questions? Try the support group
https://www.percona.com/forums/questions-discussions/percona-server-for-mongodb
---
The server generated these startup warnings when booting:
2022-01-10T10:23:05.288+09:00: Access control is not enabled for the database. Read and write access to data and configuration
2022-01-10T10:23:05.288+09:00: This server is bound to localhost. Remote systems will be unable to connect to this server. Stal interfaces. If this behavior is desired, start the server with --bind_ip 127.0.0.1 to disable this warning
---
> use mrte
switched to db mrte
> db
mrte
> db.createCollection("mrte")
{ "ok" : 1 }
> show collections
mrte
■ MRTECollector 시작
- MRTECollector는 네트워크 인터페이스의 패킷을 캡처하기 때문에, 반드시 MySQL 서버와 동일 장비에서 실행되어야 합니다. ( Default Interface는 eth0으로 되어있다 )
## MRTEollector download
[root@lee1 data1]# git clone https://github.com/kakao/MRTE2.git
Cloning into 'MRTE2'...
remote: Enumerating objects: 509, done.
remote: Total 509 (delta 0), reused 0 (delta 0), pack-reused 509
Receiving objects: 100% (509/509), 5.01 MiB | 0 bytes/s, done.
Resolving deltas: 100% (70/70), done.
[root@lee1 MRTECollector]# vi run.sh
./MRTECollector \
-mongouri='mongodb://localhost:30000?connect=direct' \
-mongodb="mrte" \
-mongocollection="mrte" \
-threads=5 \
-fastparsing=true \
-onlyselect=false \
-mysqluri='lee:lee@tcp(127.0.0.1:3306)/'
## MRTEColletor 시작
[root@lee1 MRTECollector]# sh +x run.sh
DateTime TotalPacket ValidPacket PacketDropped PacketIfDropped AssembledPackets ExpiredPackets WaitingQueueCnt MQError
2022-01-10 10:57:54 0 0 0 0 0 0 0 0
2022-01-10 10:57:55 0 0 0 0 0 0 0 0
2022-01-10 10:57:56 0 0 0 0 0 0 0 0
2022-01-10 10:57:57 0 0 0 0 0 0 0 0
2022-01-10 10:57:58 0 0 0 0 0 0 0 0
(중략)
0 0
2022-01-10 10:58:12 0 0 0 0 0 0 0 0
2022-01-10 10:58:13 2 0 0 0 1 0 0 0
--> packet이 들어가고있는것을 볼수 있다.
## 정보가 잘들어가는지 테스트 해보았다.
[root@post001 bin]# mysql -utest -ptest -h 27.96.134.134
mysql> create database lee;
Query OK, 1 row affected (0.00 sec)
mysql> use lee;
creaDatabase changed
mysql> create table test(a int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.24 sec)
## MongoDB에서 MRTE Collection 확인
> use mrte
> show collections
mrte
mrte000
mrte001
mrte002
mrte003
mrte004
> db.mrte001.find({})
{ "_id" : ObjectId("61db8e1a2bb14cb63a630670"), "ip" : "127.0.0.1", "port" : 0, "req" : { "q" : "Initial Marker for CappedCollection", "c" : 127 } }
{ "_id" : ObjectId("61db915f2bb14cb63a63072a"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 126, "q" : "85aeff0100000001ff0000000000000000000000000000000000000000000000" } }
{ "_id" : ObjectId("61db91762bb14cb63a63072f"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db918a2bb14cb63a630745"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "q" : "lee", "c" : 2 } }
{ "_id" : ObjectId("61db919e2bb14cb63a630750"), "port" : 36666, "req" : { "c" : 2, "q" : "lee" }, "ip" : "101.101.210.12" }
{ "_id" : ObjectId("61db91b22bb14cb63a630754"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "q" : "lee", "c" : 2 } }
{ "_id" : ObjectId("61db91c62bb14cb63a63075b"), "req" : { "c" : 2, "q" : "lee" }, "ip" : "101.101.210.12", "port" : 36666 }
{ "_id" : ObjectId("61db91da2bb14cb63a630760"), "req" : { "c" : 2, "q" : "lee" }, "ip" : "101.101.210.12", "port" : 36666 }
{ "_id" : ObjectId("61db91ee2bb14cb63a630764"), "port" : 36666, "req" : { "q" : "lee", "c" : 2 }, "ip" : "101.101.210.12" }
{ "_id" : ObjectId("61db92022bb14cb63a630767"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db92162bb14cb63a63076c"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db922a2bb14cb63a630770"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db923e2bb14cb63a630773"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db92522bb14cb63a630778"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db92662bb14cb63a63077c"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db927a2bb14cb63a63077f"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
{ "_id" : ObjectId("61db928e2bb14cb63a630784"), "port" : 36666, "req" : { "q" : "lee", "c" : 2 }, "ip" : "101.101.210.12" }
{ "_id" : ObjectId("61db92a22bb14cb63a630788"), "req" : { "c" : 2, "q" : "lee" }, "ip" : "101.101.210.12", "port" : 36666 }
{ "_id" : ObjectId("61db92b62bb14cb63a63078f"), "req" : { "c" : 2, "q" : "lee" }, "ip" : "101.101.210.12", "port" : 36666 }
{ "_id" : ObjectId("61db92ca2bb14cb63a630794"), "ip" : "101.101.210.12", "port" : 36666, "req" : { "c" : 2, "q" : "lee" } }
■ MRTEPlayer 시작
- Source Server에 설치된 MongoDB에서 정보를 가져와 Target DB에 실행하게 해 준다.
- TargetDB의 유저 생성 시 plugin을 mysql_native_password로 생성해주어야 된다.
## MRTEPlayer download
[root@lee2 data1]# git clone https://github.com/kakao/MRTE2.git
## TARGET DB 설정
mysql> create user mrte2@'localhost' IDENTIFIED WITH mysql_native_password BY 'mrte2';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'mrte2'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> create database mysqlslap;
Query OK, 1 row affected (0.00 sec)
## MRTEPlayer 시작
[root@lee2 MRTEPlayer]# sh +x run.sh
(중략)
DateTime TotalPacket ErrorPacket NewSession ExitSession UserRequest(Slow) Error (NoInitDB Duplicated Deadlock LockTimeout)
2022-01-12 14:06:56 0 0 0 0 0( 0) 0 ( 0 0 0 0)
2022-01-12 14:06:57 0 0 0 0 0( 0) 0 ( 0 0 0 0)
2022-01-12 14:06:58 0 0 0 0 0( 0) 0 ( 0 0 0 0)
2022-01-12 14:06:59 0 0 0 0 0( 0) 0 ( 0 0 0 0)
2022-01-12 14:07:00 0 0 0 0 0( 0) 0 ( 0 0 0 0)
DateTime TotalPacket ErrorPacket NewSession ExitSession UserRequest(Slow) Error (NoInitDB Duplicated Deadlock LockTimeout)
■ Transaction 테스트
- SourceDB에서 Transaction이 발생했을 때 TargetDB 쪽으로 잘 전달되는지 확인하였다.
## Select query TEST
[root@post001 MRTEPlayer]# mysqlslap -utest -ptest -h27.96.134.134 --concurrency=5 --iterations=10 --delimiter=";" --create-schema="employees" --query="SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;" --verbose
## Collector
DateTime TotalPacket ValidPacket PacketDropped PacketIfDropped AssembledPackets ExpiredPackets WaitingQueueCnt MQError
2022-01-12 14:12:23 0 0 0 0 0 0 0 0
2022-01-12 14:12:24 0 0 0 0 0 0 0 0
2022-01-12 14:12:25 0 0 0 0 0 0 0 0
2022-01-12 14:12:26 0 0 0 0 0 0 0 0
2022-01-12 14:12:27 0 0 0 0 0 0 0 0
2022-01-12 14:12:28 0 0 0 0 0 0 0 0
2022-01-12 14:12:29 0 0 0 0 0 0 0 0
2022-01-12 14:12:30 0 0 0 0 0 0 0 0
2022-01-12 14:12:31 0 0 0 0 0 0 0 0
2022-01-12 14:12:32 36 11 0 0 0 0 0 0
2022-01-12 14:12:33 23 7 0 0 0 0 0 0
2022-01-12 14:12:34 22 8 0 0 0 0 0 0
2022-01-12 14:12:35 45 15 0 0 0 0 0 0
2022-01-12 14:12:36 45 15 0 0 0 0 0 0
2022-01-12 14:12:37 15 5 0 0 0 0 0 0
2022-01-12 14:12:38 36 12 0 0 0 0 0 0
2022-01-12 14:12:39 39 13 0 0 0 0 0 0
2022-01-12 14:12:40 45 15 0 0 0 0 0 0
2022-01-12 14:12:41 15 5 0 0 0 0 0 0
2022-01-12 14:12:42 36 12 0 0 0 0 0 0
DateTime TotalPacket ValidPacket PacketDropped PacketIfDropped AssembledPackets ExpiredPackets WaitingQueueCnt MQError
2022-01-12 14:12:43 39 13 0 0 0 0 0 0
2022-01-12 14:12:44 41 13 0 0 0 0 0 0
2022-01-12 14:12:45 10 4 0 0 0 0 0 0
2022-01-12 14:12:46 12 4 0 0 0 0 0 0
2022-01-12 14:12:47 0 0 0 0 0 0 0 0
2022-01-12 14:12:48 0 0 0 0 0 0 0 0
2022-01-12 14:12:49 0 0 0 0 0 0 0 0
2022-01-12 14:12:50 0 0 0 0 0 0 0 0
2022-01-12 14:12:51 0 0 0 0 0 0 0 0
2022-01-12 14:12:52 0 0 0 0 0 0 0 0
2022-01-12 14:12:53 0 0 0 0 0 0
##Player
DateTime TotalPacket ErrorPacket NewSession ExitSession UserRequest(Slow) Error (NoInitDB Duplicated Deadlock LockTimeout)
>> SQLPlayer[101.101.210.12:49240] New connection created, query executed without sql player
>> SQLPlayer[101.101.210.12:49248] New connection created, query executed without sql player
>> SQLPlayer[101.101.210.12:49244] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49246] open connection
>> SQLPlayer[101.101.210.12:49246] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49240] open connection
>> SQLPlayer[101.101.210.12:49240] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49248] open connection
>> SQLPlayer[101.101.210.12:49244] open connection
>> SQLPlayer[101.101.210.12:49244] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49248] Default db is initialized, and connection is prepared
2022-01-12 14:12:44 29 0 5 2 5( 0) 0 ( 0 0 0 0)
>> SQLPlayer[101.101.210.12:49160] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49164] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49168] Default db is initialized, and connection is prepared
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49268] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49166] Default db is initialized, and connection is prepared
2022-01-12 14:12:45 18 0 1 5 8( 4) 0 ( 0 0 0 0)
>> SQLPlayer[101.101.210.12:49262] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49264] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49162] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49266] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49260] New connection created, query executed without sql player
--- pos : 70 : FLAG_CLIENT_CONNECT_WITH_DB
>> SQLPlayer[101.101.210.12:49268] open connection
>> SQLPlayer[101.101.210.12:49262] open connection
>> SQLPlayer[101.101.210.12:49264] open connection
>> SQLPlayer[101.101.210.12:49266] open connection
>> SQLPlayer[101.101.210.12:49262] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49264] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49268] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49268] Default db is initialized, and connection is prepared
>> SQLPlayer[101.101.210.12:49266] Default db is initialized, and connection is prepared
##Target MySQL Processlist
+-----+-----------------+-----------------+-----------+---------+-------+------------------------+--------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+-----------+---------+-------+------------------------+--------------------------------------------------------------------+
| 5 | event_scheduler | localhost | | Daemon | 90300 | Waiting on empty queue | |
| 767 | root | localhost | | Query | 0 | init | show processlist |
| 778 | root | localhost | llll | Sleep | 9773 | | |
| 807 | mrte2 | localhost:57318 | employees | Query | 6 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 808 | mrte2 | localhost:57320 | employees | Query | 5 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 809 | mrte2 | localhost:57322 | employees | Query | 5 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 810 | mrte2 | localhost:57324 | employees | Query | 6 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 812 | mrte2 | localhost:57338 | employees | Query | 4 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 813 | mrte2 | localhost:57340 | employees | Query | 4 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 814 | mrte2 | localhost:57342 | employees | Query | 4 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 815 | mrte2 | localhost:57346 | employees | Query | 4 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 816 | mrte2 | localhost:57344 | employees | Query | 4 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 817 | mrte2 | localhost:57358 | employees | Query | 3 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 818 | mrte2 | localhost:57360 | employees | Query | 3 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 819 | mrte2 | localhost:57362 | employees | Query | 3 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 820 | mrte2 | localhost:57364 | employees | Query | 2 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
| 821 | mrte2 | localhost:57366 | employees | Query | 3 | executing | SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000 |
+-----+-----------------+-----------------+-----------+---------+-------+------------------------+--------------------------------------------------------------------+
** 끝으로 MRTE2는 나중에 고객사에서 파라미터나 설정을 바꿔보고 싶다고 하였을 때 해당 Tool을 이용하여 TEST DB에 실제 DB의 Traffic을 발생시켜 파라미터나 설정을 바꿨을 때 안정적이고 효율적인지 테스트해 볼 수 있을 것 같다.
■ 참고
https://tech.kakao.com/2016/02/16/opensource-2-mtre/
kakao의 오픈소스 Ep2 - MRTE(MySQL Realtime Traffic Emulator)
“카카오의 오픈소스를 소개합니다” 두번째는 matt.lee와 동료들이 개발한 MySQL Realtime Traffic Emulator(MRTE)입니다. MRTE는 실서비스용 MySQL 서버의 트래픽을 수집하는 MRTE-Collector와, 수집한 데이터를
tech.kakao.com
https://github.com/kakao/MRTE2/blob/master/doc/howtorun.md
GitHub - kakao/MRTE2: MySQL Real Traffic Emulator (v2)
MySQL Real Traffic Emulator (v2). Contribute to kakao/MRTE2 development by creating an account on GitHub.
github.com
반응형
'MySQL & Maria' 카테고리의 다른 글
| [Maria] Maxscale GUI 설치 (2) | 2022.03.22 |
|---|---|
| [ MySQL & Maria ] Innodb_buffer_pool 사용량 조회 (0) | 2022.03.21 |
| [MySQL] MySQL 5 vs 8 ( Auto_increment ) (2) | 2021.12.23 |
| [Maria] Galera Cluster 성능 튜닝 (0) | 2021.12.06 |
| [ MARIA ] Galera Cluster 구성 (0) | 2021.12.03 |
Contents
소중한 공감 감사합니다