[MySQL - 테이블 단편화 optimize/analyze table 정리]

※ analyze table

1. optimizer가 사용하는 통계정보의 갱신처리입니다.
2. InnoDB에서는 자발(자동)적으로 통계 정보를 갱신하기 때문에 크게 필요없다고 합니다.
   MyISAM의 경우는 카디날리티가 정확하게 갱신되어 있지만
   InnoDB는 아주 부정확하고 analyze table을 실행하면 빈번이 값이 바뀐다.
   InnoDB의 경우 자동으로 갱신되는 조건은 아래와 같다.
   - 전에 인덱스 통계정보를 갱신한후 테이블의 전체행수의1/16이 갱신된경우
   - 전에 인덱스 통계정보를 갱신한후 20억행이상이 갱신된경우
3. analyze table은 랜덤으로 페이지를 8회추출해서 그 페이지내에 포함된 행 데이타를 조사해서
   인덱스의 통계정보를 근사치로 갱신한다. 근사치이지만 옵티마이저가 사용하기 위한 통계정보로는
   충분하다고 한다. 만약 페이지가 InnoDB 버퍼 풀에 있지 않다면 디스크 Read가 발생하게 되므로 8회되로 제한했는지 모르겠다.
4. MySQL 5.1의 InnoDB에서는 8회로 고정되어있다.
   plugin을 사용하면 innodb_stats_sample_pages 옵션을 조정하여 변경할 수 있다.

 

※ optimize table

1. InnoDB의 경우 fragmentation이 발생할 빈도가 높지는 않다. 물론 추기형이 아니므로 vacuum은 필요없다.
2. InnoDB는 MVCC구조이므로 DELETE의 경우 불필요한 로그, 데이타가 남게 되므로 이런 경우 정리가 필요하게 된다.
3. optimize table은 프라이머리키 순서로 데이타를 재배치한다. 이로 인해 인덱스의 정리도 가능해진다.
4. optimize table은 ALTER TABLE t1 ENGINE INNODB;과 동일하다.
5. MySQL은 ALTER TABLE작업은
   임시 테이블 생성 > 임시 테이블로 복제 > 기존 테이블 DROP > 임시 테이블의 이름을 기존 테이블의 이름으로 변환
   하는 식으로 수행한다. 인덱스가 추가, 갱신등으로 많은 시간을 허비하므로 인덱스를 삭제후 alter table 그후 인덱스 재생성
   식으로 빠르게 수행하도록 한다.
6. 4의 내용을 기반으로 5의 방식을 도입하면 optimize table을 보다 고속으로 수행할수 있다.
   PK이외의 인덱스를 삭제 > optimize table > 인덱스 재생성

 

 

☞Optimize table테스트

물리적인 디스크 조각모음

테이블의 단편화 현상을 해결, 차지하는 용량을 줄임

 

■ 10만건 데이터 insert

mysql> CREATE TABLE Nonprimenumber(num INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE myFunction()
    -> BEGIN
    ->     DECLARE i INT DEFAULT 1;
    ->     WHILE (i <= 100000) DO
    ->         INSERT INTO `Nonprimenumber` VALUE (i);
    ->         SET i = i + 1;
    ->     END WHILE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call myFunction();


mysql> select count(*) from Nonprimenumber;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.01 sec)


※ 10만건 데이터 당시 데이터 사이즈
mysql> select table_name, round(sum(data_length+index_length)/1024/1024,1) "Size(MB)" from information_schema.tables  where table_name='Nonprimenumber' group by 1;
+----------------+----------+
| TABLE_NAME     | Size(MB) |
+----------------+----------+
| Nonprimenumber |      2.5 |
+----------------+----------+
1 row in set (0.00 sec)



[root@jh-master jh]# ll
total 10244
-rw-r----- 1 mysql mysql 10485760 Jul 27 10:13 Nonprimenumber.ibd

 

 

■ 4만건 delete

mysql> delete from Nonprimenumber where num between 30000 and 70000;
Query OK, 40001 rows affected (0.17 sec)
mysql> select count(*) from Nonprimenumber;
+----------+
| count(*) |
+----------+
|    59999 |
+----------+
1 row in set (0.01 sec)


※ 6만건 데이터 당시 데이터 사이즈
mysql> select table_name, round(sum(data_length+index_length)/1024/1024,1) "Size(MB)" from information_schema.tables  where table_name='Nonprimenumber' group by 1;
+----------------+----------+
| TABLE_NAME     | Size(MB) |
+----------------+----------+
| Nonprimenumber |      2.5 |
+----------------+----------+
1 row in set (0.00 sec)


## 삭제 이전과 사이즈 동일
[root@jh-master jh]# ll
total 10244
-rw-r----- 1 mysql mysql 10485760 Jul 27 10:13 Nonprimenumber.ibd

 

 

■ Optimize table 진행

database내 모든 테이블에 대해 실행

[root@jh-master ~]# mysqlcheck -u root -p --optimize --databases jh
Enter password:
jh.Nonprimenumber
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK


## 사이즈 줄어듦
[root@jh-master jh]# ll
total 8196
-rw-r----- 1 mysql mysql 8388608 Jul 27 11:10 Nonprimenumber.ibd

 

※ 테이블별 따로 수행 방법

mysql> optimize table jh.Nonprimenumber;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| jh.Nonprimenumber | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| jh.Nonprimenumber | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.30 sec)

 

 

☞ ANALYZE TABLE 테스트

인덱스를 재생성하여 성능을 최적화, 키를 재분배

InnoDB에서는 어느정도 이상의 변경이 발생하면 자발적으로 통계 정보를 갱신하기 때문에 수동으로 해줄 필요없음.

데이터베이스 analyze 수행 방법

[root@jh-master ~]# mysqlcheck -u root -p --analyze --databases jh
Enter password:
jh.Nonprimenumber                                  OK

 

 

참고

 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=parkjy76&logNo=30136379533 

MySQL optimize/analyze table 정리