[mySQL5.5] 14장. Table Maintenance

 

n  Table Maintenance

 

n  CHECK TABLE

   -  for MyISAM, InnoDB, and ARCHIVE ,CSV(MySQL 5.1.9이상)

   -  partitioned table지원(MySQL 5.1.27이상)

  

             CHECK TABLE tbl_name [, tbl_name] ... [option] ...

             option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

 

             1) FOR UPGRADE : compatible 점검(with the current version of MySQL)

             2) QUICK : 데이터 행의 incorrent link를 체크하지 않는다.

             3)  FAST : 정상적으로 close안된 테이블에 대해서만 체크한다.

             4) CHANGED : 마지막 체크이후에 변동된 테이블과 비정상적으로 닫힌 테이블만 체크한다.

        5) MEDIUM: 각 행마다 deleted링크가 valid한지 점검하고, 인덱스 checksum을 점검한다.

             6) EXTENDED : 인덱스를 통해 각 행를 lookup가능한지 점검한다.이것은 테이블이 정상이라는 것을 완벽하게 보장하지만, 시간이 많이 소요된다.

                               

mysql>  check table City EXTENDED;

+-------------------+-------+----------+----------+

| Table             | Op    | Msg_type | Msg_text                    |

+-------------------+-------+----------+----------+

| world_innodb.City | check | status   | OK                            |

+-------------------+-------+----------+----------+

 

n  CHECKSUM TABLE

   - 테이블 checksum정보를 표시합니다.

   - checksum is reported if it is available, or NULL otherwise.

             CHECKSUM TABLE tbl_name [option]

             1) QUICK : live checksum 보여줌, MyISAM만 가능,
아주 빠름,테이블 생성시 CHECKSUM=1옵션으로 생성되어야 한다.

             2)  EXTENDED : 전체 테이블을 스캔하면서 checksum을 매 행마다 계산하므로 아주 느리다.

 

 

n  REPAIR TABLE

   - 손상된(corrupted ) 테이블을 복구,수리합니다.

   - works for MyISAM and for ARCHIVE ,CSV

   - USE_FRM option : partitioned 테이블에 사용 못한다.

 

             REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE    tbl_name [, tbl_name] ...

                 [QUICK] [EXTENDED] [USE_FRM]

             1)QUICK : 인덱스만 복구한다.

                           myisamchk --recover --quick과 동일.

             2) EXTENDED : 인덱스를 매행당 읽어서 인덱스를 생성.

                        myisamchk --safe-recover.

             3) USE_FRM :  .MYI 파일이 손실 또는 손상된 경우에 사용.

                       .frm을 읽어서 .MYI파일을 재생성하는 것입니다.

 

n  ANALYZE TABLE    tbl_name [, tbl_name] ...

    - 인덱스 통계 생성(index cardinality( the number of unique values )

- SHOW INDEXES)

    - MyISAM, and InnoDB tables

    - MyISAM, 인 경우 myisamchk --analyze과 동일

    - supported for partitioned tables

  

mysql> show indexes from city\G

*************************** 1. row ***************************

       Table: city

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: ID

   Collation: A

 Cardinality: 4051

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

1 row in set (0.00 sec)

 

 

n  OPTIMIZE TABLE  :

   - 대량의 데이터를 삭제했거나  다수의 variable-length(VARCHAR, VARBINARY, BLOB, or TEXT ) 행을 변경한 경우에 사용한다,

   - 행은 삭제되어도 linked list는 유지됩니다. 이후에 INSERT는 old row positions을 재사용합니다.

      unused space를 회수하고 디스크 공간을 병합하기 위해 사용합니다.

   - 주기적으로 실행한다.

   - only for MyISAM, InnoDB, and ARCHIVE tables

             OPIMIZE  TABLE    tbl_name [, tbl_name] ...

 

             1) For MyISAM tables, OPTIMIZE TABLE works as follows:

 

                           1. If the table has deleted or split rows, repair the table.

 

                           2. If the index pages are not sorted, sort them.

 

                           3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

 

             2) For InnoDB tables, OPTIMIZE TABLE

                           1. rebuilds the table to update index statistics and free unused space in the clustered index.

 

 

 

n  실습 1 : Analyze

 

mysql> use world_innodb;

 

1. 현재 table_rows을 조회합니다. innodb는 estimate건수를 보여줍니다.
mysql>  select table_rows from information_schema.tables where table_name='city'

        and table_schema='world';

+------------+

| table_rows |

+------------+

|       3868 |

+------------+

2. 실제 테이블의 행수를 조회합니다.

mysql> select count(*) from city;

+----------+

| count(*) |

+----------+

|     4079 |

+----------+

3. 이 상태에서 plan을 조회하면, statistics의 행수가 표시됩니다.

mysql>  explain select * from city\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: city

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3868

        Extra:

 

4. 테이블 통계 수집할 sample페이지 수를 증거시키면, 통계가 더 정확합니다.

mysql> set global innodb_stats_sample_pages =16;  --default:8

Query OK, 0 rows affected (0.00 sec)

 

mysql>  select count(*) from world.city;

+----------+

| count(*) |

+----------+

|     4079 |

+----------+

1 row in set (0.00 sec)

 

5. 통계를 수집합니다.

mysql> analyze table world.city;

+------------+---------+----------+----------+

| Table      | Op      | Msg_type | Msg_text |

+------------+---------+----------+----------+

| world.city | analyze | status   | OK       |

+------------+---------+----------+----------+

1 row in set (0.00 sec)

 

6. table_rows 를 조회하면, 실재 건수에 가까움을 알수 있다.

mysql> select table_rows from information_schema.tables where table_name='city'

    -> and table_schema='world';

+------------+

| table_rows |

+------------+

|       4094 |

+------------+

 

7. plan에도 새로운 통계가 반영됨을 알 수 있다.

mysql> explain select * from world.city\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: city

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4094

        Extra:

8. 인덱스 통계의 CARDINALITY도 잘 반영됨을 알 수 있다.

mysql> select COLUMN_NAME,CARDINALITY from  information_schema.statistics where table_name='city'

    -> and table_schema='world';

+-------------+-------------+

| COLUMN_NAME | CARDINALITY |

+-------------+-------------+

| ID          |        4094 |

+-------------+-------------+

결론 : Optimizer가 효과적인 plan을 생성하기 위해서는 주기적인 Analyze작업이 필요합니다.

 

n  실습 2 : Optimize

 

mysql> use world_innodb;

Database changed
1. 임시 테이블을 생성한 후, 일부 데이터를 삭제합니다.

mysql> create table city_temp like City;

mysql> alter table city_temp engine=myisam;

 

mysql> insert into city_temp select * from City;

Records: 4079  Duplicates: 0  Warnings: 0

 

mysql> delete from city_temp where id between 1001 and 2000;

Query OK, 1000 rows affected (0.01 sec)

 

2. fragment 를 확인 할 수 있습니다.

mysql> show table status like 'city_temp'\G

*************************** 1. row ***************************

           Name: city_temp

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 3079

 Avg_row_length: 67

    Data_length: 273293

….

   .

      Data_free: 67000

 ………

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

 

3. optimize 를 수행합니다.

mysql> optimize table city_temp;

+------------------------+----------+----------+----------+

| Table                  | Op       | Msg_type | Msg_text |

+------------------------+----------+----------+----------+

| world_innodb.city_temp | optimize | status   | OK       |

+------------------------+----------+----------+----------+

1 row in set (0.00 sec)

 

4. 2번 결과와 비교하면 , fragment가 없어짐을 알수 있습니다.

mysql> show table status like 'city_temp'\G

*************************** 1. row ***************************

…..

    Data_length: 206293   < ---- 273293

Max_data_length: 18858823439613951

   Index_length: 63488

      Data_free: 0   < ---- 67000

 ….

 

5. flush table city_temp;

 

n  실습 3 : Check & Repair

 

6. world_innodb 디렉토리로 이동한 후, MYI 파일을 일부 손상 합니다.

[root@EDYDR51P0 world_innodb]# pwd

/var/lib/mysql/world_innodb

[root@EDYDR51P0 world_innodb]# ls city_temp*

city_temp.frm  city_temp.MYD  city_temp.MYI

[root@EDYDR51P0 world_innodb]# vi city_temp.MYI

    :  몇 바이트를 삭제합니다.

7. 테이블 상태를 점검합니다.

mysql> check tables City, City_temp;

mysql> check tables City, city_temp;

+------------------------+-------+----------+-------------------------------------------------------------

| Table                  | Op    | Msg_type | Msg_text                                                                  |

+------------------------+-------+----------+---------------------------------------

| world_innodb.City      |check | status   | OK                                                                       

| world_innodb.city_temp |check | Error | Incorrect key file for table './world_innodb/city_temp'; try to repair it |

| world_innodb.city_temp |check| Error   | Incorrect key file for table 'city_temp'; try to repair it                |

| world_innodb.city_temp | check | error    | Corrupt                                                                   |

+------------------------+-------+----------+-------------------------------------------------------------

 

8. 손상된 테이블 city_temp를 repair합니다.

mysql> repair table city_temp extended;

+------------------------+--------+----------+---------------------------------------------------------------------------+

| Table                  | Op     | Msg_type | Msg_text                                                                  |

+------------------------+--------+----------+---------------------------------------------------------------------------+

| world_innodb.city_temp | repair | Error    | Incorrect key file for table './world_innodb/city_temp'; try to repair it |

| world_innodb.city_temp | repair | Error    | Incorrect key file for table 'city_temp'; try to repair it                |

| world_innodb.city_temp | repair | error    | Corrupt                                                                   |

+------------------------+--------+----------+---------------------------------------------------------------------------+

3 rows in set (0.00 sec)

 

mysql> repair table city_temp use_frm;

+-----------------------+--------+----------+---------------------------------------------+

| Table                 | Op     | Msg_type | Msg_text                                    |

+-----------------------+--------+----------+---------------------------------------------+

| world_innodb.city_tem | repair | Error    | Table 'world_innodb.city_tem' doesn't exist |

| world_innodb.city_tem | repair | status   | Operation failed                            |

+-----------------------+--------+----------+---------------------------------------

9. 테이블 상태를 점검합니다.

mysql> check table city_temp;

+------------------------+-------+----------+----------+

| Table                  | Op    | Msg_type | Msg_text |

+------------------------+-------+----------+----------+

| world_innodb.city_temp | check | status   | OK       |

+------------------------+-------+----------+----------+

1 row in set (0.00 sec)