***** 자동 통계 수집 ******
- innodb_stats_on_metadata =1
- 수집 시점
. When the table is first opened
. When the table size changes by 1/16th overall since the last time statistics were taken
. If more than 2,000,000,000 rows are inserted (if this is before to the 1/16th rule)
. When anybody accesses any of
SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,
INFORMATION_SCHEMA.STATISTICS for InnoDB tables (for each and every InnoDB table in the output)
<< 테스트 >>
1. 자동통계 수집인지 확인합니다.
mysql> select @@innodb_stats_on_metadata;
+----------------------------+
| @@innodb_stats_on_metadata |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
2. 통계를 조회합니다.
mysql> show table status like 'city'\G
*************************** 1. row ***************************
Name: city
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3641
Avg_row_length: 112
Data_length: 409600
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 4080
Create_time: 2012-04-22 07:50:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3. 다시 통계를 조회하면 변경되었음(2와 다름)을 알수 있습니다.
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 3868 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.00 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
4. sampling page 를 증가시키면, 통계가 더 정확해 집니다.
mysql> set global innodb_stats_sample_pages =16;
Query OK, 0 rows affected (0.00 sec)
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 4321 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.00 sec)
5. 하지만, 계속 통계가 변경됩니다.
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 4094 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.00 sec)
6. 이번에는 통계 수집을 manual로 변경합니다.(GLOBAL)
mysql> set innodb_stats_on_metadata=false;
ERROR 1229 (HY000): Variable 'innodb_stats_on_metadata' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set GLOBAL innodb_stats_on_metadata=false;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_stats_on_metadata;
+----------------------------+
| @@innodb_stats_on_metadata |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
7. 통계가 변경되지 않고 5번 결과와 동일합니다.
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 4094 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.00 sec)
8. analyze를 할때만 통계가 변경됩니다.
mysql> analyze table city;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| world.city | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 3641 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.09 sec)
9. 통계가 변경되지 않죠?
mysql> select table_rows,update_time,check_time from information_schema.tables
-> where table_name='city'
-> and table_schema='world';
+------------+-------------+------------+
| table_rows | update_time | check_time |
+------------+-------------+------------+
| 3641 | NULL | NULL |
+------------+-------------+------------+
1 row in set (0.00 sec)
mysql> notee;
