ʻO ka nīnau maʻamau i lawe ʻia e kahi mea hoʻomohala i kāna DBA a i ʻole ka mea nona ka ʻoihana e lawe mai i kahi mea aʻoaʻo PostgreSQL aneane like ke kani ʻana: "No ke aha e lōʻihi ai ka hoʻopau ʻana o nā noi ma ka waihona?"
Nā kumu maʻamau:
- algorithm pono ole
ke hoʻoholo ʻoe e HUI i kekahi mau CTE ma luna o ʻelua mau ʻumi tausani o nā moʻolelo - ʻikepili pili ʻole
inā ʻokoʻa loa ka puʻunaue maoli o ka ʻikepili i ka papaʻaina mai ka mea i hōʻiliʻili ʻia e ANALYZE i ka manawa i hala - "hoʻopili" i nā kumuwaiwai
a ʻaʻole lawa ka mana hoʻolaʻa hoʻolaʻa o ka CPU, ʻo nā gigabytes o ka hoʻomanaʻo e hoʻomau mau ʻia, a ʻaʻole hiki i ka disk ke mālama i nā "makemake" āpau o ka waihona. - ke ālai ʻana mai nā kaʻina hana hoʻokūkū
A inā paʻakikī loa ka hoʻopaʻa ʻana a me ka nānā ʻana, a laila no nā mea āpau a mākou e pono ai hoʻolālā nīnau, hiki ke loaʻa me ka hoʻohana ʻana (ʻOi aku ka maikaʻi, ʻoiaʻiʻo, e wehewehe koke (ANALYZE, BUFFERS) ...) ai ʻole .
Akā, e like me ka mea i ʻōlelo ʻia ma ka palapala like,
"He noʻeau ka hoʻomaopopo ʻana i kahi hoʻolālā, a no ka hoʻomaʻamaʻa ʻana e pono ai i kahi nui o ka ʻike ..."
Akā hiki iā ʻoe ke hana me ka ʻole inā hoʻohana ʻoe i ka mea hana pono!
He aha ke ʻano o ka hoʻolālā nīnau? ʻO kekahi mea e like me ia:
Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (relname = $1)
Filter: (oid = $0)
Buffers: shared hit=4
InitPlan 1 (returns $0,$1)
-> Limit (actual time=0.019..0.020 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 5
Buffers: shared hit=1a i ʻole penei:
"Append (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
" Buffers: shared hit=3"
" CTE cl"
" -> Seq Scan on pg_class (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
" Buffers: shared hit=3"
" -> Limit (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> CTE Scan on cl (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> Limit (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
" Buffers: shared hit=2"
" -> CTE Scan on cl cl_1 (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
" Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"Akā ʻo ka heluhelu ʻana i ka hoʻolālā ma ka kikokikona "mai ka pepa" he paʻakikī a maopopo ʻole:
- hōʻike ʻia ma ka node e hoʻohui i nā kumuwaiwai subtree
ʻo ia hoʻi, no ka hoʻomaopopo ʻana i ka nui o ka manawa e hoʻokō ai i kahi node, a i ʻole ka nui o kēia heluhelu ʻana mai ka papa ʻaina i lawe mai i ka ʻikepili mai ka disk, pono ʻoe e unuhi i kekahi mai kekahi. - pono ka manawa node e hoonui me na puka lou
ʻAe, ʻo ka unuhi ʻana ʻaʻole ia ka hana paʻakikī e pono e hana "ma ke poʻo" - ma hope o nā mea a pau, ua hōʻike ʻia ka manawa hoʻokō e like me ka awelika no ka hoʻokō ʻana o kahi node, a hiki i nā haneli o lākou. - maikaʻi, a ke pale nei kēia mau mea iā mākou mai ka pane ʻana i ka nīnau nui - no laila ʻo wai "ka loulou nāwaliwali loa"?
I ko mākou hoʻāʻo ʻana e wehewehe i kēia mau mea āpau i nā haneli o kā mākou mea hoʻomohala, ua ʻike mākou mai waho mai ua like ia me kēia:
A ʻo ia hoʻi, pono mākou ...
Nā mea hana
I loko o ia mea ua ho'āʻo mākou e hōʻiliʻili i nā mīkini kī āpau e kōkua i ka hoʻomaopopo ʻana "ʻo wai ka hewa a me ka mea e hana ai" e like me ka hoʻolālā a me ke noi. ʻAe, a kaʻana like i kāu ʻike me ke kaiāulu.
E hui a hoʻohana -
ʻIke ʻia o nā papahana
He maʻalahi ke hoʻomaopopo i ka hoʻolālā ke ʻano like me kēia?
Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms
ʻAʻole naʻe.
Akā e like me kēia, ma ke ano hoopokoleke hoʻokaʻawale ʻia nā hōʻailona kī, ʻoi aku ka maopopo:
Akā inā ʻoi aku ka paʻakikī o ka hoʻolālā, e hele mai ʻo ia e hoʻopakele mahele manawa piechart ma nā nodes:
ʻAe, no nā koho paʻakikī loa ʻo ia i ka wikiwiki e kōkua palapala holomua:
No ka laʻana, aia kekahi mau kūlana koʻikoʻi ke loaʻa i kahi hoʻolālā ke kumu maoli ma mua o hoʻokahi:
Nā hōʻailona hana
ʻAe, inā ua waiho ʻia a ʻike ʻia ke ʻano holoʻokoʻa o ka hoʻolālā a me kāna mau wahi ʻeha, no ke aha e hōʻike ʻole ai iā lākou i ka mea hoʻomohala a wehewehe iā lākou ma ka "ʻōlelo Lūkini"?
Ua hōʻiliʻili mua mākou i ʻelua mau kakini o ia mau ʻōlelo paipai.
Mea hoʻolaha nīnau laina-i-laina
I kēia manawa, inā hoʻokomo ʻoe i ka nīnau kumu ma luna o ka hoʻolālā i kālailai ʻia, hiki iā ʻoe ke ʻike i ka nui o ka manawa i hoʻolilo ʻia ma kēlā me kēia ʻōlelo - e like me kēia:
...a i ʻole e like me kēia:
Ke hoʻololi nei i nā ʻāpana i kahi noi
Inā "hoʻopili" ʻoe ʻaʻole wale i kahi noi i ka hoʻolālā, akā i kāna mau ʻāpana mai ka laina DETAIL o ka log, hiki iā ʻoe ke kope hou iā ia i kekahi o nā koho:
- me ke pani waiwai ma ke noi
no ka hoʻokō pololei ʻana i kāu kumu a me ka hoʻolaha hou akuSELECT 'const', 'param'::text; - me ke pani waiwai ma o PREPARE/EXECUTE
e hoʻohālike i ka hana a ka mea hoʻonohonoho, ke nānā ʻole ʻia ka ʻāpana parametric - no ka laʻana, i ka wā e hana ai i nā papa ʻāpana.DEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
Waihona o nā papahana
Hoʻopili, kālailai, kaʻana like me nā hoa hana! E mālama ʻia nā hoʻolālā a hiki iā ʻoe ke hoʻi iā lākou ma hope:
Akā inā ʻaʻole ʻoe makemake e ʻike nā poʻe ʻē aʻe i kāu hoʻolālā, mai poina e nānā i ka pahu "mai hoʻolaha i ka waihona".
Ma nā ʻatikala aʻe e kamaʻilio wau e pili ana i nā pilikia a me nā hoʻoholo e kū mai i ka wā e nānā ana i kahi hoʻolālā.
Source: www.habr.com