[MariaDB - Galera Cluster & ProxySQL read/write split]

Galera Cluster를 구축한 뒤, proxysql을 이용하여 Master에서는 read+write Slave에서는 read 가 되도록 READ/WRITE Split을 할 수 있도록 구성하였습니다.

 

 

테스트 환경

Hostname	IP	Version
galera001	192.168.100.40	MariaDB10.2.12
galera002	192.168.100.41	MariaDB10.2.12
galera003	192.168.100.42	MariaDB10.2.12
Haproxy	192.168.100.43	HA-Proxy version 2.5

 

※ 사전 mariadb 설치 진행

 

 

 

■ galera 설치 & 기동 (galera1,2,3)

[root@jh-galera001 ~]# yum -y install rsync nmap lsof perl-DBI nc


[root@jh-galera001 ~]# setenforce 0
setenforce: SELinux is disabled



[root@jh-galera001 ~]# vi /etc/my.cnf.d/galera.cnf

[galera]
wsrep_on=ON
wsrep_provider=/mysql/lib/libgalera_smm.so
wsrep_cluster_name='galera'
wsrep_cluster_address='gcomm://192.168.100.40,192.168.100.41,192.168.100.42'
wsrep_node_address='192.168.100.40'
wsrep_node_name='node1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_provider_options="gcache.size = 512M; gcache.name = /tmp/galera.cache; gcache.page_size = 100M"



## DB기동
<Master>
[root@jh-galera001 ~]# mysqld_safe --user=mysql --wsrep-new-cluster &

<Slave>
[root@jh-galera002]# mysqld_safe --user=mysql &
[root@jh-galera003]# mysqld_safe --user=mysql &



MariaDB [(none)]> show status like '%wsrep_%';
+------------------------------+-------------------------------------------------------------+
| Variable_name                | Value                                                       |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                                    |
| wsrep_apply_oool             | 0.000000                                                    |
| wsrep_apply_window           | 0.000000                                                    |
| wsrep_causal_reads           | 0                                                           |
| wsrep_cert_deps_distance     | 0.000000                                                    |
| wsrep_cert_index_size        | 0                                                           |
| wsrep_cert_interval          | 0.000000                                                    |
| wsrep_cluster_conf_id        | 6                                                           |
| wsrep_cluster_size           | 3                                                           |
| wsrep_cluster_state_uuid     | 2c1ebb08-dc8c-11ec-8702-6ad61ff40f0a                        |
| wsrep_cluster_status         | Primary                                                     |
| wsrep_commit_oooe            | 0.000000                                                    |
| wsrep_commit_oool            | 0.000000                                                    |
| wsrep_commit_window          | 0.000000                                                    |
| wsrep_connected              | ON                                                          |
| wsrep_desync_count           | 0                                                           |
| wsrep_evs_delayed            |                                                             |
| wsrep_evs_evict_list         |                                                             |
| wsrep_evs_repl_latency       | 0.000310563/0.000358755/0.000406947/4.8192e-05/2            |
| wsrep_evs_state              | OPERATIONAL                                                 |
| wsrep_flow_control_paused    | 0.000000                                                    |
| wsrep_flow_control_paused_ns | 0                                                           |
| wsrep_flow_control_recv      | 0                                                           |
| wsrep_flow_control_sent      | 0                                                           |
| wsrep_gcomm_uuid             | bcd69c74-dc8c-11ec-b97b-1b7b3abba9f1                        |
| wsrep_incoming_addresses     | 192.168.100.42:3306,192.168.100.40:3306,192.168.100.41:3306 |
| wsrep_last_committed         | 0                                                           |
| wsrep_local_bf_aborts        | 0                                                           |
| wsrep_local_cached_downto    | 18446744073709551615                                        |
| wsrep_local_cert_failures    | 0                                                           |
| wsrep_local_commits          | 0                                                           |
| wsrep_local_index            | 1                                                           |
| wsrep_local_recv_queue       | 0                                                           |
| wsrep_local_recv_queue_avg   | 0.133333                                                    |
| wsrep_local_recv_queue_max   | 2                                                           |
| wsrep_local_recv_queue_min   | 0                                                           |
| wsrep_local_replays          | 0                                                           |
| wsrep_local_send_queue       | 0                                                           |
| wsrep_local_send_queue_avg   | 0.000000                                                    |
| wsrep_local_send_queue_max   | 1                                                           |
| wsrep_local_send_queue_min   | 0                                                           |
| wsrep_local_state            | 4                                                           |
| wsrep_local_state_comment    | Synced                                                      |
| wsrep_local_state_uuid       | 2c1ebb08-dc8c-11ec-8702-6ad61ff40f0a                        |
| wsrep_protocol_version       | 8                                                           |
| wsrep_provider_name          | Galera                                                      |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |
| wsrep_provider_version       | 25.3.23(r3789)                                              |
| wsrep_ready                  | ON                                                          |
| wsrep_received               | 15                                                          |
| wsrep_received_bytes         | 1575                                                        |
| wsrep_repl_data_bytes        | 0                                                           |
| wsrep_repl_keys              | 0                                                           |
| wsrep_repl_keys_bytes        | 0                                                           |
| wsrep_repl_other_bytes       | 0                                                           |
| wsrep_replicated             | 0                                                           |
| wsrep_replicated_bytes       | 0                                                           |
| wsrep_thread_count           | 2                                                           |
+------------------------------+-------------------------------------------------------------+
58 rows in set (0.00 sec)



MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

 

 

 

 

 

■ ProxySQL 설치 (proxysql서버)

다운로드 URL : https://github.com/sysown/proxysql/releases

Releases · sysown/proxysql

[root@jh-haproxy ~]# wget https://github.com/sysown/proxysql/releases/download/v2.4.1/proxysql-2.4.1-1-centos7.x86_64.rpm

[root@jh-haproxy ~]# yum -y install proxysql-2.4.1-1-centos7.x86_64.rpm

[root@proxysql ~]# systemctl start proxysql

 

 

 

 

 

■ mariadb client 설치 (proxysql서버)

레포지토리 URL : https://mariadb.org/download/?t=repo-config&d=CentOS+7+%28x86_64%29&v=10.2&r_m=yongbok

Download MariaDB Server - MariaDB.org

## repo 설정.
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash


[root@proxysql yum.repos.d]# vi mariadb.repo


[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.7/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1


[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1


[mariadb-tools]
name = MariaDB Tools
baseurl = https://downloads.mariadb.com/Tools/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
gpgcheck = 1
enabled = 1


[root@proxysql ~]# yum clean all
Loaded plugins: fastestmirror, langpacks
Cleaning repos: base epel extras mariadb updates
Cleaning up everything
Cleaning up list of fastest mirrors


[root@proxysql yum.repos.d]# sudo yum install  MariaDB-client​

 

 

 

 

■ ProxySQL DB 접속 (proxysql서버)

기본은 port가 6032로 설정되있습니다.

또한 기본 사용자 ID와 Password는 admin과 admin입니다.

 

[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.000 sec)


MySQL [(none)]> select * from global_variables where variable_name like '%galera%';
+----------------------------------------------------+----------------+
| variable_name                                      | variable_value |
+----------------------------------------------------+----------------+
| mysql-monitor_galera_healthcheck_interval          | 5000           |
| mysql-monitor_galera_healthcheck_timeout           | 800            |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3              |
+----------------------------------------------------+----------------+
3 rows in set (0.002 sec)​

 

 

 

 

 

■ Monitor 유저 생성 (galera1서버)

[root@proxysql ~]# vi /etc/proxysql.cnf
...
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
...


[root@jh-galera001 ~]# mysql -uroot -proot

MariaDB [(none)]> create user 'monitor'@'192.168.100.%' identified by 'monitor';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all on *.* to 'monitor'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)​

 

 

 

 

 

■ ProxySQL에 galera 정보 추가 (proxysql서버)

(1번 그룹 write/ 2번 그룹 read)

MySQL [(none)]> show create table mysql_servers\G
*************************** 1. row ***************************
       table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.000 sec)



## 1번 Write group
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values (1,'192.168.100.40',3306),(1,'192.168.100.41',3306),(1,'192.168.100.42',3306);
Query OK, 3 rows affected (0.000 sec)


## 2번 Read group
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values (2,'192.168.100.40',3306),(2,'192.168.100.41',3306),(2,'192.168.100.42',3306);
Query OK, 3 rows affected (0.000 sec)



MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.100.40 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.100.41 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.100.42 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.100.40 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.100.41 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.100.42 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.000 sec)




##QUERY를 날릴 User 생성  ( 해당 유저가 DB에도 있어야됩니다. )
<ProxySQL>
MySQL [(none)]> INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('jh','jh',1,1);
Query OK, 1 row affected (0.000 sec)

MySQL [(none)]> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| jh       | jh       | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.000 sec)



<Galera1> DB에서도 유저 생성
MariaDB [(none)]>  create user 'kim'@'192.168.100.%' identified by 'kim';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'kim'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)



## Query rule에 따른 트랜잭션 분배설정 (1번 group-> write   2번 group -> read)
MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '.*@.*', 1, 1);
Query OK, 1 row affected (0.000 sec)

MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 2, 0);
Query OK, 1 row affected (0.000 sec)

MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 2, 1);
Query OK, 1 row affected (0.000 sec)


* match_pattern: https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-query_processor_regex


MySQL [(none)]> select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+---------------------+-----------------------+
| rule_id | active | match_pattern       | destination_hostgroup |
+---------+--------+---------------------+-----------------------+
| 1       | 1      | .*@.*               | 1                     |
| 2       | 1      | ^SELECT.*           | 2                     |
| 3       | 1      | ^SELECT.*FOR UPDATE | 2                     |
+---------+--------+---------------------+-----------------------+
3 rows in set (0.000 sec)



## 스케줄
MySQL [(none)]> show create table scheduler\G;
*************************** 1. row ***************************
       table: scheduler
Create Table: CREATE TABLE scheduler (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
    filename VARCHAR NOT NULL,
    arg1 VARCHAR,
    arg2 VARCHAR,
    arg3 VARCHAR,
    arg4 VARCHAR,
    arg5 VARCHAR,
    comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.000 sec)



MySQL [(none)]> INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 1000, '/usr/share/proxysql/tools/proxysql_galera_checker.sh', 1, 2, 1, 1, '/var/lib/proxysql/proxysql_galera_checker.log');
Query OK, 1 row affected (0.000 sec)
- arg1 -> write group
- arg2 -> read group
* https://linuxglobe.com/centos/centos-7/proxysql-for-mariadb-galera-clustering-centos-7.html



MySQL [(none)]> select * from scheduler;

+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
| id | active | interval_ms | filename                                             | arg1 | arg2 | arg3 | arg4 | arg5                                          | comment |
+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
| 1  | 1      | 1000        | /usr/share/proxysql/tools/proxysql_galera_checker.sh | 1    | 2    | 1    | 1    | /var/lib/proxysql/proxysql_galera_checker.log |         |
+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
1 row in set (0.000 sec)



MySQL [(none)]> show variables like '%mysql-query_retries_on_failure%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| mysql-query_retries_on_failure | 1     |
+--------------------------------+-------+
1 row in set (0.000 sec)


MySQL [(none)]> SET mysql-query_retries_on_failure=10;
Query OK, 1 row affected (0.000 sec)



## 지금까지의 설정을 DISK단에 저장
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;



[root@proxysql ~]# vi /var/lib/proxysql/proxysql_galera_checker.log
Thu May 26 16:20:25 KST 2022 ###### proxysql_galera_checker.sh SUMMARY ######
Thu May 26 16:20:25 KST 2022 Hostgroup writers 1
Thu May 26 16:20:25 KST 2022 Hostgroup readers 2
Thu May 26 16:20:25 KST 2022 Number of writers 1
Thu May 26 16:20:25 KST 2022 Writers are readers 1
Thu May 26 16:20:25 KST 2022 log file /var/lib/proxysql/proxysql_galera_checker.log
Thu May 26 16:20:25 KST 2022 ###### HANDLE WRITER NODES ######  --> WRITE는 192.168.100.40번만 ONLINE이고 나머지는 OFFLINE
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.40:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 1:192.168.100.40:3306 is already ONLINE: 1 of 1 write nodes
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.41:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 Changing server 1:192.168.100.41:3306 to status OFFLINE_SOFT. Reason: max write nodes reached (1)
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.42:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 Changing server 1:192.168.100.42:3306 to status OFFLINE_SOFT. Reason: max write nodes reached (1)
Thu May 26 16:20:25 KST 2022 ###### HANDLE READER NODES ######    --> READ는 모든 NODE가 ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.40:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.40:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.41:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.41:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.42:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.42:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 ###### SUMMARY ######
Thu May 26 16:20:25 KST 2022 --> Number of writers that are 'ONLINE': 1 : hostgroup: 1
Thu May 26 16:20:25 KST 2022 --> Number of readers that are 'ONLINE': 3 : hostgroup: 2
Thu May 26 16:20:25 KST 2022 ###### Loading mysql_servers config into runtime ######
Thu May 26 16:20:26 KST 2022 ###### proxysql_galera_checker.sh SUMMARY ######
Thu May 26 16:20:26 KST 2022 Hostgroup writers 1
Thu May 26 16:20:26 KST 2022 Hostgroup readers 2
Thu May 26 16:20:26 KST 2022 Number of writers 1
Thu May 26 16:20:26 KST 2022 Writers are readers 1
Thu May 26 16:20:26 KST 2022 log file /var/lib/proxysql/proxysql_galera_checker.log​

 

 

 

 

 

■ READ / WRITE TEST

[root@proxysql tmp]# mysqlslap -h 192.168.100.43 -P6033 -ujh -pjh --concurrency=50 --delimiter=";" --create-schema="employees" --query="select * from salaries limit 100000;" --verbose


[root@proxysql tmp]# mysqlslap -h 192.168.100.43 -P6033 -ujh -pjh --concurrency=50 --delimiter=";" --create-schema="test" --query="insert into test.host values(@@hostname);" --verbose

[root@proxysql tmp]# mysql -h 192.168.100.43  -P6033 -ujh -pjh -e "select * from test.host;"
+-----------+
| a         |
+-----------+
| galera001 |
| galera001 |
| galera001 |
| galera001 |
| galera001 |
| galera001 |
(생략)
| galera001 |
| galera001 |
| galera001 |
| galera001 |
| galera001 |
| galera001 |
+-----------+​

 

 

 

 

 

■ PROXY SQL로 들어온 Query들 확인

## SELECT QUERY
MySQL [(none)]>SELECT hostgroup,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;
+-----------+--------------------+--------------------------+------------+----------+
| hostgroup | digest             | SUBSTR(digest_text,0,25) | count_star | sum_time |
+-----------+--------------------+--------------------------+------------+----------+
| 1         | 0x82A12D4C4E7B0A28 | select @@hostname        | 100        | 265333   |
| 1         | 0x79A65E8D38E670BE | SELECT @@session.auto_in | 202        | 255603   |
| 2         | 0x585E02549E37B5CB | select * from test.host  | 28         | 19324    |
| 2         | 0x810E1BDC27270605 | select * from host       | 10         | 6594     |
| 1         | 0x82A12D4C4E7B0A28 | select @@hostname        | 8          | 4192     |
+-----------+--------------------+--------------------------+------------+----------+

## SELECT 이외 QUERY
MySQL [(none)]> SELECT hostgroup,digest,SUBSTR(digest_text,0,50),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text NOT LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;
+-----------+--------------------+-------------------------------------------+------------+----------+
| hostgroup | digest             | SUBSTR(digest_text,0,50)                  | count_star | sum_time |
+-----------+--------------------+-------------------------------------------+------------+----------+
| 1         | 0xCC0D4E5CF7FFA399 | insert into test.host values(@@hostname)  | 201        | 7624785  |
| 1         | 0x02033E45904D3DF0 | show databases                            | 3          | 24855    |
| 1         | 0x9546412A89F519C1 | create table host(a varchar(?))           | 1          | 6744     |
| 1         | 0x6A6D318B70BE4BF6 | insert into test.host values(@@hostname); | 1          | 3521     |
| 1         | 0xFD5ED827C71A419A | insert into host values(@@hostname)       | 1          | 3352     |
+-----------+--------------------+-------------------------------------------+------------+----------+

 

 

 

참고

 

 

[ ProxySQL DOCS ]

https://proxysql.com/documentation/main-runtime/

Main (runtime tables definition) - ProxySQL

https://proxysql.com/documentation/galera-configuration/

Galera Configuration - ProxySQL