MySQL PMM(Percona Monitoring and Management) 소개 설치

 

l  Version : MySQL, MariaDB, PMM

 

PMM Percona Monitoring and Management 약자로 PostgreSQL MySQL, MongoDB 위한 오픈소스 데이터베이스 모니터링 관리 솔루션이다. 이를 통해 데이터베이스 시스템의 상태를 관찰하고, 동작의 새로운 패턴을 탐색하고, 문제를 해결하고, 온프레미스나 클라우드 어디에 있든 데이터베이스 관리 작업을 수행할 있다. PMM 데이터베이스와 해당 호스트로부터 수천 개의 기본 성능 지표를 수집한다. 그리고 UI 대시보드를 제공하여 데이터를 시각화 한다. 추가 기능에는 데이터베이스 상태 평가를 위한 조언을 제공하기도 한다.

 

 

 

PMM 서버, 클라이언트 구조로 노드들에 PMM Client 설치하여 중앙의 PMM 서버로 데이터를 전송하는 방식이다. 화면을 보면 그라파나(Grafana) 대시보드를 사용하는 것을 있다. 그리고 데이터를 수집하는 에이전트는 프로메테우스(Prometheus) 사용한다.

 

PMM Client

l  pmm-admin : 각종 exporter들을 구동 제어한다. pmm-admin 노드 관리를 효과적으로 하기 위해, PMM서버에 포함된 Consul 서버에 exporter 에이전트 접속 정보(아이피와 포트) 등록한다. Prometheus 수집주기마다, Consul 등록된 에이전트 리스트를 가져와서 모니터링 메트릭을 수집한다.

l  node_expoter, mysql_expoter : 모니터링 대상 노드(서버) 서버에 데몬 형태로 구동되는 에이전트로, Prometheus에서 에이전트 포트로 메트릭 요청을 하면 현재 서버의 상태를 전송하는 역할을 한다.

 

PMM Server

l   QNAP : DB서버를 모니터링 , 시스템의 메트릭 뿐만 아닌, 슬로우 쿼리나, 각종 이벤트 정보들을 pmm-server 데이터를 전달해야 하는데, Prometeus 이러한 데이터를 받아들이지 않는다. 이러한 데이터를 받기 위해 QAN API 사용한다. expoter에서 pmm-server 80포트를 사용하여 /qan-api 데이터를 전송하면, 내부의 nginx 서비스가 전달한 데이터를 받아서, 127.0.0.1:9001 리다이렉션하여 쿼리를 저장한다.

l   Prometheus : 타임시리즈 기반의 저장소로, 데이터 수집을 비롯해서 저장 그리고 쿼리 질의, Alert 에이전트의 포트에 접근해서, 모니터링 시스템의 메트릭을 Pull 방식으로 끌어오는 역할을 수행한다.

l   Consul : pmm-admin pmm consul 모니터링이 대상들의 아이피와 포트 번호를 넣어주면, Prometheus 정보를 바탕으로 등록된 scrape_interval마다 DB서버 에이전트에 접근하여 모니터링 지표를 수집한다.

l   Grafana : 수집된 데이터를 Web UI 제공한다.

 

 

PMM설치의 경우, 서버는 컨테이너로 제공되며, 클라이언트는 컨테이너 또는 전용 패키지 설치 파일로 제공된다. 이번 포스트에서는 Docker에서 PMM 서버를 실행하고, Client 패키지로 설치한 다음, 모니터링 서버를 등록하는 과정을 살펴본다.

 

[PMM 서버 설치]

도커 이지지를 다운로드 한다.

docker pull percona/pmm-server:2

 

데이터를 저장할 호스트 볼륨을 생성한다.

docker volume create pmm-data

 

이미지를 실행하여 PMM 서비스를 실행한다. 이때, 관리를 위한 443 포트와 UI 제공하기 위한 80포트를 사용하는데, 포트가 충돌나지 않도록 적절히 포트를 포워딩하여사용한다. ( 포스트에서는 도커 사용법을 다루지는 않는다.)

docker run --detach --restart always \
--publish 443:443 --publish 80:80 \
-v pmm-data:/srv \
--name pmm-server \
percona/pmm-server:2

 

서버가 정상적으로 실행되었으면, 웹브라우저에서 PMM 서버가 설치된 IP 입력하여 UI 대시보드에 접속한다. 필자의 경우 컨테이너를 생성할 , 8080포트를 사용하도록 매핑하였다. 기본 ID 패스워드는 admin / admin 이다. 정상적으로 접속이 되면 우선 서버 설치는 정상적으로 완료되었다고 판단하면 된다.

http://172.30.1.49:8080

 

 

 

 

[PMM Client 설치]

모니터링할 대상 서버에는 PMM Client 설치한다. 운영체제에 따라 전용 패키지 또는 설치 파일을 다운로드하여 설치할 있다. 실습 환경은 우분투이며 편의상 패키지로 설치하였다. 환경에 따른 설치 방법은 아래 링크를 참고한다.

l   Set up PMM Client  : https://docs.percona.com/percona-monitoring-and-management/setting-up/client/index.html

 

apt update
apt-get install -y pmm2-client

 

 

Client 설치가 설치가 완료되었으면 PMM 서버에 수집할 클라이언트 목록을 등록해야 한다. 아래 명령어로 등록할 있다.

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@X.X.X.X:443

l   X.X.X.X : PMM 서버 IP 입력한다.

l   443 : 기본 포트 번호. 컨테이너 실행시 포트 번호를 다르게 매핑하였다면 수정해야한다.

l   Admin/admin : PMM 서버의 기본 접속 정보이다. 만약 이전 UI 통해서 수정하였다면 부분도 수정한다.

 

여기까지 진행되었으면 PMM 대시보드에서 모니터링의 대상 서버가 추가된 것을 확인할 있다. 대시보드에서는 Node Names에서 확인할 있다. 정보가 업데이트 되기까지 약간의 시간이 걸릴수도 있다.

 

이번 실습에서는 MySQL 모니터링을 하기 때문에 MySQL 서비스에 대한 모니터링을 있도록 MySQL 등록한다. 사용자 환경에 따라 필요한 서비스를 등록한다.  지원되는 서비스 목록은 아래와 같다.

l   MySQL (and variants Percona Server for MySQL, Percona XtraDB Cluster, MariaDB)

l   MongoDB

l   PostgreSQL

l   ProxySQL

l   Amazon RDS

l   Microsoft Azure

l   Google Cloud Platform (MySQL and PostgreSQL)

l   Linux

l   External services

l   HAProxy

l   Remote instances

 

MySQL 서비스에 대한 정보를 수집할 있도록 PMM Client 사용할 계정 다양한 시스템 환경을 설정한다. 설정 방법은 공식 문서를 참고할 있도록 한다.

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/client/mysql.html

 

이번 포스트에서는 슬로우 쿼리 로그를 모니터링 있도록 서비스를 등록하였다.

pmm-admin add mysql --query-source=slowlog --username=pmm --password=pass

 

대시보드를 살펴보면 MySQL 서비스가 등록되었으며, 관련 지표를 확인할 있다.

 

 

PMM 내부 적으로 알림 시스템(alert-manager) 가지고 있어, 특정 임계치를 설정하였을 , 임계치를 넘으면 알림을 받을 있다. 이번 포스트에서는 다루지 않지만, 이러한 알림 시스템까지 설정하여 활용하면 시스템에 대한 상태를 매번 지켜보지 않아도 문제가 발생하였을 , 메신저 등으로 알림을 받을 있어 빠르게 문제 인지 원인을 파악할 있다.

 

 

[참고자료]

l   https://prometheus.io/docs/introduction/overview/

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/server/docker.html

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/client/mysql.html

l   https://prometheus.io/docs/introduction/overview/

 

 

 

 

 

2023-08-22 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Percona, DB모니터링, PMM, MySQL모니터링, 퍼코나 모니터링, Grafana, Promethus, 슬로우쿼리모니터링

MySQL Galera Cluster + ProxySQL에서 Galera Cluster 특성을 고려한 R/W 호스트 그룹 설정 하기

 

l  Version : MySQL, MariaDB, Galera Cluster, ProxySQL 2.X

 

Galera Cluster (이하 갈레라 클러스터”) 다중 마스터 동기 복제를 제공하는 솔루션으로 DB 간의 직접적인 데이터 공유가 없는 형태의 오픈소스 고가용성 솔루션이다.  갈레라 클러스터는 동기 방식의 복제구조를 사용하는 멀티마스터 RDB 클러스터로 제공된다.

ProxySQL 여러 데이터베이스에 대해 동일한 커넥션을 재사용할 있도록 멀티 플렉싱 기능과 쿼리를 분석하여 Write/Read 분산하는 다양한 기능을 제공하는 솔루션이다.

l  ProxySQL 이란 무엇인가 : https://sungwookkang.com/1528

l  MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기 : https://sungwookkang.com/1536

 

ProxySQL 2.x 부터는 Galera Cluster 좀더 유연하게 지원한다. 이번 포트스에서는 MySQL Galera cluster + ProxySQL 구성된 환경에서 Galera Cluster 특성을 고려한 ProxySQL Write, Read 호스트 그룹 전략에 대해서 알아본다.

 

[mysql_galera_hostgroups]

ProxySQL에서 호스트 그룹에 따라 Write, Read 역할을 정의한다. Galera Cluster 환경에서는 mysql_galera_hostgroups에서 해당 노드의 역할을 정의할 있다. 아래 스크립트를 실행하면 mysql_galera_hostgroups 테이블의 정보를 확인할 있다.

select * from mysql_galera_hostgroups\G

 

Admin> select * from mysql_galera_hostgroupsG
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 20
reader_hostgroup: 30
offline_hostgroup: 9999
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 20
comment:

 

 

l   writer_hostgroup : 쓰기 가능한 (read_only=0) 모든 구성원을 포함할 호스트 그룹의 ID

l   backup_writer_hostgroup : 쓰기 백업 노드의 그룹 ID. 클러스터가 다중 쓰기 모드(read_only=0 여러 노드가 있는 경우)에서 실행 중이고 max_writers 전체 쓰기 노드 수보다 작은 수로 설정된 경우 추가 노드는 backup_writer_hostgroup 으로 이동된다.

l   reader_hostgroup : 읽기 가능한 (read_only=1 노드) 모든 구성원을 포함할 호스트 그룹의 ID

l   offline_hostgroup : 클러스터에서 참여하지 않는 노드들에 대한 그룹ID. ProxySQL 모니터링에서 호스트가 OFFLINE 것으로 확인되면 호스트가 offline_hostgroup으로 이동된다.

l   Active : 호스트 그룹을 활성화하는 부울 (0 또는 1)

l   max_writers : writer 호스트 그룹에서 허용되는 최대 노드 수를 제어한다. Writer_hostgroup 노드 수가 max_writers 수보다 경우 추가 노드는 backup_writer_hostgroup으로 이동된다.

l   writer_is_also_reader : 1이면 writer_hostgroup 노드가 reader_hostgroup에도 배치되어 읽기에 사용된다. 2 설정하면 backup_writer_hostgroup 노드가 writer_hostgroup 노드 대신 reader_hostgroup 배치된다.

l   max_transactions_behind : 오래된 읽기를 방지하기 위해 노드가 SHUNNED되기 전에 클러스터의 노드가 대기할 있는 최대 쓰기 세트 수를 결정한다. (wsrep_local_recv_queue Galera 변수를 쿼리하여 결정됨).

l   Comment : 사용자가 정의한 목적에 맞게 사용할 있는 텍스트 필드

 

 

ProxySQL MySQL 상태 변수를 모니터링하여 Galera 상태 확인을 수행한다.

l   read_only : 설정값이 ON 경우, writer_is_also_reader 1 아니면 ProxySQL 정의된 호스트를 reader_hostgroup으로 그룹화한다.

l   wsrep_desync : 설정값이 ON 경우, ProxySQL 노드를 사용할 없는 것으로 표시하여 offline_hostgroup으로 이동한다.

l   wsrep_reject_queries : 설정값이 ON이면 ProxySQL 노드를 사용할 없는 것으로 표시하고 이를 offline_hostgroup으로 이동한다. (특정 유지 관리 상황에서 유용함).

l   wsrep_sst_donor_rejects_queries : 설정값이 ON이면 ProxySQL Galera 노드가 SST 기증자 역할을 하는 동안 해당 노드를 사용 불가로 표시하여 offline_hostgroup으로 이동한다.

l   wsrep_local_state : 해당 값이 동기화를 의미하는 4 이외의 값을 반환하면 ProxySQL 노드를 사용할 없는 것으로 표시하고 offline_hostgroup으로 이동한다.

l   wsrep_local_recv_queue : 해당 값이  max_transactions_behind보다 높으면 노드가 회피된다.

l   wsrep_cluster_status : 상태가 기본이 아닌 다른 상태로 반환되면 ProxySQL 노드를 사용할 없는 것으로 표시하고 offline_hostgroup으로 이동한다.

 

ProxySQL 2.x mysql_galera_hostgroups 매개변수와 mysql_query_rules 정책을 함께 결합함으로써 훨씬 다양한 Galera Cluster 구성에 대한 유연성을 갖게 되었다. 예를 들어 쿼리 규칙의 대상 호스트 그룹으로 정의된 단일 쓰기, 다중 쓰기 다중 읽기 호스트 그룹을 가질 있으며, 쓰기 수를 제한하고 오래된 읽기 동작을 보다 세밀하게 제어할 있다.

 

ProxySQL에서 max_writers writer_is_also_reader 변수는 ProxySQL 백엔드 MySQL 서버를 동적으로 그룹화하는 방법을 결정할 있으며 커넥션 분산 쿼리 라우팅에 직접적인 영향을 끼친다. 예를 들어 아래와 같이 MySQL 백엔드(노드) 서버가 등록되어 있다고 가정한다. 등록된 노드는 아래 스크립트로 확인 가능하다.

 

Admin> select hostgroup_id, hostname, status, weight from mysql_servers;

 

Admin> select hostgroup_id, hostname, status, weight from mysql_servers;
+--------------+--------------+--------+--------+
| hostgroup_id | hostname     | status | weight |
+--------------+--------------+--------+--------+
| 10           | DB1          | ONLINE | 1      |
| 10           | DB2          | ONLINE | 1      |
| 10           | DB3          | ONLINE | 1      |
+--------------+--------------+--------+--------+

 

그리고 현재 구성되어 있는 mysql_galera_hostgroup 설정도 살펴본다.

Admin> select * from mysql_galera_hostgroups\G

 

 

Admin> select * from mysql_galera_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 20
 reader_hostgroup: 30
offline_hostgroup: 9999
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 20
comment:

 

현재 노드 설정에서 모든 호스트가 가동되어 실행 중임을 고려하면 ProxySQL 호스트를 아래와 같이 다양한 시나리오로 그룹화할 있다.

 

그림에 따른 3가지 케이스의 특징을 한번 살펴본다.

Configuration Description
writer_is_also_reader=0 l  호스트를 2개의 호스트 그룹(writer backup_writer)으로 그룹화한다.
l  Writer backup_writer 일부이다.
l  Write Reader 아니므로 read_only=1 설정된 호스트가 없기 때문에 호스트 그룹 30(Reader)에는 아무것도 없다. 읽기 전용 플래그를 활성화하는 것은 Galera에서 일반적인 관행이 아니다.
writer_is_also_reader=1 l  호스트를 3개의 호스트 그룹(writer, backup_writer reader)으로 그룹화한다.
l  Galera 변수 read_only=0 영향을 미치지 않으므로 writer 호스트 그룹 30(reader)에도 있다.
l  writer backup_writer 일부가 아니다.
writer_is_also_reader=2 l  writer_is_also_reader=1 유사하지만 writer backup_writer 일부이다.

 

그룹 구성에 따른 특성을 활용하면 사용자는 특정 워크로드에 맞는 호스트 그룹 대상을 다양하게 선택할 있다. "핫스팟" 쓰기는 다중 마스터 충돌을 줄이기 위해 하나의 서버로만 이동하도록 구성할 있다. 충돌하지 않는 쓰기는 다른 마스터에 균등하게 분배될 있다. 쓰기는 최신 서버로 전달될 있으며 분석 읽기는 슬레이브 복제본으로 전달될 있다.

 

[Galera 클러스터용 ProxySQL 배포]

아래 그림과 같이 ClusterControl 의해 배포된 3노드 Galera Cluster 구성되어 있다고 가정하고, 시나리오를 만족하기 위해 클러스터를 세팅하는 과정을 다루어 본다.

 

시나리오는 아래와 같다.

WordPress 애플리케이션은 Docker에서 실행되고 WordPress 데이터베이스는 베어메탈 서버에서 실행되는 Galera Cluster에서 호스팅 된다. 우리는 WordPress 데이터베이스 쿼리 라우팅을 제어하고 데이터베이스 클러스터 인프라를 완전히 활용하기 위해 WordPress 컨테이너와 함께 ProxySQL 컨테이너를 실행하기로 결정했다. 읽기-쓰기 비율이 80%-20%이므로 ProxySQL 다음과 같이 구성하려고 한다.
l   모든 쓰기를 하나의 Galera 노드로 전달(충돌 감소, 쓰기에 집중)
l   다른 Galera 노드에 대한 모든 읽기의 균형을 분배 (대부분의 워크로드에 대해 나은 분배).

 

시나리오를 만족할 있도록 ProxySQL 구성한다. 먼저 컨테이너에 매핑할 있도록 Docker 호스트 내부에 ProxySQL 구성 파일을 만든다.

$ mkdir /root/proxysql-docker
$ vim /root/proxysql-docker/proxysql.cnf

 

아래와 같이 설정값을 입력한다. (코드에 대한 자세한 설명은 아래부분에서 하나씩 설명한다.)

datadir="/var/lib/proxysql"
 
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    web_enabled=true
    web_port=6080
    stats_credentials="stats:admin"
}
 
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.1.30"
    connect_timeout_server=10000
    monitor_history=60000
    monitor_connect_interval=200000
    monitor_ping_interval=200000
    ping_interval_server_msec=10000
    ping_timeout_server=200
    commands_stats=true
    sessions_sort=true
    monitor_username="proxysql"
    monitor_password="proxysqlpassword"
    monitor_galera_healthcheck_interval=2000
    monitor_galera_healthcheck_timeout=800
}
 
mysql_galera_hostgroups =
(
    {
        writer_hostgroup=10
        backup_writer_hostgroup=20
        reader_hostgroup=30
        offline_hostgroup=9999
        max_writers=1
        writer_is_also_reader=1
        max_transactions_behind=30
        active=1
    }
)
 
mysql_servers =
(
    { address="db1.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db2.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db3.cluster.local" , port=3306 , hostgroup=10, max_connections=100 }
)
 
mysql_query_rules =
(
    {
        rule_id=100
        active=1
        match_pattern="^SELECT .* FOR UPDATE"
        destination_hostgroup=10
        apply=1
    },
    {
        rule_id=200
        active=1
        match_pattern="^SELECT .*"
        destination_hostgroup=30
        apply=1
    },
    {
        rule_id=300
        active=1
        match_pattern=".*"
        destination_hostgroup=10
        apply=1
    }
)
 
mysql_users =
(
    { username = "wordpress", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 },
    { username = "sbtest", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 }
)

 

코드에서 살펴보면 mysql_galera_hostgroups 구성은 아래와 같이 정의되어 있다.

mysql_galera_hostgroups =
(
    {
        writer_hostgroup=10
        backup_writer_hostgroup=20
        reader_hostgroup=30
        offline_hostgroup=9999
        max_writers=1
        writer_is_also_reader=1
        max_transactions_behind=30
        active=1
    }
)

 

writer 그룹은 10, backup 그룹은 20, reader 그룹은 30으로 정의 되어있다. max_writers 1 설정하여 모든 쓰기가 전송되어야 하는 호스트 그룹 10 대해 단일 쓰기로 작동하도록 하였다. 그런 다음 writer_is_also_reader 1 정의하여 모든 Galera 노드를 읽기 노드에 참여할 있도록 하였다. 이렇게 하면 읽기 요청은 모든 노드에 균등하게 배포할 있다. Offline 그룹은 9999이며 ProxySQL 작동하지 않는 Galera 노드를 감지한 경우 offline_hostgroup으로 이동시킨다.

 

mysql_servers 클러스터에 참여하는 노드 정보를 정의한다. 시나리오에서는 모든 노드를 쓰기 가능한 호스트 그룹인 10으로 MySQL 서버를 구성하였다.

mysql_servers =
(
    { address="db1.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db2.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db3.cluster.local" , port=3306 , hostgroup=10, max_connections=100 }
)

 

위의 구성을 그림으로 표현하면 ProxySQL 아래와 같이 호스트 그룹을 구성하고 인식한다.

 

 

mysql_query_rules 쿼리 라우팅을 정의한다. 요구 사항에 따라 모든 읽기는 작성자(호스트 그룹 20) 제외한 모든 Galera 노드로 전송되어야 하며 다른 모든 것은 단일 작성자의 호스트 그룹 10으로 전달된다.

mysql_query_rules =
(
    {
        rule_id=100
        active=1
        match_pattern="^SELECT .* FOR UPDATE"
        destination_hostgroup=10
        apply=1
    },
    {
        rule_id=200
        active=1
        match_pattern="^SELECT .*"
        destination_hostgroup=20
        apply=1
    },
    {
        rule_id=300
        active=1
        match_pattern=".*"
        destination_hostgroup=10
        apply=1
    }
)

 

마지막으로 ProxySQL 통해 전달될 MySQL 사용자를 정의한다.

mysql_users =
(
    { username = "wordpress", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 },
    { username = "sbtest", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 }
)

 

우리는 transaction_persistent 0으로 설정하여 사용자로부터 오는 모든 연결이 읽기 쓰기 라우팅에 대한 쿼리 규칙을 준수하도록 한다. 그렇지 않으면 연결이 하나의 호스트 그룹에 도달하게 되어 로드 밸런싱의 목적이 상실하기 때문이다. 모든 MySQL 서버에서 해당 사용자를 먼저 생성하는 것을 잊지 않도록 한다. ClusterControl 활용해서 사용자를 추가하는 경우 [관리] -> [스키마 사용자 기능] 사용하여 해당 사용자를 생성할 있다.

 

설정 파일 생성이 완료되었으면 이제 컨테이너를 실행한다. ProxySQL 컨테이너를 시작할 ProxySQL 구성 파일을 바인드 마운트로 매핑하기 때문에 아래와 같은 명령어로 실행한다.

$ docker run -d
--name proxysql2
--hostname proxysql2
--publish 6033:6033
--publish 6032:6032
--publish 6080:6080
--restart=unless-stopped
-v /root/proxysql/proxysql.cnf:/etc/proxysql.cnf
severalnines/proxysql:2.0

 

마지막으로 ProxySQL 컨테이너 포트 6033 가리키도록 WordPress에서 데이터베이스 연결 포트를 변경한다.

$ docker run -d
--name wordpress
--publish 80:80
--restart=unless-stopped
-e WORDPRESS_DB_HOST=proxysql2:6033
-e WORDPRESS_DB_USER=wordpress
-e WORDPRESS_DB_HOST=passw0rd
wordpress

 

지금까지의 서비스 구성을 그림으로 표현하면 아래 아키텍처와 같다.

 

 

ProxySQL 컨테이너를 영구적으로 유지하려면 /var/lib/proxysql/ Docker 볼륨에 매핑하거나 바인드를 마운트 해야한다. 아래 스크립트는 컨테이너 실행시 설정 파일을 영구 저장소로 바인드하는 예제이다.

$ docker run -d
--name proxysql2
--hostname proxysql2
--publish 6033:6033
--publish 6032:6032
--publish 6080:6080
--restart=unless-stopped
-v /root/proxysql/proxysql.cnf:/etc/proxysql.cnf
-v proxysql-volume:/var/lib/proxysql
severalnines/proxysql:2.0

 

위와 같이 영구 저장소로 실행하면 컨테이너를 다시 시작 /root/proxysql/proxysql.cnf 사용하지 않는 다는 것에 주의한다. 이는 /var/lib/proxysql/proxysql.db 존재하는 경우 ProxySQL 구성 파일에서 로드 옵션을 건너뛰고 대신 SQLite 데이터베이스에 있는 항목을 로드하는 ProxySQL 다중 계층 구성 때문이다. (initial 플래그로 proxysql 서비스를 시작하면 설정 파일의 값으로 시작한다.)  ProxySQL 구성 관리는 구성 파일을 사용하는 대신 포트 6032 ProxySQL 관리 콘솔을 통해 수행해야 한다.

 

ProxySQL 서비스 시작 사용하는 구성파일에 대한 자세한 정보는 아래 글을 참고한다.

l   ProxySQL 서비스에 필요한 설정값을 어디에 저장하고 재사용할까? : https://sungwookkang.com/1535

 

 

[Monitoring]

ProxySQL 프로세스 로그는 기본적으로 syslog 기록되며 표준 docker 명령을 사용하여 있다.

$ docker ps
$ docker logs proxysql2

 

현재 ProxySQL에서 운영중인 호스트 그룹을 확인하려면 runtime_mysql_servers 테이블을 쿼리한다. 아래 스크립트는 호스트 환경에서 컨테이너의 ProxySQL 관리 콘솔에 접속한다.

$ docker exec -it proxysql2 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '

 

$ docker exec -it proxysql2 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '
Admin> select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 10           | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.22 | ONLINE |
| 30           | 192.168.0.23 | ONLINE |
| 20           | 192.168.0.22 | ONLINE |
| 20           | 192.168.0.23 | ONLINE |
+--------------+--------------+--------+

 

만약 특정 쓰기 노드가 다운되면 해당 노드는 offline_hostgroup (HID 9999)으로 이동된다. 예제에서는 192.168.0.21 노드가 다운되어 9999 그룹으로 이동되었다.

Admin> select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 10           | 192.168.0.22 | ONLINE |
| 9999         | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.22 | ONLINE |
| 30           | 192.168.0.23 | ONLINE |
| 20           | 192.168.0.23 | ONLINE |
+--------------+--------------+--------+

 

위의 토폴로지 변경 사항은 아래 그림과 같다.

 

 

ProxySQL에서 admin-web_enabled=true 설정할 경우 통계 UI 사용할 있다. UI 액세스하려면 포트 웹브라우저로 Docker 호스트6080 포트로 접속한다. (, http://192.168.0.200:8060) 사용자 이름을 묻는 메시지가 표시되면 admin-stats_credentials 정의된 자격 증명을 입력한다. 로그인하면 아래와 같은 통계 화면을 있다.

 

 

MySQL 연결 테이블을 모니터링하면 모든 호스트 그룹에 대한 연결 분포 개요를 확인할 있다.

Admin> select hostgroup, srv_host, status, ConnUsed, MaxConnUsed, Queries from stats.stats_mysql_connection_pool order by srv_host;

 

Admin> select hostgroup, srv_host, status, ConnUsed, MaxConnUsed, Queries from stats.stats_mysql_connection_pool order by srv_host;
+-----------+--------------+--------+----------+-------------+---------+
| hostgroup | srv_host     | status | ConnUsed | MaxConnUsed | Queries |
+-----------+--------------+--------+----------+-------------+---------+
| 20        | 192.168.0.23 | ONLINE | 5        | 24          | 11458   |
| 30        | 192.168.0.23 | ONLINE | 0        | 0           | 0       |
| 20        | 192.168.0.22 | ONLINE | 2        | 24          | 11485   |
| 30        | 192.168.0.22 | ONLINE | 0        | 0           | 0       |
| 10        | 192.168.0.21 | ONLINE | 32       | 32          | 9746    |
| 30        | 192.168.0.21 | ONLINE | 0        | 0           | 0       |
+-----------+--------------+--------+----------+-------------+---------+

 

위의 출력 값을 살펴보면 쿼리 규칙에 의해 호스트 그룹 30 아무 것도 처리하지 않음을 있다.

 

Galera 노드와 관련된 통계는 mysql_server_galera_log 테이블에서 있다.

>  select * from mysql_server_galera_log order by time_start_us desc limit 3 \G

 

Admin>  select * from mysql_server_galera_log order by time_start_us desc limit 3 \G
*************************** 1. row ***************************
                       hostname: 192.168.0.23
                           port: 3306
                  time_start_us: 1552992553332489
                success_time_us: 2045
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL
*************************** 2. row ***************************
                       hostname: 192.168.0.22
                           port: 3306
                  time_start_us: 1552992553329653
                success_time_us: 2799
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL
*************************** 3. row ***************************
                       hostname: 192.168.0.21
                           port: 3306
                  time_start_us: 1552992553329013
                success_time_us: 2715
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL

 

쿼리 결과 내용은 특정 타임스탬프에 대한 모든 Galera 노드의 관련 MySQL 변수/상태 상태를 반환한다. 구성에서는 Galera 상태 확인이 2초마다 실행되도록 구성되어 있다. (monitor_galera_healthcheck_interval=2000). 따라서 클러스터에 토폴로지 변경이 발생하는 경우 최대 장애 조치 시간은 2초이다.

 

지금까지 MySQL Galera 클러스터 환경에서 ProxySQL 조합하여 사용할 , 쓰기, 읽기 그룹에 대한 정의와 ProxySQL 컨테이너로 구성하는 과정을 다루었다. 실제 운영환경에서는 각자의 노드 구성과 SLA 모두 다르기 때문에 내용을 이해하고 활용할 있도록 한다.

 

[참고자료]

l   How to Run and Configure ProxySQL 2.0 for MySQL Galera Cluster on Docker : https://severalnines.com/blog/how-run-and-configure-proxysql-20-mysql-galera-cluster-docker/

l  ProxySQL 이란 무엇인가 : https://sungwookkang.com/1528

l  MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기 : https://sungwookkang.com/1536

l  ProxySQL 서비스에 필요한 설정값을 어디에 저장하고 재사용할까? : https://sungwookkang.com/1535

 

 

 

2023-08-10 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Galera Cluster, 마리아디비, 마이에스큐엘, 갈레라 클러스터, MariaDB 복제, MariaDB Cluster, MySQL Cluster, MariaDB Replication, MySQL, ProxySQL, ProxySQL설정, ProxySQL 구성관리, ProxySQL 시작, Proxy Container

MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기

 

l  Version : MySQL, MariaDB, Galera Cluster

 

Galera Cluster (이하 갈레라 클러스터”) 다중 마스터 동기 복제를 제공하는 솔루션으로 DB 간의 직접적인 데이터 공유가 없는 형태의 오픈소스 고가용성 솔루션이다.  현재 갈레라 클러스터는 MySQL Percona Xtradb 클러스터와 MariaDB 가지 버전을 지원한다. 특히 MariaDB에서는 Galera Cluster 공식적으로 지원한다. 이번 포스트에서는 갈레라 클러스터의 개념과 장단점에 대해서 알아본다.

 

갈레라 클러스터는 동기 방식의 복제구조를 사용하는 멀티마스터 RDB 클러스터로 제공된다. 갈레라 클러스터는 인증 기반 복제를 제공하며 노드간 통신을 위해 wsrep API 사용한다. 데이터 복제는 논리적으로는 완전 동기이지만 실제 write tablespace commit하는 과정은 별개이고 노드간에는 비동기로 동작한다. 그래서 갈레라 클러스터에서는 이러한 방식을 virtually synchronous replication이라 부른다.

 

 

[Galera Cluster 특징]

l  다중 쓰기 : Active-Active 방식의 다중 Master 구조이기 때문에 모든 노드에서 Read/Write 가능하다.

l  동기 복제 : 슬레이브 지연이 없고 노드 충돌시 데이터 손실이 없다.

l  일관적 데이터 유지 : 모든 노드는 항상 같은 데이터 상태를 유지 한다.

l  장애조치 : 노드 장애가 발생하였을 빠르게 모니터링 되어 전환되므로 서비스 중단이 최소화 된다. 멀티 포인트 쓰기를 지원하므로 전환이 쉽다. 특정 노드 장애가 전체 서비스에 영향을 미치지 않는다. 이론적으로 모든 노드가 쓰기 가능하기 때문에 모든 노드가 다운되기 전까지는 서비스 유지가 가능하다.

l  자동 노드 복제 : 노드를 추가하거나 관리를 위해 종료 또는 노드에서 제거 해야 경우 증분 데이터 또는 기본 데이터를 수동으로 백업할 필요가 없다. 갈레라 클러스터는 노드를 추가할 경우 자동으로 온라인 노드의 데이터를 가져오고 일관성을 유지한다.

 

[Galera Cluster 단점]

l   동기 복제 특성상 클러스터의 성능은 가장 성능이 낮은 노드에 의해 결정된다. 그래서 최대한 동일한 성능의 서버로 구성하는 것이 좋다.

l   동기 복제이기 때문에 쓰기가 많이 발생할 경우 다른 아키텍처(비동기 복제 )보다 상대적으로 성능이 저하되며 스케일 아웃에 한계가 있다.

l   신규 노드가 조인되거나 대규모 노드 조인시 지연이 발생하면 데이터 전체를 복사(SST, 상태 스냅샷 전동) 하는 문제가 발생한다. 이때 데이터를 제공하는 서버를 Donor라고 하는데 Donor 노드는 복제 데이터를 전송하는 동안 읽기 쓰기 서비스가 중단된다.

l   인증 기반 복제를 사용하기 때문에 인증이 실패할 경우 데드락이 발생할 가능성이 있다.

 

[Galera Cluster 제약사항]

l   MyISAM 일부와 InnoDB 스토리지 엔진 테이블만 지원한다.

l   노드 간의 쿼리 실행 순서를 피하기 위해 모든 테이블에서 기본키를 필수적으로 사용한다.

n  기본 키가 없는 테이블에서는 DELETE 작업이 지원되지 않는다.

n  기본 키가 없는 테이블의 행은 다른 노드에서 다른 순서로 나타날 있다.

l   Query Cache 비활성화한다

l   XA 트랜잭션 (전역 트랜잭션) 지원하지 않는다.

l   일반 쿼리 로그와 슬로우 쿼리 로그는 테이블로 보낼 없다. 이러한 로그를 활성화하는 경우 log_output=FILE 설정하여 로그를 파일로 전달해야 한다.

 

 

Galera Cluster wsrep(Write-Set Replication) API 통해 노드와 데이터를 동기화 한다. 여기서 wsrep API DBMS 복제를 위한 인터페이스이며 실제 동기화 구현은 Galera Replication Plugin에서 이루어 진다.

 

 

노드에서 쓰기나 업데이트가 발생할 경우 노드간에 데이터를 복제하고 업데이트 내용을 GCache 영역에 저장한다. GCache 복제된 트랜잭션을 위한 임시 저장소 역할을 한다.

 

 

Galera Cluster 인증기반 복제를 사용한다. 인증 기반 복제의 주요 컨셉은 트랜잭션이 충돌이 없다고 가정하고 커밋 지점에 도달할 때까지 관례적으로 실행된다는 것이다. 이를 낙관적 실행이라고 한다.

Galera Cluster에서 인증 기반 복제 구현은 트랜잭션의 전역 순서에 따라 다르다. Galera Cluster 복제 중에 트랜잭션에 전역 시퀀스 번호 또는 seqno 할당한다. 트랜잭션이 커밋 지점에 도달하면 노드는 마지막으로 성공한 트랜잭션의 시퀀스 번호와 시퀀스 번호를 확인한다. 사이의 간격을 통해 모든 트랜잭션은 해당 트랜잭션과 기본 충돌이 있는지 확인한다. 충돌이 감지되면 인증 테스트에 실패한다.

  그림에서 데이터가 변경되고 커밋되기 까지의 순서를 나타내면 아래와 같다.

1.          클라이언트가 데이터를 수정하고 커밋 요청을 서버에 요청한다.

2.          서버는 커밋 요청을 받으면 실제 커밋을 실행하기 전에 트랜잭션 변경된 행의 기본 키에 의해 데이터베이스에 대한 모든 변경 사항이 쓰기 세트로 수집된다.

3.          데이터베이스는 쓰기 세트를 다른 모든 노드로 전송한다.

4.          쓰기 세트는 기본 키를 사용하여 결정적 인증 테스트를 거친다. 작업은 쓰기 세트를 생성한 노드를 포함하여 클러스터의 노드에서 수행된다.

5.          노드가 쓰기 세트를 적용할 있는지 여부를 결정한다.

6.          인증 테스트에 실패하면 노드는 쓰기 세트를 삭제하고 클러스터는 원래 트랜잭션을 롤백한다. 그러나 테스트가 성공하면 트랜잭션이 커밋되고 쓰기 세트가 클러스터의 나머지 부분에 적용된다.

 

 

Galera Cluster Replication Write 정책은 First Committer Win이다. 트랜잭션이 커밋되는 시점에 다른 노드에 유효한 트랜잭션인지 여부를 체크하는 방식으로 동작한다. 클러스터 내에서 트랜잭션은 모든 서버에 동시에 반영되거나 전부 반영되지 않는 경우 하나로 동작한다. 이러한 이유로 Write 트랜잭션은 하나의 서버로 사용하는 것을 권장한다.

트랜잭션을 시작하는 시점(BEGIN)에는 자신의 노드에서는 Pessimistic Locking으로 동작하나, 노드 사이에서는 Optimistic Locking Model 동작한다. 먼저 트랜잭션을 자신의 노드에 수행을 하고, 커밋을 시점에 다른 노드로부터 해당 트랜잭션에 대한 유효성을 확인한다. 일반적으로 InnoDB 같이 트랜잭션을 지원하는 시스템인 경우 SQL 시작되는 시점에서 Lock 확인할 있으나, 갈레라 클러스터에서는 커밋되는 시점에 노드 트랜잭션 유효성 체크한다.

 

 

트랜잭션 커밋 또는 롤백 결정은 네트워크를 통한 다른 노드와 통신에서 결정된다. 결정 요소에는 아래 항목들의 상태가 포함된다.

l   네트워크 왕복 시간

l   노드에서 유효성 체크 시간

l   노드에서 데이터 반영 시간

 

Galera Cluster 필요한 최소 노드 수는 2개이다. 그러나 최소 3개의 노드가 권장된다. 최대 노드 제한은 없다. 그러나 10 이상의 노드가 있는 단일 클러스터는 네트워크 또는 인터넷에서 너무 많은 노드를 동기화하는 지연이 발생할 있다. 최대 노드 구성은 네트워크 구성에 따라 달라지므로 각자의 환경에 맞게 구성하도록 한다.

 

Galera Cluster 트랜잭션 크기를 명시적으로 제한하지 않지만 쓰기 집합은 단일 메모리 상주 버퍼로 처리되므로 결과적으로 매우 트랜잭션(: LOAD DATA) 노드 성능에 부정적인 영향을 미칠 있다. 이를 방지하기 위해 wsrep_max_ws_rows wsrep_max_ws_size 시스템 변수는 기본적으로 트랜잭션 행을 128K, 트랜잭션 크기를 2Gb 제한한다. 필요한 경우 사용자는 이러한 제한을 늘릴 있다. (향후 버전에서는 트랜잭션 조각화에 대한 지원을 추가할 예정이라고 한다.)

 

 

MySQL MariaDB에서 고가용성을 위한 솔루션으로 Galera Cluster 대해서 살펴보았다. 다양한 솔루션이 있지만 Galera Cluster 특징은 모든 노드가 마스터 역할로 클러스터에 참여하는 구조로 운영되며, 데이터베이스 사이의 연결이 아닌 외부 wsrep API 통한 데이터 복제가 이루어진다는 점에서 흥미로웠다. 이후 클러스터 구성 방법과 장애조치시 마스터 선정, 옵션에 대한 설정 과정을 다른 포스트에서 다뤄볼 예정이다.

 

[참고자료]

l   https://galeracluster.com/library/documentation/

l   http://galeracluster.com/documentation-webpages/galera-documentation.pdf

l   http://galeracluster.com/documentation-webpages/index.html

l   https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html

l   https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/

l   https://severalnines.com/resources/whitepapers/galera-cluster-mysql-tutorial/

l   https://www.slideshare.net/marcotusa/galera-explained-3

l   MariaDB Galera Cluster - Known Limitations : https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/

l   https://galeracluster.com/library/faq.html

l   https://www.slideshare.net/AbdulManaf19/mariadb-galera-cluster-63088921

 

 

 

2023-08-07 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Galera Cluster, 마리아디비, 마이에스큐엘, 갈레라 클러스터, MariaDB 복제, MariaDB Cluster, MySQL Cluster, MariaDB Replication

MySQL/MariaDB Sort Buffer

 

·       Version : MySQL,  MariaDB

 

MySQL/MariaDB에서는 데이터를 정렬(ORBER BY)하기 위해 별도의 메모리 공간을 할당하는데 이때 사용되는 메모리가 Sort buffer이다. 메모리는 정렬이 필요할 경우에만 할당되며, 쿼리 실행이 완료되면 시스템으로 즉시 반납된다. Soft buffer 크기는 시스템 설정 변수인 sort_buffer_size 조정 있다. sort_buffer_size byte단위로 표시된다.

show variables where Variable_Name like '%sort_buffer%';

 

 

정렬해야하는 데이터의 크기가 작은 경우에는 Sort buffer 만으로 충분할 수도 있지만, 정렬해야하는 데이터가 sort buffer보다 경우에는 정렬해야하는 데이터를 여러 조각으로 나누어서 처리되며, 임시 저장을 위해 디스크를 사용한다. 그리고 디스크에 저장된 데이터를 다시 메모리로 읽어 처리하고, 다시 디스크에 저장하는 행위를 반복한다.

 

 

여러  조각으로 나누어진 데이터를 정렬을 위해서 디스크에 쓰고 읽고를 반복하는 것을 Multi-Merge 라고 하는데,  Multi-Merge 횟수는  sort_merge_passes 라는 변수에 누적되기 때문에 모니터링이 가능하다. Multi-Merge 발생하게되면 디스크를 사용하는데, 디스크의 성능은 메모리보다 느리기 때문에 실제 쿼리 요청이 느려질 있다. 이러한 디스크 사용을 줄이기 위해 sort_buffer_size 증가 시킬수도 있지만, 특정 크기 이상 부터는 이상의 성능 향상을 기대하기 어렵다. 아래 그림의 테스트 자료를 보면 1MB 부터는 성능 향상이 없는것을 확인할 있다.

출처 : https://www.percona.com/blog/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/

 

sort_buffer_size 영역은 세션 마다 할당되기 때문에 너무 sort_buffer_size 자칫하면 메모리 부족현상을 겪을 있다. 또한 메모리가 부족할시 OS에서는 강제적으로 프로세스를 Kill 있으므로 MySQL/MariaDB 프로세스가 중지될 수도 있기 때문에 주의해야 한다.

 

 

2019-09-19 / 강성욱 / http://sungwookkang.com

 

MySQL, MariaDB, Sort Buffer, sort_buffer_size, sort_merge_passes, Multi-Merge

MySQL/MariaDB Full Table Scan

 

·       Version : MySQL,  MariaDB

 

Full Table Scan 인덱스를 사용하지 않고 테이블 데이터를 처음부터 끝까지 읽어서 요청 작업을 처리하는것을 의미한다. MySQL MariaDB에서는 아래와 같은 조건일때, Full Table Scan 사용한다.

·       테이블의 레코드 건수가 적어서 인덱스를 통해 읽는것보다 직접 테이블을 읽는것이 빠르다고 판된될때 (일반적으로 매우 적은페이지( 1페이지)일때)

·       WHERE절이나 ON절에 사용할 있는 인덱스가 없을 경우

·       인덱스 레인지스캔을 사용할 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

 

테이블 전체를 읽는 작업은 많은 디스크 읽기 오버헤드를 발생시키기 때문에 데이터를 읽을때 한번에 여러개의 블록이나 페이지를 읽어오도록 설계되어 있다. 하지만 MariaDB에서는 테이블 스캔을 실행 할때 한번에 몇개의 페이지를 읽어 올지 설정하는 변수는 없다.

 

InnoDB XtraDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 Read Ahead 작업이 자동으로 시작된다. 작업은 어떤 영역의 데이터가 앞으로 필요해질 것을 예측하여 요청이 오기전에 미리 읽어 버퍼풀에 두는 것을 의미한다.

 

풀테이블 스캔이 실행되면 처음 몇개의 페이지는 포그라운드 스레드(Foreground thread, 클라이언트 스레드) 페링지를 읽기를 실행하지만, 특정 시점부터는 일긱 작업을 백그라운드 스레드로 넘겨 한버네 4 또는 8개의 페이지를 읽으면서 계속해서 페이지를 증가시켜 최대 64개의 페이지까지 읽어 버퍼풀에 저장한다. 포그라운드 스레드는 버퍼풀의 데이터를 가져다 쓰면 되므로 쿼리가 빨리 처리된다. MariaDB XtraDB에서는 Read Ahead 설정은 innodb_read_ahead_threshold 시스템 변수를 이용해 변경할 있다.   옵션값을 낮추면 자주 Read Ahread 발생한다. 하지만 빈번한 Read Ahead 불필요한 I/O 작업을 유발시키므로 주의해야 한다.

 

 

 

2019-09-17 / 강성욱 / http://sungwookkang.com

 

MySQL, MariaDB, Full Table Scan, 테이블 스캔, Read Ahead, 리드어헤드, 미리읽기,

MariaDB 히스토그램 (옵티마이저가 실행계획 생성시 참고하는 데이터 분포 정보)

 

·       Version : MariaDB 10.0

 

히스토그램은 테이블의 컬럼값의 분포를 분석할 있는 통계 정보이다. 히스토그램 정보가 없는 경우에는 하나의 컬럼에서 유니크한 값의 개수에 기반해서 대략적인 분포도를 예측하는 형태로 실행 계획의 비용이 계산되었다.  또한 인덱스가 생성된 컬럼에 대해서만 유니크한 개수가 관리되었다. 이처럼 히스토그램이 관리되지 않으면 컬럼에 대한 데이터 분포를 예측하여 옵티마이저가 잘못된 실행 계획을 생성할 수도 있다.

 

히스토그램  기반 통계는 옵티마이저가 선택한 쿼리 계획을 개선하기 위한 메커니즘으로 MariaDB 10.0 버전에서 도입되었다.  이전까지는 실행계획을 생성할 인덱싱 되지 않은 컬럼의 조건은 모두 무시되었다. 이번에 도입된 히스토그램은 메커니즘은 인덱스로 만들어진 컬럼 뿐만 아니라 인덱싱되지 않은 컬럼에 대해서 모두 히스토그램 정보를 저장할 있도록 개선되었다. 모든 테이블의 모드 컬럼에 대해서 최대값, 최소값, NULL 값을 가진 비율을 계산하여 mysql.column_stats 라는 테이블에 저장되어 관리된다.

 

MariaDB 10.0 Height-Balanced Histogram 알고리즘을 사용한다. 알고리즘은 컬럼의 모든 값을 정렬해서 동일한 레코드 건수가 되도록 그룹을 개로 나눈다. 그리고 그룹의 마지막 (정렬된 상태에서 가장 큰값) 히스토그램에 저장한다. MariaDB에서는 그룹의 개수를 histogram_size 라는 시스템 설정 변수로 제어할 있다. histogram_size 0 ~ 255 값을 가지며, 크게 설정하면 히스토그램의 정확도는 높아지겠지만 그만큼 저장 공간 분석 시간이 필요하다.

 

 

MariaDB에서는 히스토그램 값을 그룹별로 한바이트씩 할당해서 VARBINARY(255) 타입의 histogram 컬럼에 저장한다. 실제 저장되는 값은 단순 그룹의 최대값이 아닌 아래와 같은 계산을 거쳐서 결과를 histogram 컬럼의 바이트에 순서대로 저장한다.  a값이 클수록 히스토그램의 정확도는 높아지지만 저장공간을 많이 사용한다.

그룹 최대값 / (컬럼 최대값 칼럼 최소값) * a

 

MariaDB에서 히스토그램 생성은 ANALYZE TABLE 명령으로 다른 통계 정보와 함께 생성된다. histogram_size 기본값은 0으로 히스토그램을 사용하지 않는다는 것을 뜻한다.  히스토그램을 사용하려면 반드시 histogram_size 0보다 값으로 설정한다. 또한 정확도를 위해서 histogram_type SINGLE_PREC_HB 또는 DOUBLE_PREC_HB 설정 있다.

 

optimizer_user_condition_selectivity 옵션은 옵티마이저가 최상의 실행 계획을 사용할 있도록 통계를 제어한다.

·       optimizer_user_condition_selectivity = 1 : MariaDB 5.5 사용되었던 선택도 방식을 그대로 유지

·       optimizer_user_condition_selectivity = 2 : 인덱스가 생성된 컬럼에 대해서만 선택도 사용

·       optimizer_user_condition_selectivity = 3 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용 안함)

·       optimizer_user_condition_selectivity = 4 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용)

·       optimizer_user_condition_selectivity = 5 : 모든 컬럼에 대해서 선택도 사용하면 레인지 스캔이 불가한 컬럼에 대해서 샘플링해서 선택도 사용

 

 

[참고자료]

·       Histogram-Based Statistics : https://mariadb.com/kb/en/library/histogram-based-statistics/

·       http://www.aioug.org/sangam14/images/Sangam14/Presentations/201446_garg_ppt.ppt.pdf

 

 

 

2019-09-17 / 강성욱 / http://sungwookkang.com

 

MariaDB, MariaDB 10.0, 히스토그램, histogram, optimizer_user_condition_selectivity, ANALYZE TABLE, mysql.column_stats, histogram_size, histogram_type

MySQL/MariaDB ROW Size Limit

 

·       Version : MySQL 5.7

 

SQL Server에서 사용하던 일부 데이터를 MySQL 서버로 마이그레이션 작업 진행중, 컬럼수가 많은 (또는 컬럼의 길이가 테이블) 테이블이 MySQL에서는 아래와 같은 오류와 함께 테이블이 생성되지 않는 문제가 발생하였다.

Error Code: 1118. Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

 

해당 원인을 찾아본 결과, SQL Server 경우 페이지의 크기가 8K 고정이 되어있고, Row 최대 사이즈는  8K 이다. 하지만  MySQL 경우 InnoDB 사용할 경우 페이지 사이즈를 사용자가 설정 있으며, 페이지에서 사용할 있는 최대 ROW크기는 페이지 크기의 절반보다 약간 작다. 예를들어 MySQL 기본 Page 크기는 16K 인데, 최대 크기는 8K 보다 약간 작다.

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

또한 BLOB이나 TEXT 경우 LOB 영역을 사용한다고 하더라도, 9~12바이트의 포인터 값을 가지고 있다.

최근 SSD 보급으로 SSD 최적화된 아키텍처를 제공하기 위해 일부 서비스에서는 페이지의 크기로 4K 할당하는 경우가 있다.   경우 로우에 저장할 있는 데이터의 크기가 작아지므로 미리 데이터를 설계할때 주의가 필요하다.

 

[참고자료]

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

 

 

 

2019-07-19 / 강성욱 / http://sungwookkang.com

 

MySQL, MySQL column limit, MySQL row size, MySQL Innodb, page size, Row size limit, MariaDB

ETL data error with MariaDB ODBC 3.0 (from Aurora to MS SQL)

 

·      Version : SQL Server 2016, AWS Aurora, Maria ODBC 3.0

 

최근 다양한 DB환경이 구성되면서 DW(Data Warehouse) 구축할 , ETL 작업 또한 이기종 DB (데이터베이스가 다른 종류간의 ETL) 작업이 증가하는 추세이다. 필자는 현재 다양한 데이터베이스를 구축 운영하고 있으며 MySQL 에서 MS SQL ETL과정중 예기치 못한 데이터 오류가 많이 발생 하였다. AWS 있는 Aurora (MySQL 5.6) 데이터 원본에서MS SQL Server ETL하는 과정에서 Decimal값이 정상적으로 표시되지 않고 /100 값으로 변환되어 출력되어지는 문제점이 발견되었다.


 

ETL방식은 SQL Server에서 MariaDB ODBC 3.0 Driver 사용하여 AWS Aurora 데이터베이스 데이터를 오픈 쿼리 방식으로 가져온다. 모든 데이터에 대해서 /100 으로 변환되지는 않으며 decimal 데이터 타입에서만 변환되는것을 확인하였다. 구글에서 Maria odbc decimal error 검색해보니 이미 많은 사용자들이 해당 이슈를 보고 하였고 관련 솔루션도 소개되어 있다.


 

다양한 해결 방법중에 MariaDB ODBC 3.03 Driver 에서는 해당 이슈가 해결되었다는 글이 있어 드라이버를 업그레이드 진행 하였다. 그리고 테스트 결과 정상적으로 값이 출력됨을 확인할 있었다.


 

 

각종  ODBC Driver 대한 버전은 ODBC Data Source Administrator에서 Drivers 탭에서 확인할 있다.


 

·       MariaDB ODBC Driver 설치 또는 업그레이드시 시스템 재부팅이 요구되므로 주의한다.

 

 

2018-05-30 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

Aurora My5.6, ETL, MariaDB ODBC driver error, ODBC decimal error, ETL, ODBC driver, MySQL, MariaDB, MSSQL


MySQL/MariaDB Memory 모니터링

 

·      Version : MySQL 5.7.21, Ubuntu 16.0.4

 

Memory 사용률은 모든 운영체제에서 중요한 모니터링 지표이다. 다양한 어플리케이션이 서버에서 실행되면서 실제 물리메모리를 초과하는 메모리 요구가 발생할 있기 때문에 메모리 모니터링도 중요하다. 실제 데이터베이스의 경우 메모리가 부족하여 성능저하가 크게 발생하는 경우가 많다.

 

아래 명령어는 Unbuntu Linux에서 5 간격으로 메모리 사용량을 확인하는 명령이다.

watch -n 5 free –m

 


 

리눅스는 메모리의 효율적인 운영을 위해 전체 메모리에서 미리  Buffers + Cached 값을 자동으로 할당해 놓는다. 만일 어플리케이션에서 메모리가 필요할 경우 Cached 할당된 메모리를 자동으로 반환해 어플리케이션에 할당한다.

 

MySQL 서버는 다양한 종류의 메모리 버퍼(메모리 캐시) 사용한다. 사용영역에 따라 크게 전역과 지역으로 나눌 있다.

·       MySQL/MariaDB 아키텍처 메모리 할당 사용 구조 : http://sqlmvp.kr/220404814981

전역 메모리 (모든 커넥션 또는 스레드에서 공유)

지역메모리 (세션별로 할당, 공유하지 않음)

·       InnoDB Buffer pool

·       MyISAM key cache

·       Join buffer

·       Sort buffer

·       Read buffer

 

전체 메모리 사용량을 예상하여 할당하려면 아래와 같은 공식을 사용한다.

전역 메모리 사용량 + (지역적 메모리 사용량 X 최대 동시 커넥션 )

 

만약 물리 메모리 대비 InnoDB 버퍼풀 크기 비율을 과도하게 높게 하였을때 활성 커넥션(Active DB Connections) 증가로 세션 단위의 메모리 사용량이 증가한다면 MySQL 서버가 사용하는 전체 메모리는 물리메모리를 초과할 있으며 서버의 성능저하로 이어질 있다. 따라서 아래와 같은 가이드를 제공하여 성능 저하를 예방할 있다.

·       운영체제가 사용하는 적정 메모리 용량을 제외한 Buffer pool 할당

·       MySQL 서버에서 제공하는 Max Connection 제한

·       Max Connection 만큼 세션이 증가할 있으므로 세션별로 사용해야 하는 메모리 사용량 제한

·       물리 메모리를 초과하지 않는 범위에서 MySQL 서버가 전역적으로 사용해야하는 메모리 영역에 대한 최대 사용랑 제한

 

메모리 관련 설정을 메모리 사용량에 영향을 있는 변수는 약간의 유휴 메모리를 유지될 있게 설정하는 것을 권장한다.

·       MySQL/MariaDB Memory 관련 설정 변수 : http://sqlmvp.kr/220365937569

 

Swap 메모리가 빈번한 경우 서버에서 실행되는 어플리케이션이 메모리를 적절하게 사용하지 못하거나 물리 메모리가 부족하다는 것을 의미하기 때문에 Swap 지수가 0보다 경우 어느 어플리케이션이 메모리를 많이 사용하는지 확인이 필요하다.

 

 

[참고자료]

·       How MySQL Uses Memory : https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

 

 

2018-03-27 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, linux, Memory 모니터링, watch, InnoDB Buffer pool, MySQL 메모리 사용량, 전역 메모리, 지역 메모리, 세션 메모리, Max connection



MySQL/MariaDB CPU 모니터링

 

·      Version : MySQL 5.7.21, Ubuntu 17.4

 

CPU 사용률은 모든 운영체제에서 가장 중요한 모니터링 지표이다. 이번 포스트는 기본적인 CPU 모니터링 방법에 대해서 살펴본다. 리눅스에서는 다양한 명령어를 사용하여 CPU 사용률을 확인할 있다.

·       Top 명령을 사용한 CPU  모니터링 : http://sqlmvp.kr/221024130336

 

기본적으로 CPU 사용량을 확인하기 위해서는 mpstat명령을 사용할 있다. 일반적으로 데이터베이스의 경우 시스템을 독립해서 사용하기 때문에 대부분의 CPU 사용량은 CPU System, CPU User, CPU IOwait 차지한다.

mpstat

 


 

[IOwait 높은 경우]

CPU 사용량이 높으면서 상대적으로 IOwait 사용량이 높다면 디스크의 I/O 오퍼레이션에 의한 병목을 의심해야 한다. 위에서 언급했듯이 일반적으로 데이터베이스 서버의 경우 독립적으로 운영하기 때문에 항목이 높다면 MySQL 서버가 실행되는 서버의 디스크 성능이 쿼리 트래픽을 처리하지 못해서  발생할 있다. 이때 초당 쿼리 유입량을 함께 확인하여 실제 요청이 많은지 확인할 필요가 있다. 또한 최적화 되지 않은 쿼리로 인하여 요청량은 적지만 디스크에 많은 부하를 일으키는 경우 IOwait 높게 나타날 있다. 경우는 적절한 인덱스를 사용하도록 가이드하거나 불필요한 정렬이나 그룹핑을 하지 않도록 하는것도 방법이다. 또한 빈번한 서브쿼리의 경우 결과를 임시테이블에(메모리보다 결과가 클겅우) 적재하여 처리하기 때문에 디스크 사용이 높을 있다.

 

[CPU User 높은 경우]

CPU IOwait CPU System상태가 낮은대 반해 CPU User 비율이 높은것은 MySQL 서버가 CPU 안정적으로 사용하고 있는 것으로 판단할 있다. 하지만 전체 CPU 높은 상태에서 CPU User 높은 경우 헤비한 쿼리(악성쿼리) 발생하지 않는지 확인해볼 필요가 있다. 또한 단순하게 CPU 사용량만으로는 판단할 없으면 요청되는 쿼리량을 확인하여 쿼리량이 많으면서 CPU 높으면 당연하지만 쿼리 요청량이 적은반면에 높다면 헤비 쿼리를 의심할 있다.

 

[CPU System 높은 경우]

CPU System  사용률이 높은 경우는 리눅스의 Kernel 프로세스의 CPU 사용량이 높은것으로 판단할 있다. 경우는 MySQL 서버가 커널이 실행해야하는 명령을 많이 수행한다는 뜻으로 커널의 의존성을 낮추기 위한 최적화 방안이 필요하다. 특히 서버가 데이터베이스 전용으로 사용하지 않고 다양한 어플리케이션이 함께 동작한다면 다른 요인으로 인해서 커널 사용량이 높을 수도 있다.

 

2018-03-26 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, linux, CPU 모니터링, mpstat, IOwait, CPU User, CPU Sytem, CPU 병목



MySQL/MariaDB 서버 상태 모니터링

 

·      Version : MySQL 5.7.21

 

MySQL 서버는 MySQL 관리를 위한 전용 유틸리티인 mysqladmin 기본적으로 제공한다. mysqladmin 이용하면 MySQL 서버의 주요 설정값과 상태 정보를 쉽게 확인 있다.

 

mysqladmin 명령어 위치와 사용형식은 다음과 같다.

·       명령어 위치 : /usr/local/mysql/bin/mysqladmin

·       사용형식 : mysqladmin –u root –p 명령어

 

도움말은 mysqladmin –help 명령어로 확인할 있다.


 

mysqladmin 에서 ‘status’ 옵션을 함께 사용하면 기본적인 상태 값을 모니터링 있다. 하지만 여기에 나타나는  결과값은 시간별 값이 아닌 누적값으로 직관적으로 현재 상태를 확인하기 어렵다.

mysqladmin –u root –p status

 


https://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html

·       Uptime : MySQL 서버가 가장 최근에 시작된 시각부터 현재까지의 시간을 단위로 보여줌

·       Threads : 현재 실행 중인 스레드

·       Questions : MySQL 서버가 가장 최근에 시작된 시각부터 현재까지 누적된 MySQL 명령어

·       Slow queries : 일정 시간이 이상(사용자 지정) 느린 쿼리

·       Opens : 서버가 오픈한 테이블

·       Flush tables : 서버가 실행한 flush-*, reload 명령수

 

아래 명령은 1 간격으로 status 상태를 모니터링 하는 스크립트이다.

mysqladmin –u root –p  -i1 status

 


 

Status 옵션이 기본적인 정보만을 제공하였다면 extended-status 옵션은 상세한 값을 제공한다. 옵션은  MySQL 서버의 모든 서버 상태 변수(Variables) 값을 보여준다.

mysqladmin –u root –p extended-status

 


 

아래 스크립트는 1 간격으로 모든 서버의 변수 상태 값을 나타낸다.

mysqladmin –u root –p  -i1 extended-status

 

 

MySQL 데이터베이스 엔진에서 extended-status 동일한 정보를 확인하는 벙빕은 show global status 명령이 있다.

show global status;

 


 

 

2018-03-23 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, show global status, MySQL 상태 모니터링, MySQL ,mysql 기본 정보, MariaDB 상태 정보 호가인, mysqladmin,


MySQL/MariaDB 서버 커넥션 사용 현황 모니터링

 

·      Version : MySQL 5.7.21

 

MySQL 서버는 MySQL 관리를 위한 전용 유틸리티인 mysqladmin 기본적으로 제공한다. mysqladmin 이용하면 MySQL 서버의 주요 설정값과 상태 정보를 쉽게 확인 있다.

 

mysqladmin 명령어 위치와 사용형식은 다음과 같다.

·       명령어 위치 : /usr/local/mysql/bin/mysqladmin

·       사용형식 : mysqladmin –u root –p 명령어

 

도움말은 mysqladmin –help 명령어로 확인할 있다.


 

mysqladmin 에서 ‘processlist’ 옵션을 함께 사용하면 현재 MySQL 서버의 DB 커넥션 현황과 커넥션별 스레드 상태 등을 데이터베이스 커넥션의 기본적인 정보를 모니터링 있다.

mysqladmin –u root –p processlist

 


 

아래 명령은 mysqladmin 이용하여 유휴(sleep)상태가 아닌 커넥션을 1초마다 모니터링한다.

mysqladmin –u root –p –i1 processlist | grep –v Sleep

 


 

grep 옵션

·       -i : 패턴에 대한 대소문자 구별 없이 검색

·       -r : 서브디렉토리 이하 파일들까지 포함하여 검색

·       -v [패턴] : -v 이하 패턴을 포함하지 않는 줄을 기준으로 검색

·       -l : 패턴에 맞는 문서이름만 출력

 

mysql 서버에서도 현재의 커넥션 정보를 확인할 있다.

show processlist;

 


 

 

 

 

2018-03-22 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, show processlist, MySQL 커넥션 확인, MySQL ,thread client, MariaDB 커넥션 확인, mysqladmin, grep, 커넥션 모니터링






MySQL/MariaDB Cluster Index

 

·      Version : MySQL / MariaDB

 

인덱스에서 클러스터링은 값이 비슷한 것을 묶어서 저장하는 형태로 구현된다. 방법은 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다. MySQL에서는 InnoDB 스토리지엔진에서 클러스터링 인덱스를 지원한다. (인덱스는 MySQL엔진이 아닌 스토리지 엔진이 담당한다)

클러스터링 인덱스( 클러스터) 테이블의Primary  key(이하 PK 표시) 대해서만 적용되는 내용이다. PK 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 중요한 포인트는 PK 값에 따라 레코드의 저장 위치가 변경되는데 뜻한 해당 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것이다. 따라서 PK 값으로 클러스터링된 테이블은 PK 자체에 대한 의존도가 높기 때문에 PK 결정할때 데이터의 특정 등을 신중히 판단해야 한다. 일반적으로 InnoDB 처럼 클러스터링 인덱스로 저장되는 테이블은 PK 기반 검색이 빠르며, 대신 레코드 저장이나 PK 변경(데이터 위치가 변경될 있으므로) 상대적으로 느릴수 밖에 없다.

 

클러스터링 인덱스 구조는 일반 B-Tree 인덱스 비슷하게 닮아 있다.


 

하지만 B-Tree 리프노드와는 달리 클러스터링의 인덱스의 리프노드에는 레코드의 모든 칼럼이 같이 저장되어 있다.



클러스터링 테이블은 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다. InnoDB에서 PK 없는 테이블에 대해서는 아래 순서대로 PK 대체할 컬럼을 선택한다.

1.     프라이머리 키가 있으면 기본적으로 PK 클러스터 키로 선택

2.     NOT NULL 옵션의 유니크 인덱스 (unique index)중에서 번째 인덱스를 클러스터 키로 선택

3.     자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 클러스터 키로 선택.

InnoDB  스토리지 엔진이 적절한 키를 찾지 못하여 내부적으로 자동 증가 컬럼을 추가한 경우 이는 사용자에게 노출되지 않으며 쿼리에서 명시적으로 사용할 없다. 이는 사용자에게 아무런 혜택이 없으므로 가능한 PK 명시 있도록 한다.

 

[클러스터 인덱스 장점]

1.     PK(클러스터 ) 검색할때 처리 성능이 매우 빠름

2.     테이블의 모든 보조 인덱스가 PK 가지고 있기 때문에 인덱스만으로 처리 있는 경우가 많음(커버링 인덱스)

 

[클러스터 인덱스 단점]

1.     테이블의 모든 보조 인덱스가 클러스터 키를 갖기 때문에 값의 크기가 경우 전체적으로 인덱스의 크기가 커짐

2.     보조 인덱스를 통해 검색 할때 PK 값을 한번 확인해야하기 때문에 성능이 조금 느림

3.     INSERT PK 인해 저장위치가 결정되기 때문에 처리 성능이 느림

4.     PK 변경할때 DELETE INSERT 작업이 필요하기 때문에 처리 성능이 느림.

 

[클러스터 테이블 사용시 주의사항]

1.     PK 크기가 커지면 보조 인덱스도 자동으로 커지므로 크기에 주의

2.     PK 커지더라도 비즈니스적으로 빈번하게 사용되는 대표적인 값이면 PK 설정하여 사용

3.     PK 반드시 명시할 . (명시하지 않을 경우 내부적으로 자동 증가값 사용)

 

 

2017-11-09 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, Clustering Table, 클러스터 테이블, 클러스터 , Index, 인덱스, InnoDB, 스토리지 엔진




MySQL/MariaDB 테이블의 Row count 구하기

 

·      Version : MySQL 5.6

 

MySQL / MariaDB 에서 테이블의 Row count 구하는 방법에 대해서 알아본다. 기본적으로 단일 테이블에 대한 Row count 아래 스크립트로 간단히 확인할 있다.

SELECT 

     COUNT(*)

 FROM

    table_name;

 

특정 데이터베이스에 대해 모든 테이블의 Row count 구하는 경우 아래 스크립트를 사용할 있다. 조회하려는 데이터베이스의 테이블 정보를 참조하여 Row count 스크립트르 생성하여 호출하는 방식이다.

SELECT 

     CONCAT(GROUP_CONCAT(CONCAT('SELECT \'',table_name,'\' table_name, COUNT(*) rows FROM ', table_name)SEPARATOR ' UNION '),' ORDER BY table_name' ) INTO @sql 

 FROM

(

SELECT 

            table_name

FROM

            information_schema. tables

WHERE table_schema =  'DBNAME'

            AND table_type =  'BASE TABLE' ) table_list

 

 

PREPARE s FROM @sql ;

EXECUTE s;

DEALLOCATE PREPARE s;

 

MySQL 8.0 이상을 사용하는 경우 CTE 사용할 있다.

WITH table_list AS (

SELECT

            table_name

FROM information_schema. tables 

WHERE table_schema = 'DBNAME'

            AND table_type = 'BASE TABLE'

)

 

SELECT

            CONCAT(GROUP_CONCAT(CONCAT("SELECT '" ,table_name, "' table_name,COUNT(*) rows FROM " ,table_name) SEPARATOR " UNION "), ' ORDER BY table_name') INTO  @sql

FROM  table_list;

 

PREPARE s FROM @sql ;

EXECUTE s;

DEALLOCATE PREPARE s;

 

데이터베이스의 모든 테이블에 대한 Row count 얻는 가장 빠른 방법은 information_schema 데이터베이스에서 조회하는 것이다.

SELECT 

            table_name,

            table_rows

FROM

            information_schema. tables

WHERE table_schema =  'DBNAME'

ORDER BY table_name;

 


 

하지만 방법은 실제 데이터 행수와 동기화 되지 않기 때문에 정확하기 않을 있다. 이를 방지 하려면 데이터를 조회하기 전에 ANALYZE TABLE 명령을 실행하도록 한다.

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...

 

실제 정확한 행수가 필요하지 않는 경우information_schema 조회하는것이 데이터베이스 오버헤드를 줄일 있어 권장하는 방법이다.

 

 

[참고자료]

·       https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

·       https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

 

2017-10-25 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, MySQL row count, MySQL information_schema, Derived table, Group_concat, concat, table variables



MySQL/MariaDB InnoDB Storage Engine (Data Page)

 

·