PostgreSQL Architecture And Performance Monitoring

PostgreSQLͷ಺෦ߏ଄
ͱ
PostgreSQLͷ؂ࢹͷجຊ
1PTUHSF42-ΧϯϑΝϨϯε

View Slide

Φεεϝຊʂ
͚ͩͲͷ࿩

View Slide

ɹQHͷ
ೖ໳هࣄ͕͋Δ

View Slide

What is it?
ͳͥ؂ࢹʢϞχλϦϯάʣ͕ඞཁ͔

View Slide

What is it?
• ͍ͪૣ͘ো֐ൃੜΛ֬ೝͰ͖ΔΑ͏ʹ͢ΔͨΊ
• ෮چʹ͍ͪૣ͘औΓֻ͔ΕΔΑ͏ʹ͢ΔͨΊ
• ঢ়گͷมԽΛ࣌ܥྻͰ؅ཧ͢Δ͜ͱͰɺΩϟύγςΟ
ϓϥϯχϯά΍ো֐ͷ༧ஹͷ೺Ѳʹ໾ཱͯΔͨΊ

View Slide

What is it?
αʔϏεΛਖ਼͘͠ӡ༻͢ΔͨΊʹ͸
ඞཁෆՄܽͳࣄʂ

View Slide

What is it?
PostgreSQLΛݟΔ

View Slide

What is it?
PostgreSQLΛݟΔ
↓
ͦͯ͠஌Δ

View Slide

What is it?
ਖ਼͘͠ӡ༻͢ΔͨΊͷҰา໨

View Slide

͋͐͡Μͩ
̍ɹࣗݾ঺հ
̎ɹPostgreSQLͷ಺෦ߏ଄
̏ɹPostgreSQLͷ౷ܭ৘ใ
̐ɹPostgreSQLͷϞχλϦϯά
̑ɹ·ͱΊ

View Slide

ࣗݾ঺հ
໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
೥ྸɿ33ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
৬ۀɿCustomer Reliability Engineering
ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ
ɹɹɹ೔ຊPostgreSQLϢʔβձ
ɹɹɹɹɹɹ ษڧձ୲౰
ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

View Slide

Mackerel

View Slide

͋͐͡Μͩ
̍ɹࣗݾ঺հ
̑ɹ·ͱΊ

View Slide

ԿΛϞχλϦϯά͢Δ͔ʁ

View Slide

ˣ
಺෦ߏ଄Λ஌Βͳ͍ͱ
஋͕ཧղͰ͖ͳ͍

View Slide

PostgreSQLͷϓϩηε
L
L
E
E
L
X E
I N
D
AE
E
W

W
E
E

View Slide

ϓϩηε໊ આ໌
Ϛελʔαʔό ࠷ॳʹىಈ͞ΕΔ਌ϓϩηε
ϥΠλ ڞ༗όοϑΝͷ಺༰ΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ
WALϥΠλ WALόοϑΝͷ಺༰ΛWALϑΝΠϧʹॻ͖ग़͢ɻ
νΣοΫϙΠϯλ શͯͷμʔςΟʔϖʔδΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ
ࣗಈVACUUMϥϯνϟ ઃఆʹ͕ͨͬͯࣗ͠ಈVACUUMϫʔΧΛىಈ͢Δɻ
ࣗಈVACUUMϫʔΧ ࣗಈVACUUMΛ࣮ߦ͢Δɻෳ਺ىಈ͢Δ͜ͱ͕͋Δɻ
౷ܭ৘ใίϨΫλ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใΛऩू͢Δɻ
όοΫΤϯυϓϩηε ΫϥΠΞϯτͷ઀ଓཁٻຖʹىಈ͠ɺཁٻʹରͯ͠ॲཧ͢Δɻ
ϩΨʔ PostgreSQLͷϩάΛϑΝΠϧ΁ॻ͖ग़͢ɻ
ΞʔΧΠό WALϩάΛΞʔΧΠϒ͢Δɻ
WALηϯμ ϨϓϦέʔγϣϯ࣌ʹWALΛεϨʔϒαʔόʹసૹ͢Δɻ
WALϨγʔό ϨϓϦέʔγϣϯ࣌ʹWALΛϚελʔαʔό͔Βड৴͢Δɻ
ओͳϓϩηε܈

View Slide

ओͳϝϞϦ܈
໊લ આ໌
ڞ༗όοϑΝ
(shared_buffers)
ςʔϒϧ΍ΠϯσοΫεͷσʔλΛΩϟογϡ͢ΔྖҬͰ͢ɻ
WALόοϑΝ
(wal_buffers)
σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍τϥϯβΫγϣϯϩάΛΩϟογϡ͢ΔྖҬͰ͢ɻ
ՄࢹੑϚοϓ
(Visibility Map)
ςʔϒϧͷσʔλ͕ࢀরग़དྷΔ͔൱͔؅ཧ͢Δ৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹॲཧର৅ͷϖʔ
δ͔൑அ͢Δࡍʹར༻͞Ε·͢ɻ·ͨՄࢹੑϚοϓ͸VACUUMॲཧ΍֤ߋ৽ॲཧͷࡍʹߋ৽͞Ε·͢ɻ
PostgreSQL 9.2Ҏ߱Ͱ͸ΠϯσοΫεɾΦϯϦʔɾεΩϟϯͱݴ͏ͱͯ΋ߴ଎ͳݕࡧํࣜͷࡍʹ΋ར༻͞
Ε·͢ɻ
ۭ͖ྖҬϚοϓ
(Free Scan Map)
ςʔϒϧ্ͷར༻ՄೳͳྖҬΛࢦࣔ͢͠৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹશ͘ࢀর͞Ε͍ͯͳ͍
ߦΛ୳ۭ͖ͯ͠ྖҬͱͯ͠࠶ར༻ग़དྷΔঢ়ଶʹ͠·͢ɻͦͷޙɺ௥Ճ΍ߋ৽࣌ʹۭ͖ྖҬϚοϓΛ୳ࡧ
͠ɺۭ͖ྖҬΛ࠶ར༻͠·͢ɻ

View Slide

໊લ આ໌
σʔλϑΝΠϧ
ςʔϒϧσʔλͷ࣮ମ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧ͸ෳ਺ͷ8192όΠτͷϖʔδ
(OracleDBͰ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ
̍Fileͷ࠷େ͸1GBͰ͢ɻ
INDEXϑΝΠϧ
INDEX৘ใ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧͱಉ༷ʹෳ਺ͷ8192όΠτͷϖʔδ(OracleDB
Ͱ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ
WALϑΝΠϧ
Write Ahead LoggingͷུͰτϥϯβΫγϣϯϩάΛPostgreSQLͰ͸WALͱݺͼ·͢ɻߋ৽ʹؔΘΔ৘ใ
ΛهԱ͢Δ͜ͱͰσʔλϕʔεͷӬଓੑͷอূΛߦ͍ͬͯ·͢ɻpg_xlogʢPostgreSQL 10͔Β͸pg_walʣ
σΟϨΫτϦ഑Լʹอଘ͞Εɺ16MBͷݻఆαΠζͰ࡞੒͞Ε·͢ɻ
ओͳϑΝΠϧ܈

View Slide

όοΫΤϯυϓϩηε
PostgreSQLͷϝϞϦ
όοΫΤϯυϓϩηε
ϫʔΫϝϞϦ ϫʔΫϝϞϦ
ڞ༗όοϑΝ 8"-όοϑΝ
04ͷόοϑΝΩϟογϡ
σʔλϑΝΠϧ 8"-
%#
σΟεΫྖҬ
ϝϞϦྖҬ
ϓϩηεྖҬ

View Slide

PostgreSQLͷσʔλ
%#ྖҬ 8"-ྖҬ
ΞʔΧΠϒ
8"-ྖҬ
σΟεΫྖҬ
ςʔϒϧϑΝΠϧ
*/%&9ϑΝΠϧ
8"-
ΞʔΧΠϒ
ϑΝΠϧ
7.
'4.

View Slide

PostgreSQLͷσʔλߏ଄
(#ͷςʔϒϧ
ςʔϒϧϑΝΠϧ
(#
7.
'4.
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
ộ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ộ
ߦσʔλ
ߦσʔλ
ςʔϒϧϑΝΠϧ
(#

View Slide

(#ͷςʔϒϧ
ςʔϒϧϑΝΠϧ
(#
7.
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
ộ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ộ
ߦσʔλ
ߦσʔλ
ςʔϒϧϑΝΠϧ
(#
ςʔϒϧͷϖʔδϔομ͸
CZUFݻఆ
'4.

View Slide

(#ͷςʔϒϧ
ςʔϒϧϑΝΠϧ
(#
7.
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
ộ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ộ
ߦσʔλ
ߦσʔλ
ΞΠςϜϙΠϯλ ߦϔομ

͸CZUF
ςʔϒϧϑΝΠϧ
(#
'4.

View Slide

(#ͷςʔϒϧ
ςʔϒϧϑΝΠϧ
(#
7.
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
,#ϖʔδ
ộ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ộ
ߦσʔλ
ߦσʔλ
ςʔϒϧϑΝΠϧ
(#
࣮ࡍͷϨίʔυͷαΠζ
'4.

View Slide

PostgreSQLͷߦσʔλߏ଄
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ộ
ߦσʔλ
ߦσʔλ
JE ໊લ ࡞੒೔࣌
IPHF
'VHB
'PP
#BS
JOU 5FYU UJNFTUBNQ
CZUF CZUFจࣈྻͷCZUF CZUF

View Slide

PostgreSQLͷINDEXσʔλߏ଄
ϖʔδϔομ
ϊʔυϔομ
ϊʔυϔομ
ộ
*/%&9ΤϯτϦ
*/%&9ΤϯτϦ
JE ໊લ ࡞੒೔࣌
IPHF
'VHB
'PP
#BS
JOU
CZUF

View Slide

ϖʔδϔομ
ϊʔυϔομ
ϊʔυϔομ
ộ
*/%&9ΤϯτϦ
*/%&9ΤϯτϦ
JE ໊લ ࡞੒೔࣌
IPHF
'VHB
'PP
#BS
JOU
CZUF
ओΩʔͷ*/%&9ͷ৔߹
JEྻͷ஋Λ֤*/%&9ΤϯτϦ
ʹอ࣋͢Δ

View Slide

ϖʔδϔομ
ϊʔυϔομ
ϊʔυϔομ
ộ
*/%&9ΤϯτϦ
*/%&9ΤϯτϦ
JE ໊લ ࡞੒೔࣌
IPHF
'VHB
'PP
#BS
JOU
CZUF
ϊʔυϔομ͸CZUF
ϖʔδϔομͷݻఆ͸CZUF

View Slide

PostgreSQLͷσʔλ
%#ྖҬ 8"-ྖҬ
ΞʔΧΠϒ
8"-ྖҬ
σΟεΫྖҬ
ςʔϒϧϑΝΠϧ
*/%&9ϑΝΠϧ
8"-
ΞʔΧΠϒ
ϑΝΠϧ
7.
'4.
௨ৗ͸ͭ.#ͷ8"-ηάϝϯτ
ϑΝΠϧΛ॥؀ར༻
ෆཁͳ8"-͸ࣗಈతʹॳظԽ͞ΕΔ
ʢNBY@XBM@TJ[FͷઃఆˡҎ߱ʣ

View Slide

PostgreSQLͷϓϩηε
L
L
E
E
L
X E
I N
D
AE
E
W

W
E
E

View Slide

PostgreSQLͷσʔλ
%#ྖҬ 8"-ྖҬ
ΞʔΧΠϒ
8"-ྖҬ
σΟεΫྖҬ
ςʔϒϧϑΝΠϧ
*/%&9ϑΝΠϧ
8"-
ΞʔΧΠϒ
ϑΝΠϧ
7.
'4.
ΞʔΧΠϒϞʔυΛ0/ʹ͢Δͱ8"-
ͷফࣦΛ๷͙ͨΊʹຊདྷ॥؀͢Δ8"-
Λ࢒͓ͯ͘͠

View Slide


View Slide

ˣ
42-ͷྲྀΕΛ஌Βͳ͍ͱ
Կ͕ى͍ͬͯ͜Δ͔Θ͔Βͳ͍

View Slide

2VFSZͷड৴
ߏจղੳ
ॻ͖׵͑
࣮ߦܭըੜ੒࠷దԽ
࣮ߦ
݁Ռૹ৴
1BSTFS
42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจ໦ͷੜ੒
3FXSJUFS
7JFXɾ3VMFʹجͮ͘ߏจ໦ͷॻ͖׵͑
1MBOOFS0QUJNBJ[FS
࣮ߦܭըͷੜ੒౷ܭ৘ใͳͲΛར༻ͨ͠࠷దԽ
&YFDVUPS
࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷ௥هͳͲ
42-จͷॲཧ͞ΕΔྲྀΕ

View Slide

'30.۟
0/۟
+0*/۟
8)&3&۟
(3061#:۟
)"7*/(۟
4&-&$5۟
%*45*/$5۟
03%&3#:۟
-*.*5۟
42-จͷධՁ͞ΕΔॱ
IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM

View Slide

'30.۟
0/۟
+0*/۟
8)&3&۟
(3061#:۟
)"7*/(۟
4&-&$5۟
%*45*/$5۟
03%&3#:۟
-*.*5۟
42-จͷධՁ͞ΕΔॱ
IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM
ͲΕ͘Β͍ͷσʔλΛѻ͏͔
೗ԿʹσʔλΛߜΓࠐΉ͔

View Slide

1PTUHSF42-͸௥هܕ

View Slide

1
2
3

1
3

2
PostgreSQL(

View Slide

1
2
3

1
3
2

2
PostgreSQL(

View Slide

1
2
3

1
3
2

2
PostgreSQL(

͜ͷ࢒ͬͨچϨίʔυ
Λ៉ྷ͢Δͷ͕7"$66.

View Slide

1PTUHSF42-͸௥هܕ

View Slide

1PTUHSF42-͸௥هܕ
ˣ
)05 )FBQ0OMZ5VQMF
ͱ
'*--'"$503

View Slide

HOTͱFILLFACTOR
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ߦσʔλ
ߦσʔλ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ`
ߦσʔλ`
ߦσʔλ
ߦσʔλ
ߦσʔλΛ
ߋ৽

View Slide

HOTͱFILLFACTOR
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ߦσʔλ
ߦσʔλ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ`
ߦσʔλ`
ߦσʔλ
ߦσʔλ
ߦσʔλΛ
ߋ৽
͜ͷۭ͖ྖҬΛ؅ཧ͢Δ࢓૊Έ͕
'*--'"$503
ςʔϒϧϑΝΠϧͷσϑΥϧτ͸
ͳͷͰۭ͖ྖҬΛ࡞Βͳ͍

View Slide

HOTͱFILLFACTOR
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ߦσʔλ
ߦσʔλ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ`
ߦσʔλ`
ߦσʔλ
ߦσʔλ
ߦσʔλΛ
ߋ৽
ݕࡧ࣌ͷಈ࡞
ΞΠςϜϙΠϯλΛ֬ೝ
ΞΠςϜϙΠϯλ`Λ֬ೝ
ߦσʔλ`Λऔಘ

View Slide

HOTͱFILLFACTOR
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ߦσʔλ
ߦσʔλ
ϖʔδϔομ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ
ΞΠςϜϙΠϯλ`
ߦσʔλ`
ߦσʔλ
ߦσʔλ
ߦσʔλΛ
ߋ৽
ۭ͖ྖҬΛ࢖͑Δͱ*/%&9Λߋ
৽͢Δ͜ͱແ͘σʔλΛߋ৽
ݹ͍Ϩίʔυ͸7"$66.Λ଴
ͨͣʹ࠶ར༻Մೳ

View Slide

HOTͱFILLFACTOR
)05ߋ৽͕ར༻Ͱ͖Δ৚݅
w ಉҰϖʔδ಺ʹߋ৽ޙͷσʔλΛ
อଘͰ͖Δ
w *OEFYྻΛߋ৽͠ͳ͍

View Slide

1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ

View Slide

1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ
ˣ
ϞχλϦϯάͷצॴ͕ݟ͑ͯ͘Δ

View Slide

͋͐͡Μͩ
̍ɹࣗݾ঺հ
̑ɹ·ͱΊ

View Slide

PostgreSQLͷ౷ܭ৘ใ
౷ܭ৘ใίϨΫλ͕
αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใΛऩू

View Slide

L
L
E
E
L
X E
I N
D
AE
E
W

W
E
E

View Slide

αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ

View Slide

αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ
ˣ
౷ܭ৘ใ

View Slide

౷ܭ৘ใ
ެࣜυΩϡϝϯτ
IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMNPOJUPSJOHTUBUTIUNM

View Slide

QPTUHSFTRMDPOGͰ
QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ

View Slide

QPTUHSFTRMDPOGͰ
QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ
42-จͷ࣮ߦ࣌ؒɺ࣮ߦճ਺ɺऔಘͨ͠ʢ·ͨ͸ߋ৽ͨ͠ʣ
Ϩίʔυ਺ͳͲ͕อଘ͞ΕΔ

View Slide

౷ܭ৘ใΛ·ͱΊͨศརͳ7JFX

View Slide

౷ܭ৘ใͷओͳView
7JFX໊ આ໌
QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ
QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ
QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ
QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ
QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ
QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ
QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ
QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ

View Slide

7JFX໊ આ໌
νΣοΫϙΠϯτͷ࣮ߦճ਺΍࣮ߦ࣌ؒͳͲ͕ݟΕΔ
ߋ৽͕ܹ͍͠৔߹ʹ͸νΣοΫϙΠϯτ͕ॏཁͳՕॴʹͳΔ

View Slide

7JFX໊ આ໌
ࠓ·͞ʹ࣮ߦ͞Ε͍ͯΔ42-ͳͲ͕Θ͔Δ
2VFSZ͕͍ࢗͬͯ͞Δͱ͖΍Ұ෦ͷ8FCαʔόͷΞΫηε͕ॏ͍౳͕ݟ͑ͯ͘Δ

View Slide

7JFX໊ આ໌
σʔλϕʔεશମͷࢀরɾ௥Ճɾߋ৽ɾ࡟আճ਺ͳͲ͕ݟΕΔ
σουϩοΫͷճ਺΍Ұ࣌ϑΝΠϧͷ࡞੒ճ਺΋ݟΕΔͷͰʮٸʹ%#͕ॏ͘ͳΔ
લʹؾ෇͘ʯͨΊʹܧଓతʹϞχλϦϯά͕େࣄ

View Slide

7JFX໊ આ໌
QH@TUBU@EBUBCBTFΛΑΓৄࡉʹςʔϒϧ୯ҐͰݟΕΔ༷ʹͳͬͨ7JFX
ߋʹ)05ͷߋ৽ߦ਺΍ෆཁͳλϓϧɺ7"$66.ͷ࣮ߦճ਺ͳͲ͕ݟΕΔ

View Slide

7JFX໊ આ໌
QH@TUBU@VTFS@UBCMFTʹࣅ͍ͯΔ͕τϥϯβΫγϣϯதͷσʔλͷΈ͕දࣔ͞ΕΔ
QH@TUBU@YBDU@VTFS@UBCMFTʹදࣔ͞Ε͍ͯΔ৔߹͸ɺQH@TUBU@VTFS@UBCMFTʹ͸
ؚ·Ε͍ͯͳ͍

View Slide

7JFX໊ આ໌
*/%&9ͷར༻ঢ়گ͕Θ͔Δ7JFX
*/%&9Λར༻ͨ͠ճ਺ɺ*/%&9ʹؚ·ΕΔΧϥϜͷར༻ݸ਺ɺ*/%&9Λར༻ͯ͠
औΓग़ͨ͠ߦ਺ͳͲ͕ݟΕΔ
*OEFY0OMZ4DBO͕༗ޮ׆༻͞Ε͍ͯΔ৔߹͸JEY@UVQ@SFBEʹରͯ͠JEY@UVQ@GFUDI
͕গͳ͘ͳΔɻ
8)&3&ͷൣғ͕޿͍৔߹ͳͲ͸JEY@UVQ@GFUDIͷ૿ՃྔΛݟΔ͜ͱͰ܏޲Λ༧ଌ
͢Δ͜ͱ͕Ͱ͖Δ

View Slide

7JFX໊ આ໌
ςʔϒϧ୯Ґͷ*0͕Θ͔ΔͷͰόοϑΝΩϟογϡͷঢ়گΛܭࢉ͢Δ͜ͱ͕Ͱ͖Δ
IFBQ@CMLT@SFBE͕IFBQ@CMLT@IJUΑΓ΋͔ͳΓগͳ͍ͷͰ͋Ε͹΄΅ΧʔωϧΛݺͼ
ग़͢͜ͱແ݁͘ՌΛฦͤͯΔͷͰૣ͍͸ͣɻ
50"45ͷঢ়گ΋ݟΕΔͨΊɺେ͖ͳσʔλΛѻ͍ͬͯΔςʔϒϧͰ͸ཁ؂ࢹ

View Slide

7JFX໊ આ໌
QH@TUBUJP@VTFS@UBCMFTͷ*/%&9൛
όοϑΝΩϟογϡͷώοτ཰ͳͲܭࢉͰ͖Δ

View Slide

౷ܭ৘ใʹ͸42-ͰΞΫηεͰ͖Δ

View Slide

౷ܭ৘ใͷ׆༻ͷྫ
w ൃߦ͞Ε͍ͯΔ2VFSZ
w ϩοΫͷ಺༰
w */%&9ͷར༻ঢ়گ
w νΣοΫϙΠϯτͷॲཧঢ়گʜFUD

View Slide

Ωϟογϡώοτ཰ͷ֬ೝ
=# SELECT datname,
round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database WHERE blks_read > 0;
datname | cache_hit_ratio
----------+-----------------
postgres | 99.00
※ blks_hit+blks_read ʹ஫ҙ

View Slide

ςʔϒϧͷ
=# SELECT relname,
round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_tables
WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;
relname | cache_hit_ratio
------------------+-----------------
pgbench_accounts | 97.00
pgbench_tellers | 99.00
pgbench_history | 99.00
pgbench_branches | 99.00

View Slide

ΠϯσοΫεͷ
=# SELECT relname, indexrelname,
round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2)
AS cache_hit_ratio FROM pg_statio_user_indexes
WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;
relname | indexrelname | cache_hit_ratio
------------------+-----------------------+------------------
pgbench_tellers | pgbench_tellers_pkey | 90.00
pgbench_branches | pgbench_branches_pkey | 99.00
pgbench_accounts | pgbench_accounts_pkey | 99.00

View Slide

දεΩϟϯ͋ͨΓͷಡΈऔΓߦ਺ͷ֬ೝ
=# SELECT relname, seq_scan, seq_tup_read,
seq_tup_read/seq_scan AS tup_per_read FROM pg_stat_user_tables
WHERE seq_scan > 0 ORDER BY tup_per_read DESC;
relname | seq_scan | seq_tup_read | tup_per_read
------------------+----------+--------------+--------------
pgbench_accounts | 1 | 100000 | 100000
pgbench_tellers | 153613 | 1000010 | 6
pgbench_branches | 35659 | 16461 | 0

View Slide

)05ߋ৽ͷൺ཰ͷ֬ೝ
=# SELECT relname, n_tup_upd, n_tup_hot_upd,
round(n_tup_hot_upd*100/n_tup_upd, 2) AS hot_upd_ratio
FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY hot_upd_ratio;
relname | n_tup_upd | n_tup_hot_upd | hot_upd_ratio
------------------+-----------+---------------+---------------
pgbench_accounts | 100000 | 96079 | 96.00
pgbench_tellers | 100000 | 99921 | 99.00
pgbench_branches | 100000 | 99548 | 99.00

View Slide

-FUT1PTUHSFT
lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z
IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT

View Slide

͋͐͡Μͩ
̍ɹࣗݾ঺հ
̑ɹ·ͱΊ

View Slide

PostgreSQLͷϞχλϦϯά
౷ܭ৘ใΛ࢖͏ͱ
1PTUHSF42-ΛϞχλϦϯάͰ͖Δ

View Slide

͔͠͠ྺ࢙Λ࣋ͨͳ͍

View Slide

͔͠͠ྺ࢙Λ࣋ͨͳ͍
ˣ
࣌ܥྻ%#ʹอଘͯ͠ՄࢹԽ

View Slide


View Slide

߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ
ϓϩηε؂ࢹ ˓ ˓ ˓
༰ྔ؂ࢹ ☓ ˓ ˓
ϩά؂ࢹ ☓ ˓ ˓
ύϑΥʔϚϯε
؂ࢹ
☓ ☓ ˓

View Slide

༰ྔ؂ࢹ ☓ ˓ ˓
ϩά؂ࢹ ☓ ˓ ˓
ύϑΥʔϚϯε
؂ࢹ
☓ ☓ ˓
΋ͬͱ΋γϯϓϧ QH@JTSFBEZͰྑ͍

͔͠͠αʔϏε͕མͪΔ·ͰΘ͔Βͳ͍

View Slide

༰ྔ؂ࢹ ☓ ˓ ˓
ϩά؂ࢹ ☓ ˓ ˓
ύϑΥʔϚϯε
؂ࢹ
☓ ☓ ˓
ঢ়ଶͷมԽ͕Θ͔Δ
͔͠͠มԽͷࠩ෼͕ͳ͍ͨΊ
༧ஹʹ͸ؾ෇͖ʹ͍͘

View Slide

༰ྔ؂ࢹ ☓ ˓ ˓
ϩά؂ࢹ ☓ ˓ ˓
ύϑΥʔϚϯε
؂ࢹ
☓ ☓ ˓
աڈͱࠓͷࠩ෼Λൺֱ͢Δ͜ͱͰݱঢ়Λ
ΑΓਖ਼͘͠೺ѲͰ͖Δ
άϥϑͰՄࢹԽ͢Δ͜ͱͰΘ͔Γ΍͍͢

View Slide

1PTUHSF42-Λӡ༻͢ΔͨΊͷ؂ࢹ

View Slide

ϞχλϦϯάͷର৅
w σΟεΫ༰ྔ؂ࢹ
w 7"$66.؂ࢹ
w ϨϓϦέʔγϣϯ؂ࢹ
w αʔόϩά؂ࢹ

View Slide

w 7"$66.؂ࢹ
w αʔόϩά؂ࢹ
σΟεΫ༰ྔɺσʔλϕʔεɺΦϒδΣΫτ౳ͷ
αΠζΛ؂ࢹ

View Slide

σΟεΫ༰ྔ؂ࢹ
w σΟεΫ༰ྔ͸04ίϚϯυ EGɺEVrTͳͲ
Ͱ؂ࢹ
w ಛʹҎԼͷྖҬʹ஫ҙ
w σʔλϑΝΠϧ
w 8"-ϑΝΠϧ
w ΞʔΧΠϒྖҬ ϩʔΧϧσΟεΫʹ഑ஔ͍ͯ͠Δ৔߹

w σʔλϕʔε΍ΦϒδΣΫταΠζ͸ؔ਺Ͱ֬ೝ
w QH@EBUBCBTF@TJ[F bEBUBCBTF`

w QH@SFMBUJPO@TJ[F bPCKFDU`

w QH@UPUBM@SFMBUJPO@TJ[F bUBCMF`

View Slide

PostgreSQLͷσʔλ
%#ྖҬ 8"-ྖҬ
ΞʔΧΠϒ
8"-ྖҬ
σΟεΫྖҬ
ςʔϒϧϑΝΠϧ
*/%&9ϑΝΠϧ
8"-
ΞʔΧΠϒ
ϑΝΠϧ
7.
'4.
EG΍EVTͰ֬ೝ͢Δ
ΞʔΧΠϒϑΝΠϧ͸όοΫΞοϓͷ΍Γํ
΍ස౓ʹΑͬͯอଘ͢Δର৅͕͔ΘΔͷͰ
ઃܭ࣌ʹ͔ͬ͠Γͱݟੵ΋Δࣄ

View Slide

PostgreSQLͷσʔλ
%#ྖҬ 8"-ྖҬ
ΞʔΧΠϒ
8"-ྖҬ
σΟεΫྖҬ
ςʔϒϧϑΝΠϧ
*/%&9ϑΝΠϧ
8"-
ΞʔΧΠϒ
ϑΝΠϧ
7.
'4.
σʔλϕʔε΍ΦϒδΣΫταΠζ͸ؔ਺Ͱ֬ೝ
QH@SFMBUJPO@TJ[F
ʹ͸*/%&9໊΋ࢦఆͰ͖Δ
QH@UPUBM@SFMBUJPO@TJ[F
ʹ͸*/%&9ͳͲͷαΠζ΋ؚ·ΕΔ

View Slide

w 7"$66.؂ࢹ
w αʔόϩά؂ࢹ
౷ܭ৘ใ͔Β࣮ߦঢ়ଶΛ؂ࢹ͢Δ

View Slide

VACUUMͷඞཁੑ
7"$66.ͱ7"$66.'6--͕͋Δ
w 7"$66.Λ࣮ߦ͢Δ໨త
w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
w τϥϯβΫγϣϯ*%पճͷճආ
w 7"$66.'6--Λ࣮ߦ͢Δ໨త
w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ

View Slide

7"$66.ͱ7"$66.'6--͕͋Δ
w 7"$66.Λ࣮ߦ͢Δ໨త
w 7"$66.'6--Λ࣮ߦ͢Δ໨త
VACUUMͷඞཁੑ
QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ
͞Ε͍ͯΔࣄΛ֬ೝ͢Δ

View Slide

7"$66.ͱ7"$66.'6--͕͋Δ
w 7"$66.Λ࣮ߦ͢Δ໨త
w 7"$66.'6--Λ࣮ߦ͢Δ໨త
VACUUMͷඞཁੑ
QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ
͞Ε͍ͯΔࣄΛ֬ೝ͢Δ
7"$66.࣮ߦ࣌͸ύϑΥʔϚϯεྼԽͷτϦΨʔ
ʹͳΓ΍͍͢ͷͰ࣮ߦλΠϛϯάΛอଘ͢Δ

View Slide

w 7"$66.؂ࢹ
w αʔόϩά؂ࢹ
8"-ͷసૹঢ়گ΍λΠϜϥΠϯͳͲΛ֬ೝ͢Δ
ࠓ೔͸ϨϓϦέʔγϣϯͷηογϣϯ͕͋ΔͷͰׂѪ

View Slide

w 7"$66.؂ࢹ
w αʔόϩά؂ࢹ
Τϥʔϩάͷग़ྗ݁ՌͳͲΛ؂ࢹ͢Δ

View Slide

PostgreSQLͷϩά
MPH@NJO@NFTTBHFTͷ
ΤϥʔϨϕϧΛࢦఆ͢Δ

View Slide

PostgreSQLͷϩά
ਂࠁ౓ ࢖༻ํ๏ TZTMPH
%(%( ։ൃऀ͕࢖༻͢Δ࿈ଓత͔ͭΑΓৄࡉͳ৘ใΛఏڙ͠·͢ɻ %(
*/'0
7"$66.7&3#04&ͷग़ྗͳͲͷɺϢʔβʹΑͬͯ҉໧తʹཁٻ
͞Εͨ৘ใΛఏڙ͠·͢ɻ
*/'0
/05*$&
௕͍ࣝผࢠͷ੾Γ٧Ίʹؔ͢Δ஫ҙͳͲɺϢʔβͷิॿʹͳΔ৘ใ
Λఏڙ͠·͢ɻ
/05*$&
8"3/*/(
τϥϯβΫγϣϯϒϩοΫ֎Ͱͷ$0..*5ͷ༷ͳɺϢʔβ΁ͷܯࠂ
Λఏڙ͠·͢ɻ
/05*$&
&3303 ݱࡏͷίϚϯυΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ 8"3/*/(
-0(
νΣοΫϙΠϯτͷ׆ಈͷ༷ͳɺ؅ཧऀʹؔ৺ͷ͋Δ৘ใΛใࠂ͠
·͢ɻ
*/'0
'"5"- ݱࡏͷηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ &33
1"/*$
શͯͷσʔλϕʔεηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใ
ࠂ͠·͢ɻ
$3*5

View Slide

PostgreSQLͷϩά
ᮢ஋Λ௒͑ͨ42-ͷ؂ࢹ
߲໨໊ ༻్
MPH@NJO@FSSPS@TUBUFNFOU ࢦఆͷΤϥʔϨϕϧʹ֘౰͢Δ42-Λه࿥͢Δ
MPH@NJO@EVSBUJPO@TUBUFNFOU ࢦఆͨ͠ϛϦඵҎ্ͷ࣮ߦ࣌ؒͷ42-Λه࿥͢Δ
MPH@MPDL@XBJUT
ϩοΫ֫ಘʹཁ͕ͨ࣌ؒ͠λΠϜΞ΢τͨ͠42-
Λه࿥͢Δ

View Slide

͜ΕΒΛՄࢹԽͯ͠
1PTUHSF42-ͷৼΔ෣͍Λ஌Δ

View Slide

ՄࢹԽͷͨΊͷπʔϧ
w .BDLFSFMQMVHJO
w ;BCCJYQH@NPO[
w QH@TUBUTJOGPQH@TUBUT@SFQPSUFS

View Slide

w .BDLFSFMQMVHJO
w ;BCCJYQH@NPO[
؆୯Ͱ4BB4ͳͷͰ4FSWFSΛ༻ҙ͠ͳ͍͍ͯ͘ͷͰָ
ଟ͘ͷϛυϧ΢ΣΞʹରԠ͍ͯ͠ΔͷͰҰׅ؅ཧͰ͖Δ
͔͠͠1PTUHSF42-ͷৄࡉΛݟΔʹ͸߲໨͕গͳ͍

View Slide

w .BDLFSFMQMVHJO
w ;BCCJYQH@NPO[
.BDLFSFMಉ༷ʹෳ਺ͷϛυϧ΢ΣΞʹରԠ
1PTUHSF42-ͷࡉ͔͍৘ใ·ͰݟΕΔ
4FSWFS͕ඞཁͰ୆਺͕૿͑Δͱ;BCCJYͷνϡʔχϯά΋ඞཁ

View Slide

w .BDLFSFMQMVHJO
w ;BCCJYQH@NPO[
1PTUHSF42-ઐ༻πʔϧ
ৄࡉͳ৘ใ·ͰՄࢹԽͰ͖Δ
Έ͔͔੡ͳͷͰ೔ຊޠυΩϡϝϯτ΋͋Δ
͔͠͠1PTUHSF42-ઐ༻ͳ্ʹ؀ڥߏங͕൥ࡶ

View Slide

ࣗ෼ʹ߹ͬͨπʔϧΛબͿ

View Slide

ࣗ෼ʹ߹ͬͨπʔϧΛબͿ
ˣ
ΠνϩʔͷόοτΛ࢖͔ͬͨΒͱݴͬͯ
ΠνϩʔʹͳΕΔΘ͚Ͱ͸ͳ͍

View Slide

͋͐͡Μͩ
̍ɹࣗݾ঺հ
̑ɹ·ͱΊ

View Slide

·ͱΊ
ઌਓͷ஌ܙΛ࢖͏

View Slide

·ͱΊ
ˣ
ެࣜυΩϡϝϯτΛಡ΋͏

View Slide

·ͱΊ
ˣ
ެࣜυΩϡϝϯτΛಡ΋͏
҆৺ͷ೔ຊޠυΩϡϝϯτ

View Slide

·ͱΊ
·ͣ͸ՄࢹԽΛ͢Δ

View Slide

·ͱΊ
ਪଌΑΓܭଌ

View Slide

·ͱΊ
ਪଌΑΓܭଌ
↓
ܭଌΑΓ؍ଌ

View Slide

·ͱΊ
ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ
ະདྷΛਖ਼͘͠༧ଌͰ͖Δ

View Slide

View Slide

·ͱΊ
ΤϯδχΞʹ͸ࠜڌ͕ඞཁ

View Slide

·ͱΊ
ΤϯδχΞʹ͸ࠜڌ͕ඞཁ
↓
ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍

View Slide

·ͱΊ

View Slide

·ͱΊ
ςετίʔυ͸ϓϩάϥϜͷ඼࣭ͷՄࢹԽ
ϞχλϦϯά͸αʔϏεͷ඼࣭ͷՄࢹԽ

View Slide

·ͱΊ
lߴ౓ʹൃୡͨ͠γεςϜͷҟৗ͸
ਆͷౖΓͱݟ෼͚͕͔ͭͳ͍z
Z@VVLJ

View Slide

·ͱΊ
ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍

View Slide

·ͱΊ
ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍
↓
඼࣭ΛՄࢹԽ͚ͨͩ͠Ͱ͸վળ͸͞Εͳ͍

View Slide

·ͱΊ
lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz
:BTVIJSP0OJTIJ

View Slide

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ

View Slide

PostgreSQL Architecture And Performance Monitoring

PostgreSQL Architecture And Performance Monitoring

soudai sone

More Decks by soudai sone

Other Decks in Technology

Featured

Transcript