#devspace

RealMySQL 8.0을 읽고 정리했습니다.

DBMS Variables 비교 페이지를 만들 때 글로벌인지 세션인지 대해서 구분할 일도 있고 했는데,,

이번 기회에 정리해보고자 한다.

MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉜다.

글로벌 변수

• MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수
• MySQL 서버 자체에 관련된 설정일 때가 많음
• MySQL 서버에서 단 하나만 존재
  • innodb_buffer_pool_size : InnoDB 버퍼 풀 크기
  • key_buffer_size : MyISAM의 키 캐시 크기

세션 변수

• MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용
• 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것
• 기본 값: 글로벌 변수 / 각 클라이언트가 가지는 값: 세션 변수
• autocommit: 각 클라이언트에서 쿼리 단위로 자동 커밋 수행 여부 결정
• 커넥션별로 설정값을 서로 다르기 지정, 한번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없음
• 세션 변수 중 my.cnf에 명시해 초기화할 수 있는 변수 → var scope가 대부분 Both
  • MySQL 서버가 기억만 하고 있다가 실제 클라이언트와의 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용되는 값
• 순수하게 Session 범위인 시스템 변수는 my.cnf에 초기값을 명시할 수 없음, 커넥션이 만들어지는 순간부터 해당 커넥션에만 유효한 설정 변수임

MySQLDBA 운영교육에서 퍼코나 툴킷에 대해 접하게 되었다.

어떤 기능을 하는 툴인지 알아보자!

Percona Toolkit

• Percona 사에서 제작한 Toolkit 으로서 DB의 성능 추이, Disk 사용량, DB 내 통계정보를 정리하여 쉽게 보여주는 tool
• 기능 별로 스크립트가 구성되어 있음 (위치: /db/mysql/util/ptkit/bin)

아래 공식 사이트를 보면 다른 DBMS에 대해서도 지원하는 것 같은데

우리 팀에서는 내가 아는 한에서는 일단 MySQL 운영에서 사용하고 있다.

https://percona.com/

MySQL 운영을 도와주는 툴이고, 종류가 많은데 크게 3가지를 사용한다고 한다.

pt-query-digest
slow query가 많을 경우, 쿼리를 parsing하고 결과를 summary하여 보여줌
쿼리의 유형, 빈도, 비중 등 확인

pt-summary
하드웨어 정보 요약, DB서버의 하드웨어 스펙를 뽑아서 저장할 때 좋음
디스크, 메모리 등

pt-online-schema-change
online alter를 할 수 있게 해주는 툴

어떨 때, 어디에, 어떻게 사용하는 건지는 차차 더 알아봐야겠다..

• 참고 사이트
  • https://dbknowledge.tistory.com/12 
  • https://m.blog.naver.com/sory1008/221173627094

클러스터 테이블 vs 논클러스터 테이블 비교

클러스터란?

• 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장하는 방법
• 비슷한 레코드들을 동시에 조회하는 경우가 많아 효율적으로 조회하는 방법으로 고안됨
• PK가 비슷한 레코드끼리 묶어서 저장하는 것 (MySQL)

클러스터 테이블(인덱스)

• 테이블당 1개씩만 허용됨
• PK 생성 시 그 컬럼은 자동으로 클러스터 인덱스가 생성됨
  * 일반 인덱스와의 차이
  • 클러스터링 되어 있다는 요소가 추가됨
  • 옵티마이저에서 일반 인덱스보다 높은 랭킹
• PK를 설정하지 않은 경우는? InnoDB가 다음의 우선순위대로 대체 컬럼을 선택
  • UNIQUE NOT NULL 중 첫번째 인덱스를 클러스터 키로 선택
  • 자동으로 유니크한 값을 가지도록 AUTO_INCREMENT 컬럼을 내부적으로 추가해 클러스터 키로 선택
    * 하지만, 자동으로 추가되는 컬럼은 사용자에게 보이지 않아서 SQL에서 사용할 수가 없음
• PK에 의해 레코드 저장 위치가 결정됨
  • PK값이 변경되면 -> 레코드의 물리적으로 행을 재배열함 -> 랜덤 액세스가 많이 발생해서 I/O 증가
  • PK를 신중히 결정해야 함
• 인덱스 페이지의 리프 페이지가 곧 데이터. 즉, 테이블 자체가 인덱스
• InnoDB 스토리지 엔진에서만 지원함
• 장점
  • 그룹된 컬럼 데이터 행들이 같은 데이터 블록에 저장되기 때문에 디스크 I/O가 줄어 SELECT 시 처리 성능이 매우 빠름
  • 클러스터된 테이블 사이에 조인이 발생하면 처리 시간이 단축됨
  • INSERT, UPDATE, DELETE 시 항상 정렬 상태를 유지함
  • 이미 정렬된 상태로 저장되기 때문에 인덱스 테이블이 필요하지 않아 논클러스터보다 DB 용량을 덜 차지함
  • 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)
• 단점
  • 논클러스터보다 검색 속도는 더 빠르지만 INSERT, UPDATE, DELETE는 느림

논클러스터 테이블(인덱스)

• 테이블당 249개 허용됨
• 레코드의 원본은 정렬되지 않고, 인덱스 테이블만 정렬됨
  • 인덱스 페이지는 로그파일에 저장됨
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터보다 검색 속도는 느리지만 INSERT, UPDATE, DELETE는 더 빠름

비교

• 쉽게 책에 비유하자면 클러스터 테이블은 페이지를 알기 때문에 바로 그 페이지를 펴는 것(바로 접근)이고, 논클러스터 테이블은 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것(한번 거쳐서 접근)과 같음

참고링크

• https://enterone.tistory.com/230
• https://vaert.tistory.com/110
• https://gilssang97.tistory.com/52
• https://brownbears.tistory.com/183
• https://junghn.tistory.com/entry/DB-클러스터-인덱스와-넌클러스터-인덱스-개념-총정리
• http://www.gurubee.net/lecture/1114

Django는 설치할 때 기본적으로 SQLite가 연동됩니다.

하지만 실제 프로젝트를 위해 MySQL 연동 작업을 진행해보겠습니다.

1. 커넥터 설치

mysqlclient 라는 라이브러리를 설치하면 파이썬과 MySQL 통신이 가능합니다.

pip install mysqlclient

2. settings.py 설정

DATABASES = {
    'default' : {
        'ENGINE': 'django.db.backends.mysql',    
        'NAME': '연동할 데이터베이스 이름',                  
        'USER': 'DB 접속 계정명',                          
        'PASSWORD': 'DB 접속 비밀번호',                  
        'HOST': '실제 DB 주소',                     
        'PORT': '3306',         # 기본 포트 주소                  
    }
}

3. DB 감지

해당 DB에 존재하는 테이블들을 model로 만들어줌

# 앱 안에 models.py에 바로 저장하기
python manage.py inspectdb > [앱이름]/models.py

# 앱 안에 models.py에 바로 저장하기 (멀티 DB)
python manage.py inspectdb --database 'db명' > [앱이름]/models.py

# 출력만 하기
python manage.py inspectdb

4. 테이블 생성

모델이 변경되었으니 마이그레이션 적용하기

python manage.py makemigrations
python manage.py migrate

과제 내용

• 스크립트를 실행하면 비밀번호 없이 접속할 수 있도록 작성
• 가장 최신 버전을 설치함

sh install_mysql.sh

개발환경

• NHN Cloud
• CentOS 7.9

프로세스

코드

• yum repository 업데이트

• MySQL이 이미 설치되어 있는지 확인
  • 설치된 경우 삭제

• MySQL 설치

• MySQL 실행
  • 임시 비밀번호로 로그인

• 설치결과 로그 파일로 저장

결과