ʻO kahi ala ʻenehana i ka hoʻoponopono ʻana iā PostgreSQL: nā hoʻokolohua me nā ʻikepili. Nikolay Samokhvalov

Manaʻo wau e heluhelu ʻoe i ka transcript o ka hōʻike a Nikolai Samokhvalov "Ke ala ʻoihana i ka hoʻoponopono ʻana i ka PostgreSQL: nā hoʻokolohua ma nā ʻikepili"

Shared_buffers = 25% - he nui a liʻiliʻi paha? A i ʻole pololei? Pehea ʻoe e ʻike ai inā kūpono kēia - ʻoi aku ka lōʻihi - kūpono i kāu ʻōlelo?

ʻO ka manawa kēia e hoʻokokoke ai i ka pilikia o ke koho ʻana i nā ʻāpana postgresql.conf "e like me ke kanaka makua." ʻAʻole me ke kōkua o nā "auto tuners" makapō a i ʻole nā ​​ʻōlelo aʻo kahiko mai nā ʻatikala a me nā blog, akā ma muli o:

1. nā hoʻokolohua i hōʻoia pono ʻia ma nā ʻikepili, lawe ʻia ma nā ʻano nui a ma lalo o nā kūlana kokoke loa e "hakakā" i nā mea,
2. ʻike hohonu i nā hiʻohiʻona o ka DBMS a me OS.

Ke hoʻohana nei iā Nancy CLI (https://gitlab.com/postgres.ai/nancy), e nānā mākou i kahi hiʻohiʻona kikoʻī - nā shared_buffers kaulana - i nā kūlana like ʻole, i nā papahana like ʻole a hoʻāʻo e noʻonoʻo pehea e koho ai i kahi hoʻonohonoho kūpono no kā mākou ʻoihana, waihona a me ka ukana.

E kamaʻilio mākou e pili ana i nā hoʻokolohua me nā waihona. He moʻolelo kēia i ʻoi aku ma mua o ʻeono mahina.

He wahi liʻiliʻi e pili ana iaʻu. ʻO ka ʻike me Postgres ma mua o 14 mau makahiki. Ua hoʻokumu ʻia kekahi mau ʻoihana pūnaewele. Ua hoʻohana ʻia ʻo Postgres ma nā wahi āpau.

ʻO ka hui ʻo RuPostgres ma Meetup, 2nd wahi ma ka honua. Ke hoʻokokoke mālie nei mākou i 2 poʻe. RuPostgres.org.

A ma nā PC o nā ʻaha kūkā like ʻole, me ka Highload, ke kuleana wau no ka ʻikepili, ʻo ia hoʻi ʻo Postgres mai ka hoʻomaka.

A i nā makahiki i hala iho nei, ua hoʻomaka hou au i kaʻu hana kūkākūkā Postgres 11 manawa manawa mai ʻaneʻi.

A i kaʻu hana ʻana i kēia i kekahi mau makahiki i hala aku nei, ua hoʻomaha wau i ka hana lima ikaika me Postgres, mai 2010 paha. Pīhoihoi au i ka liʻiliʻi o ka loli o ka hana maʻamau o kahi DBA, a me ka nui o ka hana lima e pono ai e hoʻohana. A manaʻo koke wau ua hewa kekahi mea ma ʻaneʻi, pono wau e hoʻokaʻawale i nā mea āpau.

A no ka mamao loa, aia ka hapa nui o nā mea kūʻai aku i ke ao. A he nui nā mea i hana ʻia, maopopo. Nā mea hou aʻe e pili ana i kēia ma hope. ʻO ia hoʻi, ua hopena kēia mau mea a pau i ka manaʻo he nui nā mea hana, ʻo ia hoʻi, kekahi ʻano kahua e hoʻokaʻawale i nā hana DBA a pau i hiki ke mālama ʻia ka nui o nā waihona.

ʻAʻole e komo kēia hōʻike:

• "ʻO nā pōkā kālā" a me nā ʻōlelo like - hoʻonoho i 8 GB a i ʻole 25% shared_buffers a e maikaʻi ʻoe. ʻAʻole nui e pili ana i shared_buffers.
• Hardcore "loko".

He aha ka hopena?

• E loaʻa nā loina hoʻolālā a mākou e hoʻohana a hoʻomohala. Aia nā ʻano manaʻo like ʻole e ala mai ana ma ke ala a me nā mea hana like ʻole a mākou e hana ai no ka hapa nui ma Open Source, ʻo ia hoʻi, hana mākou i ke kumu ma Open Source. Eia kekahi, loaʻa iā mākou nā tiketi, ʻo nā kamaʻilio āpau ma Open Source. Hiki iā ʻoe ke ʻike i kā mākou e hana nei i kēia manawa, nā mea e hiki mai ana i ka hoʻokuʻu hou aʻe, etc.
• E loaʻa pū kekahi ʻike i ka hoʻohana ʻana i kēia mau loina, kēia mau mea hana i nā ʻoihana he nui: mai nā hoʻomaka liʻiliʻi a hiki i nā hui nui.

Pehea ka ulu ʻana o kēia?

ʻO ka mea mua, ʻo ka hana nui o kahi DBA, ma kahi o ka hōʻoia ʻana i ka hana ʻana o nā manawa, ka hoʻonohonoho ʻana i nā backup, etc.

I kēia manawa ua hoʻonohonoho ʻia e like me kēia. Nānā mākou i ka nānā ʻana, ʻike mākou i kekahi mea, akā nalo mākou i kekahi mau kikoʻī. Hoʻomaka mākou e ʻeli me ka akahele, maʻamau me ko mākou mau lima, a hoʻomaopopo i ka mea e hana ai me ia ʻano a i ʻole.

A aia ʻelua mau ala. ʻO Pg_stat_statements ka hopena paʻamau no ka ʻike ʻana i nā nīnau lohi. A me ka nānā ʻana i nā lāʻau Postgres me ka hoʻohana ʻana i pgBadger.

He koʻikoʻi ko kēlā me kēia ala. I ke ala mua, ua kiola mākou i nā ʻāpana āpau. A inā mākou e ʻike i nā pūʻulu SELECT * FROM papa kahi i like ke kolamu me ka "?" aiʻole "$" mai Postgres 10. ʻAʻole mākou i ʻike inā he index scan kēia a i ʻole he seq scan. Ua hilinaʻi nui i ka ʻāpana. Inā ʻoe e hoʻololi i kahi waiwai i ʻike ʻole ʻia ma laila, e lilo ia i kahi hōʻailona index. Inā hoʻololi ʻoe i kahi waiwai e noho ana ma 90% o ka papaʻaina ma laila, e ʻike ʻia ka seq scan, no ka mea ʻike ʻo Postgres i nā helu. A he drawback nui kēia o nā pg_stat_statements, ʻoiai ke holo nei kekahi mau hana.

ʻO ka pōʻino nui loa o ka loiloi log ʻaʻole hiki iā ʻoe ke loaʻa "log_min_duration_statement = 0" ma ke ʻano he lula. A e kamaʻilio pū kākou no kēia. No laila, ʻaʻole ʻoe e ʻike i ke kiʻi holoʻokoʻa. A ʻo kekahi nīnau, wikiwiki loa, hiki ke hoʻopau i ka nui o nā kumuwaiwai, akā ʻaʻole ʻoe e ʻike no ka mea aia ma lalo o kāu paepae.

Pehea e hoʻoponopono ai nā DBA i nā pilikia i loaʻa iā lākou?

No ka laʻana, ua loaʻa iā mākou kekahi pilikia. He aha ka hana mau? Inā he mea hoʻomohala ʻoe, a laila e hana ana ʻoe i kekahi mea i like ʻole ka nui. Inā he DBA ʻoe, a laila loaʻa iā ʻoe ka hoʻokūkū. A hiki i hoʻokahi wale nō. A he ʻeono mahina ma hope. A manaʻo ʻoe e hele ʻoe i ka hana. A ʻike ʻia nā DBA a laila e nānā i ka hana, ma kahi kope. A ke hana nei lākou i kahi kuhikuhi no ka manawa pōkole, e hōʻoia e kōkua ia, e hāʻule a hāʻawi i nā mea hoʻomohala i hiki iā lākou ke hoʻokomo i loko o nā faila migration. ʻO kēia ke ʻano o ka lapuwale e hana nei i kēia manawa. A he pilikia kēia.

• Hoʻonohonoho hoʻokani.
• E hoʻonui i ka hoʻonohonoho o nā kuhikuhi.
• E hoʻololi i ka nīnau SQL ponoʻī (ʻo ke ala paʻakikī loa kēia).
• Hoʻohui i ka hiki (ʻo ke ala maʻalahi i ka nui o nā hihia).

Nui nā mea e pili ana i kēia mau mea. Nui nā lima ma Postgres. Nui ka ʻike. Nui nā kuhikuhi ma Postgres, mahalo pū i nā mea hoʻonohonoho o kēia ʻaha kūkā. A pono e ʻike ʻia kēia mau mea a pau, a ʻo ia ka mea e manaʻo ai ka poʻe non-DBA e like me ka DBA e hoʻomaʻamaʻa ana i ka kilokilo ʻeleʻele. ʻO ia hoʻi, pono ʻoe e aʻo no 10 mau makahiki e hoʻomaka e hoʻomaopopo i kēia mau mea maʻamau.

A he mea hakakā wau i kēia kilokilo ʻeleʻele. Makemake wau e hana i nā mea āpau i loaʻa ka ʻenehana, a ʻaʻohe intuition i kēia mau mea a pau.

Nā laʻana ola maoli

Ua nānā au i kēia ma nā papahana ʻelua, me kaʻu ponoʻī. Ke haʻi mai nei kekahi moʻomanaʻo moʻomanaʻo iā mākou he maikaʻi ka waiwai o 1 no default_statistict_target. ʻAe, e hoʻāʻo kākou ma ka hana ʻana.

A eia mākou, e hoʻohana ana i kā mākou mea hana i ʻelua makahiki ma hope, me ke kōkua o nā hoʻokolohua ma nā ʻikepili a mākou e kamaʻilio nei i kēia lā, hiki iā mākou ke hoʻohālikelike i ka mea i hala a me ka mea i lilo.

A no kēia pono mākou e hana i kahi hoʻokolohua. He ʻehā ʻāpana.

• ʻO ka mua ke kaiapuni. Pono mākou i kahi ʻāpana lako. A i koʻu hele ʻana i kekahi hui a kau inoa i kahi ʻaelike, ʻōlelo wau iā lākou e hāʻawi mai iaʻu i ka lako like me ka hana ʻana. No kēlā me kēia o kāu Masters, pono iaʻu ma kahi o hoʻokahi ʻāpana lako e like me kēia. A i ʻole he mīkini virtual kēia ma Amazon a i ʻole Google, a i ʻole pono wau i ka ʻāpana like o ka lako. ʻO ia hoʻi, makemake au e hana hou i ke kaiapuni. A i loko o ka manaʻo o ke kaiapuni mākou e hoʻokomo i ka mana nui o Postgres.
• ʻO ka ʻāpana ʻelua ke kumu o kā mākou noiʻi. He waihona waihona kēia. Hiki ke hana ʻia ma nā ʻano he nui. E hōʻike wau iā ʻoe pehea.
• ʻO ka hapakolu ka ukana. ʻO kēia ka manawa paʻakikī loa.
• A ʻo ka hapa ʻehā ka mea a mākou e nānā ai, ʻo ia hoʻi ka mea a mākou e hoʻohālikelike ai me ka mea. E ʻōlelo mākou hiki iā mākou ke hoʻololi i hoʻokahi a ʻoi aku paha nā ʻāpana i ka config, a i ʻole hiki iā mākou ke hana i kahi kuhikuhi, etc.

Ke hoʻomaka nei mākou i kahi hoʻokolohua. Eia nā pg_stat_statements. Aia ma ka hema ka mea i hanaia. Ma ka ʻākau - he aha ka mea i hana.

Ma ka hema default_statistics_target = 100, ma ka ʻākau = 1. ʻIke mākou ua kōkua kēia iā mākou. Ma ka holoʻokoʻa, ua maikaʻi nā mea a pau ma 000%.

Akā inā mākou e kaomi i lalo, aia nā pūʻulu o nā noi mai pgBadger a mai pg_stat_statements. ʻElua mau koho. E ʻike mākou ua hāʻule kekahi noi e 88%. A eia ke hele mai nei ke ala ʻenekinia. Hiki iā mākou ke eli hou aʻe i loko no ke aha mākou i piholo ai. Pono ʻoe e hoʻomaopopo i ka mea i hana ʻia me ka helu. No ke aha e ʻoi aku ka nui o nā bākeke ma nā helu i nā hopena ʻino.

A i ʻole hiki iā mākou ke ʻeli, akā e hana i ka "ALTER TABLE ... ALTER COLUMN" a hoʻihoʻi i 100 mau bākeke i ka helu o kēia kolamu. A laila me kahi hoʻokolohua ʻē aʻe hiki iā mākou ke hōʻoia ua kōkua kēia pā. ʻO nā mea a pau. He ala ʻenekinia kēia e kōkua iā mākou e ʻike i ke kiʻi nui a hoʻoholo i ka ʻikepili ma mua o ka intuition.

ʻElua mau laʻana mai nā wahi ʻē aʻe. Aia nā hoʻokolohua CI i ka hoʻāʻo ʻana no nā makahiki he nui. A ʻaʻohe papahana ma kona noʻonoʻo pono e ola me ka ʻole o ka hoʻāʻo ʻana.

Ma nā ʻoihana ʻē aʻe: i ka mokulele, i ka ʻoihana kaʻa, ke hoʻāʻo mākou i ka aerodynamics, loaʻa iā mākou ka manawa e hana ai i nā hoʻokolohua. ʻAʻole mākou e hoʻokuʻu i kahi mea mai kahi kiʻi kiʻi pololei i ka lewa, a i ʻole mākou e lawe koke i kahi kaʻa ma ke ala. Eia kekahi laʻana, aia kahi pūnana makani.

Hiki iā mākou ke huki i nā hopena mai ka nānā ʻana i nā ʻoihana ʻē aʻe.

ʻO ka mea mua, loaʻa iā mākou kahi kaiapuni kūikawā. Kokoke ia i ka hana ʻana, akā ʻaʻole kokoke. ʻO kāna hiʻohiʻona nui, ʻo ia ka mea maʻalahi, hiki ke hoʻihoʻi ʻia a me ka maʻalahi e like me ka hiki. A pono e loaʻa nā mea hana kūikawā no ka hana ʻana i ka ʻike kikoʻī.

ʻO ka mea nui paha, ke hoʻomaka mākou i ka mokulele a lele, ʻaʻole i liʻiliʻi ka manawa e aʻo ai i kēlā me kēia millimeter o ka ʻēheu ma mua o ko mākou puka makani. Loaʻa iā mākou nā mea hana diagnostic hou aku. Hiki iā mākou ke lawe i nā mea kaumaha i hiki ʻole iā mākou ke kau i ka mokulele i ka lewa. Pela no me Postgres. Hiki iā mākou, i kekahi mau hihia, ke hoʻohana i ka hoʻopaʻa inoa piha ʻana i ka nīnau i ka wā hoʻokolohua. A ʻaʻole makemake mākou e hana i kēia i ka hana. Hiki iā mākou ke hoʻolālā e hoʻohana i kēia me ka auto_explain.

A e like me kaʻu i ʻōlelo ai, ʻo ke kiʻekiʻe kiʻekiʻe o ka automation ʻo ia ka mea e paʻi mākou i ke pihi a hana hou. ʻO kēia ke ʻano e pono ai, i nui ka hoʻokolohua, no laila aia ma ke kahawai.

Nancy CLI - ke kumu o ka "database laboratory"

A pēlā mākou i hana ai i kēia mea. ʻO ia hoʻi, ua kamaʻilio wau e pili ana i kēia mau manaʻo i Iune, kokoke i hoʻokahi makahiki i hala. A ua loaʻa iā mākou ka mea i kapa ʻia ʻo Nancy CLI ma Open Source. ʻO kēia ke kumu no ke kūkulu ʻana i ke keʻena ʻikepili.

Nancy - Aia ma Open Source, ma Gitlab. Hiki iā ʻoe ke ʻōlelo, hiki iā ʻoe ke hoʻāʻo. Ua hāʻawi wau i kahi loulou i nā paheʻe. Hiki iā ʻoe ke kaomi ma luna a ma laila kokua ma na mea a pau.

ʻOiaʻiʻo, aia ka nui o ka hoʻomohala ʻana. Nui nā manaʻo ma laila. Akā ʻo kēia kahi mea a mākou e hoʻohana ai i kēlā me kēia lā. A loaʻa iā mākou kahi manaʻo - no ke aha i ka wā e holoi ai mākou i nā laina 40, hiki i lalo i IO, a laila hiki iā mākou ke hana i kahi hoʻokolohua a nānā i nā kikoʻī hou aku e hoʻomaopopo i ka mea e hana nei a laila hoʻāʻo e hoʻoponopono i ka lele. ʻO ia hoʻi, ke hana nei mākou i kahi hoʻokolohua. No ka laʻana, hoʻololi mākou i kekahi mea a ʻike i ka hopena i ka hopena. A ʻaʻole mākou e hana i kēia i ka hana. ʻO kēia ke kumu o ka manaʻo.

Ma hea e hiki ai kēia hana? Hiki i kēia ke hana ma ka ʻāina, ʻo ia hoʻi, hiki iā ʻoe ke hana ma nā wahi āpau, hiki iā ʻoe ke holo ma ka MacBook. Pono mākou i kahi docker, e hele kāua. ʻo ia wale nō. Hiki iā ʻoe ke holo iā ia i kekahi manawa ma kahi ʻāpana lako, a i ʻole ma kahi mīkini virtual, ma nā wahi āpau.

A aia kekahi manawa e holo mamao ma Amazon ma EC2 Instance, ma nā kiko. A he manawa maikaʻi loa kēia. No ka laʻana, i nehinei ua hana mākou ma mua o 500 mau hoʻokolohua ma ka laʻana i3, e hoʻomaka ana me ka muli loa a hoʻopau me i3-16-xlarge. A he 500 mau hoʻokolohua he $64. He 15 mau minuke kēlā me kēia. ʻO ia hoʻi, ma muli o ka hoʻohana ʻia ʻana o nā kiko ma laila, ʻoi aku ka liʻiliʻi - he 70% uku hoʻemi, ʻo ka hoʻolimalima kālā kekona a Amazon. Hiki iā ʻoe ke hana nui. Hiki iā ʻoe ke hana i ka noiʻi maoli.

A kākoʻo ʻia ʻekolu mana nui o Postgres. ʻAʻole paʻakikī loa ka hoʻopau ʻana i kekahi mau mea kahiko a me ka mana hou 12th pū kekahi.

Hiki iā mākou ke wehewehe i kahi mea ma nā ʻano ʻekolu. ʻO kēia:

• Waihona/sql waihona.
• ʻO ke ala nui ʻo ka clone i ka papa kuhikuhi PGDATA. E like me ke kānāwai, lawe ʻia ia mai ka server backup. Inā loaʻa iā ʻoe nā waihona binary maʻamau, hiki iā ʻoe ke hana i nā clones mai laila. Inā loaʻa iā ʻoe nā ao, a laila e hana ke keʻena kapua e like me Amazon a me Google i kēia no ʻoe. ʻO kēia ke ala nui loa e clone i ka hana maoli. Penei kā mākou wehe ʻana.
• A kūpono ke ala hope no ka noiʻi inā makemake ʻoe e hoʻomaopopo pehea e hana ai kekahi mea ma Postgres. ʻO kēia ka pgbench. Hiki iā ʻoe ke hana me ka pgbench. Hoʻokahi wale nō koho "db-pgbench". E haʻi aku ʻoe iā ia i ka pālākiō. A e hana ʻia nā mea a pau ma ke ao, e like me ka ʻōlelo.

A hoʻouka:

• Hiki iā mākou ke hoʻokō i ka ukana i hoʻokahi kaula SQL. ʻO kēia ke ala mua loa.
• A hiki iā mākou ke hoʻohālike i ka ukana. A hiki iā mākou ke hoʻohālike mua i nā mea a pau ma kēia ala. Pono mākou e hōʻiliʻili i nā lāʻau a pau. A he ʻeha. E hōʻike wau iā ʻoe i ke kumu. A me ka hoʻohana ʻana i ka pgreplay mākou e pāʻani ai, i kūkulu ʻia i loko o Nancy.
• A i ʻole kahi koho ʻē aʻe. ʻO ka ukana i kapa ʻia ʻo craft, a mākou e hana ai me kahi nui o ka hoʻoikaika. Ke nānā nei i kā mākou ukana i kēia manawa ma ka ʻōnaehana hakakā, huki mākou i nā hui kiʻekiʻe o nā noi. A me ka hoʻohana ʻana i ka pgbench hiki iā mākou ke hoʻohālikelike i kēia ukana i ka hale hana.

• Pono mākou e hana i kekahi ʻano SQL, ʻo ia hoʻi, nānā mākou i kekahi ʻano o ka neʻe ʻana, hana i kahi kuhikuhi ma laila, hoʻokō i ka ANALAZE ma laila. A ke nānā nei mākou i ka mea i hana ʻia ma mua o ka ʻūhā a ma hope o ka ʻūhā. Ma keʻano laulā, kekahi SQL.
• Hoʻololi mākou i hoʻokahi a ʻoi aku paha nā ʻāpana i ka config. Hiki iā mākou ke haʻi iā mākou e nānā, no ka laʻana, nā waiwai 100 ma Amazon no kā mākou waihona terabyte. A i loko o kekahi mau hola e loaʻa iā ʻoe ka hopena. Ma ke ʻano he lula, e lawe ʻoe i kekahi mau hola e kau i kahi waihona terabyte. Akā aia kahi papa i ka hoʻomohala ʻana, loaʻa iā mākou kahi moʻo, ʻo ia hoʻi, hiki iā ʻoe ke hoʻohana mau i ka pgdata like ma ka server like a nānā. E hoʻomaka hou nā Postgres a e hoʻonohonoho hou ʻia nā cache. A hiki iā ʻoe ke hoʻokele i ka ukana.

• Hiki mai kahi papa kuhikuhi me nā waihona like ʻole, e hoʻomaka ana mai nā kiʻi paʻi pgmoku'āina***. A ʻo ka mea hoihoi loa he pg_stat_statements, pg_stat_kcacke. ʻO kēia nā mea hoʻonui ʻelua e nānā i nā noi. ʻAʻole i loaʻa i ka pg_stat_bgwriter nā helu helu pgwriter wale nō, akā aia pū kekahi ma ka ʻaoʻao a me ke ʻano o ka hoʻoneʻe ʻana o nā kua i nā pale lepo. A he mea hoihoi ke ʻike. No ka laʻana, ke hoʻonohonoho mākou i shared_buffers, he mea hoihoi loa ia e ʻike i ka nui o nā mea a pau i pani ai.
• Ke hōʻea mai nei nā lāʻau postgres. ʻElua lāʻau - kahi moʻolelo hoʻomākaukau a me kahi moʻolelo hoʻoiho hoʻouka.
• ʻO kahi hiʻohiʻona hou ʻo FlameGraphs.
• Eia kekahi, inā ʻoe i hoʻohana i nā koho pgreplay a i ʻole pgbench no ka pāʻani ʻana i ka ukana, a laila he ʻōiwi maoli kā lākou huahana. A e ʻike ʻoe i ka latency a me ka TPS. Hiki ke hoʻomaopopo i ko lākou ʻike ʻana.
• ʻIke pūnaewele.
• Nānā CPU kumu a me IO. ʻOi aku kēia no ka hiʻohiʻona EC2 ma Amazon, inā makemake ʻoe e hoʻolauna i nā manawa like 100 i loko o kahi kaula a holo i nā holo like ʻole 100 ma laila, a laila e loaʻa iā ʻoe nā hoʻokolohua 10. A pono ʻoe e hōʻoia ʻaʻole ʻoe e ʻike i kahi hewa hewa i hoʻokaumaha ʻia e kekahi. ʻO nā mea ʻē aʻe e ʻeleu ana ma kēia ʻāpana o ka lako a he liʻiliʻi kāu kumu waiwai i koe. ʻOi aku ka maikaʻi o ka haʻalele ʻana i ia mau hopena. A me ke kōkua ʻana o sysbench mai Alexey Kopytov, hana mākou i nā loiloi pōkole e hiki mai ana a hiki ke hoʻohālikelike ʻia me nā mea ʻē aʻe, ʻo ia hoʻi, e hoʻomaopopo ʻoe i ke ʻano o ka CPU a me ke ʻano o ka IO.

He aha nā pilikia ʻenehana ma muli o ka laʻana o nā ʻoihana like ʻole?

E ʻōlelo mākou makemake mākou e hana hou i ka ukana maoli me ka hoʻohana ʻana i nā lāʻau. He manaʻo maikaʻi inā kākau ʻia ma Open Source pgreplay. Hoʻohana mākou. Akā no ka hana maikaʻi ʻana, pono ʻoe e ʻae i ka logging hulina piha me nā ʻāpana a me ka manawa.

Aia kekahi mau pilikia me ka lōʻihi a me ka hōʻailona manawa. E hoʻopau mākou i kēia lumi kuke holoʻokoʻa. ʻO ka nīnau nui inā hiki iā ʻoe ke uku a ʻaʻole paha?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

ʻO ka pilikia ʻaʻole hiki ke loaʻa. ʻO ka mea mua, pono ʻoe e hoʻomaopopo i ke kahawai e kākau ʻia i ka log. Inā loaʻa iā ʻoe nā pg_stat_statements, hiki iā ʻoe ke hoʻohana i kēia nīnau (e loaʻa ka loulou ma nā kiʻi paheʻe) no ka hoʻomaopopo ʻana i ka nui o nā paita e kākau ʻia i kēlā me kēia kekona.

Nānā mākou i ka lōʻihi o ka noi. Ke haʻalele nei mākou i ka ʻoiaʻiʻo ʻaʻohe ʻāpana, akā ʻike mākou i ka lōʻihi o ka noi a ʻike mākou i ka nui o nā manawa i hoʻokō ʻia. Ma kēia ala hiki iā mākou ke koho i ka nui o nā bytes i kekona. Hiki iā mākou ke hana hewa i ʻelua manawa, akā e hoʻomaopopo maoli mākou i ke kauoha ma kēia ʻano.

Hiki iā mākou ke ʻike he 802 mau manawa i kēlā me kēia kekona ua hoʻokō ʻia kēia noi. A ʻike mākou e kākau ʻia nā bytes_per sec - 300 kB/s me ka hoʻohui a i ʻole ka liʻiliʻi. A, ma ke ʻano he lula, hiki iā mākou ke hāʻawi i kēlā kahe.

Akā! ʻO ka ʻoiaʻiʻo aia nā ʻōnaehana logging ʻokoʻa. A ʻo ka maʻamau o ka poʻe he "syslog".

A inā he syslog kāu, a laila loaʻa paha iā ʻoe ke kiʻi e like me kēia. E lawe mākou i ka pgbench, hiki ke hoʻopaʻa inoa i ka nīnau a ʻike i ka mea e hana nei.

Me ka hoʻopaʻa inoa ʻole - ʻo ia ke kolamu ma ka ʻaoʻao hema. Loaʻa iā mākou he 161 TPS. Me ka syslog - aia kēia ma Ubuntu 000 ma Amazon, loaʻa iā mākou he 16.04 TPS. A inā mākou e hoʻololi i ʻelua mau ala logging ʻē aʻe, a laila ʻoi aku ka maikaʻi o ke kūlana. ʻO ia hoʻi, ua manaʻo mākou e hāʻule, akā ʻaʻole i ka nui like.

A ma CentOS 7, kahi i komo pū ai ka journald, e hoʻohuli i nā lāʻau i kahi ʻano binary no ka huli maʻalahi, a me nā mea ʻē aʻe, a laila he moeʻuhane ma laila, hāʻule mākou i nā manawa 44 i TPS.

A ʻo kēia ka mea e noho ai nā kānaka. A pinepine i nā ʻoihana, ʻoi aku ka nui, paʻakikī loa kēia e hoʻololi. Inā hiki iā ʻoe ke haʻalele i ka syslog, e ʻoluʻolu e haʻalele iā ia.

• E loiloi i ka IOPS a kākau i ke kahe.
• E nānā i kāu ʻōnaehana logging.
• Inā ʻoi aku ka nui o ka ukana i manaʻo ʻia, e noʻonoʻo i ka laʻana.

Loaʻa iā mākou nā pg_stat_statements. E like me kaʻu i ʻōlelo ai, aia ma laila. A hiki iā mākou ke lawe a wehewehe i kēlā me kēia hui o nā noi i kahi ala kūikawā i kahi faila. A laila hiki iā mākou ke hoʻohana i kahi hiʻohiʻona maʻalahi i ka pgbench - ʻo ia ka hiki ke hoʻokomo i nā faila me ka hoʻohana ʻana i ke koho "-f".

Hoʻomaopopo i ka nui o ka "-f". A hiki iā ʻoe ke haʻi me ke kōkua o "@" ma ka hopena i ke kaʻana o kēlā me kēia faila. ʻO ia, hiki iā mākou ke ʻōlelo e hana i kēia ma 10% o nā hihia, a ʻo kēia ma 20%. A e hoʻokokoke kēia iā mākou i ka mea a mākou e ʻike ai i ka hana.

Pehea mākou e hoʻomaopopo ai i ka mea i loaʻa iā mākou i ka hana? He aha ka mahele a pehea? He wahi kaawale keia. Hoʻokahi huahana hou aʻe mākou postgres-checkup. He kahua hoʻi ma Open Source. A ke hoʻomohala nei mākou i kēia manawa.

Ua hānau ʻia ʻo ia no nā kumu like ʻole. No nā kumu ʻaʻole lawa ka nānā ʻana. ʻO ia hoʻi, hele mai ʻoe, nānā i ke kumu, nānā i nā pilikia e noho nei. A, ma ke ʻano he kānāwai, hana ʻoe i kahi health_check. Inā he DBA ʻike ʻoe, a laila hana ʻoe i ka health_check. Ua nānā mākou i ka hoʻohana ʻana i nā indexes, etc. Inā loaʻa iā ʻoe ka OKmeter, a laila maikaʻi. He nānā maikaʻi kēia no Postgres. OKmeter.io - e ʻoluʻolu e hoʻokomo iā ia, ua hana maikaʻi ʻia nā mea āpau ma laila. Ua uku ʻia.

Inā ʻaʻohe āu, a laila ʻaʻole nui kāu. I ka nānā ʻana, aia ka CPU, IO, a laila me ka mālama ʻana, a ʻo ia wale nō. A makemake hou mākou. Pono mākou e ʻike i ka hana ʻana o ka autovacuum, pehea ka hana ʻana o ka checkpoint, i ka io pono mākou e hoʻokaʻawale i ka māka mai ka bgwriter a me nā hope, etc.

ʻO ka pilikia ke kōkua ʻoe i kahi ʻoihana nui, ʻaʻole hiki iā lākou ke hoʻokō koke i kekahi mea. ʻAʻole hiki iā lākou ke kūʻai koke iā OKmeter. Malia paha e kūʻai lākou i loko o ʻeono mahina. ʻAʻole hiki iā lākou ke hāʻawi wikiwiki i kekahi mau pūʻolo.

A ua hele mai mākou me ka manaʻo e pono mākou i kahi mea hana kūikawā ʻaʻole pono e hoʻokomo i kekahi mea, ʻo ia hoʻi, ʻaʻole pono ʻoe e hoʻokomo i kekahi mea ma ka hana. E hoʻouka iā ia ma kāu kamepiula, a i ʻole ma kahi kikowaena nānā mai kahi āu e holo ai. A e kālailai ʻo ia i nā mea he nui: ʻo ka ʻōnaehana hana, ka ʻōnaehana faila, a me nā Postgres ponoʻī, e hana ana i kekahi mau nīnau māmā e hiki ke holo pololei i ka hana a ʻaʻohe mea e hāʻule.

Ua kapa mākou iā ia ʻo Postgres-checkup. Ma nā ʻōlelo lapaʻau, he nānā olakino maʻamau kēia. Inā pili ka automotive, a laila e like me ka mālama. Hana ʻoe i ka mālama ʻana i kāu kaʻa i kēlā me kēia ʻeono mahina a i ʻole hoʻokahi makahiki, ma muli o ka brand. Ke mālama nei ʻoe i kāu waihona? ʻO ia hoʻi, hana mau ʻoe i ka noiʻi hohonu? Pono e hana ia. Inā hana ʻoe i nā waihona, a laila e hana i kahi nānā, ʻaʻole ia he mea nui.

A loaʻa iā mākou kahi mea hana. Ua hoʻomaka ʻo ia e puka ikaika ma kahi o ʻekolu mahina i hala. He ʻōpiopio nō ʻo ia, akā he nui ma laila.

E hōʻiliʻili i nā pūʻulu nīnau "ʻoi loa" - hōʻike K003 ma Postgres-checkup

A aia kekahi pūʻulu o nā hōʻike K. ʻEkolu hōʻike i kēia manawa. A aia kahi hōʻike K003. Aia ka luna mai nā pg_stat_statements, hoʻokaʻawale ʻia e ka total_time.

Ke hoʻokaʻawale mākou i nā pūʻulu noi ma ka total_time, ʻike mākou i ka hui e hoʻouka nui i kā mākou ʻōnaehana, ʻo ia hoʻi. No ke aha wau e inoa ai i nā hui nīnau? No ka mea, ua kiola mākou i nā palena. ʻAʻole kēia mau noi hou, akā nā pūʻulu o nā noi, ʻo ia hoʻi.

A inā mākou e loiloi mai luna a lalo, e hoʻomāmā mākou i kā mākou kumuwaiwai a hoʻopaneʻe i ka manawa e pono ai mākou e hoʻomaikaʻi. He ala maikaʻi loa kēia e mālama i ke kālā.

ʻAʻole paha kēia he ala maikaʻi loa e mālama ai i nā mea hoʻohana, no ka mea, ʻaʻole paha mākou e ʻike i nā hihia liʻiliʻi, akā hoʻonāukiuki loa kahi i kali ai ke kanaka i 15 kekona. ʻO ka huina, he kakaikahi lākou ʻaʻole mākou e ʻike iā lākou, akā pili mākou i nā kumuwaiwai.

He aha ka mea i hana ʻia ma kēia pākaukau? Lawe mākou i ʻelua mau kiʻi. Hāʻawi ʻo Postgres_checkup iā ʻoe i kahi delta no kēlā me kēia metric: ka manawa holoʻokoʻa, nā kelepona, nā lālani, shared_blks_read, etc. ʻO ia, ua helu ʻia ka delta. ʻO ka pilikia nui me nā pg_stat_statements ʻaʻole ia e hoʻomanaʻo i ka wā i hoʻonohonoho hou ʻia. Inā hoʻomanaʻo ʻo pg_stat_database, ʻaʻole hoʻomanaʻo nā pg_stat_statements. ʻIke ʻoe aia kahi helu o 1, akā ʻaʻole maopopo mākou i hea mākou i helu ai.

A ma ʻaneʻi mākou e ʻike ai, eia mākou ʻelua mau kiʻi. ʻIke mākou ʻo ka delta i kēia hihia he 56 kekona. ʻAha pōkole loa. Hoʻokaʻawale ʻia e ka huina_manawa. A laila hiki iā mākou ke hoʻokaʻawale, ʻo ia hoʻi, puʻunaue mākou i nā metric āpau i ka lōʻihi. Inā mākou e puʻunaue i kēlā me kēia metric i ka lōʻihi, e loaʻa iā mākou ka helu o nā kelepona i kekona.

Ma hope aʻe, ʻo ka nui_time no kekona kaʻu metric punahele. Ana ʻia i kekona, i kēlā me kēia kekona, ʻo ia hoʻi, ʻehia kekona i lawe ʻia i kā mākou ʻōnaehana no ka hoʻokō ʻana i kēia pūʻulu o nā noi i kēlā me kēia kekona. Inā ʻike ʻoe ma mua o hoʻokahi kekona i kēlā me kēia kekona ma laila, ʻo ia ka mea pono ʻoe e hāʻawi i ʻoi aku ma mua o hoʻokahi kumu. He metric maikaʻi loa kēia. Hiki iā ʻoe ke hoʻomaopopo i kēia hoa, no ka laʻana, pono i ʻekolu mau cores.

ʻO kēia kā mākou ʻike, ʻaʻole wau i ʻike i kekahi mea like me ia ma nā wahi ʻē aʻe. E ʻoluʻolu - he mea maʻalahi loa kēia - kekona i kekona. I kekahi manawa, inā he 100% kāu CPU, a laila he hapalua hola i kēlā me kēia kekona, ʻo ia hoʻi, ua hoʻolilo ʻoe i ka hapalua hola e hana i kēia mau noi.

A laila ʻike mākou i nā lālani i kēlā me kēia kekona. ʻIke mākou i ka nui o nā lālani i kekona i hoʻi mai.

A laila aia kekahi mea hoihoi. Ehia mau shared_buffers a mākou i heluhelu ai i kēlā me kēia kekona mai ka shared_buffers ponoʻī. Aia nā hits i laila, a lawe mākou i nā lālani mai ka cache ʻōnaehana hana a i ʻole mai ka disk. ʻO ka koho mua he wikiwiki, aʻo ka lua a hikiʻole paha ke wikiwiki, ma muli o ke kūlana.

A ʻo ke ala ʻelua o ka hoʻokaʻawale ʻana, ʻo ia ka puʻunaue i ka helu o nā noi i kēia hui. Ma ke kolamu ʻelua e hoʻokaʻawale ʻia ʻoe i hoʻokahi nīnau i kēlā me kēia nīnau. A laila he mea hoihoi - ehia mau milliseconds i loko o kēia noi. ʻIke mākou i ke ʻano o kēia nīnau ma ka awelika. 101 milliseconds i koi ʻia no kēlā me kēia noi. ʻO kēia ka metric kuʻuna pono mākou e hoʻomaopopo.

ʻEhia lālani i hoʻihoʻi ʻia kēlā me kēia nīnau ma ka awelika? ʻIke mākou i ka hoʻi ʻana o kēia hui. Ma ka awelika, ehia ka nui i lawe ʻia mai ka cache a heluhelu. ʻIke mākou ua hūnā maikaʻi nā mea a pau. Paʻa paʻa no ka hui mua.

A ʻo ka hā o ke kaula i loko o kēlā me kēia laina he aha ka hapa o ka huina. He mau kelepona kā mākou. E ʻōlelo kākou he 1. A hiki iā mākou ke hoʻomaopopo i ka hāʻawi ʻana o kēia hui. ʻIke mākou i kēia hihia, hāʻawi ka hui mua ma lalo o 000%. ʻO ia hoʻi, lohi loa ʻaʻole mākou e ʻike ma ke kiʻi holoʻokoʻa. A ʻo ka hui ʻelua he 000% ma nā kelepona. ʻO ia hoʻi, ʻo 0,01% o nā kelepona a pau ka hui ʻelua.

He hoihoi nō ka Total_time. Ua hoʻohana mākou i ka 14% o kā mākou manawa hana ma ka hui mua o nā noi. A no ka lua - 11%, etc.

ʻAʻole wau e hele i nā kikoʻī, akā aia nā subtleties ma laila. Hōʻike mākou i kahi hewa ma luna, no ka mea ke hoʻohālikelike mākou, lana paha nā kiʻi paʻi, ʻo ia hoʻi, hāʻule paha kekahi mau noi a ʻaʻole hiki ke hele hou i ka lua, a ʻike ʻia kekahi mau mea hou. A ma laila mākou e helu ai i ka hewa. Inā ʻike ʻoe i ka 0, a laila maikaʻi kēlā. ʻAʻohe hewa. Inā piʻi ka hapa hapa i ka 20%, ua maikaʻi.

A laila hoʻi mākou i kā mākou kumuhana. Pono mākou e hoʻomākaukau i ka hana. Lawe mākou mai luna a lalo a hele a hiki i ka 80% a i ʻole 90%. ʻO ka maʻamau he 10-20 mau pūʻulu kēia. A hana mākou i nā faila no pgbench. Hoʻohana maʻamau mākou ma laila. I kekahi manawa, ʻaʻole pono kēia. A ma Postgres 12 e nui aʻe nā manawa e hoʻohana ai i kēia ala.

A laila loaʻa iā mākou ka 80-90% i ka manawa_nui ma kēia ala. He aha kaʻu e kau ai ma hope o "@"? Nānā mākou i nā kelepona, nānā i ka nui o ka ukupanee a hoʻomaopopo i ka nui o ka ukupanee ma ʻaneʻi. Mai kēia mau pākēneka hiki iā mākou ke hoʻomaopopo pehea e kaulike ai i kēlā me kēia faila. Ma hope o kēlā, hoʻohana mākou i ka pgbench a hele i ka hana.

Loaʻa iā mākou K001 a me K002.

ʻO K001 hoʻokahi kaula nui me ʻehā mau kaha. He ʻano kēia o kā mākou ukana holoʻokoʻa. E ʻike i ke kolamu ʻelua a me ka lālani ʻelua. ʻIke mākou ma kahi o hoʻokahi a me ka hapa kekona i kēlā me kēia kekona, ʻo ia hoʻi inā ʻelua mau cores, a laila maikaʻi. Aia ma kahi o 75% ka nui. A e hana like me kēia. Inā loaʻa iā mākou he 10 cores, a laila e mālie mākou. Ma kēia ala hiki iā mākou ke loiloi i nā kumuwaiwai.

ʻO K002 kaʻu e kapa nei i nā papa nīnau, ʻo ia hoʻi, SELECT, INSERT, UPDATE, DELETE. A okoa E KOHO NO KA UPDATE, no ka mea, he laka.

A maʻaneʻi hiki iā mākou ke hoʻoholo i ka SELECT he poʻe heluhelu maʻamau - 82% o nā kelepona a pau, akā i ka manawa like - 74% i total_time. ʻO ia hoʻi, ua kapa ʻia lākou he nui, akā hoʻopau i nā kumuwaiwai liʻiliʻi.

A hoʻi mākou i ka nīnau: "Pehea mākou e koho ai i nā shared_buffers pono?" ʻIke au i ka hapa nui o nā hōʻailona e pili ana i ka manaʻo - e ʻike kākou i ke ʻano o ka throughput, ʻo ia hoʻi. Hoʻohana pinepine ʻia ia ma TPS a i ʻole QPS.

A ke hoʻāʻo nei mākou e ʻoki i nā hana he nui i kēlā me kēia kekona mai ke kaʻa me ka hoʻohana ʻana i nā ʻāpana tuning. Eia ka 311 no kekona no ke koho.

Akā ʻaʻohe mea e holo i ka hana a hoʻi i ka home me ka wikiwiki holoʻokoʻa. He lapuwale kēia. Pela no me na waihona. ʻAʻole pono mākou e kaʻa me ka wikiwiki piha, ʻaʻohe mea e hana. ʻAʻohe kanaka e noho ana i ka hana, nona ka 100% CPU. ʻOiai, ola paha kekahi, ʻaʻole maikaʻi kēia.

ʻO ka manaʻo, ʻo ka hoʻokele maʻamau mākou i ka 20 pakeneka o ka hiki, ʻoi aku ka maikaʻi ma mua o 50%. A ke hoʻāʻo nei mākou e hoʻonui i ka manawa pane no kā mākou mea hoʻohana ma mua o nā mea āpau. ʻO ia hoʻi, pono mākou e hoʻohuli i kā mākou knobs i loaʻa kahi latency liʻiliʻi ma 20% wikiwiki, kūlana. He manaʻo kēia a mākou e hoʻohana ai i kā mākou hoʻokolohua.

A ʻo ka mea hope loa, nā ʻōlelo paipai:

• E ʻoluʻolu e hana i ka Database Lab.
• Inā hiki, e hana ia ma ke koi i mea e wehe ai no kekahi manawa - pāʻani a hoʻolei aku. Inā he ao kou, a laila hele ʻole kēia, ʻo ia hoʻi, nui ke kū.
• E hoihoi. A inā hewa kekahi mea, e nānā me nā hoʻokolohua pehea e hana ai. Hiki ke hoʻohana ʻia ʻo Nancy e hoʻomaʻamaʻa iā ʻoe iho e nānā i ka hana ʻana o ke kumu.
• A manaʻo i ka manawa pane liʻiliʻi.
• A mai makaʻu i nā kumu Postgres. Ke hana ʻoe me nā kumu, pono ʻoe e ʻike i ka ʻōlelo Pelekane. Nui nā manaʻo ma laila, wehewehe ʻia nā mea a pau ma laila.
• A e nānā mau i ke olakino o ka waihona, ma ka liʻiliʻi hoʻokahi i kēlā me kēia ʻekolu mahina, me ka lima, a i ʻole Postgres-checkup.

ʻO kāu mau nīnau

Mahalo nui loa! He mea hoihoi loa.

ʻElua ʻāpana.

ʻAe, ʻelua ʻāpana. ʻAʻole maopopo iaʻu. Ke hana māua ʻo Nancy, hiki iā mākou ke hoʻololi i hoʻokahi ʻāpana a i ʻole kahi hui holoʻokoʻa?

Loaʻa iā mākou kahi koho delta config. Hiki iā ʻoe ke huli i laila e like me kou makemake i ka manawa hoʻokahi. Akā pono ʻoe e hoʻomaopopo i ka wā e hoʻololi ai ʻoe i nā mea he nui, hiki iā ʻoe ke huki i nā hopena hewa.

ʻAe. No ke aha wau i nīnau ai? No ka mea paʻakikī ke hana i nā hoʻokolohua inā loaʻa iā ʻoe hoʻokahi wale nō parameter. Hoʻopaʻa ʻoe iā ia, ʻike i ka hana. Hoʻokuʻu wau iā ia i waho. A laila hoʻomaka ʻoe i ka mea aʻe.

Hiki iā ʻoe ke hoʻopaʻa i ka manawa like, akā pili ia i ke kūlana, ʻoiaʻiʻo. Akā ʻoi aku ka maikaʻi o ka hoʻāʻo ʻana i hoʻokahi manaʻo. He manaʻo ko mākou i nehinei. He kūlana pili loa ko mākou. ʻElua mau config. A ʻaʻole hiki iā mākou ke hoʻomaopopo i ke kumu o ka ʻokoʻa nui. A ua ulu ka manaʻo e pono ʻoe e hoʻohana i ka dichotomy i mea e hoʻomaopopo mau ai a ʻike i ke ʻano o ka ʻokoʻa. Hiki iā ʻoe ke hana i ka hapalua o nā ʻāpana like, a laila he hapaha, a pēlā aku.

A he nīnau hou aʻe. He ʻōpiopio a ulu ka papahana. Ua mākaukau ka palapala, aia kahi wehewehe kikoʻī?

Ua hana au i kahi loulou ma laila i ka wehewehe ʻana i nā ʻāpana. Aia ma laila. Akā ʻaʻole nui nā mea i laila. Ke ʻimi nei au i ka poʻe manaʻo like. A loaʻa iaʻu i koʻu wā e hoʻokani ai. He ʻoluʻolu loa kēia. Ke hana nei kekahi me aʻu, kōkua kekahi a hana i kekahi mea ma laila. A inā makemake ʻoe i kēia kumuhana, e hāʻawi i ka manaʻo no ka mea i nalowale.

Ke kūkulu mākou i ka hale hana, malia paha e loaʻa nā manaʻo. E nana kakou. Mahalo iā ʻoe!

Aloha! Mahalo no ka hōʻike! Ua ʻike au aia ke kākoʻo ʻo Amazon. Aia kekahi manaʻo e kākoʻo iā GSP?

He nīnau maikaʻi. Hoʻomaka mākou e hana. A hoʻopau mākou i kēia manawa no ka mea makemake mākou e mālama i ke kālā. ʻO ia, aia ke kākoʻo e hoʻohana ana i ka holo ma localhost. Hiki iā ʻoe ke hana i kahi laʻana iā ʻoe iho a hana ma ka ʻāina. Ma ke ala, ʻo ia kā mākou e hana ai. Hana wau i kēia ma Getlab, ma laila ma GSP. Akā ʻaʻole mākou i ʻike i ke kumu o ka hana ʻana i kēlā ʻano orchestration, no ka mea, ʻaʻohe wahi maʻalahi o Google. Aia ??? nā manawa, akā he mau palena ko lākou. ʻO ka mea mua, he 70% wale nō kā lākou hoʻemi a ʻaʻole hiki iā ʻoe ke pāʻani me ke kumukūʻai ma laila. Ma nā wahi, hoʻonui mākou i ke kumukūʻai e 5-10% e hōʻemi i ka hiki ke kiki ʻia ʻoe. ʻO ia, mālama ʻoe i nā kiko, akā hiki ke lawe ʻia mai iā ʻoe i kēlā me kēia manawa. Inā ʻoe e ʻōlelo ʻoi aʻe ma mua o nā mea ʻē aʻe, e make ʻoe ma hope. Loaʻa iā Google nā ​​kikoʻī like ʻole. A aia kekahi palena ʻino loa - ola wale lākou no 24 mau hola. A i kekahi manawa makemake mākou e holo i kahi hoʻokolohua no nā lā 5. Akā hiki iā ʻoe ke hana i kēia ma nā kiko; ʻo nā kiko i kekahi manawa mau mahina.

Aloha! Mahalo no ka hōʻike! Ua ʻōlelo ʻoe i ka nānā ʻana. Pehea ʻoe e helu ai i nā hewa stat_statements?

He nīnau maikaʻi loa. Hiki iaʻu ke hōʻike a haʻi iā ʻoe i nā kikoʻī nui. I ka pōkole, ke nānā nei mākou i ka lana ʻana o ka pūʻulu noi: ʻehia ka mea i hāʻule a me ka nui o nā mea hou i ʻike ʻia. A laila nānā mākou i ʻelua metric: total_time a me nā kelepona, no laila ʻelua mau hewa. A ke nānā nei mākou i ka hāʻawi ʻana o nā hui lana. ʻElua mau pūʻulu: ka poʻe i haʻalele a me ka poʻe i hiki mai. E ʻike kākou i kā lākou hāʻawi ʻana i ke kiʻi holoʻokoʻa.

ʻAʻole ʻoe makaʻu e huli ia i ʻelua a ʻekolu mau manawa i ka manawa ma waena o nā kiʻi paʻi?

ʻO ia hoʻi, ua kākau inoa hou lākou a i ʻole?

No ka laʻana, ua hoʻomaka mua ʻia kēia noi, a laila hele mai a hoʻomaka hou ʻia, a laila hele hou a hoʻomaka hou. A ua helu ʻoe i kekahi mea ma ʻaneʻi, a ma hea ia mea a pau?

ʻO ka nīnau maikaʻi, pono mākou e nānā.

Ua hana au i kekahi mea like. ʻOi aku ka maʻalahi, ʻoiaʻiʻo, ua hana hoʻokahi wau. Akā, pono iaʻu e hoʻonohonoho hou, hoʻihoʻi hou i nā stat_statements a ʻike i ka manawa o ke kiʻi paʻi i ka liʻiliʻi ma mua o kahi hapa, ʻaʻole i hiki i ke kaupaku o ka nui o nā stat_statements hiki ke hōʻiliʻili ma laila. A ʻo koʻu hoʻomaopopo ʻana, ʻaʻohe mea i hoʻoneʻe ʻia.

Ae 'ae.

Akā ʻaʻole maopopo iaʻu pehea e hana ai me ka hilinaʻi.

ʻO ka mea pōʻino, ʻaʻole wau e hoʻomanaʻo pono inā hoʻohana mākou i ka kikokikona nīnau ma laila a i ʻole queryid me pg_stat_statements a nānā iā ia. Inā nānā mākou i ka queryid, a laila ma ke kumumanaʻo ke hoʻohālikelike nei mākou i nā mea like.

ʻAʻole, hiki iā ia ke hoʻokuke ʻia i nā manawa he nui ma waena o nā kiʻi paʻi a hele hou mai.

Me ka id like?

ʻAe.

E aʻo mākou i kēia. He nīnau maikaʻi. Pono kākou e aʻo. Akā i kēia manawa, ʻo ka mea a mākou e ʻike ai ua kākau ʻia 0 ...

ʻO kēia, ʻoiaʻiʻo, he hihia kakaʻikahi, akā ua pīhoihoi wau i koʻu ʻike ʻana e hiki ke hoʻoneʻe i nā stat_statemetns ma laila.

Nui nā mea ma Pg_stat_statements. Ua ʻike mākou i ka ʻoiaʻiʻo inā loaʻa iā ʻoe track_utility = on, a laila ʻike pū ʻia kāu mau pūʻulu.

ʻAeʻo ia.

A inā loaʻa iā ʻoe ka java hibernate, he random, a laila hoʻomaka ka papaʻaina hash ma laila. A i ka wā e hoʻopau ai ʻoe i kahi noi i hoʻouka ʻia, hoʻopau ʻoe i nā hui 50-100. A ʻoi aku ka paʻa o nā mea a pau ma laila. ʻO kahi ala e hakakā ai i kēia ʻo ka hoʻonui ʻana i ka pg_stat_statements.max.

ʻAe, akā pono ʻoe e ʻike i ka nui. A i kekahi mea pono mākou e makaʻala iā ia. ʻO ia kaʻu hana. ʻO ia hoʻi, loaʻa iaʻu nā pg_stat_statements.max. A ʻike wau i ka manawa o ka snapshot ʻaʻole wau i hiki i 70%. ʻAe, no laila ʻaʻohe mea i nalowale iā mākou. E hoʻoponopono hou. A mālama hou mākou. Inā ʻoi aku ka liʻiliʻi o ka paʻi kiʻi aʻe ma mua o 70, a laila ʻaʻole paha ʻoe i nalowale i kekahi mea.

ʻAe. ʻO ka paʻamau i kēia manawa he 5. A lawa kēia no nā poʻe he nui.

ʻAe maʻamau.

Video:

PS Ma koʻu inoa ponoʻī, e hoʻohui au inā loaʻa i ka Postgres ka ʻikepili huna a ʻaʻole hiki ke hoʻokomo ʻia i loko o ka hoʻāʻo ʻana, a laila hiki iā ʻoe ke hoʻohana. Anonymizer PostgreSQL. ʻO ka papahana e like me kēia:

Source: www.habr.com