Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Takayuki Honda
August 09, 2018
1.6k

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

Takayuki Honda

August 09, 2018
Tweet

Transcript

  1. αϯϓϧςʔϒϧ $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&ɹ,&:
  2. αϯϓϧσʔλ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF 

      IPHF    IPHF    IPHF
  3. ͜͜ʹσʔλΛೖΕ͍ͨ 64&3@*% 130+&$5@$0%& $6450.&3@*% $0..&/5    IPHF 

      IPHF    GVHB    IPHF    IPHF
  4. ࢓༷  #&(*/  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
  5. 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" 
  6. ผͷΫΤϦ͕ಉ͡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 | +————+----------------+-----------------------+-----------+----------+------------- +-----------------------------+-----------+-----------+-------------+-------------+ ˞ΧϥϜҰ෦ུ
  7. σʔλ͕ແ͍ͷͰ*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)
  8. ਤղ 130+&$5@$0%& $6450.&3@*%     ˣ ˣ 

       ᶃVQEBUF ᶄVQEBUF 5Y 5Y ᶅJOTFSU ᶆJOTFSU ᶇ☠%FBE-PDL
  9. ظ଴ͷ৽੕*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/ ʙ
  10. ͕͢͞ʹ.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; ɻɻɻɻ
  11. KPJO@CV⒎FS@TJ[F w ΠϯσοΫεΛ࢖༻͠ͳ͍+0*/Ͱ࢖༻͞ΕΔɻ w σϑΥϧτ஋,# Ҏ߱  w ઃఆ஋͕.#ͩͬͨ w

    ࢀߟ
 IUUQTXXXQFSDPOBDPNCMPH IPXJTKPJO@CV⒎FS@TJ[FBMMPDBUFE
  12. εϨουຖͷ࣮ফඅϝϞϦྔΛܭଌ 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 | |