$30 off During Our Annual Pro Sale. View Details »

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

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

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

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

Okumura Takahiro

September 11, 2018
Tweet

More Decks by Okumura Takahiro

Other Decks in Technology

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