mariadb 성능 향상

기초

데이터 베이스의 모든 이름(데이터 베이스, 테이블), 칼럼에는 소문자를 사용하는게 좋으며 공백 대신 _를 사용합니다.
데이터 베이스 조작어 (CREATE, SELECT 등)는 대문자를 사용하는 것이 좋습니다. 

인덱스

인덱스 조건 : select 많음, insert,update,delete 적은 테이블

바이너리 로그

바이너리 로그는 복제를 위해 사용되거나 PIT (Point in Time) 복구를 위해서도 사용됨

테이블 파티셔닝

하나의 큰 테이블로 사용하는 경우 인덱스도 커지고 물리적인 공간도 필요

가상칼럼

다른 칼럼의 의해서 자동으로 설정된 기능, insert 이후 값 변경은 불가

mariadb 정규표현식 기능 확장

Client 연결 처리 방식

1개의 클라이언트 연결 = 1개의 전용 쓰레드 생성
1000개의 클라이언트 연결 = 1000개의 전용 쓰레드 생성

: 경합이 심해지고 성능 떨어짐, 상태와 상관없이 1000개가 생성되어 있어야 한다.

슬로우 쿼리 설정

쿼리 타임이 몇초 이상인 쿼리에 대해 로그 파일에 기록하여 원인 분석

mysql 테이블 유지보수

check table 'table 명'
optimize table 'table 명'

Thread Pool

커뮤니티 버젼 사용 할 수 없음
엔터프라이즈 버젼에서 사용 가능 (커넥션이 많아도 처리량이 계속 유지)

show variables like '%thread%';

| thread_handling | one-thread-per-connection |

cat /etc/my.cnf.d/server.cnf

[mysqld]

thread_handling=pool-of-threads

show variables like '%thread%';

| thread\_handling | pool-of-threads |

thread\_pool_idle_timeout : 유효 스레드가 종료 전 대기하는 시간 제한

thread_pool_max_threads : 풀에 있는 스레드의 최대수 제한

thread_pool_size : 동시에 CPU 사용할 수 있는 스레드 수

thread_pool_oversubscribe : 값이 클수록 더 많은 스레드를 동시에 실행 할 수 있음

thread_pool_stall_limit : 롱 쿼리 제한 설정, 제한에 도달하면 새로운 스레드를 만들거나 풀이 일어남

hint

sql 문에 정보를 주어 실행에 빠른 처리 결과을 기대
쿼리 프로파일링
쿼리가 처리되는 동안 단계별 작업시간에 대한 통계정보 제공 (성능을 예측)

I/O

Copying to tmp table : 메모리에 있는 tmp TABLE 에 복사하는 상태

tmp_table_size 33554432
max_heap_table_size 134217728

연관있는 변수 옵션 (1기가 정도 확장?)

copying to tmp table : 정렬하는 데이터 크기가 tmp_table_size, max_heap_table_size 보다 작아서 memory 내에 tmp 임시테이블 생성
copying to tmp disk : 정렬하는 데이터 크기가 tmp_table_size, max_heap_table_size 보다 커져서 memory가 아닌 디스크에 tmp 임시 테이블 생성

tmpdir=/temp001/masvc01/OLDDB:/data001/masvc01/OLDDB

복수지정 tmpdir 은 라운드로빈 처럼 사용

BIG TABLES = 0 | 1

1로 설정하면 모든 임시 테이블들은 메모리가 아닌 디스크에 저장된다. 그러나 에러문인 The table tbl_name is full은 큰 임시 테이블을 필요로하는 SELECT 연산에 대해서는 일어나지 않는다. 새로운 연결에 대한 기본값은 0(메모리 임시 테이블)이다. 보통, In-메모리 테이블들은 필요할 경우 disk 기반 테이블들로 자동으로 변환되기 때문에, 여러분은 이 값을 설정할 필요가 없다. (Note: 이 변수는 SQL_BIG_TABLES로 명명되었다.)

참고(원문) 사이트

https://opentutorials.org/module/384/3462