ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

? 7? ?? ?? ??? ?? ??????

?
?
EXEM
EXEM

7? 6?? ??? ?? ?? ?? ??? ?????^^

1 of 30
Downloaded 54 times
? 7? ?? ?? ??? ?? EXEM seminar report no. 007 (2016.07.06) Research & Contents Team
Table of Agenda ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 01. 02. 03. [PostgreSQL] Vacuum? ?? ?? ? (3?) [MySQL] Page? Undo page deep internal ?? [MySQL] Hint ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 01. [PostgreSQL] Vacuum? ?? ?? ? (3?) ???: ?????? ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. ?? 1? 1) Vacuum ?? ? ??? 2) Vacuum ?? ?? 3) ?? Vacuum VS Vacuum full (????) 2? 1) dump? ?? ¡°?? Vacuum VS Vacuum full¡± [PostgreSQL] Vacuum? ?? ?? ? 3? 1) XID wraparound and freezing tuple 2) Autovacuum
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? ????? ?? MVCC ??: XID(???? ID)? ??? ???? ?? ? 232 ?? ??? ?? (?? 40?) ? 40? ????: ? XID(20?) + ? XID(20?) ? ? XID 20?? ???, ? XID 20?? ???? ?? ? ?? ????? ?? ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid ? XID(20?) ? XID(20?) Visible ( Invisible )
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? XID wraparound ??? ??? ???? ?? ?? ?? ? Insert¨Conly ?????? ?? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue ??(visible) ??(invisible) Visible Visible Invisible
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? ??? ¡°FREEZE¡±? ????, ? FrozenXID? ?? XID ?? ???? ?? ???? ?? ??? ? 20? ????? ??? ?? FrozenXID ?, ?? ??? ??? ???? XID ?? ?? ?? ? ??? ?? ??(data freezing)? VACUUM FREEZE ???? ?? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue ??(visible) ??(invisible) Visible VisibleVisible
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple Skip ?? ?? pg_class ???? relfrozenxid ??? : VACUUM ???? ? ??? ??? ??? XID ?? ??? ?? ???? ID ? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? autovacuum ??? ?? ??? ???? ?? ???? ???? ???? ID ?? ??? ?? ? Table age? (vacuum_freeze_table_age ¨C vacuum_freeze_min_age)?? ??, ?? ???? ???? ?? ??? ?? VACUUM ???? ?? ???? ?? ?? ? XID ?? ??? ? ??? ?? ?? 0?? ????, VM ???? ?? ?? ?? ??? ?? ???: autovacuum_freeze_max_age * 0.95
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? ? ????? ?? VACUUM? ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? ? ???? VACUUM ??? ?? anti-wraparound VACUUM? ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple FrozenXID? ??? ? ? XID? ??? ?? ??? ?? anti-wraparound VACUUM ??? relfrozenxid ?? ????? ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 20? 2? 1.5? 1. XID Wraparound and Freezing Tuple ? ? ???? vacuum ?? ?? ?? ???? ??? ? ?? ?? : ??? vacuum ????, 20? ¨C vacuum_freeze_min_age ???? ???? ? ???? ??, vacuum ?? ??? ??? ?? Autovacuum ??? ???? ????, autovacuum_freeze_max_age ?? ???? vacuum ?? ?? ? ? ???? ?? vacuum ??? ??? ?? ????, (Autovacuum_freeze_max_age ¨C vacuum_freeze_min_age) ? ??? ???? ?? ?, autovacuum ?? ??
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum Autovacuum launcher Autovacuum_naptime ??? ??? ? ???? ? ?? ??? ? ?????? ??? ? ??? worker ????? ?? ??? ?? Autovacuum workers Autovacuum_max_workers worker ???? ?? ?? AUTOVACUUM??? VACUUM ??? ANALYZE ??? ????? ?? ?? ? ???? ?? Vacuum ??? ?? ? ??? ???? ??? Vacuum Full ??? ?? ? ??? ???? ?? ?? ?? ??? ??? ??? ?? ??: http://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-60849690
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum ? Autovacuum ??? ??? ??? ?? ??? ?? ???? Vacuum ??? ???? ?? ?? ??? ?? ? ?? ? ??? ?????? ???? ???? ?? autovacuum? ?? ?? ? Autovacuum ??? ???? ????, ?? DB ???? ???? ?? ?? DB? ??? Vacuum ??? ?? ? ? Autovacuum ??? ??? ?? ??/??/?? ? ??, ??? ??? ?? ???? ?? (?? ???? ???? ??) Vacuum ??? ?? ?? ???? ?? ? ??? ? ???? ???! ??? ??? ?? ?? ?? ??? ??, Vacuum Full DBA
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum autovacuum_naptime autovacuum ??? ?? ¡°activity rounds¡±?? ?, autovacuum ??? ?? ?? ??, ?? ?? (?? ? 1? ¨C ?(Ãë)? ??) autovacuum_vacuum_threshold / autovacuum_analyze_threshold ? ??? ??? ????/??? ??? ?? ??? ??? VACUUM/ANAYLZE ? ??? ??, ?? ??? ?? ?? ? (?? ? 50) autovacuum_freeze_max_age ? ??? ??? ??? vacuum ???? pg_class.relfrozenxid ?? ??? ??, ? ?? ??? ? ??? vacuum ?? ?? autovacuum_vacuum_cost_delay autovacuum ??? ?? ?? ???, ??? ?? ?? autovacuum_vacuum_cost_limit autovacuum ??? ??? ? ?? ?? ?? autovacuum_vacuum_scale_factor ? ??? ??? autovacuum_vacuum_threshold ?? ???? vacuum ??? ? ???? ? ?, ????? ??? ??? ??? (???? 0.2 = ??? ??? 20%)
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Autovacuum? ???? ?? 2. Autovacuum 1. ??? ??? autovacuum_freeze_max_age ???? ??? ???? ??? ???, ? ???? ??? Vacuum ?? * ???? ??: relfrozenxid ?? ?? age () ??? ??? ? 2. ???? ??? ????, Vacuum ???? ?????, Vacuum ?? * Vacuum ??? = Vacuum ?? ??? + Vacuum ??? * ??? ? Vacuum ?? ???: autovacuum_vacuum_threshold ? Vacuum ???: autovacuum_vacuum_scale_factor ? ???: pg_class.reltuples
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 02. [MySQL] Page? Undo page deep internal ?? ???: ?????? ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Page? ???? ?1?? Page? 16 KB ?? Page? Header/Trailer ?? ?1?? Page? 16 KB ?? Page ?? ????(T1)? ?? ??
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Clustered indexes Page ?? (1/2) Page ?? ????(T1)? ?? ?? 03 06 02 0e 00 00 00 10 00 30 50 50 31 30 31 30 20 ¡­ 00 00 00 af 06 22 bc 00 00 61 0c 01 10 41 20 41 20 ¡­ trx_id ¡°af0622¡± hex?? ???? 11470370 The fields of the roll pointer structure are: 1-bit ¡°is insert¡± flag, 7-bit rollback segment ID, 4-byte page number and 2-byte page offset of the undo log location. ? create table exem_i.t1 ( c1 char(14), c2 char(2) , c3 char(6), c4 char(3) , primary key(c1) ) engine=innodb ; ? create index t1_ix1 on exem_i.t1( c3,c4 ) ; ? Insert into t1 values (¡®PP1010¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ; ? Insert into t1 values (¡®PP1020¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ; ? Insert into t1 values (¡®PP1030¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ;
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Clustered indexes Page ?? (2/2) Page ?? ????(T1)? ?? ??? PK???? ??? ???? Page ???? ? Infimum ????? ???? Next Offset ??? ??? ??
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Secondary indexes Page ?? Page ?? ????(T1) ? ???? ?????? ??? Non-PK ???? Page ???? ? Clustered indexes ???? ??? ??? Infimum ????? ???? Next Offset ??? ??? ?? ? ???? rowid ? ??? PK ??? ????.
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 03. [MySQL] Hint ??? ???: ?????? ???
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ?? ??? ?? ?? ? 5.7.7?? ??? ??? ? ?? ?????optimizer_switch ??? ??? ??? ????? ??? ? ?? ??? BKA Batched Key Access join???? /*+ BKA(@qb|table) */ NO_BKA /*+ NO_BKA(@qb|table) */ BNL Block Nested-Loop join???? /*+ BNL(@qb|table) */ NO_BNL /*+ NO_BNL(@qb|table) */ MRR Multi-Range Read ????? ???? /*+ MRR(table|index) */ NO_MRR /*+ NO_MRR(table|index) */ MAX_EXECUTION_TIME SELECT ????? ??? ? (N milliseconds) /*+ MAX_EXECUTION_TIME(n) */ NO_ICP Index Condition Pushdown?????? ????? ? /*+ NO_ICP(table|index) */ NO_RANGE_OPTIMIZATION Range ??? ???? ??? ? ?? ?? ??? ??? Merge? Loose ??? ??? ??? /*+ NO_RANGE_OPTIMIZATION(ta ble|index) */ QB_NAME ????? ??? ??? /*+ QB_NAME(qbname) */ SEMIJOIN ?????? ?? ? ???? (DUPSWEEDOUT, FIRSTMA TCH, LOOSESCAN, MATERIALIZATION) /*+ SEMIJOIN(@qb strategy) */ NO_SEMIJOIN /*+ NO_SEMIJOIN(@qb strategy) */ SUBQUERY ?????? ??(INTOEXISTS, MATERIALIZATION) /*+ SUBQUERY(@qb strategy) */
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ?? ??? ?? ?? ? ???? ??? ??? ? ??? ?? (http://dev.mysql.com/d oc/refman/5.7/en/keywo rds.html) USE INDEX ??? ???? ?? USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GR OUP BY}] ([index_list]) INGONRE INDEX ??? ???? ???? ?? IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY| GROUP BY}] (index_list) FORCE INDEX ??? ???? ??? ?? FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY| GROUP BY}] (index_list) STRAIGHT_JOIN ??? ??? FROM?? ??? ?? SELECT STRAIGHT_JOIN ... FROM SQL_SMALL_RESULT ?????? ??? '??' ???? ??? SELECT SQL_SMALL_RESULT ... FROM SQL_BIG_RESULT ?????? ??? '?' ???? ??? SELECT SQL_BIG_RESULT ... FROM SQL_BUFFER_RESULT ??? ?????? ??, ?????? ???? SELECT SQL_BUFFER_RESULT ... FROM SQL_CACHE ??? ????(query_cache_type=2)? ??? SELECT SQL_CACHE ... FROM SQL_NO_CACHE ??? ????? ???? ?? SELECT SQL_NO_CACHE ... FROM SQL_CALC_FOUND_ROWS LIMIT? ?? ??? ?? ???? ????, FOUND_ROWS()??? ????. SELECT SQL_CALC_FOUND_ROWS ... FROM HIGH_PRIORITY ????? ????(ex. Myisam)???? ???? ??, ?? ??? ??? ??? ? ?? ???? ????? ??, SELECT HIFH_PRIORITY ... FROM LOW_PRIORITY SELECT LOW_PRIORITY ... FROM DELAYED INSERT, REPLACE??? ??? ???? ??? ?? INSERT DELAYED INTO ...
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ignore index(key|index) ?? ?? mysql> explain select * from employees.employees where emp_no<30000 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | employees | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 39392 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ mysql> explain select * from employees.employees ignore index(primary) where emp_no<30000; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL| 299113 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? straight join ?? ?? mysql> explain select * from employees,departments limit 1; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | departments | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> explain select STRAIGHT_JOIN * from employees,departments limit 1; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | NULL | | 1 | SIMPLE | departments | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (.00 sec)
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? SQL_CACHE ?? ?? mysql> select * from employees.employees limit 100000; mysql> show status like 'Qcache_inserts'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 3 | +----------------+-------+ mysql> select sql_cache * from employees.employees limit 100000; mysql> show status like 'Qcache_inserts'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 4 | +----------------+-------+
? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? NO_BNL ?? ?? mysql> explain select * from departments join employees limit 1; +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | straight | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ mysql> explain select /*+ no_bnl(employees) */ * from departments straight join employees limit 1; +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | straight | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
Research & Contents Blog Video E-mail NAVER http://cafe.naver.com/playexem ITPUB http://blog.itpub.net/31135309/ Wordpress https://playexem.wordpress.com/ ºÝºÝߣshare http://www.slideshare.net/playexem Youtube https://www.youtube.com/channel/UC5wKR _-A0eL_Pn_EMzoauJg Tudou http://www.tudou.com/home/maxgauge/ ?? ??: ?????? ??? edu@ex-em.com ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved.

More Related Content

? 7? ?? ?? ??? ?? ??????

  • 1. ? 7? ?? ?? ??? ?? EXEM seminar report no. 007 (2016.07.06) Research & Contents Team
  • 2. Table of Agenda ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 01. 02. 03. [PostgreSQL] Vacuum? ?? ?? ? (3?) [MySQL] Page? Undo page deep internal ?? [MySQL] Hint ???
  • 3. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 01. [PostgreSQL] Vacuum? ?? ?? ? (3?) ???: ?????? ???
  • 4. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. ?? 1? 1) Vacuum ?? ? ??? 2) Vacuum ?? ?? 3) ?? Vacuum VS Vacuum full (????) 2? 1) dump? ?? ¡°?? Vacuum VS Vacuum full¡± [PostgreSQL] Vacuum? ?? ?? ? 3? 1) XID wraparound and freezing tuple 2) Autovacuum
  • 5. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? ????? ?? MVCC ??: XID(???? ID)? ??? ???? ?? ? 232 ?? ??? ?? (?? 40?) ? 40? ????: ? XID(20?) + ? XID(20?) ? ? XID 20?? ???, ? XID 20?? ???? ?? ? ?? ????? ?? ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid ? XID(20?) ? XID(20?) Visible ( Invisible )
  • 6. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? XID wraparound ??? ??? ???? ?? ?? ?? ? Insert¨Conly ?????? ?? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue ??(visible) ??(invisible) Visible Visible Invisible
  • 7. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ? ??? ¡°FREEZE¡±? ????, ? FrozenXID? ?? XID ?? ???? ?? ???? ?? ??? ? 20? ????? ??? ?? FrozenXID ?, ?? ??? ??? ???? XID ?? ?? ?? ? ??? ?? ??(data freezing)? VACUUM FREEZE ???? ?? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue ??(visible) ??(invisible) Visible VisibleVisible
  • 8. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple Skip ?? ?? pg_class ???? relfrozenxid ??? : VACUUM ???? ? ??? ??? ??? XID ?? ??? ?? ???? ID ? ??: http://www.slideshare.net/hadoopxnttdata/postgresql-xid-wraparound-another-issue
  • 9. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? autovacuum ??? ?? ??? ???? ?? ???? ???? ???? ID ?? ??? ?? ? Table age? (vacuum_freeze_table_age ¨C vacuum_freeze_min_age)?? ??, ?? ???? ???? ?? ??? ?? VACUUM ???? ?? ???? ?? ?? ? XID ?? ??? ? ??? ?? ?? 0?? ????, VM ???? ?? ?? ?? ??? ?? ???: autovacuum_freeze_max_age * 0.95
  • 10. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? ? ????? ?? VACUUM? ???
  • 11. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid 20? 2? 1.5? ? ???? VACUUM ??? ?? anti-wraparound VACUUM? ???
  • 12. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 1. XID Wraparound and Freezing Tuple FrozenXID? ??? ? ? XID? ??? ?? ??? ?? anti-wraparound VACUUM ??? relfrozenxid ?? ????? ??: http://www.slideshare.net/pgdayasia/introduction-to-vacuum-freezing-and-xid
  • 13. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 20? 2? 1.5? 1. XID Wraparound and Freezing Tuple ? ? ???? vacuum ?? ?? ?? ???? ??? ? ?? ?? : ??? vacuum ????, 20? ¨C vacuum_freeze_min_age ???? ???? ? ???? ??, vacuum ?? ??? ??? ?? Autovacuum ??? ???? ????, autovacuum_freeze_max_age ?? ???? vacuum ?? ?? ? ? ???? ?? vacuum ??? ??? ?? ????, (Autovacuum_freeze_max_age ¨C vacuum_freeze_min_age) ? ??? ???? ?? ?, autovacuum ?? ??
  • 14. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum Autovacuum launcher Autovacuum_naptime ??? ??? ? ???? ? ?? ??? ? ?????? ??? ? ??? worker ????? ?? ??? ?? Autovacuum workers Autovacuum_max_workers worker ???? ?? ?? AUTOVACUUM??? VACUUM ??? ANALYZE ??? ????? ?? ?? ? ???? ?? Vacuum ??? ?? ? ??? ???? ??? Vacuum Full ??? ?? ? ??? ???? ?? ?? ?? ??? ??? ??? ?? ??: http://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-60849690
  • 15. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum ? Autovacuum ??? ??? ??? ?? ??? ?? ???? Vacuum ??? ???? ?? ?? ??? ?? ? ?? ? ??? ?????? ???? ???? ?? autovacuum? ?? ?? ? Autovacuum ??? ???? ????, ?? DB ???? ???? ?? ?? DB? ??? Vacuum ??? ?? ? ? Autovacuum ??? ??? ?? ??/??/?? ? ??, ??? ??? ?? ???? ?? (?? ???? ???? ??) Vacuum ??? ?? ?? ???? ?? ? ??? ? ???? ???! ??? ??? ?? ?? ?? ??? ??, Vacuum Full DBA
  • 16. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 2. Autovacuum autovacuum_naptime autovacuum ??? ?? ¡°activity rounds¡±?? ?, autovacuum ??? ?? ?? ??, ?? ?? (?? ? 1? ¨C ?(Ãë)? ??) autovacuum_vacuum_threshold / autovacuum_analyze_threshold ? ??? ??? ????/??? ??? ?? ??? ??? VACUUM/ANAYLZE ? ??? ??, ?? ??? ?? ?? ? (?? ? 50) autovacuum_freeze_max_age ? ??? ??? ??? vacuum ???? pg_class.relfrozenxid ?? ??? ??, ? ?? ??? ? ??? vacuum ?? ?? autovacuum_vacuum_cost_delay autovacuum ??? ?? ?? ???, ??? ?? ?? autovacuum_vacuum_cost_limit autovacuum ??? ??? ? ?? ?? ?? autovacuum_vacuum_scale_factor ? ??? ??? autovacuum_vacuum_threshold ?? ???? vacuum ??? ? ???? ? ?, ????? ??? ??? ??? (???? 0.2 = ??? ??? 20%)
  • 17. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Autovacuum? ???? ?? 2. Autovacuum 1. ??? ??? autovacuum_freeze_max_age ???? ??? ???? ??? ???, ? ???? ??? Vacuum ?? * ???? ??: relfrozenxid ?? ?? age () ??? ??? ? 2. ???? ??? ????, Vacuum ???? ?????, Vacuum ?? * Vacuum ??? = Vacuum ?? ??? + Vacuum ??? * ??? ? Vacuum ?? ???: autovacuum_vacuum_threshold ? Vacuum ???: autovacuum_vacuum_scale_factor ? ???: pg_class.reltuples
  • 18. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 02. [MySQL] Page? Undo page deep internal ?? ???: ?????? ???
  • 19. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Page? ???? ?1?? Page? 16 KB ?? Page? Header/Trailer ?? ?1?? Page? 16 KB ?? Page ?? ????(T1)? ?? ??
  • 20. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Clustered indexes Page ?? (1/2) Page ?? ????(T1)? ?? ?? 03 06 02 0e 00 00 00 10 00 30 50 50 31 30 31 30 20 ¡­ 00 00 00 af 06 22 bc 00 00 61 0c 01 10 41 20 41 20 ¡­ trx_id ¡°af0622¡± hex?? ???? 11470370 The fields of the roll pointer structure are: 1-bit ¡°is insert¡± flag, 7-bit rollback segment ID, 4-byte page number and 2-byte page offset of the undo log location. ? create table exem_i.t1 ( c1 char(14), c2 char(2) , c3 char(6), c4 char(3) , primary key(c1) ) engine=innodb ; ? create index t1_ix1 on exem_i.t1( c3,c4 ) ; ? Insert into t1 values (¡®PP1010¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ; ? Insert into t1 values (¡®PP1020¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ; ? Insert into t1 values (¡®PP1030¡¯,¡¯A¡¯,¡¯A¡¯,¡¯A¡¯) ;
  • 21. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Clustered indexes Page ?? (2/2) Page ?? ????(T1)? ?? ??? PK???? ??? ???? Page ???? ? Infimum ????? ???? Next Offset ??? ??? ??
  • 22. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Page? Undo page deep internal ?? Secondary indexes Page ?? Page ?? ????(T1) ? ???? ?????? ??? Non-PK ???? Page ???? ? Clustered indexes ???? ??? ??? Infimum ????? ???? Next Offset ??? ??? ?? ? ???? rowid ? ??? PK ??? ????.
  • 23. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. 03. [MySQL] Hint ??? ???: ?????? ???
  • 24. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ?? ??? ?? ?? ? 5.7.7?? ??? ??? ? ?? ?????optimizer_switch ??? ??? ??? ????? ??? ? ?? ??? BKA Batched Key Access join???? /*+ BKA(@qb|table) */ NO_BKA /*+ NO_BKA(@qb|table) */ BNL Block Nested-Loop join???? /*+ BNL(@qb|table) */ NO_BNL /*+ NO_BNL(@qb|table) */ MRR Multi-Range Read ????? ???? /*+ MRR(table|index) */ NO_MRR /*+ NO_MRR(table|index) */ MAX_EXECUTION_TIME SELECT ????? ??? ? (N milliseconds) /*+ MAX_EXECUTION_TIME(n) */ NO_ICP Index Condition Pushdown?????? ????? ? /*+ NO_ICP(table|index) */ NO_RANGE_OPTIMIZATION Range ??? ???? ??? ? ?? ?? ??? ??? Merge? Loose ??? ??? ??? /*+ NO_RANGE_OPTIMIZATION(ta ble|index) */ QB_NAME ????? ??? ??? /*+ QB_NAME(qbname) */ SEMIJOIN ?????? ?? ? ???? (DUPSWEEDOUT, FIRSTMA TCH, LOOSESCAN, MATERIALIZATION) /*+ SEMIJOIN(@qb strategy) */ NO_SEMIJOIN /*+ NO_SEMIJOIN(@qb strategy) */ SUBQUERY ?????? ??(INTOEXISTS, MATERIALIZATION) /*+ SUBQUERY(@qb strategy) */
  • 25. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ?? ??? ?? ?? ? ???? ??? ??? ? ??? ?? (http://dev.mysql.com/d oc/refman/5.7/en/keywo rds.html) USE INDEX ??? ???? ?? USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GR OUP BY}] ([index_list]) INGONRE INDEX ??? ???? ???? ?? IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY| GROUP BY}] (index_list) FORCE INDEX ??? ???? ??? ?? FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY| GROUP BY}] (index_list) STRAIGHT_JOIN ??? ??? FROM?? ??? ?? SELECT STRAIGHT_JOIN ... FROM SQL_SMALL_RESULT ?????? ??? '??' ???? ??? SELECT SQL_SMALL_RESULT ... FROM SQL_BIG_RESULT ?????? ??? '?' ???? ??? SELECT SQL_BIG_RESULT ... FROM SQL_BUFFER_RESULT ??? ?????? ??, ?????? ???? SELECT SQL_BUFFER_RESULT ... FROM SQL_CACHE ??? ????(query_cache_type=2)? ??? SELECT SQL_CACHE ... FROM SQL_NO_CACHE ??? ????? ???? ?? SELECT SQL_NO_CACHE ... FROM SQL_CALC_FOUND_ROWS LIMIT? ?? ??? ?? ???? ????, FOUND_ROWS()??? ????. SELECT SQL_CALC_FOUND_ROWS ... FROM HIGH_PRIORITY ????? ????(ex. Myisam)???? ???? ??, ?? ??? ??? ??? ? ?? ???? ????? ??, SELECT HIFH_PRIORITY ... FROM LOW_PRIORITY SELECT LOW_PRIORITY ... FROM DELAYED INSERT, REPLACE??? ??? ???? ??? ?? INSERT DELAYED INTO ...
  • 26. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? ignore index(key|index) ?? ?? mysql> explain select * from employees.employees where emp_no<30000 +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | employees | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 39392 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ mysql> explain select * from employees.employees ignore index(primary) where emp_no<30000; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL| 299113 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 27. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? straight join ?? ?? mysql> explain select * from employees,departments limit 1; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | departments | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> explain select STRAIGHT_JOIN * from employees,departments limit 1; +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | NULL | | 1 | SIMPLE | departments | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (.00 sec)
  • 28. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? SQL_CACHE ?? ?? mysql> select * from employees.employees limit 100000; mysql> show status like 'Qcache_inserts'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 3 | +----------------+-------+ mysql> select sql_cache * from employees.employees limit 100000; mysql> show status like 'Qcache_inserts'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Qcache_inserts | 4 | +----------------+-------+
  • 29. ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved. Hint ??? NO_BNL ?? ?? mysql> explain select * from departments join employees limit 1; +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | straight | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+---------------------------------------+ mysql> explain select /*+ no_bnl(employees) */ * from departments straight join employees limit 1; +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | straight | NULL | index | NULL | dept_name | 122 | NULL | 9 | 100.00 | Using index | | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299113 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
  • 30. Research & Contents Blog Video E-mail NAVER http://cafe.naver.com/playexem ITPUB http://blog.itpub.net/31135309/ Wordpress https://playexem.wordpress.com/ ºÝºÝߣshare http://www.slideshare.net/playexem Youtube https://www.youtube.com/channel/UC5wKR _-A0eL_Pn_EMzoauJg Tudou http://www.tudou.com/home/maxgauge/ ?? ??: ?????? ??? edu@ex-em.com ? Copyrights 2001~2016, EXEM CO.,LTD. All Rights Reserved.