2016년 2월 15일 월요일

LZ4 압축 테이블 성능 테스트

1.목적

Facebook MySQL에 LZ4 압축 알고리즘을 사용할때 압축 레벨에 따라 성능을 비교하고 최적의 압축 레벨을 확인해보기 위해서 테스트를 진행했다. 


2.테스트 환경

1.서버

모델DL360p Gen8
RAM48G
CPU
Processor type : 6 Core (HyperThread : Enable)
Number of CPU : 2
OS
CentOS release 6.7
DISKFusion IO

2.DB

DB VersionFacebook 5.6.21
Table Size53614072 rows
OS File Size70.94GB

1.Variables

innodb_flush_method = ALL_O_DIRECT     
innodb_doublewrite = 0                
innodb_flush_neighbors = 1        
innodb_checksum_algorithm=CRC32    
innodb_log_compressed_pages = OFF  
innodb_compression_failure_threshold_pct = 5
innodb_compression_pad_pct_max = 50
innodb_compression_level = 1       
innodb_defragment_fill_factor = 0.75
innodb_defragment_frequency = 50



3.테스트 

1.Compress Level 0

1. Compress Table

Alter
alter table log ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION=LZ4 COMPRESSION_LEVEL=0 COMPACT_METADATA=0;
[xxxx@test][testdb 22:11:39] > show global status like 'Innodb_zip_8%';
+-------------------------------------------------+-------------+
| Variable_name                                   | Value       |
+-------------------------------------------------+-------------+
| Innodb_zip_8192_compressed                      | 20269721    |
| Innodb_zip_8192_compressed_ok                   | 19934692    |
| Innodb_zip_8192_compressed_seconds              | 1100.997702 |
| Innodb_zip_8192_compressed_ok_seconds           | 1058.771631 |
| Innodb_zip_8192_compressed_primary              | 19726531    |
| Innodb_zip_8192_compressed_primary_ok           | 19407538    |
| Innodb_zip_8192_compressed_primary_seconds      | 1012.604778 |
| Innodb_zip_8192_compressed_primary_ok_seconds   | 974.485347  |
| Innodb_zip_8192_compressed_secondary            | 543190      |
| Innodb_zip_8192_compressed_secondary_ok         | 527154      |
| Innodb_zip_8192_compressed_secondary_seconds    | 88.392924   |
| Innodb_zip_8192_compressed_secondary_ok_seconds | 84.286284   |
| Innodb_zip_8192_decompressed                    | 335613      |
| Innodb_zip_8192_decompressed_seconds            | 4.678981    |
| Innodb_zip_8192_decompressed_primary            | 308430      |
| Innodb_zip_8192_decompressed_primary_seconds    | 3.010049    |
| Innodb_zip_8192_decompressed_secondary          | 27183       |
| Innodb_zip_8192_decompressed_secondary_seconds  | 1.668932    |
+-------------------------------------------------+-------------+

2.Decompress Table

Select
select * from log limit 40000000, 1;
[xxxx@test][testdb 22:25:09] > show global status like 'Innodb_zip_8%';
+-------------------------------------------------+-----------+
| Variable_name                                   | Value     |
+-------------------------------------------------+-----------+
| Innodb_zip_8192_compressed                      | 0         |
| Innodb_zip_8192_compressed_ok                   | 0         |
| Innodb_zip_8192_compressed_seconds              | 0.000000  |
| Innodb_zip_8192_compressed_ok_seconds           | 0.000000  |
| Innodb_zip_8192_compressed_primary              | 0         |
| Innodb_zip_8192_compressed_primary_ok           | 0         |
| Innodb_zip_8192_compressed_primary_seconds      | 0.000000  |
| Innodb_zip_8192_compressed_primary_ok_seconds   | 0.000000  |
| Innodb_zip_8192_compressed_secondary            | 0         |
| Innodb_zip_8192_compressed_secondary_ok         | 0         |
| Innodb_zip_8192_compressed_secondary_seconds    | 0.000000  |
| Innodb_zip_8192_compressed_secondary_ok_seconds | 0.000000  |
| Innodb_zip_8192_decompressed                    | 3562550   |
| Innodb_zip_8192_decompressed_seconds            | 54.960069 |
| Innodb_zip_8192_decompressed_primary            | 3562538   |
| Innodb_zip_8192_decompressed_primary_seconds    | 54.959415 |
| Innodb_zip_8192_decompressed_secondary          | 12        |
| Innodb_zip_8192_decompressed_secondary_seconds  | 0.000654  |
+-------------------------------------------------+-----------+

3.Result

1.파일 사이즈

-rw-rw---- 1 mysql mysql  6123683840 Jan  8 21:19 log#P#PF_20151207235959.ibd
-rw-rw---- 1 mysql mysql  6354370560 Jan  8 21:30 log#P#PF_20151208235959.ibd
-rw-rw---- 1 mysql mysql  7910457344 Jan  8 21:44 log#P#PF_20151211235959.ibd
-rw-rw---- 1 mysql mysql 13618905088 Jan  8 22:01 log#P#PF_20151224235959.ibd
-rw-rw---- 1 mysql mysql  9974054912 Jan  8 22:13 log#P#PF_20151228235959.ibd

2.소요시간

Compressed
(1100.997702/20269721) * 1000000 = 54.32
Compress per Second
(20269721/1100.997702) = 18,410.32
Decompressed
(54.960069/3562550) * 1000000 = 15.43
Decompress per Second
(3562550/54.960069) = 64,820.7

3.OS 자원 사용량

<Compress>

<Decompress>



2.Compress Level 1

1.Compress Table

Alter
alter table log ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION=LZ4 COMPRESSION_LEVEL=1 COMPACT_METADATA=0;
 [xxxx@test][testdb 21:56:42] > show global status like 'Innodb_zip_8%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| Innodb_zip_8192_compressed                      | 20358745   |
| Innodb_zip_8192_compressed_ok                   | 20022110   |
| Innodb_zip_8192_compressed_seconds              | 183.496362 |
| Innodb_zip_8192_compressed_ok_seconds           | 178.277086 |
| Innodb_zip_8192_compressed_primary              | 19844358   |
| Innodb_zip_8192_compressed_primary_ok           | 19523101   |
| Innodb_zip_8192_compressed_primary_seconds      | 165.862590 |
| Innodb_zip_8192_compressed_primary_ok_seconds   | 161.484191 |
| Innodb_zip_8192_compressed_secondary            | 514387     |
| Innodb_zip_8192_compressed_secondary_ok         | 499009     |
| Innodb_zip_8192_compressed_secondary_seconds    | 17.633772  |
| Innodb_zip_8192_compressed_secondary_ok_seconds | 16.792895  |
| Innodb_zip_8192_decompressed                    | 337219     |
| Innodb_zip_8192_decompressed_seconds            | 4.342334   |
| Innodb_zip_8192_decompressed_primary            | 310489     |
| Innodb_zip_8192_decompressed_primary_seconds    | 2.804355   |
| Innodb_zip_8192_decompressed_secondary          | 26730      |
| Innodb_zip_8192_decompressed_secondary_seconds  | 1.537979   |
+-------------------------------------------------+------------+

2.Decompress Table

Select
select * from log limit 40000000, 1;
 
[xxxx@test][testdb 22:25:12] > show global status like 'Innodb_zip_8%';
+-------------------------------------------------+-----------+
| Variable_name                                   | Value     |
+-------------------------------------------------+-----------+
| Innodb_zip_8192_compressed                      | 0         |
| Innodb_zip_8192_compressed_ok                   | 0         |
| Innodb_zip_8192_compressed_seconds              | 0.000000  |
| Innodb_zip_8192_compressed_ok_seconds           | 0.000000  |
| Innodb_zip_8192_compressed_primary              | 0         |
| Innodb_zip_8192_compressed_primary_ok           | 0         |
| Innodb_zip_8192_compressed_primary_seconds      | 0.000000  |
| Innodb_zip_8192_compressed_primary_ok_seconds   | 0.000000  |
| Innodb_zip_8192_compressed_secondary            | 0         |
| Innodb_zip_8192_compressed_secondary_ok         | 0         |
| Innodb_zip_8192_compressed_secondary_seconds    | 0.000000  |
| Innodb_zip_8192_compressed_secondary_ok_seconds | 0.000000  |
| Innodb_zip_8192_decompressed                    | 3574310   |
| Innodb_zip_8192_decompressed_seconds            | 56.734205 |
| Innodb_zip_8192_decompressed_primary            | 3574298   |
| Innodb_zip_8192_decompressed_primary_seconds    | 56.733536 |
| Innodb_zip_8192_decompressed_secondary          | 12        |
| Innodb_zip_8192_decompressed_secondary_seconds  | 0.000668  |
+-------------------------------------------------+-----------+

3.Result

1.파일 사이즈

-rw-rw---- 1 mysql mysql  6136266752 Jan  8 21:17 log#P#PF_20151207235959.ibd
-rw-rw---- 1 mysql mysql  6366953472 Jan  8 21:26 log#P#PF_20151208235959.ibd
-rw-rw---- 1 mysql mysql  7927234560 Jan  8 21:36 log#P#PF_20151211235959.ibd
-rw-rw---- 1 mysql mysql 13778288640 Jan  8 21:50 log#P#PF_20151224235959.ibd
-rw-rw---- 1 mysql mysql 10070523904 Jan  8 21:59 log#P#PF_20151228235959.ibd

2.소요시간

Compressed
(183.496362/20358745) * 1000000 = 9.01
Compress per Second
(20358745/183.496362) = 110,949.04
Decompressed
(56.734205/3574310) * 1000000 = 15.87
Decompress per Second
(3574310/56.734205) = 63,000.97

3.OS 자원 사용량

<Compress>


<Decompress>


4.결론

1.압축률

Level 0
LeveL 1
25.73
25.02
(압축전 데이터 사이즈 - 압축후 데이터 사이즈 )/ 압축전 데이터 사이즈 * 100  

2.소요시간

Level 0
LeveL 1
Compressed54.329.01
Compress per Second18,410.32110,949.04
Decompressed15.4315.87
Decompress per Second 64,820.763,000.97

3.OS 자원 사용량

<Compress>


<Decompress>


4.결론

레벨별로 압축률에 큰 차이가 없지만 데이터를 압축하는데 있어서 Level 1 압축이 더 빠른 성능을 보였다. 하지만 압축률이 낮은대신에 Select에서는 Level 0이 우세한 성능을 보였다. 
압축할때는 OS 자원 사용에 있어서는 Level 0 더많이 사용했다. 반대로 압축을 해제할때에는 Level1이 근소하게 더 많이 사용했다. 
결론적으로 DML발생이 적은 서비스에서는 LEVEL0이 유리하며 DML이 빈번한 서비스에서는 LEVEL1이 유리하다. 

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

댓글 없음:

댓글 쓰기

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