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

92d88ab60080357b192be6bcfb4f9865?s=47 Takayuki Honda
August 09, 2018
1.2k

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

92d88ab60080357b192be6bcfb4f9865?s=128

Takayuki Honda

August 09, 2018
Tweet

Transcript

  1. %#"͕։ൃνʔϜʹΑ ͘ฉ͖͔ΕΔ͜ͱબ  .Z42-$BTVBM5BMLTJO'VLVPLB7PM 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@*%ACJHJOU  /05/6--"650@*/$3&.&/5  A130+&$5@$0%&ACJHJOU  /05/6--

     A$6450.&3@*%ACJHJOU  /05/6--  A$0..&/5AWBSDIBS  $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@UBCMF4&5$0..&/5GVHB8)&3&130+&$5@$0%& "/%$6450.&3@*% 2VFSZ0, SPXTB⒎FDUFE TFD 

    3PXTNBUDIFE$IBOHFE8BSOJOHT 6QEBUFͯ݅ͩͬͨ͠৔߹͸*OTFSU  */4&35*/50VTFS@UBCMF 130+&$5@$0%& $6450.&3@*% $0..&/5  7"-6&4   GVHB   $0..*5
  9. 6QEBUF͕ۭৼΔͱΪϟοϓϩοΫΛऔΔ 59  #&(*/  61%"5&VTFS@UBCMF4&5$0..&/5GVHB8)&3&130+&$5@$0%&"/%$6450.&3@*% 2VFSZ0, SPXTB⒎FDUFE TFD 

    3PXTNBUDIFE$IBOHFE8BSOJOHT 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@UBCMF4&5$0..&/5GVHB8)&3&130+&$5@$0%&"/%$6450.&3@*%  2VFSZ0, SPXTB⒎FDUFE

    TFD  3PXTNBUDIFE$IBOHFE8BSOJOHT 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. εϨουຖͷ࣮ফඅϝϞϦྔΛܭଌ NZTRMTFMFDU GSPNTZTNFNPSZ@CZ@UISFBE@CZ@DVSSFOU@CZUFT       

     cUISFBE@JEcVTFScDVSSFOU@DPVOU@VTFEcDVSSFOU@BMMPDBUFEcDVSSFOU@BWH@BMMPDcDVSSFOU@NBY@BMMPDcUPUBM@BMMPDBUFEc         ccIPOEB!YYYYYYYYYcc.J#c,J#c.J#c.J#c ccIPOEB!YYYYYYYYYcc,J#c,J#c,J#c.J#c ccSETBENJO!MPDBMIPTUcc,J#cCZUFTc,J#c.J#c ccTRMTJHOBM@IBOEMFSccCZUFTcCZUFTcCZUFTcCZUFTc ccTRMDPNQSFTT@HUJE@UBCMFccCZUFTcCZUFTcCZUFTcCZUFTc ccTRMNBJOccCZUFTcCZUFTcCZUFTcCZUFTc ccTRMUISFBE@UJNFS@OPUJpFSccCZUFTcCZUFTcCZUFTcCZUFTc ccJOOPECJP@XSJUF@UISFBEccCZUFTcCZUFTcCZUFTcCZUFTc 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. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ