DBAが開発チームによく聞きかれること3選+1 MySQLCasualFukuoka Vol.7

Transcript

1. %#"͕։ൃνʔϜʹΑ ͘ฉ͖͔ΕΔ͜ͱબ  .Z42-
   $BTVBM
   5BMLT
   JO
   'VLVPLB
   7PM
   5BLBZVLJ
   )POEB !WJEBJTVLJ
   
2. ࣗݾ঺հ w 5BLBZVLJ
   )POEB
   !WJEBJTVLJ
   w ౎಺ͷ๭ࣾͰ%#"΍ͬͯ·͢
   w .Z/"ձͰ-5ਓһ͕଍Γͳ͍ͱͷ࿩Λฉ͖ɺ੎͍ͰࢀՃ͠·͠ ͨ
   w -5͸ॳΊͯͰ͕͢ποίϛ͸ͲΜͲΜ͓ئ͍͠·͢ ?@?ʢެ։ ͢Δ࣌಺ॹͰ௚͠·͢ʣ
3. ࠓ೔ͷ͓୊ wҰ೥લ͘Β͍ʹࠓͷձࣾʹ དྷͯΑ͘ฉ͔Εͨࣄͱରॲ ͨ͠ࣄΛͭΒͭΒͱ࿩͠· ͢ɻ
4. σουϩοΫ͕ͨ͘͞Μൃੜ͢Δ ΜͰ͚͢Ͳɾɾ
5. αϯϓϧςʔϒϧ $3&"5&
   5"#-&
   AVTFS@UBCMFA
   
   
   
   A64&3@*%A
   CJHJOU 
   /05
   /6--
   "650@*/$3&.&/5
   
   
   
   A130+&$5@$0%&A
   CJHJOU 
   /05
   /6--
   
   
   
   A$6450.&3@*%A
   CJHJOU 
   /05
   /6--
   
   
   
   A$0..&/5A
   WBSDIBS 
   $0--"5&
   VUGNC@CJO
   
   
   13*."3:
   ,&:
   A64&3@*%A
   
   
   6/*26&
   ,&:
   A6,@1&0+&$5@$0%&@$6450.&3@*%A
   A130+&$5@$0%&A A$6450.&3@*%A
   
   &/(*/&*OOP%#
   %&'"6-5
   $)"34&5VUGNC
   $0--"5&VUGNC@CJO ˡ͕͜͜6/*26&ɹ,&:
6. αϯϓϧσʔλ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF    IPHF    IPHF    IPHF
7. ͜͜ʹσʔλΛೖΕ͍ͨ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF    IPHF    GVHB    IPHF    IPHF
8. ࢓༷ 
   #&(*/
   
   61%"5&
   VTFS@UBCMF
   4&5
   $0..&/5
   
   GVHB
   8)&3&
   
   130+&$5@$0%&
   
   
   "/%
   $6450.&3@*%
   
   
   2VFSZ
   0,
   
   SPXT
   B⒎FDUFE
   
   TFD
   3PXT
   NBUDIFE
   
   
   $IBOHFE
   
   
   8BSOJOHT
   
   6QEBUFͯ݅ͩͬͨ͠৔߹͸*OTFSU
   
   */4&35
   */50
   VTFS@UBCMF
   130+&$5@$0%& $6450.&3@*% $0..&/5
   7"-6&4
     GVHB 
   
   $0..*5
9. 6QEBUF͕ۭৼΔͱΪϟοϓϩοΫΛऔΔ 59
   
   #&(*/
   
   61%"5&
   VTFS@UBCMF
   4&5
   $0..&/5
   
   GVHB
   8)&3&
   
   130+&$5@$0%&
   
   
   "/%
   $6450.&3@*%
   
   
   2VFSZ
   0,
   
   SPXT
   B⒎FDUFE
   
   TFD
   3PXT
   NBUDIFE
   
   
   $IBOHFE
   
   
   8BSOJOHT
   
   mysql> SELECT * FROM performance_schema.data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2098:1064 ENGINE_TRANSACTION_ID: 2098 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: honda OBJECT_NAME: user_table PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140578507493192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 2098:3:5:4 ENGINE_TRANSACTION_ID: 2098 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: honda OBJECT_NAME: user_table PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: UK_PEOJECT_CODE_CUSTOMER_ID OBJECT_INSTANCE_BEGIN: 140578507490152 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 100, 100020 2 rows in set (0.00 sec) -0$,@.0%&
   9 ("1
   -0$,@%"5"
    
10. ผͷΫΤϦ͕ಉ͡6QEBUFΛ౤͛Δͱ
    ΪϟοϓϩοΫΛ̎ॏͰऔΔ 59
    
    #&(*/
    
    61%"5&
    VTFS@UBCMF
    4&5
    $0..&/5
    
    GVHB
    8)&3&
    
    130+&$5@$0%&
    
    
    "/%
    $6450.&3@*%
    
    
    2VFSZ
    0,
    
    SPXT
    B⒎FDUFE
    
    TFD
    3PXT
    NBUDIFE
    
    
    $IBOHFE
    
    
    8BSOJOHT
    
    mysql> SELECT * FROM performance_schema.data_locks\G; +--------+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ | INNODB | 2255:1070 | 2255 | 49 | 51 | user_table | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 2255:9:5:4 | 2255 | 49 | 51 | user_table | UK_PEOJECT_CODE_CUSTOMER_ID | RECORD | X,GAP | GRANTED | 100, 100020 | | INNODB | 2254:1070 | 2254 | 48 | 140 | user_table | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 2254:9:5:4 | 2254 | 48 | 140 | user_table | UK_PEOJECT_CODE_CUSTOMER_ID | RECORD | X,GAP | GRANTED | 100, 100020 | +————+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ ˞ΧϥϜҰ෦ུ
    
11. σʔλ͕ແ͍ͷͰ*OTFSU͕ͦΕͧΕ࣮ߦ͞Ε
    %FBE-PDLൃੜ 1)Tx1:ͰInsert mysql> INSERT INTO user_table (PROJECT_CODE,CUSTOMER_ID,COMMENT) VALUES (100,100015,'fuga'); Tx2ͷϩοΫ଴ͪ 2)Tx2:ͰInsert mysql> INSERT INTO user_table (PROJECT_CODE,CUSTOMER_ID,COMMENT) VALUES (100,100015,'fuga'); Tx1ͷϩοΫ଴ͪͰσουϩοΫ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 3)Tx2͸ϩʔϧόοΫ͞ΕTx1ͷINSERT͸࣮ߦ͞ΕΔɻ Query OK, 1 row affected (17.22 sec)
12. ਤղ 130+&$5@$0%& $6450.&3@*%     ˣ ˣ     ᶃVQEBUF ᶄVQEBUF 5Y 5Y ᶅJOTFSU ᶆJOTFSU ᶇ
    ☠%FBE-PDL
13. ରԠ wجຊతʹ%FBE-PDL͸ϩδοΫଆͰ௚ͯ͠΋ Β͏͔͠ͳ͍ͷͰɺ࠶ݱͤͯ͞ΞϓϦέʔγϣ ϯΤϯδχΞʹ͓౉͢͠Δ༁Ͱ͕͢ɺ.Z42- ͷϩοΫ͸தʑ૝ఆͮ͠Β͍ॴͰൃੜ͠·͢ Ͷɻ
    w˞͖͞΄ͲͷϩοΫ͸෼཭Ϩϕϧ
    
    3FBE
    $PNJUUFEͰ͸ൃੜ͠·ͤΜɻ
    
14. ΧϥϜ௥Ճ͍ͨ͠ΜͰ͚͢Ͳɾɾ
15. ΦϯϥΠϯͰग़དྷ·͔͢ʁ w .Z42-
    ͔Β0/-*/&
    "-5&3
    5"#-& "-(03*5).*/1-"$& ͕͋ΔͷͰΦϯϥΠϯͰ࣮ߦ ग़དྷ·͢ɻ
    
    w ͨͩ͠%%-࣮ߦͷ࢝ΊͱऴΘΓͰ.FUBEBUB-PDLΛऔΓ·͢ɻ ςʔϒϧ࠶ߏஙத΋ෛՙߴΊɻ
    w ͳͷͰෛՙͷ௿͍࣌ؒଳʹ࣮ߦ͢Δͷ͕޷·͍͠ɻ
16. ͲΕ͘Β͍͔͔Γ·͔͢ʁ w ͜Ε͕ͳ͔ͳ͔೉͍͠ɻ
    w ςʔϒϧͷσʔλαΠζʹԠ͕ͯ࣌ؒ͡มΘΔͷͰɺຊ൪ͱ ಉ౳ͷσʔλͰςετ
    w ͔ͭຊ൪ͱಉ౳ͷෛՙͰςετʢಉΦϒδΣΫτͰߋ৽ɾࢀ র͕͋Δͱ͔͔Δ͕࣌ؒେ෼มΘΔʣ
    w ຊ൪ͱಉ͡ෛՙΛֻ͚Δͬͯ݁ߏΊΜͲ͍ɾɾ
    w ͱ͍͏༁Ͱ೔࿨ͬͯϝϯςೖΕͪΌ͏࣌΋··͋Γ·͢ɻ
17. ظ଴ͷ৽੕
    *OTUBOU
    "EE
    $PMVNO ALTER TABLE t1 ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT; Currently, InnoDB supports INSTANT algorithm for these operations: ▪Change index option ▪Rename table (in ALTER way) ▪SET/DROP DEFAULT ▪MODIFY COLUMN ▪Add/drop virtual columns ▪Add columns(non-generated) – We call this instant ADD COLUMN ▪ ࢀߟ
    ▪ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ ▪ https://mysqlserverteam.com/the-mysql-8-0-12-maintenance-release-is-generally-available/ ▪ https://mysql.wisborg.dk/2018/07/29/mysql-8-0-12-instant-alter-table/ ʙ
18. ͕͢͞ʹ.FUBEBUB-PDL଴ͪ͸༗ΔΈ͍ͨͰ͕͢ɾɾɾ
    ૣ͘ຊ൪Ͱ࢖͍͍ͨʂʂ TX1: mysql> BEGIN; mysql> SELECT user_id FROM user_table where user_id = 1; +---------+ | user_id | +---------+ | 1 | +————+ 1 row in set (0.00 sec) mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | NULL | 140577299575536 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5617 | 55 | 20 | | TABLE | honda | user_table | NULL | 140577299549744 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5617 | 55 | 21 | +-------------+--------------------+----------------+-------------+-----------------------+------------- +---------------+-------------+-------------------+-----------------+----------------+ 2 rows in set (0.00 sec) -------------------------------------------------------------------------------------------------------------------- TX2: ALTER TABLE user_table ADD COLUMN dd INT NOT NULL DEFAULT 1000, ALGORITHM=INSTANT; ɻɻɻɻ
19. ΞϓϦαʔόʔ૿΍͍ͨ͠Μ Ͱ͚͢Ͳ ίωΫγϣϯ਺૿΍ͯ͠ ΋େৎ෉ ʁ
20. .Z42-ͷϝϞϦߏ੒ ࡶ JOOPEC@CV⒎FS@QPPM@TJ[F
    JOOPEC@MPH@pMF@TJ[F TPSU@CV⒎FS@TJ[F
    SFBE@CV⒎FS@TJ[F
    SFBE@SOE@CV⒎FS@TJ[F
    CJOMPH@DBDIF@TJ[F
    UNQ@UBCMF@TJ[F
    UISFBE@TUBDL
    OFU@CV⒎FS άϩʔόϧόοϑΝ
    ηογϣϯόοϑΝ
21. ࢖༻ϝϞϦαΠζͷݟੵ΋Γ ࡶ άϩʔόϧόοϑΝ
    
    
    
    ηογϣϯόοϑΝ
    Y
    ίωΫγϣϯ਺
    
    
    Ωϟογϡͱ͔04͕࢖͏෼
    
22. ͨͩ͠ ηογϣϯόοϑΝ͸ઃఆ஋ΛϑϧαΠζඞͣ֬อ ͢ΔΘ͚Ͱ͸ͳ͘ɺίωΫγϣϯຖʹϑϧαΠζ֬ อ͢Δ΋ͷɺ࠷খ஋͔Β࢝·ͬͯඞཁʹԠͯ͡࠷େ ஋·Ͱ֬อ͞ΕΔ΋ͷɺඞཁͳঢ়گʹͳͬͨΒϑϧ αΠζ֬อ͢Δ΋ͷɾɾͳͲ৭ʑύλʔϯ͕͋ΔΒ ͍͠ ͋Μ·ΓϚχϡΞϧʹॻ͍ͯͳ͍ʣɻ
    
23. KPJO@CV⒎FS@TJ[F w ΠϯσοΫεΛ࢖༻͠ͳ͍+0*/Ͱ࢖༻͞ΕΔɻ
    w σϑΥϧτ஋
    ,# Ҏ߱
    w ઃఆ஋͕.#ͩͬͨ
    w ࢀߟ
 IUUQTXXXQFSDPOBDPNCMPH IPXJTKPJO@CV⒎FS@TJ[FBMMPDBUFE
24. εϨουຖͷ࣮ফඅϝϞϦྔΛܭଌ NZTRM
    TFMFDU
    
    GSPN
    TZTNFNPSZ@CZ@UISFBE@CZ@DVSSFOU@CZUFT
          
    c
    UISFBE@JE
    c
    VTFS
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    DVSSFOU@DPVOU@VTFE
    c
    DVSSFOU@BMMPDBUFE
    c
    DVSSFOU@BWH@BMMPD
    c
    DVSSFOU@NBY@BMMPD
    c
    UPUBM@BMMPDBUFE
    c
          
    c
    
    
    
    
    
    
    
    
    c
    IPOEB!YYYYYYYYY
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    .J#
    
    
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    
    c
    
    .J#
    
    
    
    
    
    
    
    
    
    c
    
    .J#
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    c
    IPOEB!YYYYYYYYY
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    c
    
    .J#
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    c
    SETBENJO!MPDBMIPTU
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    
    ,J#
    
    
    
    
    
    
    
    
    c
    
    .J#
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    c
    TRMTJHOBM@IBOEMFS
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    c
    TRMDPNQSFTT@HUJE@UBCMF
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    
    c
    TRMNBJO
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    
    c
    TRMUISFBE@UJNFS@OPUJpFS
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    c
    
    
    
    
    
    
    
    
    
    c
    JOOPECJP@XSJUF@UISFBE
    
    
    
    
    
    
    
    
    
    c
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    
    
    c
    
    CZUFT
    
    
    
    
    
    
    
    
    c
    mysql> select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id = 42 and SUM_NUMBER_OF_BYTES_ALLOC <> 0; +-----------+-------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+-------------------------- +------------------------------+---------------------------+ | THREAD_ID | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | +-----------+-------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+-------------------------- +------------------------------+---------------------------+ | 42 | memory/sql/thd::main_mem_root | 673 | 671 | 8127360 | 8111040 | 0 | 2 | 16 | 0 | 16320 | 326400 | | 42 | memory/sql/THD::sp_cache | 1 | 0 | 8176 | 0 | 0 | 1 | 1 | 0 | 8176 | 8176 | | 42 | memory/sql/sp_head::main_mem_root | 97 | 0 | 1122536 | 0 | 0 | 97 | 97 | 0 | 1122536 | 1122536 | | 42 | memory/sql/sp_head::execute_mem_root | 2447 | 2447 | 19967520 | 19967520 | 0 | 0 | 2 | 0 | 0 | 16320 | | 42 | memory/sql/sp_head::call_mem_root | 797 | 797 | 6503520 | 6503520 | 0 | 0 | 2 | 0 | 0 | 16320 | | 42 | memory/sql/String::value | 951 | 920 | 90544 | 73272 | 0 | 31 | 33 | 0 | 17272 | 32808 | | 42 | memory/sql/THD::db | 809 | 808 | 3251 | 3232 | 0 | 1 | 2 | 0 | 19 | 23 | | 42 | memory/sql/user_var_entry | 7 | 5 | 2155 | 992 | 0 | 2 | 3 | 0 | 1163 | 1675 | |
25. ରԠ wશίωΫγϣϯ͕ઃఆ஋Λ'VMMͰ࢖͏ͱɺܭࢉ্ϝ ϞϦ͋;ΕΔͷͰίωΫγϣϯ૿΍ͤͳ͍ɾɾͱ͍ ͏ঢ়گͩͬͨ
    wઃఆ஋Λదਖ਼Խ͢Δ εϨουόοϑΝ͸૿΍͗͢͠ ې෺
    wࠓ͸͕Μ͕Μαʔόʔ૿΍ͯ͠·͢
26. %#"ͷਓͬͯ
    ීஈԿͯ͠ΔΜͰ͔͢ʁ
27. ৭ʑ΍ͬͯ·͢ wલهͨ͠Α͏ͳ໰͍߹ΘͤରԠ
    wςʔϒϧઃܭϨϏϡʔ
    wෛՙ؂ࢹ
    w42-ύϑΥʔϚϯενϡʔχϯά
    wτϥϒϧఆظϝϯςφϯεରԠ
    w৽όʔδϣϯݕূ
    wσʔλநग़
    wσʔλϕʔεΠϯετʔϧ
    wόοΫΞοϓઃܭ
28. ࠓ·Ͱ͜Μͳମ੍Ͱ࢓ࣄ͖ͯ͠·͕ͨ͠ %#" *OGSB %FW ʙਓ ʙਓ͘Β͍ ͨ͘͞Μ
29. Ϋϥ΢υશ੝ͷ͜ͷ࣌͝ੈɾɾ *OGSB%#" %FW Ռͯ͸ %FW*OGSB%#"
30. ࠓޙ΁ͷෆ҆ɾɾ %FW *OGSB %#" 0UIFS
31. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ