'분류 전체보기'에 해당되는 글 380건

이번에 stored function 관련 full scan 튜닝이 끝나면 innodb status를 보고 좀더 개선포인트가 있는지 본격적으로 my.cnf 메모리 튜닝을 검토해볼 생각이다.

튜닝 가이드 중에서 잘 정리된 내용(SK Cloud Z Support Portal > Cloud Z DB >이승철님)이 있어 정리해놓는다.

Mariadb의 메모리는 크게 두 가지로 분류가 됩니다.

-> 글로벌 메모리영역은 공유하고 세션메모리영역(connection당)은 공유하지 않는다.

이것에 대한 하나 예를 들면 오라클과 postgres를 mysql로 마이그레이션을 진행한다면 다른 것보다 function과 procedure의 사용이 많은지 확인해봐야하고 어느부분에서는 Dynamic sql로 변경을 해야할 수도 있다.

오라클, postgres는 function과 procedure를 글로벌메모리영역에서 사용하지만 mysql은 일단 성능도 그들의 1/2배이며, global 메모리 영역에서 쓰지 않고 session 메모리 영역에서만 사용하기 때문에 성능이슈가 발생할 가능성이 더 크다. 인덱스스캔이 제대로 된다면 별문제 없지만.. 이후 function이나 procedure가 바뀌고 기존의 실행계획이 틀어진다면 바로 풀스캔 및 디스크I/O로 이어져 성능이슈가 발생할 가능성이 있다......mysql 잇점이 있다면 경험상 postgres는 procedure가 뭔짓을 하는지 모르다가 out of memory 떨구고 DB가 죽었지만, mysql은 session(connection) 메모리가 full이어서 disk로 넘어가게 되면 이미 성능이슈로 서버의 응답시간이 지연되는 것을 이미 다른 SM이 알기 때문에 DB 장애로 넘어가기 전에 미리 확인이 가능했다.

1. Global 메모리 영역
: DB가 최초 기동되었을 때에는 메모리를 최소한만 사용하다가 설정된 값 까지 증가하며 증가한 이후에는 "메모리를 반환하지 않고" 설정 된 값 이내에서 계속 사용됩니다.
(오라클의 경우 DB기동시 설정된 값 만큼 메모리를 할당 받고 올라가는 반면 Mariadb 는 기동시 설정된 메모리 값만큼 할당 받는것이 아닌 설정된 값 만큼 서서히 증가하게 됩니다)
•Innodb_buffer_pool_size
•Key_buffer_size
•innodb_log_buffer_size
•tmp_table_size
→ Global 메모리 = Innodb_buffer_pool_size + Key_buffer_size + innodb_log_buffer_size + tmp_table_size

--> 대용량 테이블을 union all을 사용하는 sql호출이 많아지면서 성능이슈가 있었고 tmp_table_size을 사이즈를 512M -> 2G로 늘려서 DB 성능을 개선한 적이 있다. 해당 옵션을 튜닝하려면 max_heap_table_size도 같이 늘려줘야한다.

임시테이블 튜닝 가이드참조: https://sungwookkang.com/m/1229

2. Session 메모리 영역
Mariadb session 별로 사용되어지는 메모리 공간으로 Max connection 설정 값과 관련이 있습니다.
(커넥션을 맺고만 있을 경우에는 커넥션에 필요한 최소한의 메모리만 사용되어지며 조인, 정렬 등이 필요할 경우에는 아래 설정된 값만큼을 메모리에서 사용하게 됩니다.
때문에 정확한 예측값은 동시에 Active한 세션 수 이지만 기본적으로는 Max connection 수 만큼 동시 사용 세션이 수행될 수 있기에 아래 공식으로 계산되어 집니다.)
•sort_buffer_size
•read_buffer_size
•read_rnd_buffer_size
•join_buffer_size
•thread_stack
•binlog_cache_size
•Max connection
→ Session 메모리 = (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size) x Max connection

*예시
만약 Mariadb의 설정값이 다음과 같다면...
Global 메모리
•Innodb buffer pool size: 2048 MB
+ Key buffer size: 16MB
+ innodb log buffer: 10MB
+ tmp table size: 64MB
= 약 2.1 GB

Session 메모리
•( sort_buffer_size: 2MB
+ read_buffer_size: 1MB
+ read_rnd_buffer_size: 1MB
+ join_buffer_size: 0.128 MB
+ thread_stack: 0.128 MB
+ binlog_cache_size: 1MB)
x Max connection 300
= 약 1.6 GB
이 됩니다.

따라서 global 메모리 영역 (2.1GB) + Session 메모리 영역(1.6GB) 에 추가로 Mariadb 기본 기동(350MB) + performance_schema data(150MB) + OS / 파일 버퍼링 공간(전체 메모리의 약 10%) 을 고려하여
위 설정된 값을 기준으로 전체 메모리의 적정 수치는 4,5GB ~ 5GB 정도가 적당해 보입니다.
위의 설명 및 예시를 참고하시어 MariaDB의 메모리를 설정해주시면 됩니다.

참조사이트:

support.cloudz.co.kr/support/solutions/articles/42000064656--cloud-z-db-mariadb의-메모리-설정을-어떻게-해야-하나요-

How number of columns affects performance?

-> 팀이동후 DBA로 업무분석하다 처음으로 정리한 내용이었다. 당시 mysql 5.5에 MyISAM이라 시도때도 없이 Lock이 잡히고 새벽에 장애나고 ; 가장 큰 문제는 납작두꺼비처럼 옆으로 퍼져버린 테이블이 문제였는데.. 테이블 정규화를 해야하는데 소스수정범위가 너무 크다보니 개발쪽에 일정잡기가 어려워 결론적으로 현재는 옆으로 더 퍼져있고;;.. 인덱스만 추가하고 function/procedure에서 full scan 안타게 조심해서 쓰고 있다.

한번 선언한 테이블의 정규화는 많은 시간과 비용이 든다는 것을 다시 한번 느끼게 된다.

1) innoDB engine에서 테이블에 최대생성 가능한 컬럼과 인덱스

- 컬럼 1017개

- 인덱스 64개

-> 적정 컬럼 수는 40개 내외라고 한다.

2) 컬럼을 늘릴 때 발생할 수 있는 이슈

- 테이블에 인덱스 검색을 한다고 하더라도 DB 내부 Logic은 인덱스조건에 해당하는 row의 전체를 읽기 때문에 한번에 읽을 데이터의 값이 커지고 io에 부담을 준다.

DB컬럼은 여러 개의 Linked List로 구성되어있고 인덱스를 사용하더라도 해당열의 전체 값을 가져오기 때문에 컬럼이 늘어날수록 데이터 조회 비용은 늘어날 수밖에 없다.

#아래는 (프로게이밍연구실)에서 발췌한 내용임

DB에서 연결리스트를 가장 많이 사용하는 부분이 바로 테이블

간단하게 예를 들어 만든 테이블 구조입니다. 위 구조라면 한 행의 크기는 68 BYTE의 작은 테이블이지요.

온라인 게임에서 사용되는 테이블의 크기는 보통 이렇게 작은 데이터들로 이루어지기 때문에 별 문제가 발생하지 않습니다

이렇게 쿼리를 날린다면 가져오는 데이터는 총 62 BYTE의 데이터를 가져옵니다.

하지만 데이터베이스 엔진의 내부 알고리즘의 움직임에 조금 관심을 가져본다면 ‘어떤 과정을 통해 데이터를 가져오는 것일까?’라는 생각을 해보신다면 재미있는 것을 알 수 있습니다.

바로 ‘연결된 리스트’라는 것이 그 재미의 핵심입니다.

아무리 인덱스를 사용하여 데이터를 빠르게 가져온다고 해도 연결된 리스트라는 녀석은 내부적으로 한 행의 모든 데이터내용을 싹 읽어버립니다. 게임과 같이 작은 데이터를 다룬다면 큰 문제가 없겠지만 아래처럼 게시판일 경우에는? 얘기가 완전 달라지는 것이죠.

위에 보이는 것처럼 큰 데이터 열이 있다면 연결된 리스트로 데이터를 읽을 때에 어마어마한 비용이 발생하겠지요?

그런데 위에서 보이는 것과 같이 큰 크기의 열이 있으면 그만큼 많은 페이지를 읽어야 하고 어마어마한 데이터를 ‘사용하지 않더라도 읽어야 하는 것’이죠.

인덱스를 사용하면 빠르게 데이터를 조회할 수 있지만 만약 게시판에 정말 많은 량의 내용을 담은 게시물이 있을 경우 성능을 보장할 수 없습니다.

그렇다면 ‘어떻게 하면 이런 재앙을 막을 수 있을까?’에 대한 연구해봅시다.

개인적으로 저는 저렇게 큰 데이터 열을 별도의 테이블로 분리시켜야 한다고 생각합니다.

‘게시물을 가져올 때에 JOIN을 하게 되니 성능이 더 나빠지지 않느냐!?’ 라고 반박하시는 분께서 계실지 모르겠지만, 인덱스를 사용해서 정확하게 SEEK한 데이터는 무조건 0초 만에 수행됩니다.

게시물 본문과 글 번호를 별도의 테이블로 분리한 후 본문을 조회할 때에만 조인하여 가져오는 방식이 제가 생각할 수 있는 최고의 성능을 보장할 수 있는 최선의 방법입니다.

혹시라도 이런 고민을 해보신 적 있으시다면 같이 연구해보면 정말 좋을 것 같습니다.

3) 성능 테스트 (ex 컬럼count 100개)

- 결과 char, int 컬럼의 경우 100개까지 늘려도 성능에 큰 무리가 없지만

varchar 컬럼의 경우 io가 slowdown되어 성능에 큰 영향을 줌

참조사이트:

https://www.percona.com/blog/2009/09/28/how-number-of-columns-affects-performance/

1. 실행중인 쿼리의 explain 기능

1-1) 기능설명

Mysql을 관리하고 있다면 show processlist를 사용해 장시간 실행중인 sql이 없는지를 조사하는 사람이 많을 것이다.
그 결과 장시간 실행하고 있는 sql을 발견하면 왜 실행에 시간이 걸리는지 알아내기 위해 EXPLAIN 명령어를 사용한다.
EXPLAIN 명령어를 사용하기 위해 SHOW FULL PROCESSLIST 명령어로 SQL 전문을 가져온 다음, 이를 복사해서
EXPLAIN을 실행했을 거이다. 하지만 실행중인 쿼리의 길이가 길면 SQL을 출력하여 복사하는 것도 아주 번거로운 일이다.
Mysql 5.7에서는 이처럼 불필요한 조작을 할 필요가 없어졌다.
커넥션 ID를 저장하기만 해도 실행중인 SQL의 EXPLAIN 결과를 볼수 있게 되었다.
예를 들어 장시간 실행 중인 SQL의 커넥션 ID가 777이라고 하자. 리스트 3.3은 그 커넥션에 대한 EXPLAIN을 출력하는 명령어다.

#실행방법

explain for connection (프로세스ID);

1-2) 테스트

mysql> show processlist;
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User   | Host                  | db        | Command | Time | State        | Info                                                                                                 |
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 33 | xxxadm | xxx.xxx.xxx.148:42939 | xxxxxxxxx | Query   |   11 | Sending data | /* ApplicationName=DBeaver 7.0.3 - SQLEditor <Script.sql> */ select
T1.*,
case 
when (select  |
| 34 | root   | localhost             | NULL      | Query   |    0 | starting     | show processlist                                                                                     |
+----+--------+-----------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

--> 일단 프로세스 리스트로 현재 수행중인 슬로우 쿼리를 확인한다.

mysql> explain for connection 33;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

--> 안된다.. 이유는 CREATE TABLE SELECT ~ 라서 ^^;;

select문만 실행해보자.

mysql> explain for connection 33;
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
| id | select_type        | table                | partitions | type | possible_keys | key        | key_len | ref                  | rows    | filtered | Extra       |
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
|  1 | PRIMARY            | T1                   | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                 |     286 |   100.00 | NULL        |
|  4 | DEPENDENT SUBQUERY | TB_xxxxxxx           | NULL       | ref  | TB_xxxxxxx    | TB_xxxxxxx | 5       | xxx.T1.seq_no        |       1 |   100.00 | Using index |
|  3 | DEPENDENT SUBQUERY | TMP_LOAD_20200506_01 | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                 | 6851030 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | TB_xxxxxxx           | NULL       | ref  | TB_xxxxxxx    | TB_xxxxxxx | 5       | xxx.T1.seq_no        |       1 |   100.00 | Using index |
+----+--------------------+----------------------+------------+------+---------------+------------+---------+----------------------+---------+----------+-------------+
4 rows in set (0.00 sec)

--> SELECT으로 바꾸면 아래처럼 바로 확인할 수 있다. 진짜 편리한 기능이다. 장애때 유용하게 써먹을 수 있을 거 같다.

1. 목적

상용서비스에 슬로우 쿼리 이슈가 있어 확인해보니 select ~ from 안에 function을 사용했는데

이게 문제가 되어 응답시간이 느려지고 서비스 지연이 발생하였다.

근데 이게 원래는 슬로우 쿼리가 아니었는데 왜 갑자기 튀어나왔는지 보다보니

지난주에 대용량 테이블에 인덱스 추가 3개를 하고 나서 사이드 이펙트로 튀어나왔다. ㅠ

인덱스 추가하고 나서 아무래도 fucntion의 로컬 메모리(connection 당) 사용이 어느부분에서 MAX를 넘어 디스크를 사용하게 되었고 이게 Disk I/O를 치는 이유로 보인다.

--> mysql은 오라클과 달리 function이나 procedure가 global memory 영역을 사용하지 못하고 connection당 local 메모리가 my.cnf에 정의되어 있고 그 이상 사용할 경우 Disk로 넘어가 스토리지 엔진의 핸들러가 바뻐지게 되고 이게 Disk I/O를 발생시킨다.

슬로우쿼리 설정값은 2초이고 인덱스 3개 추가 후 현재는 14초 이상이 소요되고 있다.

대용량 참조하는 fucntion의 로직을 개선하려고 보니 쓰는 곳이 너무 많아서 수정하게 되면 테스트할 곳이 많다고

개발, 기획파트에서 얘기가 많다.ㅠ

고민중 fucntion에 캐시를 쓸 수 있는 DETERMINISTIC 옵션이 있는 것을 확인하고 테스트를 진행하였다.

mysql function에 DETERMINISTIC, NOT DETERMINISTIC 이라는 설정이 있다.

#장비사항

DB version : mysql 5.7.19

MEM : 16G ( innodb buffer pool :16G =테스트 용도로 Max로 잡음)

2.옵션 정의

characteristic
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
SQL SECURITY { DEFINER | INVOKER }
COMMENT 'string'

1) DETERMINISTIC
- 입력값이 같은면 결과는 늘 같다.

- 쿼리가 실행되면 그때 한번 호출하고 그 쿼리 안에서 상수값을 돌려줌
다시 쿼리가 호출되면 다시 실행됨

2) NOT DETERMINISTIC
- 입력값이 같아도 시간에 따라 결과는 다르다.

- 디펄트가 늘 변함 임으로 속도가 느려질수 있다 꼭 구분해서 사용하자

--> 따로 설정하지 않으면 NOT DETERDETERMINISTIC 이다

3. 추가 설명

DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해
항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면,
디폴트는 NOT DETERMINISTIC이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는
DETERMINISTIC를 확실하게 지정해 주어야한다.

NOW() 함수(또는 동일 기능함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게
만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은
올바르게 포함한다. 또한 RAND()도 함수내에서 일단 한번 호출되어 지면 정확하게 복사본을 만들게 된다.
(함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는
동일한 암시적 입력(implicit input)으로 간주할 수 있다.)

--> function 안에 now() 있는데 넣다 뺐다 둘다 테스트 해봐야할 거 같다.

contains sql, no sql, reads sql data, modifies sql data
특성은 함수가 데이터를 읽거나 또는 스는 정보를 제공한다. NO SQL 또는 Read sql data는 함수가 데이터를
변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이
되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야한다

--> 이렇게 얘기하긴 하는데 찾아보니 별로 중요하지 않다는 얘기가 더 많다.

1) CONTAINS SQL : 데이터를 읽거나 쓰는 명령문을 갖지 않음.
2) NO SQL : 아무런 SQL명령문이 없음.
3) READS SQL DATA : 데이터를 읽는 명령문.
4) MODIFIES SQL DATA : 데이터를 쓰는 있는 명령문
5) CONTAINS SQL : DEFAULT

6) SQL SECURITY : 사용자가 생성/수정 권한인지, 호출 권한인지 명시 

4. 테스트

4-1) 쿼리 캐시 OFF 일떄

DEFAULT 설정

-> 수행시간 13초

DETERMINISTIC  설정
-> 수행시간 13초 (똑같네;;)

4-1) 쿼리 캐시 ON 일떄

mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

#my.cnf 설정하고 재시작한다.
mysql> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

#쿼리캐시 설정 옵션설명

1) Query_Cache_Type = ON
– 예외사항을 제외한 모든 SQL결과를 메모리에 캐싱하겠다.
2) Query_Cache_Type = OFF
– Query Cache를 사용하지 않겠다.
3) Query_Cache_Type = DEMAND

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (더 느리다;;)

캐시를 쓰는지 확인해본다.

mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 18       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

--> Qcache_hits  0 캐시 안타네;; now() 때문에 그런가 now() 빼고 테스트해본다.

4-1) 쿼리 캐시 ON + function안에 now() 제거

DEFAULT 설정

-> 수행시간 14초

DETERMINISTIC 설정
-> 수행시간 14초 (똑같다;;)

mysql> show global status like 'qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091584 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 54       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

--> 역시나 Qcache_hits 0 캐시 안쓴다.

음 mysql 매뉴얼을 좀 더보니 쿼리캐시를 사용하지 못하는 경우라는 게 있다.;

[쿼리 캐시를 사용하지 못하는 경우]

1) 임시 테이블에 대한 쿼리
2) 사용자 변수의 사용(프리페어 스테이트먼트와 동일하게 작용)
3) 칼럼 기반의 권한 설정
4) LOCK IN SHARE MODE 힌트
5) FOR UPDATE 힌트
6) UDF(User Define Function)사용
7) 독립적인 SELECT문장이 아닌 일부분의 서브 쿼리
8) 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리 --> 아....
9) SQL_NO_CACHE 힌트

5.결론

쿼리캐시 사용하고 function 에 DETERMINISTIC 을 설정하더라도
스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리는 캐시를 쓰지 않는다.
--> function 튜닝은 결국 한 세션에 로컬 메모리를 적게 사용(table cache를 줄임) 옵티마이져 cost를 개선하는 방법 말곤 없는 거 같다.

function 로직 개선을 위해 개발, 기획파트랑 다시 얘기좀 해봐야겠다.

5.2 테이블 파티셔닝을 활용해 대용량 데이터를 관리하자

#파티셔닝 간단히 이해하기

파티셔닝은 하나의 커다란 데이터를 여러 개의 데이터로 분할 저장함으로써 데이터 선택 효율을 높이는 기법이다.

특히 InnoDB에서는 PK순으로 데이터가 저장되기 때문에 무작위로 PK를 생성하여 insert를 수행하면 성능이 급격히 저하될 수 있다. 이 경우 테이블에 파티셔닝을 적용하면 비효율을 어느 정도 극복할 수 있다. 하지만 파티셔닝 사용에는 다음과 같은 몇 가지 제약 사항이 있다.

1) 파티셔닝 키는 PK와 연관된 컬럼이어야 한다.

2) 파티셔닝 사용시 Unique Key혹은 Forgien Key와 같은 제약조건을 추가할 수 없다.

(단 Unique Key가 PK로 사용되는 경우는 제외)

3) 파티셔닝 적용 시 풀텍스트 인덱싱 혹은 스페셜 인덱싱 기능을 사용할 수 없다.

4) 데이터 조회시 반드시 파티셔닝 키가 포함되어야하며 그렇지 않은 경우 전체 데이터 파일을 스캔한다.

--> 반드시 파티셔닝 키가 포함되어야하며 그렇지 않을경우 Full scan 한다는 얘기.. ㅎㄷㄷ

#파티셔닝 테이블 스키마

CREATE TABLE partition_test (
ID varchar(50) NOT NULL,
NAME varchar(40) NOT NULL,
TYPE varchar(20) NOT NULL,
DETAIL varchar(255) NOT NULL,
REG_TIME datetime NOT NULL,
KEY(ID)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(REG_TIME)) (
    PARTITION P_MIN VALUES LESS THAN (to_days('19000101')),
    PARTITION P_20110516 VALUES LESS THAN (to_days('20120517')),
    PARTITION P_20110517 VALUES LESS THAN (to_days('20120518')),
    ....
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

쿼리에서 P_MIN을 넣은 이유는 불필요한 파티셔닝 데이터 스탠을 방지하기 위해서다.

날짜 데이터를 파티셔닝한 상태에서 RANGE 파티셔닝 옵션으로 범위 검색(예를 들어 BETWEEN과 같은)을 하면 가장 앞단의 파티셔닝 파일을 스캔한다. 그런데 가장 앞단의 파티셔닝에 데이터가 수천만 건 저장되어 있다면 범위 검색을 할 때마다 대용량 데이터를 매번 스캔해야 하는 비효율이 발생한다. 즉, 불필요한 스캔을 방지하기 위해 생성한 깡통 파티셔닝이라고 보면 된다(Mysql 5.5 이상에서는 Range Column 옵션으로 날짜를 파티셔닝해도 이러한 문제가 발생하지 않는다) --> Mysql 5.5 이하버전에서는 MIN을 반드시 넣어야한다는 얘기 5.5이상에서는 굳이 Min 넣을 필요 없다.

#파티셔닝 플랜확인

mysql> explain partitions
select * from partition_test
where REG_TIME BETWEEN '20120517' and '20120518'

#파티셔닝 추가

파티셔닝을 하나 더 추가하기위해서 다음과 같이 P_MAX 파티셔닝 테이블을 재정의한다. 여기서 P_MAX 파티션에 데이터가 많으면 파티셔닝이 추가되는 동안 테이블 잠금이 발생하여 서비스에 영향을 줄 수 있으므로, 재정의 전에 반드시 확인하는 습관을 들이도록 하자.

-->P_MAX에 데이터가 많으면 재정의하는데 시간이 많이 걸리므로 count(*) 데이터가 얼마나되는지 확인하라는 얘기며

혹시 예상치 못한 락이 발생할 수도 있으므로 실행 전에 파티션테이블에 락이 있는지 확인한다.

select * from information_schema.metadata_lock_info;

ALTER TABLE partition_test REORGANIZE PARTITION P_MAX INTO (
	PARTITION P_20120523 VALUES LESS THAN (to_days('20120524')),
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

--> 기존 스크립트는 MAXVALUE Drop 후에 재생성했었는데 REORANIZE 로 바꿔봐야겠다.

#파티셔닝 삭제

ALTER TALBE partition_test DROP partition P_20110516;

5.3.2 통계 테이블을 적절히 사용하자

서비스를 수행하다 보면 데이터 수를 세는 로직이 필요한 경우가 많다. 예를 들어 데이터 페이징을 위해 데이터 수를 카운트하는데, 페이징을 계산하려면 반드시 전체 데이터 수가 필요하다. 어느 경우나 마찬가지로 데이터 수가 크지 않다며 큰 문제가 없겠지만 데이터가 누적되어 대용량화되면 어느 시점부터는 성능에 커다란 저해요소가 된다.

이러한 경우에 통계 테이블을 적절하게 도입해서 관리하면 매번 데이터 건수를 확인하는 카운트 쿼리를 실행하지 않아도 현재 필요한 카운트 정보를 바로 가져올 수 있다.

위와같이 데이터를 처리하려면 로직을 일부 변경해야 한다. 데이터를 insert 하는 로직만 있었다면 데이터를 insert한 후 데이터 카운트를 변경하는 로직을 추가해야 한다.

특정 일의 데 통계를 구하는 서비스를 구현하려면 다음과 같이 간단한 쿼리를 사용하면 된다.

select count(*) from log where name = 'RED';

만약 하루에 한번씩 호출되는 쿼리라면 큰 문제가 없겠지만 애플리케이션에서 자주 위와같은 쿼리를 호출한다면 서비스 성능에 엄청난 타격을 준다.

이 문제를 해결하기 위해 통계성 테이블을 다음과 같이 추가한다.

CREATE TABLE log_stat(
name varchar('16') not null,
name_count int not null,
parimary key(name)
);

그리고 데이터를 insert할 때 log_stat에 관련 건수를 업데이트한다.

INSERT INTO log_stat (name, name_count) VALUES ('RED', 1)
ON DUPLICATE KEY UPDATE name_count = name_count + 1;

RED 데이터 카운트는 다음과 같이 수행한다.

select name_count FROM log_stat WHERE name ='RED';

이렇게 하면 RED의 데이터 수를 찾기 위해 매번 log 데이터 카운트를 수행하지 않아도 된다.

따라서 서비스 품질이 향상될 뿐만 아니라 DB 효율도 자연스럽게 높아진다.

-->상당히 유용한 정보이다. 테이블 정규화만 제대로 된다면 rownum 안쓰는 것과 더불어 상용서비스에 바로 적용검토해봐야할 거 같다.

#트랜잭션 isolation 레벨에서 테이블 잠금이 발생할 수 있음을 기억하자
Mysql의 스토리지 엔진으로 InnoDB를 사용할 떄, 전체 데이터를 스캔하는 쿼리를 실행할 경우 서비스에 처리하는 데이터에 잠금현상이 발생할 수 있다.
쿼리 실행이 종료될 때까지 다른 세션에서는 테이블 안에 있는 데이터를 변경하지 못하기 때문에 서비스에 직접적인 영향이 있다.
이것은 InnoDB의 기본 Isolation 레벨이 REPEATABLE-READ 이기 때문에 발생하는 현상으로, 세션변수 일부를 변경하여 사전에 문제를 해결할 수 있다.

#현상
Mysql 에서 트랜잭션 Isolation 레벨은 기본적으로 REPEATABLE-READ 이다.
이 상태에서 insert into select .. 혹은 create table as select ..로 데이터를 처리하면 참조하는 테이블에 테이블단위 잠금이 발생할 수 있다.
select 만 발생함에도 테이블의 데이터를 변결할 수 없는 기이한 현상이 발생하는 것이다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

#발생원인
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨이 REPEATABLE-READ로 설정되어 있기 때문에 발생한다. REPEATABLE-READ에서는 트랜잭션을 처리할 때
SELECT 결과의 일관성을 보장하기 위해 스냅샷을 이용한다.

위와 같이 insert 와 select 가 동시에 있는 경우에는 select 결과를 보장하기 위해 암묵적으로 select 임에도 불구하고 데이터 잠금과 비슷한 효과가 나타난다.
즉, select 작업이 종료될 때까지 해당데이터 변경작업을 할 수 없다.

#트랜젝션 isolation 설명

1) READ-UNCOMMITTED
다른 트랜잭션이 commit전의 상태를 볼수 있음 --> 응 안써요
바이너리 로그가 자동으로 row based 로 기록됨 (statement 설정불가, Mix 설정시 자동변환)

2) READ-COMMITED
commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 다른 특정 트랜잭션이 commit을 수행하면 해당 데이터를 read 할 수 있음
바이너리 로그가 자동으로 row based로 기록됨(statement 설정불가, Mixed 설정시 자동변환)

REPEATABLE-READ와 달리 트랜잭션 발생할 때 마다 스냅샷을 뜨는 것으로 알고 있다.

만약 반영한다면 DB의 시스템 부하관련해서 좀 더 확인해봐야한다.

3) REPEATABLE-READ
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨 --> default 옵션이다.
select 시 현재 데이터 버전의 스냅샷을 만들고 만든 스냅샷에서 데이터를 조회
동일 트랜젝션 내에서 데이터 일관성을 보장 데이터를 다시 읽으려면 트랜잭션을 다시 시작해야함

4) SERIALIZABLE
가장 높은 isolation 레벨로 트랜잭션이 완료될 때까지 select 문장이 사용하는 모든 데이터에 공유 락(shared lock)이 걸림 --> 응 안써요
다른 트랜잭션에서는 해당 영역에 관한 데이터 변경뿐만 아니라 입력도 불가

#해결방법
Isolation 레벨을 read-commited 로 변경하면 해결할 수 있다.
다음과 같이 세션 설정을 변경한 후에 create table as select, insert into select 를 수행하면 테이블 잠금 없이 데이터가 잘 처리되는 것을 확인할 수 있다.

SET GLOBAL transaction isolation level READ COMMITED;

vi /ect/my.cnf
-----------------------------------
transaction-isolation = READ-COMMITTED

단 isolation 레벨이 READ-COMMITED 인 경우에는 바이너리 로그 포맷을 MIXED로 설정해도 바이너리 로그가 ROW 기반으로 기록된다는 점을 반드시 기억해야한다.
만약 Mysql 리플리케이션 환경에서 시스템을 운영하고 있다면 설정을 변경한 후에 반드시 데이터 도익화가 정상적으로 잘 이루어지고 있는지를 주기적으로 체크해야한다.

--> replication 사용중이라면 DB 중단하고 cnf와 binlog_format=ROW 반영후 binlog reset하고

DB 재시작하는 것이 맘이 편할꺼 같은데?

#스키마 레벨에서의 접근법

#Mysql InnoDB에서 primary key는 성능에 직접적인 영향을 준다.
Mysql의 InnoDB에서는 primary key 순서로 데이터가 저장된다. primary key는 데이터에 접근하는 물리적인 주소로 사용된다고 봐도 된다.
MS-SQL의 클러스터 인덱스, 오라클의 IOT(index Organized table)라는 개념이 Mysql에서는 Primary key로 구현된 것이다.
(오라클의 클러스터 테이블 Cluster table 과는 다른 개념이다.) 즉, 인덱스 순서로 데이터가 정렬되어 디스크에 저장된다.

#B+TREE 알고리즘에 따른 pk 저장순서
pk 데이터 (4개가 있다고 하면 아래와 같이 순차적으로 생성되어있다.)
10->20-> 30-> 40

#insert into ~ pk데이터 15 가 들어간다면
pk 데이터
10->15-> 20-> 30-> 40
10과 20 사이로 들어간다. 이건 다시말하면 pk를 다시 정렬해야한다는 얘기로 부하를 야기할 수 있다.
데이터 크기가 비대해지면 UUID와 같이 무작위로 pk를 생성하여 테이블에 데이터를 넣으면 데이터가 누적됨에 따라 점차적으로 성능이 저하될 것이고
극심한 disk I/O wait에 빠져서 서비스품질이 크게 떨어질 수 있다. 데이터를 테이블에 넣을 때마다 위와 같이 순서에 맞게 물리적으로 이동시켜야하기 때문이다.

#테이블에 pk가 없다면?
1) 인덱스 중 unique 속성이 있는 key를 pk로 대체 사용
2) pk 선언도 없고 unique key도 없으면 내부적으로 6Byte 크기의 대체 pk를 생성하여 사용
결과적으로 모든 InnoDB는 pk를 선언하지 않아도 내부적으로는 pk를 가지고 있다는 것을 반드시 기억해야 한다

# 불필요한 인덱스 삭제
인덱스가 늘어날수록 insert 성능은 크게 떨어진다.
인덱스는 데이터를 접근하기 위한 필수요소지만 많은 것이 무조건 좋은 것은 아니다.
인덱스도 cpu와 메모리 자원을 소비하는 데이터라는 점을 반드시 기억하기 바란다.
pk = pk
key (보조인덱스) = pk + 인덱스

#테스트

읽으면서 한번 더 짚고 넘어갈 부분은 메모해서 정리한다.

#InnoDB 스토리지엔진
1) 트랜젝션 지원
2) MVCC (multiversion Concurrency Control - 다중 버전 동시성 제어 메커니즘)
- 행단위 잠금으로 데이터 변경 작업을 수행하기 때문에 연관이 없는 데이터를 다른 사용자가 변경할 수 있다.
3) MyISAM은 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 메모리에 올린다는 것이 가장큰 차이점이다. 메모리에 인덱스와 데이터가 적재되어 있기 때문에 메모리버퍼 크기(Innodb_buffer_pool_size)가 DB 성능에 큰 영향을 미친다.

#테이블 조인방식
단일코어(1 cpu)에 Nested Loop Join 알고리즘
- 선행테이블 A의 조건 검색 결과값 하나하나를 테이블 B와 비교하여 조인하는 방식이다.
프로그램적으로 풀자면 for문안에 for문과 유사하다. 결국 처리할 데이터가 적으면 수행속도가 빠르지만 테이블 A와 테이블 B 중 하나라도 연산해야할 데이터가 많아지면 쿼리 효율이 기하급수적으로 떨어진다.

#LIKE 검색
LIKE 조건이 '검색어%'와 같이 검색어가 앞단에 있다면 데이터 분포도를 따져서 수행한다.
LIKE 조건이 '%검색어'와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.

#성능 저하를 유발하는 불필요한 서브쿼리 (rownum 생성 제거)
- 오라클에는 rownum 개념이 있기 때문에 데이터 결과 행에 번호를 쉽게 붙일 수 있다. 아쉽게도 Mysql에는 rownum 개념이 없지만
(select @RNUM:=0) 같은 방식으로 행 번호를 매길 수 있다. 하지만 이를 잘못 사용하면 성능이 저하된다.
결과값에 불필요한 Temporary table을 만들어 저장한다.
결과적으로 엄청난 횟수의 조인 연산이 내부적으로 발생하는 것이다. 다음과 같이 불필요한 서브쿼리를 제거해서 쿼리를 다시 작성한다.
페이지 번호는 DB에서 무리하게 생성하는 것보다 애플리케이션 서버에서 생성하는 것이 Mysql의 성능을 높이는 데 유리하다.

-> 그동안 튜닝포인트 중 하나였는데 푸시해도 잘 안들어줬는데 성동찬님책에서 있네 ㅎㅎ

개발쪽에 얘기해서 쪼는데 제대로인 설명인 듯

현재는 카카오페이에 있는 성동찬님이 쓴 책으로

이번에 mysql 튜닝관련 도움을 받으려고 구매했다.

근데.. 설치를 mysql 5.1로 하라네.. 2013년 출판이네...

MyISAM 이면 어쩌지;;

페이지는 112 쪽으로 가볍게 읽을 수 있을 거 같다.