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

PostgreSQL11 設定パラメーター解体新書 / PostgreSQL 11 parameter

ester41
January 26, 2019

PostgreSQL11 設定パラメーター解体新書 / PostgreSQL 11 parameter

OSC 2019 Osakaの登壇資料です。

ester41

January 26, 2019
Tweet

More Decks by ester41

Other Decks in Technology

Transcript

  1. PostgreSQL11 ઃఆύϥϝʔλʔ
    ղମ৽ॻ
    ΦʔϓϯιʔεΧϯϑΝϨϯε
    2019 Osaka

    View full-size slide

  2. ͸͡Ίʹ
    ຊηογϣϯͰ͸ɺ࠷৽όʔδϣϯͰ͋Δ

    PostgreSQL11ͷίϯϑΟάϑΝΠϧͷղઆΛߦ͍·͢ɻ
    ύϥϝʔλʔ͸શ෦Ͱ໿250߲໨ଘࡏ͢ΔͨΊɺ

    ஌ͬͯ΋Β͍͍ͨ115߲໨Λର৅ͱ͍ͯ͠·͢ɻ

    View full-size slide

  3. ͸͡Ίʹ
    ຊηογϣϯ࡞੒ʹ͋ͨΓɺҎԼͷॻ੶Λࢀߟʹ͍ͯ͠·͢ɻ

    View full-size slide

  4. ΞδΣϯμ
    ࣗݾ঺հ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    ઀ଓઃఆɾػೳઃఆ
    ϨϓϦέʔγϣϯ
    ؂ࢹؔ܎
    νϡʔχϯά
    ऴΘΓʹ

    View full-size slide

  5. ࣗݾ঺հ
    ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ)
    ॴଐ: ೔ຊPostgreSQLϢʔβձ

    ؔ੢ࢧ෦௕
    Twitter/GitHub: @ester41
    ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ

    View full-size slide

  6. ͜ͷηογϣϯͰ͸ɺPostgreSQLͷઃఆϑΝΠϧͰ͋Δɺ
    postgresql.confͷઃఆʹ͍ͭͯղઆ͠·͢ɻ
    PostgreSQLͷ಺෦ߏ଄
    (σʔλϕʔεߏ଄)
    postgresϓϩηε
    σʔλϕʔεΫϥελ
    ઃఆϑΝΠϧ
    ɾpostgresql.conf
    ɾpg_hba.conf
    ͳͲ
    σʔλϕʔε
    εΩʔϚ
    Ϣʔβʔ
    ɾpostgres
    ͳͲ
    ςʔϒϧ
    ͳͲ

    View full-size slide

  7. PostgreSQLͷ಺෦ߏ଄
    (ϓϩηεߏ੒)
    postgres
    (όοΫΤϯυϓϩηε)
    postgres
    (ϥΠλʔ)
    postgres
    (νΣοΫϙΠϯλʔ)
    postgres
    (౷ܭ৘ใίϨΫλʔ)
    postgres
    (ࣗಈόΩϡʔϜϥϯνϟʔ)
    postgres
    (ࣗಈόΩϡʔϜϫʔΧʔ)
    postgres
    (WALϥΠλʔ)
    postgres
    (Ϛελʔαʔόʔϓϩηε)
    WALόοϑΝʔ
    WALϑΝΠϧ
    σʔλϑΝΠϧ
    ڞ༗όοϑΝʔ
    ౷ܭ৘ใ
    ىಈ࣌ʹϓϩηεΛϑΥʔΫ
    ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ

    View full-size slide

  8. PostgreSQLͷ಺෦ߏ଄
    (ϝϞϦʔߏ੒)
    ϓϩηεϝϞϦʔ
    όοΫΤϯυϓϩηε
    Ұ࣌όοϑΝʔ
    (temp_buffers)
    ࡞ۀϝϞϦʔ
    (work_mem)
    ϝϯςφϯε༻࡞ۀϝϞϦʔ
    (maintenance_work_mem)
    ڞ༗ϝϞϦʔ
    WALόοϑΝʔ
    (wal_buffers)
    ڞ༗όοϑΝʔ
    (shard_buffers)
    σʔλϑΝΠϧ
    WALϑΝΠϧ
    WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε
    ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ
    ϥΠλʔ/
    νΣοΫϙΠϯλʔϓϩηε

    View full-size slide

  9. postgresql.confʹ͍ͭͯ(1/3)
    postgresql.conf͸ɺPostgreSQLͷઃఆϑΝΠϧͱͳΓ·͢ɻ
    postgresql.confͱpostgresql.auto.confͷ̎छྨ͕ଘࡏ͠·͢ɻ
    ͸͡Ίʹpostgresql.conf͕ಡΈࠐ·Εͨޙʹ
    postgresql.auto.conf͕ಡΈࠐ·Ε·͢ɻ
    postgresql.auto.conf͸ɺϑΝΠϧࣗମΛ௚઀৮Δ͜ͱ͸ग़དྷ·ͤΜɻ
    ALTER SYSTEMจͰมߋͨ͠৔߹ʹॻ͖ࠐΈ͞Ε·͢ɻ
    ྫ) ALTER SYSTEM SET wal_level = hot_standby;
    ·ͨɺSETจͰҰ࣌มߋͰ͖Δύϥϝʔλʔ΋ଘࡏ͠·͢ɻ
    ྫ) SET enable_seqscan = off;

    View full-size slide

  10. postgresql.confʹ͍ͭͯ(2/3)
    ઃఆ஋͸ɺҎԼͷSQLͰ֬ೝ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    SELECT name, setting, context FROM pg_settings;
    ग़ྗ஋͸ҎԼͷ௨ΓͱͳΓ·͢ɻ
    name ύϥϝʔλʔ໊
    setting ݱࡏͷઃఆ஋
    content
    ύϥϝʔλʔ͕༗ޮͱͳΔλΠϛϯά
    ɾinternal: ઃఆ஋Λมߋ͢Δ͜ͱ͸Ͱ͖·ͤΜɻ
    ɾpostmaster: αʔόʔىಈ࣌ͷΈมߋ͕ՄೳͰ͢ɻ
    ɾsighup: αʔόʔϦϩʔυͰมߋ͕ՄೳͰ͢ɻ
    ɾsuperuser: εʔύʔϢʔβʔͷΈมߋͰ͖ଈ࣌൓ө͞Ε·͢ɻ
    ɾuser: શϢʔβʔͰมߋͰ͖ଈ࣌൓ө͞Ε·͢ɻ

    View full-size slide

  11. postgresql.confʹ͍ͭͯ(3/3)
    postgresql.confͷҰ෦ͷ
    ஋͸ɺPGTune
    ( https://pgtune.leopard.in.ua/ )
    ɹ
    Ͱαʔόʔ؀ڥʹ߹Θͤ
    ͨࢀߟ஋Λੜ੒͢Δ͜ͱ
    ͕ग़དྷ·͢ɻ

    View full-size slide

  12. ઀ଓઃఆɾػೳઃఆ(1/22)
    ઀ଓʹ࢖༻͢Δઃఆ΍ɺSQLͷղੳ࣌ʹ࢖༻͢ΔεΩϟϯઃఆͳͲΛղઆ
    ͠·͢ɻ
    ೝূ৘ใʹ͍ͭͯ͸OS΍ଞαʔόʔ͕བྷΉઃఆ͕ଟ਺ଘࡏ͢ΔͨΊղઆ͸
    ߦ͍·ͤΜɻ
    ެࣜυΩϡϝϯτΛ͝ࢀর͍ͩ͘͞ɻ
    https://www.postgresql.jp/document/current/html/runtime-config-
    connection.html

    View full-size slide

  13. ઀ଓઃఆɾػೳઃఆ(2/22)
    ύϥϝʔλʔ໊ listen_addresses
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ localhost
    ղઆ
    ઀ଓΛڐՄ͢ΔTCP/IPΞυϨεΛࢦఆ͠·͢ɻ
    “*”͸͢΂ͯͷ઀ଓΛڐՄ͠·͢ɻ
    ͜͜Ͱ͸͢΂ͯͷ઀ଓΛڐՄ͠ɺผઃఆϑΝΠϧͷ
    pg_hba.confϑΝΠϧͰࡉ͔͘ઃఆ͢Δ͜ͱΛਪ঑͠
    ·͢ɻ
    ਪ঑஋ *

    View full-size slide

  14. ઀ଓઃఆɾػೳઃఆ(3/22)
    ύϥϝʔλʔ໊ port
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 5432
    ղઆ
    ΫϥΠΞϯτ͔Βαʔόʔʹ઀ଓ͢Δࡍʹ࢖༻͢Δ
    ϙʔτ൪߸Ͱ͢ɻ
    σʔλϕʔεΫϥελͰϙʔτ൪߸͕ڞ༗͞Ε·͢ɻ
    1ͭͷαʔόʔʹෳ਺ͷσʔλϕʔεΫϥελΛ࡞੒͢
    Δࡍ͸ɺมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 5432(มߋͳ͠)

    View full-size slide

  15. ઀ଓઃఆɾػೳઃఆ(4/22)
    ύϥϝʔλʔ໊ max_connections
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 100
    ղઆ
    αʔόʔʹ઀ଓͰ͖Δ࠷େηογϣϯ਺Ͱ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ͳ͓ɺҰൠϢʔβʔ͕઀ଓͰ͖Δ਺͸ɺޙड़ͷ
    superuser_reserved_connectionsΛࠩ͠Ҿ͍ͨ਺ͱͳ
    Γ·͢ɻ
    ਪ঑஋ 100(มߋͳ͠)

    View full-size slide

  16. ઀ଓઃఆɾػೳઃఆ(5/22)
    ύϥϝʔλʔ໊ superuser_reserved_connections
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 3
    ղઆ
    αʔόʔʹ઀ଓͰ͖ΔεʔύʔϢʔβʔͷ࠷େηογϣ
    ϯ਺Ͱ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 3(มߋͳ͠)

    View full-size slide

  17. ઀ଓઃఆɾػೳઃఆ(6/22)
    ύϥϝʔλʔ໊ max_prepared_transactions
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 0
    ղઆ
    ೋ૚ίϛοτ༻τϥϯβΫγϣϯͷ࠷େ਺Ͱ͢ɻ
    “0”ͷ৔߹͸ೋ૚ίϛοτػೳ͕ແޮԽ͞Ε·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 0(มߋͳ͠)

    View full-size slide

  18. ઀ଓઃఆɾػೳઃఆ(7/22)
    ύϥϝʔλʔ໊ db_user_namespace
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ off
    ղઆ
    PostgreSQLͷϢʔβʔ৘ใ͸ɺσʔλϕʔεΫϥελ
    Ͱڞ༗͞Ε͍ͯ·͕͢ɺ͜ͷύϥʔϝʔλʔΛonʹ͢
    Δ͜ͱͰɺσʔλϕʔε͝ͱʹϢʔβʔ৘ใΛઃఆ͢Δ
    ͜ͱ͕ՄೳͱͳΓ·͢ɻ
    Ϣʔβʔͷ࡞੒͸ɺusername@dbnameͷΑ͏ʹͳΓ
    ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ off(มߋͳ͠)

    View full-size slide

  19. ઀ଓઃఆɾػೳઃఆ(8/22)
    ύϥϝʔλʔ໊ default_transaction_isolation
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ read committed
    ղઆ
    SQLτϥϯβΫγϣϯϨϕϧΛઃఆ͠·͢ɻ
    ҎԼͷઃఆ஋͕ଘࡏ͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ɾread uncommitted
    ɾread committed
    ɾrepeatable read
    ɾserializable
    ਪ঑஋ read committed(มߋͳ͠)

    View full-size slide

  20. ઀ଓઃఆɾػೳઃఆ(9/22)
    ύϥϝʔλʔ໊ timezone
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ GMT
    ղઆ
    ࣌ؒදࣔ࣌ɺλΠϜελϯϓղऍ࣌ͷλΠϜκʔϯΛઃ
    ఆ͠·͢ɻ
    initdbͰࢦఆͨ͠ϩέʔϧʹΑΓมΘΓ·͢ɻ
    ϩέʔϧ͕“C”ͷ৔߹͸ɺ“GMT”ͱͳΓ·͢ɻ
    ਪ঑஋ Japan

    View full-size slide

  21. ઀ଓઃఆɾػೳઃఆ(10/22)
    ύϥϝʔλʔ໊ lc_messages
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ C
    ղઆ
    දࣔ͢ΔϝοηʔδͷݴޠΛઃఆ͠·͢ɻ
    ࢖༻Ͱ͖Δݴޠ͸γεςϜʹґଘ͠·͢ɻ
    initdbͰࢦఆͨ͠ϩέʔϧʹΑΓมΘΓ·͢ɻ
    ϩέʔϧ͕“C”ͷ৔߹͸ɺ“C”ͱͳΓ·͢ɻ
    (“C”ͱ͸σϑΥϧτઃఆͱ͍͏ҙຯͰ͢ɻͭ·Γӳޠͱͳ
    Γ·͢ɻ)
    ਪ঑஋ ja_JP.UTF-8

    View full-size slide

  22. ઀ଓઃఆɾػೳઃఆ(11/22)
    ύϥϝʔλʔ໊ lc_monetary, lc_numeric, lc_time
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ C
    ղઆ
    ௨աॻࣜɺ਺஋ͷॻࣜɺ೔࣌ॻࣜΛઃఆ͠·͢ɻ
    ࢖༻Ͱ͖Δݴޠ͸γεςϜʹґଘ͠·͢ɻ
    initdbͰࢦఆͨ͠ϩέʔϧʹΑΓมΘΓ·͢ɻ
    ϩέʔϧ͕“C”ͷ৔߹͸ɺ“C”ͱͳΓ·͢ɻ
    ਪ঑஋ ja_JP.UTF-8

    View full-size slide

  23. ύϥϝʔλʔ໊ datestyle
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ iso, mdy
    ղઆ
    ೔෇࣌ࠁͷॻࣜ(ग़ྗॻࣜ)ͱɺᐆດͳ೔෇ೖྗͷղऍن
    ଇ(೥/݄/೔ͷॱংͷೖग़ྗࢦఆ)Λઃఆ͠·͢ɻ
    ਪ঑஋ iso, ymd
    ઀ଓઃఆɾػೳઃఆ(12/22)

    View full-size slide

  24. ઀ଓઃఆɾػೳઃఆ(13/22)
    ύϥϝʔλʔ໊ archive_mode
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ off
    ղઆ
    WALΞʔΧΠϏϯάػೳͷ༗ޮແޮΛઃఆ͠·͢ɻ
    archive_command͕ઃఆ͞Ε͓ͯΓɺwal_level͕minimalҎ্Ͱ͋
    Δ͜ͱ͕ඞਢͱͳΓ·͢ɻ
    ҎԼͷઃఆ஋͕ଘࡏ͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ɾoff: ػೳΛແޮԽ͠·͢ɻ
    ɾon: ௨ৗӡ༻࣌(ΞʔΧΠϒϦΧόϦ͓Αͼ
    ελϯόΠϞʔυҎ֎)ͷΈػೳΛ༗ޮԽ͠·͢ɻ
    ɾalways: ͢΂ͯͷঢ়ଶͰػೳΛ༗ޮԽ͠·͢ɻ
    ਪ঑஋ off(มߋͳ͠)

    View full-size slide

  25. ઀ଓઃఆɾػೳઃఆ(14/22)
    ύϥϝʔλʔ໊ archive_command
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ ’’(ۭന)
    ղઆ
    WALϑΝΠϧηάϝϯτΛΞʔΧΠϒ͢ΔγΣϧίϚ
    ϯυΛࢦఆ͠·͢ɻ
    ίϚϯυதͷ%p͸֨ೲ͞ΕΔϑΝΠϧͷύεͰஔ͖׵
    ͑ΒΕɺ%f͸ϑΝΠϧ໊Ͱஔ͖׵͑ΒΕ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ ’’(มߋͳ͠)

    View full-size slide

  26. ઀ଓઃఆɾػೳઃఆ(15/22)
    ύϥϝʔλʔ໊ archive_timeout
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0
    ղઆ
    WALϑΝΠϧηάϝϯτΛΞʔΧΠϒ͢Δ·Ͱͷ࣌ؒ
    Λࢦఆ͠·͢ɻ
    ඵ਺Ͱࢦఆ͠ɺ0ͷ৔߹͸ແޮԽ͞Ε·͢ɻ
    ஋Λখ͗͘͢͞͠ΔͱΞʔΧΠϒϑΝΠϧͷංେԽʹ
    ܨ͕ΔͨΊɺ60sҎ্ͷ஋Λࢦఆ͢ΔΑ͏ʹͯͩ͘͠͞
    ͍ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 0(มߋͳ͠)

    View full-size slide

  27. ઀ଓઃఆɾػೳઃఆ(16/22)
    ύϥϝʔλʔ໊ enable_material
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢ΔϚςϦΞϥΠζϊʔυͷ
    ༗ޮແޮΛઃఆ͠·͢ɻ
    ※ϚςϦΞϧ͸ɺԼҐϊʔυͷ৘ใΛϑΝΠϧʹ
    ॻ͖ग़্ͯ͠Ґϊʔυ΁৘ใΛ౉͢ࡍʹ
    ࢖༻͞Ε·͢ɻ
    ਪ঑஋ on(มߋͳ͠)

    View full-size slide

  28. ઀ଓઃఆɾػೳઃఆ(17/22)
    ύϥϝʔλʔ໊
    enable_bitmapscan, enable_gathermerge,
    enable_hashagg, enable_hashjoin, enable_indexscan,
    enable_indexonlyscan
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢Δϓϥϯϝιουͷ༗ޮແ
    ޮΛઃఆ͠·͢ɻ
    ॱʹϏοτϚοϓεΩϟϯܭըɺΪϟβʔϚʔδܭ
    ըɺϋογϡू໿ܭըɺϋογϡ݁߹ܭըɺΠϯσο
    Ϋε૸ࠪܭըɺΠϯσοΫεΦϯϦʔεΩϟϯܭըͱ
    ͳΓ·͢ɻ
    ਪ঑஋ on(มߋͳ͠)

    View full-size slide

  29. ઀ଓઃఆɾػೳઃఆ(18/22)
    ύϥϝʔλʔ໊
    enable_mergejoin, enable_nestloop,
    enable_seqscan, enable_tidscan, enable_sort
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢Δϓϥϯϝιουͷ༗ޮແ
    ޮΛઃఆ͠·͢ɻ
    ॱʹϚʔδ݁߹ܭըɺωεςουϧʔϓܭըɺγʔέ
    ϯγϟϧ૸ࠪܭըɺϋογϡ݁߹ܭըɺ໌ࣔతͳιʔ
    τɺTID ૸ࠪܭըɺ໌ࣔతͳιʔτͱͳΓ·͢ɻ
    γʔέϯγϟϧ૸ࠪܭը͓Αͼ໌ࣔతͳιʔτ͸׬શ
    ʹແޮԽ͢Δ͜ͱ͸Ͱ͖·ͤΜɻ
    ਪ঑஋ on(มߋͳ͠)

    View full-size slide

  30. ઀ଓઃఆɾػೳઃఆ(19/22)
    ύϥϝʔλʔ໊ enable_parallel_append
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢Δϓϥϯϝιουͷ༗ޮແ
    ޮΛઃఆ͠·͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    ϋογϡύʔςΟγϣχϯά͞Εͨςʔϒϧʹରͯ͠༗
    ޮͱͳΔɺύϥϨϧू໿ܭըͱͳΓ·͢ɻ
    ਪ঑஋ on(มߋͳ͠)

    View full-size slide

  31. ઀ଓઃఆɾػೳઃఆ(20/22)
    ύϥϝʔλʔ໊ enable_parallel_hash, enable_partition_pruning
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢Δϓϥϯϝιουͷ༗ޮແ
    ޮΛઃఆ͠·͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    ॱʹύϥϨϧϋογϡ݁߹ܭըɺύϥϨϧύʔςΟγ
    ϣϯܭըͱͳΓ·͢ɻ
    ύϥϨϧύʔςΟγϣϯܭը͸ɺϓϥϯχϯάͰฒྻ
    ॲཧͰύʔςΟγϣϯςʔϒϧʹରͯ͠ΞΫηε͢Δඞ
    ཁ͕͋Δ͔֬ೝͰ͖ΔػೳͰ͢ɻ
    ਪ঑஋ on(มߋͳ͠)

    View full-size slide

  32. ઀ଓઃఆɾػೳઃఆ(21/22)
    ύϥϝʔλʔ໊
    enable_partitionwise_join,
    enable_partitionwise_aggregate
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ off
    ղઆ
    ΦϓςΟϚΠβʔ͕࢖༻͢Δϓϥϯϝιουͷ༗ޮແ
    ޮΛઃఆ͠·͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    ॱʹύʔςΟγϣϯಉ࢜ͷ݁߹ɺύʔςΟγϣϯಉ࢜
    ͷू߹ͱͳΓ·͢ɻ
    ϓϥϯχϯάͰCPU͓ΑͼϝϞϦΛଟ͘࢖༻͢Δͨ
    ΊɺແޮԽ͞Ε͍ͯ·͢ɻ
    ਪ঑஋ off(มߋͳ͠)

    View full-size slide

  33. ઀ଓઃఆɾػೳઃఆ(22/22)
    ύϥϝʔλʔ໊ jit
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ off
    ղઆ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ػೳͰ͋ΔɺJIT(Just In
    Time)ίϯύΠϧΛ༗ޮԽ͠·͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    WHERE۟΍ूܭͳͲͰ࢖༻͞Ε·͕͢ɺ·ͩ҆ఆԽ͠
    ͍ͯͳ͍Α͏ͰɺσϑΥϧτͰແޮԽ͞Ε͍ͯ·͢ɻ
    ਪ঑஋ off(มߋͳ͠)

    View full-size slide

  34. ϨϓϦέʔγϣϯ(1/17)
    PostgreSQLͷϨϓϦέʔγϣϯͷઃఆʹ͍ͭͯղઆ͠·͢ɻ
    PostgreSQL͸ɺετϦʔϛϯάϨϓϦέʔγϣϯͱϩδΧϧϨϓϦέʔγϣϯͷ2ͭΛ
    ఏڙ͍ͯ͠·͢ɻ
    ετϦʔϛϯάϨϓϦέʔγϣϯ͸ɺWAL(Write Ahead Logging)Λwalsenderϓϩηε
    ͕εϨʔϒαʔόʔ΁࿈ܞ͠ɺεϨʔϒαʔόʔͷwalreceiver͕ड৴ͯ͠ϨϓϦέʔ
    γϣϯΛఏڙ͠·͢ɻ
    ϩδΧϧϨϓϦέʔγϣϯ͸ɺϓϥΠϚϦʔαʔόʔ͕WALΛϩδΧϧσίʔσΟϯά
    ͱݺ͹ΕΔػೳͰϩδΧϧϨϓϦέʔγϣϯͷϓϩτίϧʹม׵͠ɺwalsenderϓϩη
    ε͕εϨʔϒαʔόʔ΁࿈ܞ͠·͢ɻεϨʔϒαʔόʔ͸ɺόοΫάϥ΢ϯυϫʔΧʔ
    ͕ड৴ͯ͠ϨϓϦέʔγϣϯΛఏڙ͠·͢ɻ
    ৄ͘͠͸ɺϨϓϦέʔγϣϯͷղઆεϥΠυΛ͝ࢀর͍ͩ͘͞ɻ
    https://www.slideshare.net/masahikosawada98/postgresql-86891271

    View full-size slide

  35. ύϥϝʔλʔ໊ wal_level
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ replica
    ղઆ
    WALʹอ࣋͢Δ৘ใྔΛઃఆ͠·͢ɻ
    ҎԼͷઃఆ஋͕ଘࡏ͠·͢ɻ
    ͳ͓ɺ্ҐϨϕϧ͸ԼҐϨϕϧͷ৘ใΛαϙʔτ͠·͢ɻ
    ɾminimal: Ϋϥογϡ࣌΍ଈ࣌ఀࢭ͔Βͷ෮ؼʹ
    ඞཁͳ৘ใͷΈอ࣋͠·͢ɻ
    ɾreplica: WALΞʔΧΠϒ͓ΑͼετϦʔϛϯά
    ϨϓϦέʔγϣϯʹඞཁͳ৘ใΛอ࣋͠·͢ɻ
    ɾlogical: ϩδΧϧσίʔσΟϯάʹඞཁͳ৘ใΛอ࣋͠·͢ɻ
    ਪ঑஋ logical
    ϨϓϦέʔγϣϯ(2/17)

    View full-size slide

  36. ύϥϝʔλʔ໊ max_wal_senders
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 10
    ղઆ
    εϨʔϒαʔόʔ͔Βͷಉ࣌઀ଓ࠷େ਺Λࢦఆ͠·͢ɻ
    0ͷ৔߹͸ϨϓϦέʔγϣϯ͕ແޮԽ͞Ε·͢ɻ
    ૯઀ଓ਺ʹΧ΢ϯτ͞ΕΔͨΊɺmax_connectionsͷ஋
    Ҏ্ʹઃఆ͢Δ͜ͱ͸Ͱ͖·ͤΜɻ
    ϚελʔʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋
    (ετϦʔϛϯάϨϓϦέʔγϣϯͷεϨʔϒ਺ +
    ϩδΧϧϨϓϦέʔγϣϯͷαϒεΫϦϓγϣϯ਺ * 2) + 1
    ϨϓϦέʔγϣϯ(3/17)

    View full-size slide

  37. ύϥϝʔλʔ໊ max_replication_slots
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 10
    ղઆ
    αʔόʔ͕࡞੒Ͱ͖ΔϨϓϦέʔγϣϯεϩοτͷ࠷େ
    ਺Λࢦఆ͠·͢ɻ
    ϚελʔɾεϨʔϒڞʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ※ϨϓϦέʔγϣϯεϩοτ͸ɺ
    εϨʔϒͷঢ়ଶΛϚελʔ͕؅ཧ͢ΔͨΊͷػೳͰ͢ɻ
    ਪ঑஋ ϩδΧϧϨϓϦέʔγϣϯͷαϒεΫϦϓγϣϯ਺ * 2 +
    ετϦʔϛϯάϨϓϦέʔγϣϯͰ࡞੒͢ΔϨϓϦέʔγϣϯεϩοτͷ਺
    ϨϓϦέʔγϣϯ(4/17)

    View full-size slide

  38. ύϥϝʔλʔ໊ track_commit_timestamp
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ off
    ղઆ
    τϥϯβΫγϣϯͷίϛοτλΠϜΛه࿥͠·͢ɻ
    ϚελʔɾεϨʔϒڞʹઃఆΛߦ͍·͢ɻ
    ਪ঑஋ on
    ϨϓϦέʔγϣϯ(5/17)

    View full-size slide

  39. ύϥϝʔλʔ໊ wal_sender_timeout
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 60s
    ղઆ
    ࢦఆͨ࣌ؒ͠ΑΓ௕͘εϨʔϒ͔ΒͷԠ౴͕ͳ͍৔߹͸
    ϨϓϦέʔγϣϯ઀ଓΛఀࢭ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    0ͷ৔߹͸λΠϜΞ΢τػೳΛແޮԽ͠·͢ɻ
    ϚελʔʹઃఆΛߦ͍·͢ɻ
    ؀ڥʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 60s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(6/17)

    View full-size slide

  40. ύϥϝʔλʔ໊ synchronous_standby_names
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ ’’(ۭന)
    ղઆ
    ಉظ͢ΔεϨʔϒ໊ΛΧϯϚ۠੾ΓͰઃఆ͠·͢ɻ
    “*”Λઃఆ͢Δͱɺ͢΂ͯͷεϨʔϒ͕ର৅ͱͳΓ·͢ɻ
    ਖ਼֬ͳॻࣜʹ͍ͭͯ͸ɺެࣜυΩϡϝϯτΛ͝ࢀরͩ͘
    ͍͞ɻ
    https://www.postgresql.jp/document/current/html/runtime-
    config-replication.html
    ϚελʔʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ ’’(ۭന)
    ϨϓϦέʔγϣϯ(7/17)

    View full-size slide

  41. ύϥϝʔλʔ໊ vacuum_defer_cleanup_age
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0
    ղઆ
    VACUUM΍HOTͰɺෆཁߦͷ࡟আ஗ԆΛߦ͏τϥϯβΫ
    γϣϯ਺Λࢦఆ͠·͢ɻ
    0͸ଈ࠲ʹ࡟আ͞Ε·͢ɻ
    εϨʔϒͰίϯϑϦΫτ͕සൟʹൃੜ͢Δ৔߹͸ɺύϥ
    ϝʔλʔͷௐ੔Λߦͳ͍ͬͯͩ͘͞ɻ
    ϚελʔʹઃఆΛߦ͍·͢ɻ
    ؀ڥʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 0(มߋͳ͠)
    ϨϓϦέʔγϣϯ(8/17)

    View full-size slide

  42. ύϥϝʔλʔ໊ hot_standby
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ on
    ղઆ
    εϨʔϒʹରͯ͠ͷ໰͍߹ΘͤΛ༗ޮԽ͠·͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ਪ঑஋ on(มߋͳ͠)
    ϨϓϦέʔγϣϯ(9/17)

    View full-size slide

  43. ύϥϝʔλʔ໊ max_standby_archive_delay
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 30s
    ղઆ
    ϗοτελϯόΠதɺελϯόΠ͕ΞʔΧΠϒϑΝΠϧΛ
    దԠ͍ͯ͠Δ࠷தʹൃੜͨ͠ίϯϑϦΫτͷ଴ͪ࣌ؒΛ
    ઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    “-1”ͷ࣌͸ɺ໰͍߹Θ͕ͤऴྃ͢Δ·ͰదԠ͕ఀࢭ͠·
    ͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 30s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(10/17)

    View full-size slide

  44. ύϥϝʔλʔ໊ max_standby_streaming_delay
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 30s
    ղઆ
    ϗοτελϯόΠதɺελϯόΠ͕ϓϥΠϚϦʔ͔Βड
    ৴ͨ͠WALΛదԠ͍ͯ͠Δ࠷தʹൃੜͨ͠ίϯϑϦΫτ
    ͷ଴ͪ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    “-1”ͷ࣌͸ɺ໰͍߹Θ͕ͤऴྃ͢Δ·ͰదԠ͕ఀࢭ͠·
    ͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 30s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(11/17)

    View full-size slide

  45. ύϥϝʔλʔ໊ wal_receiver_status_interval
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 10s
    ղઆ
    ελϯόΠ͕WALͷड৴ਐḿঢ়ଶΛϓϥΠϚϦʔʹૹ৴
    ͢Δִؒͷ࠷খ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    wal_sender_timeoutΑΓখ͍͞஋Λઃఆ͍ͯͩ͘͠͞ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ؀ڥʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 10s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(12/17)

    View full-size slide

  46. ύϥϝʔλʔ໊ hot_standby_feedback
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ off
    ղઆ
    εϨʔϒ͕ݱࡏॲཧ͍ͯ͠Δ໰͍߹Θͤʹ͍ͭͯɺϓϥ
    ΠϚϦʔʹϑΟʔυόοΫΛૹ৴͢ΔػೳΛ༗ޮԽ͠·
    ͢ɻ
    ϨϓϦέʔγϣϯεϩοτΛ࢖༻͍ͯ͠Δ৔߹͸มߋ͕
    ඞਢͱͳΓ·͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ on
    ϨϓϦέʔγϣϯ(13/17)

    View full-size slide

  47. ύϥϝʔλʔ໊ wal_receiver_timeout
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 60s
    ղઆ
    ࢦఆͨ࣌ؒ͠ΑΓ௕͘ϓϥΠϚϦʔ͔ΒͷԠ౴͕ͳ͍৔
    ߹͸ϨϓϦέʔγϣϯ઀ଓΛఀࢭ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    0ͷ৔߹͸λΠϜΞ΢τػೳΛແޮԽ͠·͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ؀ڥʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 60s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(14/17)

    View full-size slide

  48. ύϥϝʔλʔ໊ wal_retrieve_retry_interval
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 5s
    ղઆ
    ࢦఆͨ࣌ؒ͠ΑΓ௕͘Ϛελʔ͔ΒͷԠ౴͕ͳ͍৔߹͸
    ϨϓϦέʔγϣϯ઀ଓΛఀࢭ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    εϨʔϒʹઃఆΛߦ͍·͢ɻ
    ؀ڥʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 5s(มߋͳ͠)
    ϨϓϦέʔγϣϯ(15/17)

    View full-size slide

  49. ύϥϝʔλʔ໊ max_logical_replication_workers
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 4
    ղઆ
    ϩδΧϧϨϓϦέʔγϣϯͷϫʔΧʔ࠷େ਺Λࢦఆ͠·
    ͢ɻ
    దԠϫʔΧʔͱςʔϒϧಉظϫʔΧʔͷ྆ํؚ͕·Ε·
    ͢ɻ
    max_worker_processesͷ஋Λڞ༗͠·͢ɻ
    Ϛελʔ(ύϒϦογϟʔ)ʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 2(มߋͳ͠)
    ϨϓϦέʔγϣϯ(16/17)

    View full-size slide

  50. ύϥϝʔλʔ໊ max_sync_workers_per_subscription
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 2
    ղઆ
    αϒεΫϦϓγϣϯ͝ͱͷಉظϫʔΧʔͷ࠷େ਺Λࢦఆ
    ͠·͢ɻ
    1ςʔϒϧͷฒྻ౓͸1ͰݻఆԽ͞Ε͍ͯΔͨΊɺ஋Λม
    ߋ͢Δ͜ͱͰෳ਺ςʔϒϧʹର͢Δಉظॲཧͷฒྻ౓͸
    ্͕Γ·͢ɻ
    Ϛελʔ(ύϒϦογϟʔ)ʹઃఆΛߦ͍·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 2(มߋͳ͠)
    ϨϓϦέʔγϣϯ(17/17)

    View full-size slide

  51. ؂ࢹؔ܎(1/18)
    PostgreSQL͸ͷ؂ࢹ(ϩά)ؔ܎ͷઃఆʹ͍ͭͯղઆ͠·͢ɻ
    %Τεέʔϓ͕ଟ਺ଘࡏ͠·͕͢ɺ͜͜Ͱ͸ղઆΛߦ͍·ͤΜɻ
    ৄ͘͠͸ɺެࣜυΩϡϝϯτΛࢀর͍ͯͩ͘͠͞ɻ
    https://www.postgresql.jp/document/current/html/runtime-config-
    logging.html

    View full-size slide

  52. ύϥϝʔλʔ໊ log_destination
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ stderr
    ղઆ
    αʔόʔͷϩάग़ྗઌΛઃఆ͠·͢ɻ
    ड͚෇͚Δ஋͸ɺ“stderr”ɺ“csvlog”ɺ
    “syslog”ɺ“eventlog”ͱͳΓ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ stderr(มߋͳ͠)
    ؂ࢹؔ܎(2/18)

    View full-size slide

  53. ύϥϝʔλʔ໊ logging_collector
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ off
    ղઆ
    ඪ४Τϥʔ·ͨ͸CSVॻࣜͷϩάग़ྗʹૹΒΕΔϝοη
    ʔδΛऔΓग़͠ɺϩάϑΝΠϧʹϦμΠϨΫτ͠·͢ɻ
    ͜ͷػೳ͕༗ޮͰͳ͍ͱɺϩάϑΝΠϧ͸࡞੒͞Ε·ͤ
    Μɻ
    ਪ঑஋ on
    ؂ࢹؔ܎(3/18)

    View full-size slide

  54. ύϥϝʔλʔ໊ log_directory
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ log
    ղઆ
    αʔόʔͷϩάϑΝΠϧग़ྗઌΛઃఆ͠·͢ɻ
    σϑΥϧτ஋͸૬ରύεͱͳ͓ͬͯΓɺઈରύεʹม׵
    ͢Δͱ“$PGDATA/log”ͱͳΓ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ /var/log/pgsql
    ؂ࢹؔ܎(4/18)

    View full-size slide

  55. ύϥϝʔλʔ໊ log_filename
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ postgresql-%Y-%m-%d_%H%M%S.log
    ղઆ
    ϩάϑΝΠϧͷϑΝΠϧ໊Λઃఆ͠·͢ɻ
    CSVग़ྗͷ৔߹͸ɺλΠϜελϯϓ෇͖ͷϩάϑΝΠϧ
    ໊ʹ.csvΛ෇༩ͨ͠ϑΝΠϧ໊͕࡞੒͞Ε·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ postgresql%Y%m%d.log
    ؂ࢹؔ܎(5/18)

    View full-size slide

  56. ύϥϝʔλʔ໊ log_file_mode
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0600
    ղઆ
    ϩάϑΝΠϧͷύʔϛογϣϯΛઃఆ͠·͢ɻ
    Windowsͷ৔߹͸ແࢹ͞Ε·͢ɻ
    chmod͓ΑͼumaskγεςϜίʔϧͷ਺஋ϞʔυͰهड़
    ͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 0600(มߋͳ͠)
    ؂ࢹؔ܎(6/18)

    View full-size slide

  57. ύϥϝʔλʔ໊ log_rotation_age
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 1d
    ղઆ
    ϩάϑΝΠϧͷ࠷େण໋Λઃఆ͠·͢ɻ
    ࢦఆ͕࣌ؒܦա͢Δͱɺ৽͍͠ϑΝΠϧʹϩά͕ੜ੒͞
    Ε·͢ɻ
    “0”ͷ࣌͸ɺ࣌ؒʹΑΔϑΝΠϧͷੜ੒͕ແޮͱͳΓ·
    ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 1d(มߋͳ͠)
    ؂ࢹؔ܎(7/18)

    View full-size slide

  58. ύϥϝʔλʔ໊ log_rotation_size
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 10MB
    ղઆ
    ϩάϑΝΠϧͷ࠷େ༰ྔΛઃఆ͠·͢ɻ
    ࢦఆ༰ྔΛΦʔόʔ͢Δͱɺ৽͍͠ϑΝΠϧʹϩά͕ੜ
    ੒͞Ε·͢ɻ
    “0”ͷ࣌͸ɺ༰ྔʹΑΔϑΝΠϧͷੜ੒͕ແޮͱͳΓ·
    ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 0
    ؂ࢹؔ܎(8/18)

    View full-size slide

  59. ύϥϝʔλʔ໊ log_truncate_on_rotation
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ off
    ղઆ
    ϩάϑΝΠϧͷ্ॻ͖Λઃఆ͠·͢ɻ
    ϩάϑΝΠϧͷϩʔςʔγϣϯ࣌ʹɺಉ͡ϑΝΠϧ໊͕
    طʹଘࡏ͍ͯ͠Δͱ্ॻ͖͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ on
    ؂ࢹؔ܎(9/18)

    View full-size slide

  60. ύϥϝʔλʔ໊ event_source
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ PostgreSQL
    ղઆ
    Πϕϯτϩά΁ͷग़ྗ࣌ʹ࢖༻͞ΕΔϓϩάϥϜ໊Λઃ
    ఆ͠·͢ɻ
    WindowsͷΈ༗ޮͱͳΓ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ PostgreSQL(มߋͳ͠)
    ؂ࢹؔ܎(10/18)

    View full-size slide

  61. ύϥϝʔλʔ໊ client_min_messages
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ notice
    ղઆ
    ΫϥΠΞϯτʹૹ৴͢Δϝοηʔδ֊૚Λઃఆ͠·͢ɻ
    “DEBUG5”ɺ“DEBUG4”ɺ“DEBUG3”ɺ“DEBUG2”ɺ
    “DEBUG1”ɺ“LOG”ɺ“NOTICE”ɺ“WARNING”ɺ
    “ERROR”ɺ“FATAL”ɺ“PANIC”͔Βબ୒͠·͢ɻ
    ԼҐͷ֊૚͸ɺ্Ґͷ֊૚ͷϝοηʔδΛؚΈ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ notice(มߋͳ͠)
    ؂ࢹؔ܎(11/18)

    View full-size slide

  62. ύϥϝʔλʔ໊ log_min_messages
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ warning
    ղઆ
    ϩάʹग़ྗ͢Δϝοηʔδ֊૚Λઃఆ͠·͢ɻ
    “DEBUG5”ɺ“DEBUG4”ɺ“DEBUG3”ɺ“DEBUG2”ɺ
    “DEBUG1”ɺ“LOG”ɺ“NOTICE”ɺ“WARNING”ɺ
    “ERROR”ɺ“FATAL”ɺ“PANIC”͔Βબ୒͠·͢ɻ
    ԼҐͷ֊૚͸ɺ্Ґͷ֊૚ͷϝοηʔδΛؚΈ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ warning(มߋͳ͠)
    ؂ࢹؔ܎(12/18)

    View full-size slide

  63. ύϥϝʔλʔ໊ log_min_error_statement
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ error
    ղઆ
    ΤϥʔͱͳͬͨSQLจΛɺϩάʹग़ྗ͢Δϝοηʔδ֊
    ૚Λઃఆ͠·͢ɻ
    “DEBUG5”ɺ“DEBUG4”ɺ“DEBUG3”ɺ“DEBUG2”ɺ
    “DEBUG1”ɺ“LOG”ɺ“NOTICE”ɺ“WARNING”ɺ
    “ERROR”ɺ“FATAL”ɺ“PANIC”͔Βબ୒͠·͢ɻ
    ԼҐͷ֊૚͸ɺ্Ґͷ֊૚ͷϝοηʔδΛؚΈ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ error(มߋͳ͠)
    ؂ࢹؔ܎(13/18)

    View full-size slide

  64. ύϥϝʔλʔ໊ log_min_duration_statement
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ -1
    ղઆ
    ࣮ߦ͕࣌ؒ௕͍SQLจΛϩάग़ྗ͢ΔͨΊͷ࠷খ࣌ؒΛ
    ઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    “0”ͷ৔߹͸શͯग़ྗ͞Εɺ“-1”ͷ৔߹͸ग़ྗ͕ແޮԽ͞
    Ε·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 3s
    ؂ࢹؔ܎(14/18)

    View full-size slide

  65. ύϥϝʔλʔ໊
    debug_print_parse, debug_print_rewritten,
    debug_print_plan
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ off
    ղઆ
    σόοά৘ใͷग़ྗͷ༗ޮແޮΛઃఆ͠·͢ɻ
    ༗ޮԽ͢Δ͜ͱͰɺνϡʔχϯάʹ༗ޮͳ৘ใ͕ग़ྗ͞
    ΕΔ͔΋͠Ε·ͤΜɻ
    ॱʹ“ղੳπϦʔ”ɺ“ϦϥΠλʔ৘ใ”ɺ
    “࣮ߦܭը”ͱͳΓ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ off(มߋͳ͠)
    ؂ࢹؔ܎(15/18)

    View full-size slide

  66. ύϥϝʔλʔ໊ log_hostname
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ off
    ղઆ
    ϩάग़ྗʹΫϥΠΞϯτͷϗετ໊Λग़ྗ͢ΔઃఆΛߦ
    ͍·͢ɻ
    ϗετ໊ͷ໊લղܾΛߦ͏ͨΊɺஶ͍͠ੑೳྼԽ͕ൃੜ
    ͢ΔՄೳੑ͕͋Γ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ off(มߋͳ͠)
    ؂ࢹؔ܎(16/18)

    View full-size slide

  67. ύϥϝʔλʔ໊ log_line_prefix
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ “%m [%p] ”
    ղઆ
    ϩάग़ྗ࣌ͷઌ಄෦෼ͷॻࣜจࣈྻΛઃఆ͠·͢ɻ
    ͜ͷจࣈྻͷޙΖʹϩά͕ग़ྗ͞Ε·͢ɻ
    %Τεέʔϓʹ͍ͭͯ͸ɺެࣜυΩϡϝϯτΛࢀরͯ͘͠
    ͍ͩ͞ɻ
    https://www.postgresql.jp/document/10/html/runtime-
    config-logging.html#GUC-LOG-LINE-PREFIX
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ “[%m] [%p] [%u] [%d] [%h] ”
    ؂ࢹؔ܎(17/18)

    View full-size slide

  68. ύϥϝʔλʔ໊ log_timezone
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ GMT
    ղઆ
    ϩάग़ྗ࣌ͷλΠϜκʔϯΛઃఆ͠·͢ɻ
    initdbͰࢦఆͨ͠ϩέʔϧʹΑΓมΘΓ·͢ɻ
    ϩέʔϧ͕“C”ͷ৔߹͸ɺ“GMT”ͱͳΓ·͢ɻ
    ਪ঑஋ Japan
    ؂ࢹؔ܎(18/18)

    View full-size slide

  69. νϡʔχϯά(1/46)
    PostgreSQLͷνϡʔχϯά͸؀ڥ΍σʔλྔɺ࢖༻༻్ʹ߹Θͤͯઃఆ͢
    Δඞཁ͕͋Γ·͢ɻ
    ݟੵ΋Γ࣌ʹࡉ͔͘ݕ౼Λߦ͍ͬͯͯ΋ɺ௕ظӡ༻͍ͯ͠Δͱੑೳ͕௿Լ
    ͢Δ͜ͱ͕͋ΔͨΊɺαʔόʔঢ়ଶΛ֬ೝͯ͠νϡʔχϯάΛߦ͏ඞཁ͕
    ͋Γ·͢ɻ

    View full-size slide

  70. ύϥϝʔλʔ໊ shared_buffers
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 32MB
    ղઆ
    σʔλϕʔεαʔόʔ͕࢖༻͢Δڞ༗ϝϞϦʔྔΛઃఆ͠·͢ɻ
    shared_buffersΛมߋ͢Δ৔߹͸ɺmax_wal_sizeͷมߋ΋ߦ͍·
    ͢ɻ
    ಉ࣌ʹɺΧʔωϧύϥϝʔλʔͷkernel.shmmax͓Αͼ
    kernel.shmall΋มߋ͍ͯͩ͘͠͞ɻ
    ڞ༗ϝϞϦʔͷ֓ࢉ͸ɺҎԼͷܭࢉࣜͰ֓ࢉͰ͖·͢ɻ
    = max_connections * 400 + max_prepared_transactions * 600
    + max_locks_per_transaction
    * (max_connections + max_prepared_transactions) * 270
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ γεςϜϝϞϦʔͷ25%ͷ༰ྔ
    νϡʔχϯά(2/46)

    View full-size slide

  71. ύϥϝʔλʔ໊ temp_buffers
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 8MB
    ղઆ
    ηογϣϯதʹ࢖༻͞ΕΔҰ࣌όοϑΝʔͷαΠζΛઃ
    ఆ͠·͢ɻ
    Ұ࣌όοϑΝʔΛଟ͘࢖༻͢Δ৔߹͸มߋ͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 8MB(มߋͳ͠)
    νϡʔχϯά(3/46)

    View full-size slide

  72. ύϥϝʔλʔ໊ work_mem
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 4MB
    ղઆ
    ηογϣϯதͷιʔτ΍ϋογϡૢ࡞Ͱ࢖༻͢ΔϝϞϦʔͷα
    ΠζΛઃఆ͠·͢ɻ
    େ͖͘͢Δ͜ͱͰ໰͍߹Θͤੑೳ͕޲্͠·͕͢ɺෳࡶͳ໰͍
    ߹Θͤͷ৔߹͸work_memͷ਺ഒ࢖༻͞ΕΔ͜ͱ͕͋Γ·͢ɻ
    ·ͨɺηογϣϯ୯ҐʹඞཁͱͳΔͨΊϝϞϦʔ؅ཧʹؾΛͭ
    ͚͍ͯͩ͘͞ɻ
    (γεςϜϝϞϦʔ - shared_buffers) / max_connectionsҎ্ͷ৔
    ߹͸εϫοϓ͠ɺੑೳ͕௿Լ͠·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 4MB(มߋͳ͠)
    νϡʔχϯά(4/46)

    View full-size slide

  73. ύϥϝʔλʔ໊ maintenance_work_mem
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 64MB
    ղઆ
    όΩϡʔϜ΍ΠϯσοΫε࡞੒ͳͲͷϝϯςφϯεૢ࡞
    ࣌ʹ࢖༻͢ΔϝϞϦʔͷαΠζΛઃఆ͠·͢ɻ
    ࣗಈόΩϡʔϜ͕ಈ࡞ͨ͠৔߹ɺ
    autovacuum_max_workers * maintenance_work_mem
    ͷϝϞϦʔ͕࢖༻͞Ε·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 64MB(มߋͳ͠)
    νϡʔχϯά(5/46)

    View full-size slide

  74. ύϥϝʔλʔ໊ max_stack_depth
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ 2MB
    ղઆ
    σʔλϕʔεαʔόʔͷ࣮ߦελοΫͷαΠζΛઃఆ͠·
    ͢ɻ
    Χʔωϧͷ࠷େελοΫ༰ྔ͔Β҆શ༨஍ͷͨΊͷ1MB
    Λࠩ͠Ҿ͍ͨ஋Λઃఆ͍ͯͩ͘͠͞ɻ
    Χʔωϧͷ੍ݶҎ্Λઃఆ͢ΔͱɺόοΫΤϯυϓϩη
    ε͕Ϋϥογϡ͢ΔڪΕ͕͋Γ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ ulimit -sͷ݁Ռ(KB) - 1MB
    νϡʔχϯά(6/46)

    View full-size slide

  75. ύϥϝʔλʔ໊ effective_io_concurrency
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 1
    ղઆ
    σʔλϕʔεαʔόʔͷಉ࣌σΟεΫI/Oૢ࡞਺Λઃఆ͠·͢ɻ
    “0”Λઃఆ͢ΔͱɺඇಉظI/OϦΫΤετ͕ແޮԽ͞Ε·͢ɻ
    HDDͷ৔߹͸ɺRAIDΛߏ੒͢ΔυϥΠϒ਺Λઃఆͯͩ͘͠͞
    ͍ɻ
    ͨͩ͠ɺύϦςΟ༻͸আ֎͍ͯͩ͘͠͞ɻ
    SSDͷ৔߹͸ɺ਺ඦͷ஋͕࠷దͱߟ͑ΒΕ·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋
    HDD: RAIDΛߏ੒͢ΔυϥΠϒ਺(ύϦςΟ༻͸আ֎)
    SSD: 200
    νϡʔχϯά(7/46)

    View full-size slide

  76. ύϥϝʔλʔ໊ max_worker_processes
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 8
    ղઆ
    σʔλϕʔεαʔόʔͷόοΫάϥ΢ϯυϓϩηε਺Λ
    ઃఆ͠·͢ɻ
    εϨʔϒαʔόʔ͸ɺϚελʔαʔόʔͷઃఆ஋Ҏ্ઃ
    ఆ͍ͯͩ͘͠͞ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ CPUͷ෺ཧίΞ਺
    νϡʔχϯά(8/46)

    View full-size slide

  77. ύϥϝʔλʔ໊ max_parallel_maintenance_workers
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 2
    ղઆ
    1ͭͷϢʔςΟϦςΟίϚϯυ͔ΒىಈͰ͖ΔฒྻϫʔΧ
    ʔ਺ͷ࠷େ஋Λઃఆ͠·͢ɻ
    ݱࡏ͸ɺBπϦʔΠϯσοΫε࡞੒࣌ͷΈ༗ޮͱͳΓ·
    ͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    ਪ঑஋ 2(มߋͳ͠)
    νϡʔχϯά(9/46)

    View full-size slide

  78. ύϥϝʔλʔ໊ max_parallel_workers_per_gather
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 2
    ղઆ
    1ͭͷGather·ͨ͸Gather Mergeϊʔυʹରͯ͠ىಈͰ
    ͖ΔϫʔΧʔ਺ͷ࠷େ஋Λઃఆ͠·͢ɻ
    “0”Λઃఆ͢ΔͱύϥϨϧΫΤϦʔ͕࢖༻͞Εͳ͘ͳΓ·
    ͢ɻ
    max_parallel_workersͰϓʔϧ͞Εͨϓϩηε͔Βऔಘ
    ͞Ε·͢ɻ
    ਪ঑஋ CPUͷ෺ཧίΞ਺ / 2
    νϡʔχϯά(10/46)

    View full-size slide

  79. ύϥϝʔλʔ໊ max_parallel_workers
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 8
    ղઆ
    ύϥϨϧΫΤϦʔ༻ͷ࠷େϫʔΧʔ਺Λઃఆ͠·͢ɻ
    ͜ͷ஋͸ɺmax_worker_processesͷ஋ͷ੍ݶΛड͚·
    ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ CPUͷ෺ཧίΞ਺
    νϡʔχϯά(11/46)

    View full-size slide

  80. ύϥϝʔλʔ໊ synchronous_commit
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    τϥϯβΫγϣϯͷίϛοτ͕ΫϥΠΞϯτʹ“׬ྃ”Λใࠂ͢ΔલʹWALϨ
    ίʔυ͕σΟεΫ্ʹॻ͖ࠐ·ΕΔ·Ͱ଴͔ͭͲ͏͔ͷઃఆΛߦ͍·͢ɻ
    ϨϓϦέʔγϣϯ࣌ʹ΋Өڹ͕͋Γɺઃఆ஋ͷҧ͍͸ҎԼͷ௨ΓͰ͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ on(มߋͳ͠)
    νϡʔχϯά(12/46)
    ઃఆ஋ ಉظ/ඇಉظ ϓϥΠϚϦʔ εϨʔϒ
    off ඇಉظ ଴ͨͳ͍ ଴ͨͳ͍
    local ඇಉظ ଴ͭ ଴ͨͳ͍
    remote_write ಉظ ଴ͭ ϝϞϦॻ͖ࠐΈ·Ͱ଴ͭ
    on ಉظ ଴ͭ σΟεΫॻ͖ࠐΈ·Ͱ଴ͭ
    remote_apply ಉظ ଴ͭ WALదԠ·Ͱ଴ͭ

    View full-size slide

  81. ύϥϝʔλʔ໊ wal_buffers
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ -1
    ղઆ
    σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍WALσʔλ͕࢖༻͢Δڞ
    ༗ϝϞϦʔͷྔΛઃఆ͠·͢ɻ
    “-1”ͷ৔߹͸ɺshared_buffersͷ32෼ͷ1͕ઃఆ͞Ε·
    ͢ɻ
    shared_buffers͕େ͖͍৔߹ɺWALηάϝϯτͷେ͖͞
    Ͱ͋Δ16MBΛ௒͑ΔՄೳੑ͕͋ΔͨΊɺ16MBΛઃఆ͠
    ·͢ɻ
    ਪ঑஋ 16MB
    νϡʔχϯά(13/46)

    View full-size slide

  82. ύϥϝʔλʔ໊ checkpoint_timeout
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 5min
    ղઆ
    WALνΣοΫϙΠϯτͷλΠϜΞ΢τ࣌ؒΛઃఆ͠·
    ͢ɻ
    ஋͕খ͗͢͞Δͱɺϩάʹϫʔχϯάϝοηʔδ͕ग़ྗ
    ͞Ε·͢ɻ
    ਪ঑஋ 30min
    νϡʔχϯά(14/46)

    View full-size slide

  83. ύϥϝʔλʔ໊ checkpoint_completion_target
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0.5
    ղઆ
    ࣍ͷνΣοΫϙΠϯτ͕࣮ߦ͞ΕΔ·ͰͷؒʹͲΕ͘Β
    ͍ͷ࣌ؒΛֻ͚ͯॻ͖ग़͔͢Λઃఆ͠·͢ɻ
    ਪ঑஋ (checkpoint_timeout - 2min) / checkpoint_timeout
    νϡʔχϯά(15/46)

    View full-size slide

  84. ύϥϝʔλʔ໊ max_wal_size
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 1GB
    ղઆ
    ࣍ͷνΣοΫϙΠϯτ͕࣮ࢪ͞ΕΔWALϑΝΠϧͷαΠ
    ζΛઃఆ͠·͢ɻ
    ιϑτϦϛοτͷͨΊɺ৚݅ʹΑͬͯ௒͑Δ͜ͱ͕͋Γ
    ·͢ɻ
    ߋ৽͕සൟʹى͜Δ৔߹ɺνΣοΫϙΠϯτ͕සൟʹൃ
    ੜ͢ΔͨΊੑೳ͕௿Լ͠·͢ɻ
    ϩάʹϫʔχϯά͕ग़͍ͯΔ৔߹͸มߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ 1GB(มߋແ͠)
    νϡʔχϯά(16/46)

    View full-size slide

  85. ύϥϝʔλʔ໊ min_wal_size
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 80MB
    ղઆ
    WALϑΝΠϧͷ࠷খαΠζΛઃఆ͠·͢ɻ
    WALϑΝΠϧΛ࠶ར༻ɾ࡟আ͢Δࡍͷܭࢉࣜʹ΋࢖༻͞
    Ε·͢ɻ
    ਪ঑஋ 80MB(มߋແ͠)
    νϡʔχϯά(17/46)

    View full-size slide

  86. ύϥϝʔλʔ໊ seq_page_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 1.0
    ղઆ
    σΟεΫ͔Β1ϖʔδ෼σʔλΛγʔέϯγϟϧʹऔಘ͢
    ΔࡍͷɺϓϥϯφʔͷਪఆίετΛઃఆ͠·͢ɻ
    ਪ঑஋ 1.0(มߋແ͠)
    νϡʔχϯά(18/46)

    View full-size slide

  87. ύϥϝʔλʔ໊ random_page_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 4.0
    ղઆ
    σΟεΫ͔Β1ϖʔδ෼σʔλΛϥϯμϜʹऔಘ͢Δࡍ
    ͷɺϓϥϯφʔͷਪఆίετΛઃఆ͠·͢ɻ
    HDDΛج४ʹઃఆ͞Ε͍ͯ·͢ɻ
    γʔέϯγϟϧͱϥϯμϜΞΫηεεϐʔυ͕ಉ͡SSD
    ͷ৔߹͸ɺseq_page_costͷ஋ͱಉ͡ͱ͢Δ͜ͱͰߴ଎
    ԽͰ͖·͢ɻ
    ਪ঑஋
    HDD: 4.0(มߋແ͠)
    SSD: 1.0
    νϡʔχϯά(19/46)

    View full-size slide

  88. ύϥϝʔλʔ໊ cpu_tuple_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0.01
    ղઆ
    ໰͍߹Θͤ࣌ͷߦͷॲཧίετʹର͢Δɺϓϥϯφʔͷ
    ਪఆίετΛઃఆ͠·͢ɻ
    ਪ঑஋ 0.01(มߋແ͠)
    νϡʔχϯά(20/46)

    View full-size slide

  89. ύϥϝʔλʔ໊ cpu_index_tuple_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0.005
    ղઆ
    ΠϯσοΫε૸ࠪ࣌ͷΠϯσοΫεߦͷॲཧίετʹର
    ͢Δɺϓϥϯφʔͷਪఆ஋Λઃఆ͠·͢ɻ
    ਪ঑஋ 0.005(มߋແ͠)
    νϡʔχϯά(21/46)

    View full-size slide

  90. ύϥϝʔλʔ໊ cpu_operator_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0.0025
    ղઆ
    ໰͍߹Θͤ࣌ʹ࣮ߦ͞ΕΔ֤ԋࢉࢠ΍ؔ਺ͷॲཧίετ
    ʹର͢Δɺϓϥϯφʔͷਪఆ஋Λઃఆ͠·͢ɻ
    ਪ঑஋ 0.0025(มߋແ͠)
    νϡʔχϯά(22/46)

    View full-size slide

  91. ύϥϝʔλʔ໊ parallel_tuple_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0.1
    ղઆ
    ύϥϨϧϫʔΧʔϓϩηε͔Βɺ1ߦΛଞͷϓϩηεʹస
    ૹ͢ΔͨΊͷίετʹର͢Δɺϓϥϯφʔͷਪఆ஋Λઃ
    ఆ͠·͢ɻ
    ਪ঑஋ 0.1(มߋແ͠)
    νϡʔχϯά(23/46)

    View full-size slide

  92. ύϥϝʔλʔ໊ parallel_setup_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 1000.0
    ղઆ
    ύϥϨϧϫʔΧʔϓϩηεΛىಈ͢ΔͨΊͷίετʹର
    ͢Δɺϓϥϯφʔͷਪఆ஋Λઃఆ͠·͢ɻ
    ਪ঑஋ 1000.0(มߋແ͠)
    νϡʔχϯά(24/46)

    View full-size slide

  93. ύϥϝʔλʔ໊ jit_above_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 100000
    ղઆ
    JITίϯύΠϧ͕༗ޮʹͳΔΫΤϦͷίετΛઃఆ͠·
    ͢ɻ
    JITͷىಈʹ͸͕͔͔࣌ؒΔͨΊɺ͙͢ʹ͸ىಈ͠ͳ͍Α
    ͏ʹͳ͍ͬͯ·͢ɻ
    “-1”Λઃఆ͢ΔͱJITίϯύΠϧ͕ແޮԽ͞Ε·͢ɻ
    ਪ঑஋ 100000(มߋແ͠)
    νϡʔχϯά(25/46)

    View full-size slide

  94. ύϥϝʔλʔ໊ jit_inline_above_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 500000
    ղઆ
    JITίϯύΠϧ͕ؔ਺΍ԋࢉࢠΛΠϯϥΠϯԽ͢Δ৔߹ͷ
    ΫΤϦίετΛઃఆ͠·͢ɻ
    “-1”Λઃఆ͢ΔͱJITίϯύΠϧʹΑΔΠϯϥΠϯԽ͕ແ
    ޮԽ͞Ε·͢ɻ
    ਪ঑஋ 500000(มߋແ͠)
    νϡʔχϯά(26/46)

    View full-size slide

  95. ύϥϝʔλʔ໊ jit_optimize_above_cost
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 500000
    ղઆ
    JITίϯύΠϧͷ࠷దԽʹ͔͔ΔΫΤϦίετΛઃఆ͠·
    ͢ɻ
    “-1”Λઃఆ͢ΔͱJITίϯύΠϧͷ࠷దԽ͕ແޮԽ͞Ε·
    ͢ɻ
    ਪ঑஋ 500000(มߋແ͠)
    νϡʔχϯά(27/46)

    View full-size slide

  96. ύϥϝʔλʔ໊ min_parallel_table_scan_size
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 8MB
    ղઆ
    ύϥϨϧεΩϟϯΛߟྀ͢Δ࠷খͷςʔϒϧͷσʔλα
    ΠζΛઃఆ͠·͢ɻ
    ਪ঑஋ 8MB(มߋແ͠)
    νϡʔχϯά(28/46)

    View full-size slide

  97. ύϥϝʔλʔ໊ min_parallel_index_scan_size
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 512KB
    ղઆ
    ύϥϨϧεΩϟϯ࣮ߦ࣌ʹඞͣεΩϟϯ͢ΔΠϯσοΫ
    εσʔλͷ࠷খ஋Λઃఆ͠·͢ɻ
    ϓϥϯφʔͷίετਪఆ༻ͱͳΓ·͢ɻ
    ਪ঑஋ 512KB(มߋແ͠)
    νϡʔχϯά(29/46)

    View full-size slide

  98. ύϥϝʔλʔ໊ effective_cache_size
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 4GB
    ղઆ
    ໰͍߹Θͤ࣌ʹͲͷఔ౓Ωϟογϡώοτ͢Δ͔Λ༧ଌ
    ͢ΔͨΊͷΩϟογϡαΠζΛઃఆ͠·͢ɻ
    PostgreSQLͷڞ༗όοϑΝʔ͚ͩͰͳ͘ɺOSͷϑΝΠ
    ϧΩϟογϡ΍σΟεΫΩϟογϡ౳Λߟྀ͢Δඞཁ͕
    ͋Γ·͢ɻ
    ࣮ࡍʹ͸ϝϞϦʔͷ֬อ͸ߦΘΕͣɺϓϥϯφʔͷίε
    τਪఆ༻ͱͳΓ·͢ɻ
    ਪ঑஋ γεςϜϝϞϦʔͷ50%ͷ༰ྔ
    νϡʔχϯά(30/46)

    View full-size slide

  99. ύϥϝʔλʔ໊ autovacuum_max_workers
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 3
    ղઆ
    ಉ࣌ʹ࣮ߦ͢ΔࣗಈόΩϡʔϜॲཧͷ࠷େϫʔΧʔ਺Λ
    ઃఆ͠·͢ɻ
    ਪ঑஋
    σʔλϕʔεαΠζͷ20%Λ௒͑Δେ͖ͳςʔϒϧ਺
    + 1
    νϡʔχϯά(31/46)

    View full-size slide

  100. ύϥϝʔλʔ໊ autovacuum_vacuum_threshold
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 50
    ղઆ
    όΩϡʔϜॲཧʹඞཁͳɺߋ৽·ͨ͸࡟আ͞Εͨλϓϧ
    ਺ͷ࠷খ஋Λઃఆ͠·͢ɻ
    ͜ͷύϥϝʔλʔ͸ɺશςʔϒϧʹରͯ͠దԠ͞Ε·
    ͢ɻ
    ਪ঑஋ 50(มߋແ͠)
    νϡʔχϯά(32/46)

    View full-size slide

  101. ύϥϝʔλʔ໊ autovacuum_analyze_threshold
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 50
    ղઆ
    ߋ৽ॲཧʹඞཁͳɺߋ৽·ͨ͸࡟আ͞Εͨλϓϧ਺ͷ࠷
    খ஋Λઃఆ͠·͢ɻ
    ͜ͷύϥϝʔλʔ͸ɺશςʔϒϧʹରͯ͠దԠ͞Ε·
    ͢ɻ
    ਪ঑஋ 50(มߋແ͠)
    νϡʔχϯά(33/46)

    View full-size slide

  102. ύϥϝʔλʔ໊ autovacuum_vacuum_scale_factor
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0.2
    ղઆ
    όΩϡʔϜॲཧʹඞཁͳɺߋ৽͞Εͨςʔϒϧߦ਺ͷׂ
    ߹Λઃఆ͠·͢ɻ
    ͜ͷύϥϝʔλʔ͸ɺશςʔϒϧʹରͯ͠దԠ͞Ε·
    ͢ɻ
    ਪ঑஋ 0.2(มߋແ͠)
    νϡʔχϯά(34/46)

    View full-size slide

  103. ύϥϝʔλʔ໊ autovacuum_analyze_scale_factor
    ൓ө(ର৅) ཁϦϩʔυ
    σϑΥϧτ஋ 0.1
    ղઆ
    ߋ৽ॲཧʹඞཁͳɺߋ৽͞Εͨςʔϒϧߦ਺ͷׂ߹Λઃ
    ఆ͠·͢ɻ
    ͜ͷύϥϝʔλʔ͸ɺશςʔϒϧʹରͯ͠దԠ͞Ε·
    ͢ɻ
    ਪ঑஋ 0.1(มߋແ͠)
    νϡʔχϯά(35/46)

    View full-size slide

  104. ύϥϝʔλʔ໊ search_path
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ "$user", public
    ղઆ
    ΫΤϦ౳͕εΩʔϚΛࢦఆ͍ͯ͠ͳ͍৔߹ɺεΩʔϚΛ
    ݕࡧ͢Δॱ൪Λઃఆ͠·͢ɻ
    “$user”͸ɺSESSION_USERͱಉ͡εΩʔϚ͕͋Ε͹ɺ
    ஔ׵͞Ε·͢ɻ
    ཁ݅ʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ "$user", public(มߋແ͠)
    νϡʔχϯά(36/46)

    View full-size slide

  105. ύϥϝʔλʔ໊ row_security
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ on
    ղઆ
    ߦηΩϡϦςΟϙϦγʔͷదԠʹΑͬͯΤϥʔΛൃੜ͞
    ͤΔ͔ઃఆ͠·͢ɻ
    “off”͸ɺҰͭͰ΋ϙϦγʔ͕దԠ͞ΕͨΫΤϦ͕Τϥʔ
    ͱͳΓ·͢ɻ
    ߦηΩϡϦςΟϙϦγʔʹ͍ͭͯ͸ɺެࣜυΩϡϝϯτ
    Λ͝ࢀর͍ͩ͘͞ɻ
    https://www.postgresql.jp/document/10/html/sql-
    createpolicy.html
    ਪ঑஋ on(มߋແ͠)
    νϡʔχϯά(37/46)

    View full-size slide

  106. ύϥϝʔλʔ໊ default_tablespace
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ ’’(ۭന)
    ղઆ
    CREATEίϚϯυͰ໌ࣔతʹςʔϒϧεϖʔεΛࢦఆͯ͠
    ͍ͳ͍৔߹ʹɺΦϒδΣΫτͷ࡞੒ઌͱͳΔσϑΥϧτ
    ͷςʔϒϧεϖʔεΛઃఆ͠·͢ɻ
    ۭനͷ৔߹͸ɺݱࡏͷσʔλϕʔεͷσϑΥϧτςʔϒϧ
    εϖʔεΛ࢖༻͠·͢ɻ
    Ұ࣌ςʔϒϧ͸ɺtemp_tablespacesͰࢦఆ͠·͢ɻ
    ਪ঑஋ ’’(มߋͳ͠)
    νϡʔχϯά(38/46)

    View full-size slide

  107. ύϥϝʔλʔ໊ temp_tablespaces
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ ’’(ۭന)
    ղઆ
    CREATEίϚϯυͰ໌ࣔతʹςʔϒϧεϖʔεΛࢦఆͯ͠
    ͍ͳ͍৔߹ʹɺҰ࣌ΦϒδΣΫτͷ࡞੒ઌͱͳΔσϑΥ
    ϧτͷςʔϒϧεϖʔεΛઃఆ͠·͢ɻ
    ۭനͷ৔߹͸ɺݱࡏͷσʔλϕʔεͷσϑΥϧτςʔϒϧ
    εϖʔεΛ࢖༻͠·͢ɻ
    ਪ঑஋ ’’(มߋͳ͠)
    νϡʔχϯά(39/46)

    View full-size slide

  108. ύϥϝʔλʔ໊ statement_timeout
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0
    ղઆ
    ίϚϯυ͕ΫϥΠΞϯτ͔Βαʔόʔʹ౸ୡ͔ͯ͠Βλ
    ΠϜΞ΢τ͢Δ·Ͱͷ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    0ͷ৔߹͸λΠϜΞ΢τػೳΛແޮԽ͠·͢ɻ
    ਪ঑஋ 0(มߋͳ͠)
    νϡʔχϯά(40/46)

    View full-size slide

  109. ύϥϝʔλʔ໊ lock_timeout
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0
    ղઆ
    σʔλϕʔεΦϒδΣΫτͷϩοΫ଴ͪͰλΠϜΞ΢τ
    ͢Δ·Ͱͷ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    0ͷ৔߹͸λΠϜΞ΢τػೳΛແޮԽ͠·͢ɻ
    ਪ঑஋ 0(มߋͳ͠)
    νϡʔχϯά(41/46)

    View full-size slide

  110. ύϥϝʔλʔ໊ idle_in_transaction_session_timeout
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0
    ղઆ
    ΞΠυϧঢ়ଶͷτϥϯβΫγϣϯ͕ηογϣϯλΠϜΞ
    ΢τ͢Δ·Ͱͷ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    0ͷ৔߹͸λΠϜΞ΢τػೳΛແޮԽ͠·͢ɻ
    ਪ঑஋ 0(มߋͳ͠)
    νϡʔχϯά(42/46)

    View full-size slide

  111. ύϥϝʔλʔ໊ vacuum_cleanup_index_scale_factor
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ 0.1
    ղઆ
    B-treeΠϯσοΫεͷόΩϡʔϜॲཧΛ࣮ߦ͢ΔͨΊ
    ͷɺલճͷόΩϡʔϜॲཧ͔Βมߋ͞Εͨߦ਺ͷׂ߹
    Λઃఆ͠·͢ɻ
    σϑΥϧτ஋Ͱ͸ɺ0.1%ͷมߋ͕͋ΔςʔϒϧͷΈΠ
    ϯσοΫεͷόΩϡʔϜ͕࣮ߦ͞Ε·͢ɻ
    PostgreSQL11Ͱ௥Ճ͞Εͨ৽ύϥϝʔλʔͰ͢ɻ
    ਪ঑஋ 0.1(มߋͳ͠)
    νϡʔχϯά(43/46)

    View full-size slide

  112. ύϥϝʔλʔ໊ client_encoding
    ൓ө(ର৅) ଈ࣌൓ө(શϢʔβʔ)
    σϑΥϧτ஋ sql_ascii
    ղઆ
    ΫϥΠΞϯτଆͷจࣈηοτΛઃఆ͠·͢ɻ
    ΞϓϦέʔγϣϯʹ߹Θͤͯมߋ͍ͯͩ͘͠͞ɻ
    ਪ঑஋ sql_ascii(มߋͳ͠)
    νϡʔχϯά(44/46)

    View full-size slide

  113. ύϥϝʔλʔ໊ deadlock_timeout
    ൓ө(ର৅) ଈ࣌൓ө(εʔύʔϢʔβʔ)
    σϑΥϧτ஋ 1s
    ղઆ
    ϩοΫ଴ͪͷঢ়ଶʹͳͬͨࡍʹɺσουϩοΫͷݕग़ॲ
    ཧΛ։࢝͢Δ·Ͱͷ଴ͪ࣌ؒΛઃఆ͠·͢ɻ
    ϛϦඵ୯ҐͰࢦఆ͕ՄೳͰ͢ɻ
    σουϩοΫͷݕग़ʹ͸ίετ͕ߴ͍ͨΊɺΞϓϦέʔ
    γϣϯଆͰσουϩοΫ͕ى͜Βͳ͍Α͏ʹ޻෉ͯ͘͠
    ͍ͩ͞ɻ
    ਪ঑஋ 10s
    νϡʔχϯά(45/46)

    View full-size slide

  114. ύϥϝʔλʔ໊
    max_locks_per_transaction,
    max_pred_locks_per_transaction
    ൓ө(ର৅) ཁϦελʔτ
    σϑΥϧτ஋ 64
    ղઆ
    τϥϯβΫγϣϯͷฏۉϩοΫ਺ͷ஋Λઃఆ͠·͢ɻ
    ୯ҰͷτϥϯβΫγϣϯͰɺଟ਺ͷςʔϒϧͷϩοΫ͕
    ൃੜ͢ΔΞϓϦέʔγϣϯͷ৔߹͸ɺ஋Λ૿΍͢ඞཁ͕
    ͋Γ·͢ɻ
    ਪ঑஋ 64(มߋແ͠)
    νϡʔχϯά(46/46)

    View full-size slide

  115. ͓·͚: ϝϞϦʔͷׂΓ౰ͯྫ
    GB
    0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
    2.1GB
    7.4GB
    4GB
    2.5GB
    OSɾͦͷଞ shared_buffers όοΫΤϯυϓϩηε ۭ͖༰ྔ
    αʔόʔϝϞϦʔ͕16GBͰOSͦͷଞΞϓϦͰ2.5GBΛ࢖༻͠ɺ
    όοΫΤϯυϓϩηεͷίϯϑΟάઃఆ͕σϑΥϧτͷ৔߹͸
    ҎԼͷΑ͏ʹͳΓ·͢ɻ
    γεςϜϝϞϦʔͷ25%
    (8MB + 4MB + 64MB) * 100઀ଓ
    = 7600MB ≒ 7.4GB

    View full-size slide

  116. ͓·͚: ΧʔωϧϦιʔεͷมߋ
    LinuxΧʔωϧͰͷϦιʔεมߋʹ͍ͭͯهࡌ͠·͢ɻ
    • ڞ༗ϝϞϦɾηάϝϯτͷ্ݶઃఆύϥϝʔλʔ: kernel.shmmax

    ׂΓ౰͍ͯͨϝϞϦͷόΠτ਺Λɺgetconf PAGE_SIZEͷ஋Ͱׂͬͨ஋
    • γεςϜશମͷڞ༗ϝϞϦɾϖʔδ਺ͷ࠷େ஋ઃఆύϥϝʔλʔ: kernel.shmall

    kernel.shmmax * kernel.shmmni / getconf PAGE_SIZE / 16
    • ώϡʔδϖʔδͷઃఆ: vm.nr_hugepages

    ώϡʔδϖʔδͷνϡʔχϯά͸ɺ࣮ࡍʹPostgreSQLΛ࣮ߦͯ֬͠ೝΛߦ͍·͢ɻ
    1. Ծ૝ϝϞϦͷϐʔΫ࣌αΠζΛௐࠪ͠·͢ɻ

    grep ^VmPeak /proc/`cat /var/run/postgresql*.pid | head -1`/status
    2. ώϡʔδϖʔδαΠζΛௐࠪ͠·͢ɻ

    grep ^Hugepagesize /proc/meminfo
    3. ώϡʔδϖʔδઃఆύϥϝʔλʔʹܭࢉ஋Λઃఆ͠·͢ɻ: vm.nr_hugepages

    ceil( 2ͷ஋ / 3ͷ஋ )

    View full-size slide

  117. ऴΘΓʹ
    ύϥϝʔλʔʹΑΔνϡʔχϯά͸ɺ

    ࣮ࡍͷӡ༻Ͱ͸αʔόʔߏ੒΍౤ೖ͞ΕΔσʔλྔɺ
    ൃߦ͞ΕΔSQLʹ߹Θͤͯɺ࠷దͳ஋͕ҟͳΓ·͢ɻ
    ͜·ΊʹPostgreSQLͷঢ়ଶΛ֬ೝ͠ɺ
    ҆ఆՔಇΛ໨ࢦ͍ͯͩ͘͠͞ɻ

    View full-size slide

  118. ྑ͍PostgreSQLϥΠΫΛʂ
    ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ

    View full-size slide