あなたの知らない データベースのロギングの世界 / logging queries

builderscon tokyo 2018 の発表資料です。
https://builderscon.io/tokyo/2018/session/87e13506-2f80-4fae-af9c-2421c7dbb460

※発表後に分かったこと、教えていただいたことにより、発表時の資料から若干の変更を加えています

Transcript

1. Ԟଜߊ߂(.01FQBCP *OD
   ೔
   CVJMEFSTDPO
   ͋ͳͨͷ஌Βͳ͍

   σʔλϕʔεͷϩΪϯάͷੈք
   

   View Slide

2. γχΞΤϯδχΞ
   Ԟଜߊ߂!IGN
   ٕज़෦ϓϥοτϑΥʔϜάϧʔϓ
   

   View Slide

3. !UONU͞ΜͷηογϣϯʹͰٕͨज़తରࡦͷͭ
   ʢ%#ϩΪϯάʣʹ͍͓ͭͯ࿩͠·͢
   IUUQTTQFBLFSEFDLDPNUONUXIFSFEPXFTUBSUXJUIJOGPSNBUJPOTFDVSJUZ
   

   View Slide

4. wͳͥ؂͕ࠪॏཁ͔
   w؂ࠪϩάʹٻΊΔ৘ใ
   w؂ࠪϩάΛऔΔํ๏
   w1SPYZ42-Λհͨ͠ߏ੒
   w1SPYZ42-ͷϋϚΓͲ͜Ζ
   ຊ೔ͷςʔϚ
   ͔͜͜Β.Z42-த৺
   ͜ͷล͔Β1SPYZ42-ͷ࿩
   

   View Slide

5. ͳͥ؂͕ࠪॏཁ͔
   

   View Slide

6. wʮΧϥʔϛʔγϣοϓʯʹ͓͚Δ৘ใྲྀग़ʹؔ
   ͢Δ͝ใࠂͱ͓࿳ͼ
   w IUUQTQEGQFQBCPDPNEPDVNFOUEQEG
   w IUUQTQEGQFQBCPDPNEPDVNFOUEQEG
   ൃදܦҢ
   

   View Slide

7. ͳͥ؂͕ࠪॏཁ͔
   ὎ͳʹ͔ى͖͔ͯΒ͡Ό஗͍
   

   View Slide

8. ௥੻ՄೳੑʢτϨʔαϏϦςΟʣͷ௿Լ
   ݕ஌ͷ೉қ౓্ঢ
   ճ෮ྗʢ෮چɺࣄۀܧଓʣͷ௿Լ
   ؂ࠪূ੻ͷෆ଍͕ট͘ϦεΫ
   

   View Slide

9. Πϯγσϯτͷ࣌ܥྻ
   ೔࣌ ಺༰
   2018/01/07 (೔) 20:04 ݕ஌
   2018/01/07 (೔) 22:28 Ұ࣍ରԠ׬ྃ
   2018/01/08 (݄) 01:33 ӨڹൣғҰ෦ൃ֮
   2018/01/10 (ਫ) 13:34 Өڹൣғ௥Ճൃ֮
   2018/01/10 (ਫ) 15:35 ୈࡾऀػؔʹΑΔௐࠪ։࢝
   2018/01/25 (໦) 11:58 ୈࡾऀػؔʹΑΔௐࠪ׬ྃ
   2018/01/26 (ۚ) 07:40 ৘ใެ։
   Өڹൣғͷ೺Ѳʹ͕͔͔࣌ؒΓɺ৘ใެ։ͷ஗Εʹܨ͕Δ
   

   View Slide

10. ʮ཈ࢭɾ཈੍ʯ
    ʮ༧๷ɾ๷ࢭʯ
    ʮݕ஌ɾ௥੻ʯ
    ʮճ෮ɾܧଓʯ
    ৘ใηΩϡϦςΟରࡦͷڧԽ
    

    View Slide

11. ʮ཈ࢭɾ཈੍ʯ
    ʮ༧๷ɾ๷ࢭʯ
    ʮݕ஌ɾ௥੻ʯˡຊηογϣϯͷςʔϚ
    ʮճ෮ɾܧଓʯ
    ৘ใηΩϡϦςΟରࡦͷڧԽ
    

    View Slide

12. wઌͷΠϯγσϯτͰ͸ɺෆਖ਼ϓϩάϥϜʹΑΔ
    ͷσʔλϕʔε .Z42-
    ΞΫηε͕ൃ֮
    wʮ͍ͭɺ୭͕ɺͲ͜ʹɺͲͷΑ͏ͳΫΤϦΛൃ
    ߦͨ͠ͷ͔ʯΛه࿥͠ɺ࠶ൃ๷ࢭʹ໾ཱͯΔ
    ؂ࠪϩάͷڧԽ
    

    View Slide

13. ؂ࠪϩάʹٻΊΔ৘ใ
    

    View Slide

14. σʔλϕʔε΁ͷΞΫηε৘ใͳͲΛه࿥͠ɺͲ
    ͷΑ͏ͳૢ࡞͕ߦΘΕͨͷ͔Λɺ͋ͱ͔ΒͰ΋
    ֬ೝͰ͖ΔΑ͏ʹอଘͨ͠΋ͷ
    σʔλϕʔεͷ؂ࠪϩάͱ͸
    

    View Slide

15. w42-εςʔτϝϯτʢΫΤϦʣ
    wΫϥΠΞϯτ*1
    wΫϥΠΞϯτϢʔβ
    w઀ଓσʔλϕʔε໊
    w઀ଓઌϗετ໊
    ؂ࠪϩάʹඞཁͳ৘ใྫ
    

    View Slide

16. w%#ʹྲྀΕΔ͢΂ͯͷΫΤϦΛه࿥͍ͨ͠
    w࣮ߦ͞ΕΑ͏ͱࣦͨ͠ഊΫΤϦ౳΋ؚ·ΕΔ
    ؂ࠪϩάʹඞཁͳཁ݅
    "VEJU
    %#
    DMJFOU
    %#
    DMJFOU
    

    View Slide

17. ؂ࠪϩάΛऔΔํ๏
    

    View Slide

18. .Z42-ͷઃఆ
    wHFOFSBM@MPH
    ϓϥάΠϯ
    wNZTRMBVEJU NDBGFF
    
    w.BSJB%#"VEJU1MVHJO
    %#ϓϩΩγ
    w1SPYZ42-
    w.BSJB%#.BY4DBMF
    ͍Ζ͍ΖͳΫΤϦϩάͷऔಘํ๏
    

    View Slide

19. Ұ൪खܰͳํ๏ɻ%#ɺϢʔβɺϗετɺΫΤϦ
    ϩά͕547ܗࣜͰه࿥͞Ε͍ͯΔɻ
    HFOFSBM@MPH
    2018-09-07T16:39:50.949045Z 8 Connect
    root@localhost on using Socket
    2018-09-07T16:39:50.949471Z 8 Query select
    @@version_comment limit 1
    2018-09-07T16:40:13.059736Z 8 Query SELECT *
    FROM test.t
    2018-09-07T16:40:13.762332Z 8 Quit
    

    View Slide

20. w%#΁ͷΞΫηε͕ܹ͘͠ͳΔͱϩάͷॻ͖ࠐ
    Έෛՙ͕όΧʹͳΒͳ͘ͳͬͯ͘Δ
    wҰݟ͓खܰͳํ๏͕ͩɺσʔλϕʔεͷσΟε
    Ϋ*0ʹӨڹΛٴͿ఺ʹ஫ҙ
    HFOFSBM@MPHӡ༻্ͷݒ೦఺
    

    View Slide

21. wࣦഊͨ͠ΫΤϦ͕ه࿥͞Εͳ͍
    wෆਖ਼ͳΫΤϦ͸ඞ࣮ͣߦ͞Ε͏Δ͔ʁ
    w؂ࠪϩά͸ɺෆਖ਼ͳΫΤϦ΋ؚΊͯɺ%#Ͱ࣮
    ߦ͞ΕΑ͏ͱͨ͢͠΂ͯͷΫΤϦΛه࿥͍ͨ͠
    ͷͰཁ݅Λຬͨͤͳ͍
    HFOFSBM@MPH؂ࠪͱͯ͠ͷܽ఺
    

    View Slide

22. 1PTUHSF42-Ͱ͸MPH@TUBUFNFOUBMMͱ
    MPH@MJOF@QSFpYbUVIEJF`ͷΑ
    ͏ʹ͢Ε͹ه࿥Մೳͱͷ͜ͱ
    ͪΐͬͱԣಓ1PTUHSF42-
    2018-09-10 01:14:05 UTC  postgres  172.20.0.1 
    testdb  idle  00000LOG: statement: SHOW
    server_version
    2018-09-10 01:14:08 UTC  postgres  172.20.0.1 
    testdb  idle  00000LOG: statement: SELECT *
    FROM information_schema.tables;
    

    View Slide

23. wIUUQTHJUIVCDPNNDBGFFNZTRMBVEJU
    w.Z42-αʔόϓϥάΠϯΛར༻ͨ͠؂ࠪϩά
    wMJCBVEJU@QMVHJOTPΛVTSMJC
    NZTRM
    QMVHJOҎԼʹ഑ஔ͢Δͱར༻Ͱ͖Δ
    NDBGFFNZTRMBVEJU
    

    View Slide

24. curl -L 'https://bintray.com/mcafee/mysql-audit-
    plugin/download_file?file_path=audit-plugin-
    mysql-5.7-1.1.6-784-linux-x86_64.zip' -o audit-
    plugin-mysql.zip
    unzip audit-plugin-mysql.zip
    cp audit-plugin-mysql-5.7-1.1.6-784/lib/
    libaudit_plugin.so /usr/lib/mysql/plugin/
    NZTRMBVEJU४උ
    

    View Slide

25. $ bash ./audit-plugin-mysql-5.7-1.1.6-784/utils/
    offset-extract.sh /usr/sbin/mysqld
    //offsets for: /usr/sbin/mysqld (5.7.23)
    {"5.7.23","f5943a4e0d10ed18befd36bb32aed8bc",
    6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32,
    104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0,
    0, 6360, 6384, 6368},
    NZTRMBVEJUΦϑηοτͷܭࢉ
    

    View Slide

26. --- my.cnf
    +++ /etc/my.cnf
    @@ -33,3 +33,7 @@
    innodb_log_buffer_size=32M
    innodb_log_file_size=128M
    innodb_autoextend_increment=64
    +
    +plugin-load=AUDIT=libaudit_plugin.so
    +audit_offsets=6992, 7040, 4000, 4520, 72, 2704,
    96, 0, 32, 104, 136, 7128, 4392, 2800, 2808,
    2812, 536, 0, 0, 6360, 6384, 6368
    +audit_json_file=on
    NZTRMBVEJUNZDOGͷઃఆ
    

    View Slide

27. {
    "msg-type": "activity",
    "date": "1501591974035",
    "thread-id": "1",
    "query-id": "8508",
    "user": "",
    "priv_user": "",
    "ip": "",
    "host": "",
    "cmd": "create_db",
    "query": "CREATE DATABASE IF NOT EXISTS infra"
    }
    NZTRMBVEJUϩά΁ͷग़ྗ
    WBSMJCNZTRMNZTRMBVEJUKTPO Θ͔Γ΍͍͢Α͏੔ܗͯ͠·͢
    
    

    View Slide

28. wHFOFSBM@MPHಉ༷ɺϩάͷॻ͖ࠐΈෛՙ
    wΦϑηοτͷܭࢉ͕ΊΜͲ͍͘͞ɻP⒎TFU
    FYUSBDUTIίϚϯυͷ࣮ߦʹHEC NETVN
    NZTRMEͷσόοά༻TZNCPMT͕ཁΔ
    NDBGFFNZTRMBVEJUͷݒ೦఺
    

    View Slide

29. wIUUQTNBSJBECDPNLCFONBSJBEC
    BCPVUUIFNBSJBECBVEJUQMVHJO
    wNZTRMBVEJUͱಉ͡Α͏ʹTFSWFS@BVEJUTPΛ
    VTSMJC
    NZTRMQMVHJOҎԼʹ഑ஔ͢Δͱ
    ར༻Ͱ͖Δ
    .BSJB%#"VEJU1MVHJO
    

    View Slide

30. INSTALL PLUGIN server_audit SONAME
    'server_audit.so';
    SET GLOBAL server_audit_logging = 'ON';
    .BSJB%#"VEJU1MVHJO४උ
    

    View Slide

31. 20170807 13:56:47,example.com,root,localhost,
    151722,0,CONNECT,,,0
    20170807 13:56:47,example.com,root,localhost,
    151722,73837,QUERY,,'select @@version_comment
    limit 1',0
    20170807 13:56:47,example.com,root,localhost,
    151722,73838,QUERY,,'select @@hostname',0
    20170807 13:56:47,example.com,root,localhost,
    151722,0,DISCONNECT,,,0
    .BSJB%#"VEJU1MVHJOϩά
    WBSMJCNZTRMTFSWFS@BVEJUMPH
    

    View Slide

32. wHFOFSBM@MPHಉ༷ɺϩάͷॻ͖ࠐΈෛՙ
    w.Z42-͸ࠓޙαϙʔτ͞ΕΔͷͰ͠ΐ͏͔
    ʢܥͷϓϥάΠϯ͸ແ͔ͬͨʣ
    .BSJB%#"VEJU1MVHJOͷݒ೦఺
    

    View Slide

33. )JHIQFSGPSNBODF
    .Z42-QSPYZ
    IUUQTQSPYZTRMDPN
    ΫΤϦͷϩΪϯάҎ֎ʹ΋
    ΫΤϦΩϟογϡɺϧʔςΟ
    ϯάɺϑΝΠΞ΢Υʔϧͳ
    Ͳ৭ʑͱػೳ͕͋Δ
    1SPYZ42-
    

    View Slide

34. .Z42-ͱΫϥΠΞϯτͷؒʹઃஔ͠ɺΫΤϦ
    Λ஥հ͢Δ
    1SPYZ42-
    1SPYZ42-
    %#
    DMJFOU
    %#
    DMJFOU
    

    View Slide

35. 1SPYZ42-Λհͨ͠ߏ੒
    

    View Slide

36. $ cat -A log/queries.log.00000018
    mysql:3306M-~M-`sM-YM-^PKu^E^@M-~M-~uM-YM-
    ^PKu^E^@M-~)JM-^ETaM-^Ly~^Hhello!!1$
    1SPYZ42-ͷϩά͸όΠφϦ
    IFMMPͱ͍͏ΫΤϦΛ౤༷͛ͨࢠ
    

    View Slide

37. wIUUQTHJUIVCDPNUBLBJTIJqVFOUQMVHJO
    QSPYZTRMRVFSZMPH
    w
    1SPYZ42-ͷϩά͸όΠφϦ
    

    View Slide

38. wຊ౰͸͜ͷ࿩Λ͔ͨͬͨ͠
    w1SPYZ42-ͷམͱ݀͠ʹϋϚΓ·ͬͯͨ͘ͲΓ
    ண͚ͳ͔ͬͨͷͰɺͦͷ࿩Λ͠·͢
    w1SPYZ42-ͷϋϚΓͲ͜Ζͱͯ͠঺հ͠·͕͢ɺ
    ʮΫϥΠΞϯτͱσʔλϕʔεʹதؒ૚ΛڬΉʯ
    ͱ͍͏ߏ੒ΛऔΔͱ͖ʹؾʹ͓͖͍ͯͨ͠ͱ͜
    ΖͰ΋͋Γ·͢
    Մ༻ੑΛଛͶͳ͍ΞʔΩςΫνϟ
    

    View Slide

39. 1SPYZ42-Λհͨ͠ߏ੒
    ͱʹ͔͘΍ΔΜͩύλʔϯ
    server A
    server B
    server C
    ProxySQL
    ProxySQL
    ProxySQL
    DB
    app
    app
    app
    0.0.0.0:3306
    0.0.0.0:3306
    0.0.0.0:3306
    

    View Slide

40. 1SPYZ42-Λհͨ͠ߏ੒
    Consul watch
    ProxySQL Cluster
    Client
    Client
    Client
    ProxySQL
    ProxySQL
    ProxySQL
    DB
    db-proxy.service.foo.consul
    (via Consul DNS Interface)
    ཧ૝ڷ
    

    View Slide

41. 1SPYZ42-ͷϋϚΓͲ͜Ζ
    ΫϥΠΞϯτͱσʔλϕʔεʹதؒ૚ΛڬΉߏ੒Λ
    औΔͱ͖ʹؾʹ͓͖͍ͯͨ͠ͱ͜ΖͰ΋͋Γ·͢
    

    View Slide

42. Ϣʔβ؅ཧ໰୊
    

    View Slide

43. w 1SPYZ42-͸όοΫΤϯυͷ%#ʹͭͳ͙ΞΧ΢ϯτ৘ใ
    Λࣗ෼Ͱอ͍࣋ͯ͠Δ
    w %#ͷNZTRMVTFSͱ1SPYZ42-ͷNBJONZTRM@VTFST
    ͕ζϨΔͱ໘౗
    Ϣʔβ؅ཧ໰୊
    ProxySQL (main.mysql_users)
    Client
    DB
    Connect as hello
    (using *668425423DB5…)
    *DB5193AF9213…
    con
    builders *123456788909…
    hello *668425423DB5…
    password
    username
    Client
    Client
    Connect as builders
    (using *12345678909…)
    Connect as con
    (using *12345678909…)
    ⭕
    ⭕
    ❌
    *DB5193AF9213…
    con
    bbuilders *8425423DB519…
    hello *668425423DB5…
    password
    username
    ⭕
    ❌
    

    View Slide

44. DIBSTFU໰୊
    

    View Slide

45. w1SPYZ42-͸TLJQDIBSBDUFSTFUDMJFOU
    IBOETIBLFΛαϙʔτͯ͠ͳ͍
    w1SPYZ42-ͷEFGBVMUDIBSTFUͱ͍͏֓೦΋
    ՃΘΓɺ.Z42-ͷDIBSTFUͱDMJFOUͷ
    DIBSTFUͱࡾ໾ἧ͍౿Έ
    w݁ՌɺVKJTͱ͔͋ΔͱจࣈԽ͚͢Δ
    DIBSTFU໰୊
    

    View Slide

46. wͤɺ4&5/".&4
    wجຊతʹ͸ΫϥΠΞϯτͰؤுΔ͔͠ͳͦ͞͏
    w1)1ͳΒNZTRM J
    @TFU@DIBSTFUؔ਺Λ࢖͏౳
    DIBSTFU໰୊ΛͳΜͱ͔͢Δ
    

    View Slide

47. .VMUJQMFYJOH໰୊
    

    View Slide

48. 1SPYZ42-͸ඪ४Ͱ௨৴ͷଟॏԽʢෳ਺ΫϥΠΞϯτ͔Β
    ͷ઀ଓΛ·ͱΊΔʣػೳΛఏڙ͍ͯ͠Δ
    1SPYZ42-.VMUJQMFYJOH
    ProxySQL
    Application
    A
    DB
    Client
    Client
    Client
    Client
    multiplexing
    client backend
    proxy
    

    View Slide

49. -"45@*/4&35@*%ؔ਺ͷΑ͏ͳʮηογϣϯதͷ࠷ۙͷ
    */4&35݁ՌʯΛݟΔΑ͏ͳ΋ͷͱ૬ੑ͕ѱ͍
    1SPYZ42-.VMUJQMFYJOH
    ProxySQL
    Application
    A
    DB
    Client
    Client
    Client
    Client
    multiplexing
    client backend
    proxy
    

    View Slide

50. mysql> SHOW CREATE TABLE t\G
    ******************** 1. row ********************
    Table: t
    Create Table: CREATE TABLE `t` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(10) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4_0900_ai_ci
    -"45@*/4&35@*%
    

    View Slide

51. mysql> INSERT INTO t VALUES (NULL, 'hfm');
    mysql> SELECT * FROM t;
    +----+------+
    | id | name |
    +----+------+
    | 1 | hfm |
    +----+------+
    mysql> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    | 1 |
    +------------------+
    -"45@*/4&35@*%
    

    View Slide

52. mysql> INSERT INTO t (name) VALUES ('builders'),
    ('con'), ('yeah!!');
    mysql> SELECT * FROM t;
    +----+----------+
    | id | name |
    +----+----------+
    | 1 | hfm |
    | 2 | builders |
    | 3 | con |
    | 4 | yeah!! |
    +----+----------+
    mysql> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    | 2 |
    +------------------+
    -"45@*/4&35@*%
    

    View Slide

53. ੜ੒͞Εͨ*%͸ɺ઀ଓ͝ͱʹαʔόʔ಺ʹอ࣋͞Ε·͢ɻ
    ͭ·Γɺؔ਺ʹΑͬͯࢦఆ͞ΕͨΫϥΠΞϯτʹฦ͞Εͨ஋
    ͸ɺͦͷΫϥΠΞϯτʹΑͬͯ"650@*/$3&.&/5Χϥ
    ϜʹӨڹΛ༩͑Δ࠷ۙͷεςʔτϝϯτ༻ʹ࠷ॳʹੜ੒͞Ε
    ͨ"650@*/$3&.&/5஋Ͱ͢ɻ͜ͷ஋͸ɺ΄͔ͷΫϥΠ
    Ξϯτ͕ಠࣗͷ"650@*/$3&.&/5஋Λੜ੒ͨ͠৔߹Ͱ
    ΋ӨڹΛड͚ΔՄೳੑ͸͋Γ·ͤΜɻ͜ͷಈ࡞ʹΑͬͯɺ֤
    ΫϥΠΞϯτ͸΄͔ͷΫϥΠΞϯτͷΞΫςΟϏςΟʔΛؾ
    ʹ͢Δ͜ͱͳ͘ɺϩοΫ΍τϥϯβΫγϣϯΛ࣮ߦ͠ͳ͍Ͱ
    ಠࣗͷ*%ΛऔಘͰ͖·͢ɻ
    -"45@*/4&35@*%
    
    IUUQTEFWNZTRMDPNEPDSFGNBOKBJOGPSNBUJPOGVODUJPOTIUNMGVODUJPO@MBTUJOTFSUJE
    

    View Slide

54. ੜ੒͞Εͨ*%͸ɺ઀ଓ͝ͱʹαʔόʔ಺ʹอ࣋͞Ε·͢ɻ
    ͭ·Γɺؔ਺ʹΑͬͯࢦఆ͞ΕͨΫϥΠΞϯτʹฦ͞Εͨ஋
    ͸ɺͦͷΫϥΠΞϯτʹΑͬͯ"650@*/$3&.&/5Χϥ
    ϜʹӨڹΛ༩͑Δ࠷ۙͷεςʔτϝϯτ༻ʹ࠷ॳʹੜ੒͞Ε
    ͨ"650@*/$3&.&/5஋Ͱ͢ɻ͜ͷ஋͸ɺ΄͔ͷΫϥΠ
    Ξϯτ͕ಠࣗͷ"650@*/$3&.&/5஋Λੜ੒ͨ͠৔߹Ͱ
    ΋ӨڹΛड͚ΔՄೳੑ͸͋Γ·ͤΜɻ͜ͷಈ࡞ʹΑͬͯɺ֤
    ΫϥΠΞϯτ͸΄͔ͷΫϥΠΞϯτͷΞΫςΟϏςΟʔΛؾ
    ʹ͢Δ͜ͱͳ͘ɺϩοΫ΍τϥϯβΫγϣϯΛ࣮ߦ͠ͳ͍Ͱ
    ಠࣗͷ*%ΛऔಘͰ͖·͢ɻ
    -"45@*/4&35@*%
    
    IUUQTEFWNZTRMDPNEPDSFGNBOKBJOGPSNBUJPOGVODUJPOTIUNMGVODUJPO@MBTUJOTFSUJE
    

    View Slide

55. 1SPYZ42-.VMUJQMFYJOH
    ProxySQL
    Client
    Client
    Client
    INSERT
    INSERT
    INSERT
    SELECT LAST_INSERT_ID()
    

    View Slide

56. 1SPYZ42-.VMUJQMFYJOH
    ProxySQL
    Client
    Client
    Client
    INSERT
    INSERT
    INSERT
    SELECT LAST_INSERT_ID()
    ProxySQL DB
    INSERT
    INSERT
    INSERT
    SELECT LAST_INSERT_ID()
    

    View Slide

57. 1SPYZ42-.VMUJQMFYJOH
    ProxySQL
    Client
    Client
    Client
    INSERT
    INSERT
    INSERT
    SELECT LAST_INSERT_ID()
    ProxySQL DB
    INSERT
    INSERT
    INSERT
    SELECT LAST_INSERT_ID()
    ͜ͷ*/4&35
    Ͳͷ$MJFOUग़਎
    

    View Slide

58. w.VMUJQMFYJOHͷੈքʹ͓͍ͯΫΤϦ͸ࠞ͟Δ
    wಉ࣌ʹଟ਺ͷΞΫηεͰ༰қʹࠞ͟Δ
    1SPYZ42-.VMUJQMFYJOHͷ൓ল
    

    View Slide

59. w.VMUJQMFYJOHͷੈքʹ͓͍ͯΫΤϦ͸ࠞ͟Δ
    wಉ࣌ʹଟ਺ͷΞΫηεͰ༰қʹࠞ͟Δ
    w-"45@*/4&35@*%͕ର৅ͱ͸ผςʔϒϧͷ*%
    ͱ͔औ͖ͬͯͪΌ͏
    wJEͰ͋Δ΂͖ͱ͜Ζ͕ʹ
    ͳ͍ͬͯͨΈ͍ͨͳใࠂΛ΋Βͬͯٽ͘
    1SPYZ42-.VMUJQMFYJOHͷ൓ল
    

    View Slide

60. wࣾ಺ͷΠϯςάϨʔγϣϯ؀ڥ΍εςʔδϯά
    ؀ڥͰݕূͨ͠ͱ͖͸ൃݟͰ͖ͳ͔ͬͨ
    wձࣾͷਓ͔͠৮Βͳ͍؀ڥʹ௿ΞΫηεස౓
    w࠶ݱͰ͖ͳ͍ੋඇ΋ͳ͍
    -"45@*/4&35@*%໰୊ͷࠔ೉
    

    View Slide

61. wNZTRMTMBQͰେྔͷฒྻΞΫηεΛߦͬͨ
    wฒྻ౓Λ্͛ΔͱίωΫγϣϯ͕ࠞ͟Γ࢝Ίͨ
    w.VMUJQMFYJOHػೳΛΦϑʹͨ͠Β࣏ͬͨ
    5IBOLT!L-P8
    
    -"45@*/4&35@*%໰୊ͷ࠶ݱ
    

    View Slide

62. CREATE TABLE a (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(10),
    updated BOOLEAN DEFAULT false,
    PRIMARY KEY (id)
    );
    NZTRMTMBQ
    INSERT INTO mysqlslap.a (name) VALUES ('hello');
    SELECT * FROM mysqlslap.a;
    ...
    SELECT * FROM mysqlslap.a;
    UPDATE a SET updated = true
    WHERE id = (SELECT LAST_INSERT_ID());
    create.sql
    query.sql
    

    View Slide

63. mysqlslap \
    --host=proxysql_url \
    --user=user \
    --password=******** \
    --delimiter=';' \
    --create=create.sql \
    --query=query.sql \
    --concurrency=100 \
    --iteration=1 \
    --no-drop
    NZTRMTMBQͷίϚϯυྫ
    

    View Slide

64. NZTRMTMBQͷJUFSBUJPOΛ࢖͏
    ͱɺ1SPYZ42-Ͱl/PEBUBCBTF
    TFMFDUFEzΤϥʔʹͳͬͯࢮ͵ɻ
    JUFSBUJPO͔͠ಈ͔ͳ͍
    JUFSBUJPOͱ1SPYZ42-૬ੑ໰୊
    

    View Slide

65. ؂ࠪϩάͷ׆༻ํ๏
    ʢࠓޙͷల๬ʣ
    

    View Slide

66. wେৎ෉ͳΞΫηεͱ໰୊͋ΔΞΫηεͷफ़ผ
    wͭ·Γෆਖ਼ΫΤϦͷϒϩοΩϯά΍௨ใ
    ؂ࠪϩάͷ׆༻
    

    View Slide

67. wෆਖ਼ͷఆٛ͸αʔϏε࣍ୈɺܾΊͷ෦෼΋͋Δ
    wྫ͑͹8)&3&۟ͷແ͍4&-&$5ΫΤϦ౳
    wͨͩ͠ɺಠࣗఆٛ͢Δʹ͸ਓखʹݶք͕͋Δ
    ෆਖ਼ΫΤϦͱ͸
    

    View Slide

68. ෆਖ਼ͳσʔλΞΫηε༧๷ͷݚڀ
    IUUQTSBOEQFQBCPDPNQBQFSTJPUQSPDFFEJOHLPNFJQEG
    

    View Slide

69. w ͳʹ͔ى͖͔ͯΒͰ͸஗͍ͷͰϩά͸͔ͬ͠ΓऔΔ
    w ΫϥΠΞϯτͱ%#ͷؒʹ૚ΛڬΉͱมԽ͕େ͖͍
    w ॿ͚ͯ͘Ε
    w ਐḿ͋Ε͹ϒϩάͳͲͰใࠂ͠·͢
    ·ͱΊ
    

    View Slide