2016년 2월 22일 월요일

MySQL5.6 IN(val1, ..., valN) 를 index range scan 작동원리

Overview

동등 비교 조건을 위한 최적화


동등 비교조건에 만족하는 실행계획을 세울때 index range scan을 위한 최적화시 row를 예측하기위해 직접 index를 이용해서 row수를 예측하도록 하고 있는데, 이 방식을 index dive 방식이라고 한다.
하지만 MySQL5.6부터는 index dive방식이 아니라 인덱스통계정보를 바탕으로 실행계획을 세울 수 있도록 하는데 이 방식이 index statistics방식이다.

eq_range_index_dive_limit

MySQL5.6부터 index statistics를 이용하기 위해서 eq_range_index_dive_limit  시스템 변수를 제공하고 있으며, session/global로 동적 변경이 가능하다.
MySQL5.6부터 지원되고 있으며, 버젼에 따라 디폴트값이 10 또는 200이 된다.
eq_range_index_dive_limit=N, 비교 조건에 참가하는 value 개수가 N개를 넘어서면 index statistics방식을 사용하도록 하는 시스템 변수이다.

동등 비교 조건에 참여하는 Value 개수로 최적화 방법 선택

eq_range_index_dive_limit 시스템 변수에 N값은 비교 조건에 참여하는 value 개수로  index dive 또는 index statistics 중 어떤것을 사용할것인지를 결정해주고,
MySQL5.6 이하 버젼은 비교조건을 실행할 인덱스가 존재하는 경우 직접 인덱스를 접근해서 최적화하는 inde dive방식으로 처리하다보니 explain만 실행해도 해당 인덱스를 조회함으로써 비교적 정확한 최적화를 하고 있으나, 빈번한 경우 매번 최적화 작업을 위해 매번 IO가 발생되었다.
MySQL5.6부터는 영구적 통계 정보를 활용하여 index statistics 방식으로 최적화할때 통계 정보를 이용하여 빠르게 실행된다.
영구적 통계 정보 생성 및 최적화를 위한 조건을 알아보자.

영구적 통계 정보 생성(MySQL5.6 Newfeture)

MySQL 5.6부터는 통계정보를 주기적으로  mysql.innodb_table_stats, mysql.innodb_index_stats 테이블에 영구히 저장한다.
보통 ANALYZE TABLE를 하면 통계 정보가 갱신되나,  아래와 같이 시스템 변수를 설정하여 주기적으로 통계 정보를 갱신하게 된다.
1) innodb_stats_persistent = 1 (ON)
2) innodb_stats_auto_recalc = 1 (ON)
3) innodb_stats_persistent_sample_pages = 20
4) innodb_stats_transient_sample_pages = 8

동등 비교에 참가될 value 개수로 row 예측 방식이 달라짐.

1) eq_range_index_dive_limit = 0
      : disable되어 무조건 index dive로 실행된다.(MySQL5.6이하와 동일하게 실행. MySQL5.6이하는 이러한 변수 없음.)

2) eq_range_index_dive_limit > 0
: enable되며, 디폴트는 10 또는 200이다.(MySQL5.6이상)
      (1) col_name IN(val1, ..., valN) < N
           : index dive 방식 사용.
      (2) col_name IN(val1, ..., valN) >= N
           : index statistics 방식 사용.

Test

  • 테이블 건수 :  eq_range_test : 3145728 (300만건 이상.)
  • 사용 가능한 인덱스 : 4개
  • id1컬럼을 기준으로 테스트 : (id1컬럼에 3,8,9값은 1개씩만 존재)
    SQL> select id1, count(*) from eq_range_test group by id1;
    +------+----------+
    | id1  | count(*) |
    +------+----------+
    |    1 |   943716 |
    |    2 |   943717 |
    |    3 |        1 |
    |    4 |   314573 |
    |    5 |   314573 |
    |    6 |   314573 |
    |    7 |   314573 |
    |    8 |        1 |
    |    9 |        1 |
    +------+----------+

  • 스트립트
--eq_range_test 테이블 구조 : id1컬럼에 인덱스가 4개 존재.
CREATE TABLE `eq_range_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  `id3` int(11) DEFAULT NULL,
  `regdt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id1` (`id1`),
  KEY `idx_id1_idx2` (`id1`,`id2`),
  KEY `idx_idx1_idx2_idx3` (`id1`,`id2`,`id3`)
);


--테이블 통계
SQL> select * from mysql.innodb_table_stats where table_name='eq_range_test';
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows  | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| test          | eq_range_test | 2016-02-16 14:02:25 | 3137625 |                 8425 |                    16161 |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
--인덱스 통계
SQL> select * from mysql.innodb_index_stats where table_name='eq_range_test';
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name    | index_name         | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | eq_range_test | PRIMARY            | 2016-02-16 14:02:25 | n_diff_pfx01 |    3137625 |          20 | id                                |
| test          | eq_range_test | PRIMARY            | 2016-02-16 14:02:25 | n_leaf_pages |       8367 |        NULL | Number of leaf pages in the index |
| test          | eq_range_test | PRIMARY            | 2016-02-16 14:02:25 | size         |       8425 |        NULL | Number of pages in the index      |
| test          | eq_range_test | idx_id1            | 2016-02-16 14:02:25 | n_diff_pfx01 |          2 |           8 | id1                               | <--stat_value가 2종류라고 알고있음.
| test          | eq_range_test | idx_id1            | 2016-02-16 14:02:25 | n_diff_pfx02 |    3339020 |          20 | id1,id                            |
| test          | eq_range_test | idx_id1            | 2016-02-16 14:02:25 | n_leaf_pages |       3650 |        NULL | Number of leaf pages in the index |
| test          | eq_range_test | idx_id1            | 2016-02-16 14:02:25 | size         |       4231 |        NULL | Number of pages in the index      |
| test          | eq_range_test | idx_id1_idx2       | 2016-02-16 14:02:25 | n_diff_pfx01 |          7 |           8 | id1                               |
| test          | eq_range_test | idx_id1_idx2       | 2016-02-16 14:02:25 | n_diff_pfx02 |         17 |          20 | id1,id2                           |
| test          | eq_range_test | idx_id1_idx2       | 2016-02-16 14:02:25 | n_diff_pfx03 |    3127706 |          20 | id1,id2,id                        |
| test          | eq_range_test | idx_id1_idx2       | 2016-02-16 14:02:25 | n_leaf_pages |       4685 |        NULL | Number of leaf pages in the index |
| test          | eq_range_test | idx_id1_idx2       | 2016-02-16 14:02:25 | size         |       5386 |        NULL | Number of pages in the index      |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx01 |          1 |           8 | id1                               |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx02 |          3 |          20 | id1,id2                           |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx03 |          3 |          20 | id1,id2,id3                       |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx04 |    3069035 |          20 | id1,id2,id3,id                    |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_leaf_pages |       5695 |        NULL | Number of leaf pages in the index |
| test          | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | size         |       6544 |        NULL | Number of pages in the index      |
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+

SQL> select table_name, index_name, column_name, cardinality from INFORMATION_SCHEMA.STATISTICS where table_name ='eq_range_test'\G
*************************** 1. row ***************************
 table_name: eq_range_test
 index_name: PRIMARY
column_name: id
cardinality: 3137625
*************************** 2. row ***************************
 table_name: eq_range_test
 index_name: idx_id1
column_name: id1
cardinality: 2        <------- 역시나 cardinality가 2라고 알고 있음.
*************************** 3. row ***************************
 table_name: eq_range_test
 index_name: idx_id1_idx2
column_name: id1
cardinality: 18
*************************** 4. row ***************************
 table_name: eq_range_test
 index_name: idx_id1_idx2
column_name: id2
cardinality: 58
*************************** 5. row ***************************
 table_name: eq_range_test
 index_name: idx_idx1_idx2_idx3
column_name: id1
cardinality: 18
*************************** 6. row ***************************
 table_name: eq_range_test
 index_name: idx_idx1_idx2_idx3
column_name: id2
cardinality: 58
*************************** 7. row ***************************
 table_name: eq_range_test
 index_name: idx_idx1_idx2_idx3
column_name: id3
cardinality: 58

SQL> show table status like 'eq_range_test'\G
*************************** 1. row ***************************
           Name: eq_range_test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3137625
 Avg_row_length: 43
    Data_length: 138035200
Max_data_length: 0
   Index_length: 264781824
      Data_free: 144703488
 Auto_increment: 3211216
    Create_time: 2016-02-15 15:49:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: 

eq_range_index_dive_limit=3 일때, 값의 분포도에 따른 플랜 확인

: Test1에 분포도가 좋은 경우, index range스캔시 실제 데이터를 바탕으로 rows컬럼에 표시되고 플랜 또한 정확하다.
: Test2는 분포도가 좋은 경우라도, 통계를 기반으로 플랜을 작성되었고,  rows컬럼은 예측된 row 를 기준으로 보여준다.

SQL> set session eq_range_index_dive_limit=3;

TEST1 :  N < 3  

--분포도가 나쁜 경우(index dive : full scan)
 
SQL> explain select * from eq_range_test  where id1 in (1,2);
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys                           | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | eq_range_test | ALL  | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL    | NULL | 3137625 | Using where |   <--예측된 Row수
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
 
--분포도가 좋은 경우,(index dive : idx_id1 index scan)
SQL> explain select * from eq_range_test where id1 in (3,9);
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+
| id | select_type | table         | type  | possible_keys                           | key     | key_len | ref  | rows | Extra                 |
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | eq_range_test | range | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | idx_id1 | 5       | NULL |    2 | Using index condition | <---실제 읽은 row수
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+

TEST2 : N>= 3 

 
--분포도가 나쁜 경우, (index statistics 사용 : full scan)
 
SQL>  explain select * from eq_range_test  where id1 in (1,2,4,5);
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys                           | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | eq_range_test | ALL  | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL    | NULL | 3137625 | Using where | <--예측된 Row수
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
 
--분포도가 좋은 경우, (index statistics 사용 : full scan)
 
SQL> explain select * from eq_range_test  where id1 in (3,8,9,0);
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys                           | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | eq_range_test | ALL  | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL    | NULL | 3137625 | Using where | <--예측된 Row수
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+

쿼리 실행시 읽은 row수 확인

Test1은 rows에 표시된 2 row는 실제 쿼리 실행시에도 2row를 읽었음을 알 수 있다.
Test2는 rows컬럼에 예측 row수로 표시되었고, 실제 실행시 full scan한 Row수 만큼 읽었다.

Test1 : VALUE값이 2개 

-- VALUE값이 2개
SQL> explain select * from eq_range_test where id1 in (3,9);
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+
| id | select_type | table         | type  | possible_keys                           | key     | key_len | ref  | rows | Extra                 |
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | eq_range_test | range | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | idx_id1 | 5       | NULL |    2 | Using index condition | <---실제 읽은 row수
+----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+
SQL> select * from eq_range_test where id1 in (3,9);
+-------+------+------+------+---------------------+
| id    | id1  | id2  | id3  | regdt               |
+-------+------+------+------+---------------------+
| 33333 |    3 |    3 |    3 | 2015-09-15 11:22:48 |
| 99999 |    9 |    3 |    3 | 2015-09-15 11:22:48 |
+-------+------+------+------+---------------------+
2 rows in set (0.00 sec)

[db-rand-db1][test]> show session status like 'hand%' ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |     <---- 실제 2건 읽음.
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 2     |
| Handler_read_last          | 0     |
| Handler_read_next          | 2     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

Test2 : VALUE값이 4개

-- VALUE값이 4개
SQL> explain select * from eq_range_test  where id1 in (3,8,9,0);
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys                           | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | eq_range_test | ALL  | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL    | NULL | 3137625 | Using where | <--예측된 Row수
+----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+
SQL> select * from eq_range_test  where id1 in (3,8,9,0);
+--------+------+------+------+---------------------+
| id     | id1  | id2  | id3  | regdt               |
+--------+------+------+------+---------------------+
|  33333 |    3 |    3 |    3 | 2015-09-15 11:22:48 |
|  99999 |    9 |    3 |    3 | 2015-09-15 11:22:48 |
| 888888 |    8 |    3 |    3 | 2015-09-15 11:22:55 |
+--------+------+------+------+---------------------+
3 rows in set (2.02 sec)
 
SQL> show session status like 'hand%' ;
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 0       |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 3145729 |  <--- 실제 3145729 읽음.
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+

value 개수에 상관없이 인덱스 힌트를 준 경우

use index절 :  value 개수가 N를 넘어가면 통계 정보를 이용해서 해당 인덱스를 사용될건지 판단되어 use index절이 무시될 수 있다.
force index절 : value 개수와 상관없이 force index절 인덱스를 강제로 사용한다.

use index절 사용

SQL>  explain select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0);
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | eq_range_test | ALL  | idx_id1       | NULL | NULL    | NULL | 3137625 | Using where |   <---예측된 Row수
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+

SQL> select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0);
+--------+------+------+------+---------------------+
| id     | id1  | id2  | id3  | regdt               |
+--------+------+------+------+---------------------+
|  33333 |    3 |    3 |    3 | 2015-09-15 11:22:48 |
|  99999 |    9 |    3 |    3 | 2015-09-15 11:22:48 |
| 888888 |    8 |    3 |    3 | 2015-09-15 11:22:55 |
+--------+------+------+------+---------------------+
3 rows in set (2.02 sec)                                  <----2초.
 
SQL> show session status like 'hand%' ;
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 0       |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 3145729 |          <-----실제 3145729건 읽음.
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+

force index절 사용

SQL> explain select * from eq_range_test  force index(idx_id1) where id1 in (3,8,9,0);
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows    | Extra                 |
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+
|  1 | SIMPLE      | eq_range_test | range | idx_id1       | idx_id1 | 5       | NULL | 6275248 | Using index condition |  <---예측된 Row수
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+

SQL> select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0);
+--------+------+------+------+---------------------+
| id     | id1  | id2  | id3  | regdt               |
+--------+------+------+------+---------------------+
|  33333 |    3 |    3 |    3 | 2015-09-15 11:22:48 |
| 888888 |    8 |    3 |    3 | 2015-09-15 11:22:55 |
|  99999 |    9 |    3 |    3 | 2015-09-15 11:22:48 |
+--------+------+------+------+---------------------+
3 rows in set (0.00 sec)               <---- 0초.
 
SQL> show session status like 'hand%' ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 4     |   <--- 실제 4건 읽음.
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     | 
| Handler_write              | 0     |
+----------------------------+-------+

Conclusion

MySQL5.6부터 영구적 통계 정보를 적극 활용하기 위해 eq_range_index_dive_limit 변수를 활용하도록 하였으나, 정확하지 않는 통계정보로 인하여 최적화(실행계획) 시킬때 시간 및 IO량은 줄 수는 있으나  잘못된 최적화로 인하여 빈도가 높고, 대량의 테이블을 이용한 쿼리인 경우 성능 저하를 일으킬 수 있다.
MySQL5.7.4부터 디폴트 값이 10개에서 200개로 value 개수가 늘어나는 것은 기존 방식인 index dive방식을 기존처럼 사용하도록 변경된게 아닌가 추측된다.
MySQL의 통계정보에는 아직까지 column value에 대한 histogram이 없고, row수 및 cardinality정도만 제공하고 있으므로 통계 정보를 믿고 최적화 하는건 아직 조심스럽다고 볼 수 있겠다.



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이 유리하다.