2016년 2월 15일 월요일

Compressed Table Partition Drop 성능 테스트

1. 테스트 목적

서비스 DB군에 Compressed를 적용한뒤에 파티션 드랍에 소요시간이 길어져 서비스 불가(mha failover)가 발생함에 따라서 버전별 압축 테이블 파티션의 드랍 시간을 측정과 문제 원인을 파악하기위함.

2. 테스트 환경

1. 서버 

모델DL360p Gen8
RAM48G
CPU
Processor type : 6 Core (HyperThread : Enable)
Number of CPU : 2
OS
CentOS release 6.7
DISKFusion IO
MariaDB 5.5, MySQL 5.7, Facebook 5.6

2.테이블 상태 

[mysql]> select table_name, row_format from information_schema.tables where table_name='logs';
+---------------+------------+
| table_name    | row_format |
+---------------+------------+
| logs | Compressed |
+---------------+------------+
1 row in set (0.00 sec)


3.테스트 방법

Drop partion에 소요되는 시간을 측정한다. 

1. 데이터가 메모리에 상주하는 경우


#!/bin/bash
j=1077016736842104832
i=1077016736852104832
while [ $i -le 1079191063756800000 ]
do
     mysql -uxxxx -p'xxxxx' -e "select * from KENDB.logs where id < $i and id > $j" > /dev/null
     i=`expr $i + 10000000`
     j=`expr $j + 10000000`
     #echo $000`
done

삭제 할 파티션의 데이터를 모두 메모리에 올린다. 
<Facebook 5.6.21>
xxxxx@test034:KENDB 23:07:25>alter table logs drop partition PARTITION20151206235959;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test034:KENDB 23:07:27>alter table logs drop partition PARTITION20151207235959;
Query OK, 0 rows affected (0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0
<MySQL 5.7.9>
[xxxxx@test044][KENDB 23:07:25] > alter table logs drop partition PARTITION20151206235959;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
[xxxxx@test044][KENDB 23:07:27] > alter table logs drop partition PARTITION20151207235959;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0
<Mariadb 5.5.24>
xxxxx@test055:KENDB 23:07:25>alter table logs drop partition PARTITION20151206235959;
Query OK, 0 rows affected (17.77 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test055:KENDB 23:07:43>alter table logs drop partition PARTITION20151207235959;
Query OK, 0 rows affected (43.29 sec)
Records: 0  Duplicates: 0  Warnings: 0
Version/Size
1.8G/3.2G(Mariadb)
5.7G/10G(Mariadb)
Facebook 5.6.21
0.25
0.83
MySQL 5.7.9
0.48
0.41
Mariadb 5.5.24
17.77
43.29

2. 데이터가 메모리에 상주하지 않는 경우

모든 DB를 Restart 한 후 테스트를 진행한다. 
<Facebook 5.6.21>
xxxxx@test034:KENDB 23:18:40>alter table logs drop partition PARTITION20151208235959;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test034:KENDB 23:19:09>alter table logs drop partition PARTITION20151209235959;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
<MySQL 5.7.9>
[xxxxx@test044][KENDB 23:18:40] > alter table logs drop partition PARTITION20151208235959;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
[xxxxx@test044][KENDB 23:19:09] > alter table logs drop partition PARTITION20151209235959;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
<Mariadb 5.5.24>
xxxxx@test055:KENDB 23:18:40>alter table logs drop partition PARTITION20151208235959;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test055:KENDB 23:19:09>alter table logs drop partition PARTITION20151209235959;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
Version/Size
5.9G/11G(Mariadb)
3.8G/6.7G(Mariadb)
Facebook 5.6.21
0.14
 0.09
MySQL 5.7.9
0.21
 0.13
Mariadb 5.5.24
0.18
0.14

3. 삭제 대상 이외의 데이터가 메모리에 상주하는 경우

버퍼에 일정이상의 데이터가 올라가있는 경우에도 파티션 드랍에 소요시간 확인을 위해서 테스트해보았다. 
<Facebook 5.6.21>
xxxxx@test034:KENDB 13:53:25>alter table logs drop partition PARTITION20151202235959;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test034:KENDB 13:53:26>alter table logs drop partition PARTITION20151203235959;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

<MySQL 5.7.9>
[xxxxx@test044][KENDB 13:53:25] > alter table logs drop partition PARTITION20151202235959;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0
[xxxxx@test044][KENDB 13:53:26] > alter table logs drop partition PARTITION20151203235959;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

<Mariadb 5.5.24>
xxxxx@test055:KENDB 13:53:25>alter table logs drop partition PARTITION20151202235959;
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test055:KENDB 13:53:27>alter table logs drop partition PARTITION20151203235959;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0
Version/Size
4.5G/7.9G(Mariadb)
6.2G/11G(Mariadb)
Facebook 5.6.21
0.14
 0.22
MySQL 5.7.9
0.24
 0.31
Mariadb 5.5.24
0.86
0.88

4. innodb_lazy_drop_table 활성화 후 파티션 삭제

삭제 할 파티션의 데이터를 모두 메모리에 올린다.
<Mariadb 5.5.24>
xxxxx@test055:KENDB 14:03:02>set global innodb_lazy_drop_table=1;
Query OK, 0 rows affected (0.00 sec)
xxxxx@test055:KENDB 14:03:16>alter table logs drop partition PARTITION20151207235959;
Query OK, 0 rows affected (42.87 sec)
Records: 0  Duplicates: 0  Warnings: 0


5.데이터 삭제 후 파티션 삭제

삭제 할 파티션의 데이터를 모두 메모리에 올린다.
<Facebook 5.6.21>
xxxxx@test034:KENDB 14:10:15>ALTER TABLE logs TRUNCATE PARTITION PARTITION20151215235959;
Query OK, 0 rows affected (1.62 sec)
xxxxx@test034:KENDB 14:10:25>alter table logs drop partition PARTITION20151215235959;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test034:KENDB 14:13:02>ALTER TABLE logs TRUNCATE PARTITION PARTITION20151216235959;
Query OK, 0 rows affected (1.05 sec)
xxxxx@test034:KENDB 14:13:16>alter table logs drop partition PARTITION20151216235959;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

<MySQL 5.7.9>
[xxxxx@test044][KENDB 14:10:15] > ALTER TABLE logs TRUNCATE PARTITION PARTITION20151215235959;
Query OK, 0 rows affected (2.27 sec)
[xxxxx@test044][KENDB 14:10:25] > alter table logs drop partition PARTITION20151215235959;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
[xxxxx@test044][KENDB 14:13:02] > ALTER TABLE logs TRUNCATE PARTITION PARTITION20151216235959;
Query OK, 0 rows affected (1.69 sec)
[xxxxx@test044][KENDB 14:13:17] > alter table logs drop partition PARTITION20151216235959;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

<Mariadb 5.5.24>
xxxxx@test055:KENDB 14:10:15>ALTER TABLE logs TRUNCATE PARTITION PARTITION20151215235959;
Query OK, 0 rows affected (0.54 sec)
xxxxx@test055:KENDB 14:10:25>alter table logs drop partition PARTITION20151215235959;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0
xxxxx@test055:KENDB 14:13:02>ALTER TABLE logs TRUNCATE PARTITION PARTITION20151216235959;
Query OK, 0 rows affected (0.54 sec)
xxxxx@test055:KENDB 14:13:16>alter table logs drop partition PARTITION20151216235959;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0
Version/Size
5.3G/9.3G(Mariadb)
7.5G/8.0G(Mariadb)
Facebook 5.6.21
1.62+0.00
(Truncate + Drop)
 1.05 + 0.00
(Truncate + Drop)
MySQL 5.7.9
2.27+0.01
(Truncate + Drop)
1.69+0.01
(Truncate + Drop)
Mariadb 5.5.24
0.54+0.59
(Truncate + Drop)
0.54+0.59
(Truncate + Drop)

4.결론

1.문제 이유

관련 버그 :

2.해결 방법

회피 방법으로는 해당 테이블을 삭제하기 전에 truncate로 데이터를 삭제한 후 Drop하면 해당 문제를 회피 할 수 있다. 
버전 5.1.69, 5.5.31, 5.6.11 이상 버전을 사용하는것을 권고한다.

3.결론

5.5.31, 5.6.11 이상 버전에서는  compress 테이블을 Drop 할 경우 버퍼풀에서 지워야 하는 페이지를 uncompress하는 불필요한 로직이 제거되었다는것을 확인 할 수 있다. 

작성자: 시간:
라벨: , ,

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)