🥇 Luʻu hohonu i loko o PostgreSQL helu i loko. Alexey Lesovsky

Ka unuhi ʻana o ka hōʻike 2015 na Alexey Lesovsky "Hohonu hohonu i loko o PostgreSQL i loko o nā helu helu"

ʻO ka hōʻole mai ka mea kākau o ka hōʻike: Hoʻomaopopo wau i ka lā o Nowemapa 2015 kēia hōʻike - ʻoi aku ma mua o 4 mau makahiki i hala a ua nui ka manawa i hala. ʻAʻole kākoʻo hou ʻia ka mana 9.4 i kūkākūkā ʻia ma ka hōʻike. I loko o nā makahiki 4 i hala iho nei, ua hoʻokuʻu ʻia nā mea hou 5 kahi i ʻike ʻia ai ka nui o nā mea hou, nā hoʻomaikaʻi a me nā loli e pili ana i nā helu helu, a ua pau kekahi o nā mea a ʻaʻole pili. I koʻu nānā ʻana, ua hoʻāʻo wau e kaha i kēia mau wahi i ʻole e alakaʻi hewa iā ʻoe i ka mea heluhelu. ʻAʻole au i kākau hou i kēia mau wahi, he nui, a ʻo ka hopena, e puka mai ana kahi hōʻike ʻokoʻa loa.

ʻO ka PostgreSQL DBMS kahi hana nui, a ʻo kēia ʻano hana he nui nā subsystems, ʻo ka hana i hoʻonohonoho ʻia e pili pono i ka hana o ka DBMS. I ka wā o ka hana, hōʻiliʻili ʻia nā ʻikepili a me nā ʻike e pili ana i ka hana ʻana o nā ʻāpana, kahi e hiki ai iā ʻoe ke loiloi i ka pono o PostgreSQL a hana i nā hana e hoʻomaikaʻi ai i ka hana. Eia nō naʻe, he nui kēia ʻike a hōʻike ʻia ma kahi ʻano maʻalahi. ʻO ka hoʻomaʻamaʻa ʻana i kēia ʻike a me ka unuhi ʻana i kekahi manawa he hana koʻikoʻi loa ia, a ʻo ka "zoo" o nā mea hana a me nā pono hana hiki ke hoʻopili maʻalahi i kahi DBA kiʻekiʻe.

Aloha awakea, Aloha Auinalā ʻO Aleksey koʻu inoa. E like me kā Ilya i ʻōlelo ai, e kamaʻilio wau e pili ana i nā helu PostgreSQL.

Nā helu hana ʻo PostgreSQL. ʻElua mau helu helu ʻo PostgreSQL. Nā helu hana, e kūkākūkā ʻia. A me nā helu helu hoʻonohonoho e pili ana i ka hāʻawi ʻana i ka ʻikepili. E kamaʻilio kikoʻī wau e pili ana i nā ʻikepili hana PostgreSQL, e ʻae iā mākou e hoʻoponopono i ka hana a hoʻomaikaʻi ʻia.

E haʻi wau iā ʻoe pehea e hoʻohana pono ai i nā helu helu e hoʻoponopono i nā pilikia like ʻole i loaʻa iā ʻoe a i loaʻa paha.

He aha ka mea ʻole i ka hōʻike? I ka hōʻike, ʻaʻole wau e hoʻopā i ka helu o ka mea hoʻonohonoho, no ka mea. he kumuhana kaawale keia no ka hoike kaawale e pili ana i ka malama ana o ka ikepili ma ka waihona a me ka loaa ana o ka manao o ka mea hooponopono ninau e pili ana i na ano qualitative a me quantitative o keia ikepili.

A ʻaʻohe loiloi mea hana, ʻaʻole wau e hoʻohālikelike i kekahi huahana me kekahi. ʻAʻohe hoʻolaha. E hāʻule kāua.

Makemake au e hōʻike iā ʻoe he mea pono ka hoʻohana ʻana i nā helu. Pono ia. E hoohana me ka makau ole. ʻO kā mākou mea e pono ai he SQL maʻalahi a me kahi ʻike kumu o SQL.

A e kamaʻilio mākou e pili ana i nā helu helu e koho ai e hoʻoponopono i nā pilikia.

Inā mākou e nānā iā PostgreSQL a holo i kahi kauoha ma ka ʻōnaehana hana e nānā i nā kaʻina hana, e ʻike mākou i kahi "pahu ʻeleʻele". E ʻike mākou i kekahi mau kaʻina hana e hana ai i kekahi mea, a ma ka inoa hiki iā mākou ke noʻonoʻo i kā lākou hana ma laila, nā mea a lākou e hana nei. Akā, ʻoiaʻiʻo, he pahu ʻeleʻele kēia, ʻaʻole hiki iā mākou ke nānā i loko.

Hiki iā mākou ke nānā i ka ukana CPU i loko top, hiki iā mākou ke ʻike i ka hoʻohana ʻana i ka hoʻomanaʻo ʻana e kekahi mau ʻōnaehana ʻōnaehana, akā ʻaʻole hiki iā mākou ke nānā i loko o PostgreSQL. No kēia pono mākou i nā mea hana ʻē aʻe.

A i ka hoʻomau hou ʻana, e haʻi aku wau iā ʻoe i kahi o ka manawa. Inā mākou e hōʻike iā PostgreSQL ma ke ʻano o ia ʻano hoʻolālā, a laila hiki ke pane i kahi o ka manawa. ʻElua mau mea kēia: ʻo ia ka hana ʻana i nā noi o nā mea kūʻai mai nā noi a me nā hana hope a PostgreSQL e hana ai e hoʻomau i ka holo.

Inā hoʻomaka mākou e nānā i ka ʻaoʻao hema hema, hiki iā mākou ke ʻike i ke ʻano o ka hana ʻana o nā noi mea kūʻai aku. Loaʻa ka noi mai ka noi a wehe ʻia kahi hālāwai mea kūʻai aku no ka hana hou. Hāʻawi ʻia ka noi i ka mea hoʻonohonoho. Kūkulu ka mea hoʻolālā i kahi hoʻolālā nīnau. Hoʻouna hou aku no ka hoʻokō. Aia kekahi ʻano poloka I / O ʻikepili pili me nā papa a me nā kuhikuhi. Heluhelu ʻia ka ʻikepili pono mai nā disks i ka hoʻomanaʻo ma kahi wahi kūikawā i kapa ʻia ʻo "shared buffers". ʻO nā hualoaʻa nīnau, inā he mau mea hou, holoi ʻia, ua hoʻopaʻa ʻia ma ka log transaction ma WAL. Hele kekahi ʻikepili helu i loko o ka log a i ʻole ka ʻohi helu. A hāʻawi ʻia ka hopena o ka noi i ka mea kūʻai aku. Ma hope o kēlā, hiki i ka mea kūʻai ke hana hou i nā mea āpau me kahi noi hou.

He aha kā mākou me nā hana hope a me nā kaʻina hana hope? Loaʻa iā mākou nā kaʻina hana e hoʻomau i ka holo ʻana a me ka holo maʻamau o ka waihona. E uhi pū ʻia kēia mau kaʻina hana i ka hōʻike: ʻo ia ka autovacuum, checkpointer, nā kaʻina e pili ana i ka replication, ka mea kākau moʻolelo. E hoʻopā wau i kēlā me kēia o lākou i kaʻu hōʻike.

He aha nā pilikia me ka helu?

Nui ka ʻike. Hāʻawi ʻo PostgreSQL 9.4 i nā metric 109 no ka nānā ʻana i ka ʻikepili helu. Eia nō naʻe, inā mālama ka waihona i nā papa he nui, schemas, databases, a laila pono e hoʻonui ʻia kēia mau ana a pau i ka helu o nā papa, nā waihona. ʻO ia hoʻi, ʻoi aku ka nui o ka ʻike. A he mea maʻalahi loa ke pio i loko.
ʻO ka pilikia aʻe, ʻo ia ka hōʻike ʻana o nā helu helu e nā helu helu. Inā mākou e nānā i kēia mau helu, e ʻike mākou i ka hoʻonui mau ʻana i nā helu helu. A inā ua hala ka manawa mai ka hoʻihoʻi ʻia ʻana o nā helu, e ʻike mākou i nā piliona o nā waiwai. ʻAʻole lākou e haʻi mai iā mākou i kekahi mea.
ʻAʻohe moʻolelo. Inā loaʻa iā ʻoe kekahi ʻano hemahema, hāʻule kekahi mea 15-30 mau minuke i hala, ʻaʻole hiki iā ʻoe ke hoʻohana i nā helu a ʻike i ka mea i hana ʻia 15-30 mau minuke i hala. He pilikia kēia.
ʻO ka nele o kahi mea hana i kūkulu ʻia i PostgreSQL he pilikia. ʻAʻole hāʻawi nā mea hoʻomohala kernel i kahi pono. ʻAʻohe o lākou mea like. Hāʻawi wale lākou i nā helu helu ma ka waihona. E hoʻohana, e noi aku iā ia, i nā mea āu e makemake ai, a laila e hana.
No ka mea ʻaʻohe mea hana i kūkulu ʻia i PostgreSQL, ke kumu kēia i kekahi pilikia. Nui nā mea hana ʻekolu. Ke ho'āʻo nei kēlā me kēia hui i ʻoi aku ka liʻiliʻi o nā lima pololei e kākau i kāna papahana ponoʻī. A ʻo ka hopena, he nui nā mea hana a ke kaiāulu āu e hoʻohana ai e hana me nā helu. A i loko o kekahi mau mea paahana aia kekahi mau hiʻohiʻona, ma nā mea hana ʻē aʻe ʻaʻohe hiʻohiʻona ʻē aʻe, a i ʻole kekahi mau hiʻohiʻona hou. A e kū mai kahi kūlana e pono ai ʻoe e hoʻohana i ʻelua a ʻekolu a ʻehā mau mea hana e hoʻopili ai kekahi i kekahi a loaʻa nā hana like ʻole. He mea hoʻonāukiuki loa kēia.

He aha ka hopena mai kēia? He mea nui e hiki ke lawe pololei i nā helu helu i ʻole e hilinaʻi i nā papahana, a i ʻole e hoʻomaikaʻi i kēia mau papahana iā ʻoe iho: hoʻohui i kekahi mau hana e loaʻa ai kāu pōmaikaʻi.

A pono ʻoe i ka ʻike kumu o SQL. No ka loaʻa ʻana o kekahi mau ʻikepili mai nā helu helu, pono ʻoe e hana i nā nīnau SQL, ʻo ia hoʻi, pono ʻoe e ʻike pehea e koho ai, hui ʻia.

Hōʻike ka helu helu iā mākou i kekahi mau mea. Hiki iā lākou ke hoʻokaʻawale i nā ʻāpana.

ʻO ka māhele mua nā hanana e hana nei ma ka waihona. ʻO kēia ka manawa e loaʻa ai kekahi hanana i ka waihona: kahi nīnau, kahi papaʻaina, autovacuum, commits, a laila ʻo kēia nā hanana āpau. Hoʻonui ʻia nā helu helu e pili ana i kēia mau hanana. A hiki iā mākou ke hahai i kēia mau hanana.
ʻO ka ʻāpana ʻelua ka waiwai o nā mea e like me nā papa, nā waihona. He waiwai ko lakou. ʻO kēia ka nui o nā papa. Hiki iā mākou ke hahai i ka ulu ʻana o nā papa, ka ulu ʻana o nā indexes. Hiki iā mākou ke ʻike i nā loli i ka dinamika.
A ʻo ke kolu o ka māhele ʻo ia ka manawa i hoʻohana ʻia ma ka hanana. He hanana ka noi. Loaʻa iā ia ke ana ponoʻī o ka lōʻihi. Hoʻomaka ma ʻaneʻi, pau ma ʻaneʻi. Hiki iā mākou ke hahai. ʻO ka manawa e heluhelu ai i kahi poloka mai ka disk a i ʻole ke kākau ʻana. Hoʻopili pū ʻia kēia mau mea.

Hōʻike ʻia nā kumu helu helu penei:

Ma ka hoʻomanaʻo like ʻana (shared buffers) aia kahi ʻāpana no ka waiho ʻana i ka ʻikepili static ma laila, aia kekahi mau helu helu e hoʻonui mau ʻia i ka wā e kū ai kekahi mau hanana, a i ʻole kekahi mau manawa i ka hana ʻana o ka waihona.
ʻAʻole loaʻa kēia mau helu helu a pau i ka mea hoʻohana a ʻaʻole i loaʻa i ka luna hoʻoponopono. He mau mea haʻahaʻa kēia. No ke komo ʻana iā lākou, hāʻawi ʻo PostgreSQL i kahi kikowaena ma ke ʻano o nā hana SQL. Hiki iā mākou ke koho i nā koho me ka hoʻohana ʻana i kēia mau hana a loaʻa kekahi ʻano metric (a i ʻole hoʻonohonoho ʻana o nā metric).
Eia naʻe, ʻaʻole maʻalahi ka hoʻohana ʻana i kēia mau hana, no laila ʻo nā hana ke kumu no nā manaʻo (VIEWs). ʻO kēia nā papa ʻaina e hāʻawi ana i nā ʻikepili ma kahi subsystem kikoʻī, a i ʻole kekahi mau hanana i loko o ka waihona.
ʻO kēia mau hiʻohiʻona i kūkulu ʻia (VIEWs) ka mea hoʻohana nui no ka hana ʻana me nā helu. Loaʻa iā lākou ma ka maʻamau me ka ʻole o nā hoʻonohonoho hou, hiki iā ʻoe ke hoʻohana koke iā lākou, nānā, lawe i ka ʻike mai laila. A he mau haʻi pū kekahi. He mana nā haʻawina. Hiki iā ʻoe ke hoʻouka i ka pōʻai postgresql-contrib (no ka laʻana, postgresql94-contrib), e hoʻouka i ka module e pono ai i ka hoʻonohonoho, e kuhikuhi i nā ʻāpana no ia, e hoʻomaka hou i ka PostgreSQL a hiki iā ʻoe ke hoʻohana. (Meka. Ma muli o ka hāʻawi ʻana, i nā mana hou o ka hāʻawi ʻana he ʻāpana o ka pūʻolo nui).
A aia nā haʻawina kūhelu ʻole. ʻAʻole lākou i hāʻawi ʻia me ka hoʻolaha PostgreSQL maʻamau. Pono lākou e hōʻuluʻulu a hoʻokomo ʻia ma ke ʻano he waihona. Hiki ke ʻokoʻa loa nā koho, e pili ana i ka mea i hoʻokumu ʻia e ka mea hoʻomohala o kēia haʻawina kūʻokoʻa ʻole.

Hōʻike kēia paheʻe i kēlā mau manaʻo (VIEWs) a me kekahi o kēlā mau hana i loaʻa ma PostgreSQL 9.4. E like me kā mākou e ʻike ai, nui lākou. A he mea maʻalahi loa ka huikau inā ʻoe e ʻike nei no ka manawa mua.

Eia naʻe, inā lawe mākou i ke kiʻi mua Как тратится время на PostgreSQL a kūpono me kēia papa inoa, loaʻa iā mākou kēia kiʻi. ʻO kēlā me kēia hiʻohiʻona (VIEWs), a i ʻole kēlā me kēia hana, hiki iā mākou ke hoʻohana no ke kumu hoʻokahi a i ʻole e loaʻa i nā ʻikepili kūpono ke loaʻa iā mākou ka holo ʻana o PostgreSQL. A hiki iā mākou ke loaʻa i kekahi ʻike e pili ana i ka hana o ka subsystem.

ʻO ka mea mua a mākou e nānā ai pg_stat_database. E like me kā mākou e ʻike ai, he hōʻike kēia. Loaʻa i nā ʻike he nui. ʻO ka ʻike like ʻole. A hāʻawi ia i ka ʻike maikaʻi loa i nā mea a mākou e hana nei ma ka waihona.

He aha kā mākou e lawe ai mai laila? E hoʻomaka kākou me nā mea maʻalahi loa.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

ʻO ka mea mua a mākou e nānā ai ʻo ka pākēneka o ka cache hit. He metric pono ka pākēneka o ka cache hit. Hiki iā ʻoe ke koho i ka nui o ka ʻikepili i lawe ʻia mai ka cache buffers kaʻana like, a me ka nui o ka heluhelu ʻana mai ka disk.

Ua maopopo ʻo ka ʻoi aku ka nui o ka cache i loaʻa iā mākou, ʻoi aku ka maikaʻi. Loiloi mākou i kēia metric ma ke ʻano he pākēneka. A, no ka laʻana, inā loaʻa iā mākou kahi pākēneka o kēia mau cache i ʻoi aku ma mua o 90%, a laila maikaʻi kēia. Inā hāʻule ia ma lalo o 90%, a laila ʻaʻole lawa kā mākou hoʻomanaʻo e mālama i ke poʻo wela o ka ʻikepili i ka hoʻomanaʻo. A no ka hoʻohana ʻana i kēia ʻikepili, koi ʻia ʻo PostgreSQL e komo i ka disk a ʻoi aku ka lohi ma mua o ka heluhelu ʻana o ka ʻikepili mai ka hoʻomanaʻo. A pono ʻoe e noʻonoʻo e pili ana i ka hoʻonui ʻana i ka hoʻomanaʻo: e hoʻonui i nā buffer kaʻana like, a i ʻole e hoʻonui i ka hoʻomanaʻo hao (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

He aha hou aʻe e hiki ke lawe ʻia mai kēia hōʻike? Hiki iā ʻoe ke ʻike i nā anomalies e kū nei i ka waihona. He aha ka mea i hōʻike ʻia ma ʻaneʻi? Aia nā commits, rollbacks, hana i nā faila manawa, ko lākou nui, deadlocks a me nā paio.

Hiki iā mākou ke hoʻohana i kēia noi. He mea maʻalahi kēia SQL. A hiki iā mākou ke ʻike i kēia ʻikepili no mākou iho.

A eia nā waiwai paepae. Nānā mākou i ka ratio o nā hana a me nā rollbacks. ʻO Commits kahi hōʻoia maikaʻi o ke kālepa. ʻO Rollbacks he rollback, ʻo ia hoʻi, ua hana ke kālepa i kekahi hana, kānana i ka ʻikepili, noʻonoʻo i kekahi mea, a laila ua kū ʻole, a ua hoʻolei ʻia nā hopena o ke kālepa. ʻo ia hoʻi. ʻino ka nui o nā rollbacks e hoʻonui mau ana. A pono ʻoe e pale aku iā lākou, a hoʻoponopono i ke code i hiki ʻole kēia.

Pili nā paio i ka hana hou. A pono e pale ʻia lākou. Inā loaʻa iā ʻoe kekahi mau nīnau i hoʻokō ʻia ma ka replica a kū mai nā paio, a laila pono ʻoe e nānā i kēia mau paio a ʻike i ka mea e hana nei. Hiki ke loaʻa nā kikoʻī ma nā moʻolelo. A hoʻoholo i nā paio i hiki i nā noi noi ke hana me ka hewa ʻole.

ʻO Deadlocks kekahi kūlana maikaʻi ʻole. I ka wā e hoʻokūkū ai nā noi no nā kumuwaiwai, ua komo kekahi noi i hoʻokahi punawai a lawe i ka laka, ua komo ka lua o ka noi i ka lua o ka punawai a lawe pū i ka laka, a laila komo nā noi ʻelua i nā kumuwaiwai a pāpā ʻia ke kali ʻana o ka hoalauna e hoʻokuʻu i ka laka. He kūlana pilikia nō hoʻi kēia. Pono lākou e kamaʻilio ma ke kiʻekiʻe o ke kākau hou ʻana i nā noi a me ka serializing i ke komo ʻana i nā kumuwaiwai. A inā ʻike ʻoe i ka piʻi mau ʻana o kāu mau deadlocks, pono ʻoe e nānā i nā kikoʻī i nā lāʻau, e nānā i nā kūlana i kū mai a ʻike i ke ʻano o ka pilikia.

He ʻino nō hoʻi nā faila manawa (temp_files). Inā ʻaʻole lawa ka hoʻomanaʻo o ka mea hoʻohana no ka hoʻokomo ʻana i ka ʻikepili hana, ka manawa pōkole, hana ia i kahi faila ma ka disk. A ʻo nā hana a pau i hiki iā ia ke hana i loko o kahi hoʻomanaʻo hoʻomanaʻo e hoʻomaka e hana ma ka disk. Ua lohi. Hoʻonui kēia i ka manawa hoʻokō nīnau. A ʻo ka mea kūʻai aku i hoʻouna i kahi noi iā PostgreSQL e loaʻa iā ia kahi pane ma hope iki. Inā hana ʻia kēia mau hana a pau i ka hoʻomanaʻo, e pane wikiwiki aku ʻo Postgres a e kali liʻiliʻi ka mea kūʻai aku.

pg_stat_bgwriter - Hōʻike kēia ʻike i ka hana ʻana o ʻelua mau ʻōnaehana hope PostgreSQL: checkpointer и background writer.

No ka hoʻomaka ʻana, e nānā kākou i nā wahi mana, ka mea i kapa ʻia. checkpoints. He aha nā māka? ʻO kahi mākaʻikaʻi kahi kūlana i loko o ka log transaction e hōʻike ana ua hoʻonohonoho pono ʻia nā hoʻololi ʻikepili āpau i hana ʻia ma ka log me ka ʻikepili ma ka disk. ʻO ke kaʻina hana, ma muli o ka hana a me nā hoʻonohonoho, hiki ke lōʻihi a ʻo ka hapa nui o ka hoʻonohonoho ʻana i nā ʻaoʻao lepo i nā buffer kaʻana me nā faila ʻikepili ma ka disk. No ke aha ia? Inā komo ʻo PostgreSQL i ka diski i nā manawa a pau a lawe i ka ʻikepili mai laila mai, a kākau i ka ʻikepili ma kēlā me kēia komo, e lohi. No laila, loaʻa i ka PostgreSQL kahi māhele hoʻomanaʻo, ʻo ka nui o ia mea e pili ana i nā ʻāpana i ka hoʻonohonoho. Hoʻokaʻawale ʻo Postgres i ka ʻikepili hana i kēia hoʻomanaʻo no ka hana hou ʻana a i ʻole ka nīnau ʻana. I ka hihia o nā noi hoʻololi ʻikepili, hoʻololi ʻia lākou. A loaʻa iā mākou nā mana ʻelua o ka ʻikepili. Aia kekahi ma ka hoʻomanaʻo, aia kekahi ma ka disk. A i kēlā me kēia manawa pono ʻoe e synchronize i kēia ʻikepili. Pono mākou i ka mea i hoʻololi ʻia i ka hoʻomanaʻo e hoʻonohonoho ʻia i ka disk. Pono kēia i kahi mākaʻikaʻi.

Hele ʻo Checkpoint i nā buffer kaʻana like, hōʻailona i nā ʻaoʻao lepo e pono ai lākou no ka māka. A laila hoʻomaka ka lua o ka hele ʻana ma o nā buffer kaʻana like. A ʻo nā ʻaoʻao i kaha ʻia no ka checkpoint, ua hoʻonohonoho ʻo ia iā lākou. No laila, ua hoʻonohonoho ʻia ka ʻikepili me ka disk.

ʻElua ʻano o nā wahi mana. Hoʻokō ʻia kekahi wahi hōʻoia i ka manawa pau. He mea pono a maikaʻi kēia wahi nānā - checkpoint_timed. A aia nā wahi nānā ma ke koi - checkpoint required. Hana ʻia kēlā ʻano hōʻoia inā loaʻa iā mākou kahi moʻolelo ʻikepili nui loa. Ua hoʻopaʻa mākou i ka nui o nā moʻolelo kālepa. A manaʻoʻiʻo ʻo PostgreSQL pono ia e hoʻonohonoho i kēia mau mea āpau i ka hikiwawe, e hana i kahi hōʻoia a neʻe.

A inā ʻoe e nānā i ka helu helu pg_stat_bgwriter a ʻike i kāu mea ʻoi aku ka nui o ka checkpoint_req ma mua o ka checkpoint_timed, a laila hewa kēia. No ke aha ʻino? ʻO ia ke ʻano o PostgreSQL ma lalo o ke koʻikoʻi mau i ka wā e pono ai e kākau i ka ʻikepili i ka disk. ʻAʻole hoʻoluhi koʻikoʻi ka wahi hōʻoia ma ka manawa hoʻopau a hoʻokō ʻia e like me ka papa kuhikuhi kūloko a, me he mea lā, hoʻolōʻihi ʻia i ka manawa. Loaʻa i ka PostgreSQL ka hiki ke hoʻomaha i ka hana a ʻaʻole hoʻopaʻa i ka subsystem disk. Pono kēia no PostgreSQL. A ʻo nā noi i hoʻokō ʻia i ka wā o ka mākaʻikaʻi ʻaʻole e ʻike i ke koʻikoʻi ma muli o ka hana ʻana o ka subsystem disk.

A aia ʻekolu mau ʻāpana e hoʻoponopono ai i ka helu helu:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Hāʻawi lākou iā ʻoe e hoʻokele i ka hana o nā wahi mana. Akā, ʻaʻole wau e noʻonoʻo iā lākou. He pilikia kaawale ko lakou mana.

Pākuʻi: ʻAʻole pili hou ka mana 9.4 i manaʻo ʻia ma ka hōʻike. I nā mana hou o PostgreSQL, ka palena checkpoint_segments pani ʻia e nā ʻāpana min_wal_size и max_wal_size.

ʻO ka subsystem aʻe ka mea kākau hope − background writer. He aha kāna e hana nei? Holo mau ia i loko o ka loop pau ole. Hoʻopili ʻo ia i nā ʻaoʻao i loko o nā buffer kaʻana like a holoi i nā ʻaoʻao lepo i loaʻa iā ia i ka disk. Ma kēia ala, kōkua ia i ka mea nānā i ka hana liʻiliʻi i ka wā e nānā ai.

He aha hou kāna e pono ai? Hāʻawi ia i ka pono o nā ʻaoʻao maʻemaʻe i nā buffer kaʻana inā makemake koke ʻia lākou (ma ka nui a me ka wikiwiki) e hoʻokomo i ka ʻikepili. Inā paha i kū mai kahi kūlana inā makemake ka noi i nā ʻaoʻao maʻemaʻe a aia lākou i nā buffer kaʻana like. Postgres backend lawe wale ʻo ia a hoʻohana, ʻaʻole pono ʻo ia e hoʻomaʻemaʻe iā ia iho. Akā inā ʻaʻohe ʻaoʻao like ʻole, hoʻomaha ka hope a hoʻomaka e ʻimi i nā ʻaoʻao e holoi iā lākou i ka disk a lawe iā lākou no kāna pono ponoʻī - e hoʻopilikia maikaʻi ʻole i ka manawa o ka noi e hoʻokō nei. Inā ʻike ʻoe he ʻāpana kāu maxwritten_clean nui, ʻo ia hoʻi ʻaʻole hana ka mea kākau hope i kāna hana a pono ʻoe e hoʻonui i nā ʻāpana bgwriter_lru_maxpagesi hiki iā ia ke hana i nā hana hou aʻe i hoʻokahi pōʻai, hoʻomaʻemaʻe i nā ʻaoʻao hou aʻe.

A ʻo kekahi hōʻailona pono loa buffers_backend_fsync. ʻAʻole hana nā hope i ka fsync no ka mea lohi. Hāʻawi lākou i ka fsync i ka IO stack checkpointer. Loaʻa i ka checkpointer kāna laina ponoʻī, hana ia i ka fsync a hoʻonohonoho i nā ʻaoʻao i ka hoʻomanaʻo me nā faila ma ka disk. Inā nui a piha ka queue checkpointer, a laila koi ʻia ka hope e hana i ka fsync ponoʻī a hoʻolohi kēia i ka hope., ʻo ia hoʻi, e loaʻa i ka mea kūʻai ka pane ma mua o ka hiki. Inā ʻike ʻoe he ʻoi aku ka nui o kēia waiwai ma mua o ka zero, a laila ua pilikia kēia a pono ʻoe e hoʻolohe i nā hoʻonohonoho o ka mea kākau hope a loiloi pū i ka hana o ka subsystem disk.

Pākuʻi: _Ke wehewehe nei ka kikokikona ma lalo nei i nā manaʻo helu helu e pili ana me ka hoʻopili hou ʻana. ʻO ka hapa nui o ka nānā a me nā inoa hana i kapa hou ʻia ma Postgres 10. ʻO ke kumu o nā inoa hou ke hoʻololi. xlog maluna o wal и location maluna o lsn i nā inoa hana / nānā, etc. Laʻana kūikawā, hana pg_xlog_location_diff() ua kapa hou ia pg_wal_lsn_diff()._

Nui nō hoʻi kā mākou ma ʻaneʻi. Akā, pono mākou i nā mea pili i ka wahi.

Inā ʻike mākou ua like nā waiwai āpau, a laila maikaʻi kēia a ʻaʻole haʻalele ka replica ma hope o ka haku.

ʻO kēia kūlana hexadecimal ma ʻaneʻi ke kūlana ma ka log transaction. Hoʻonui mau ia inā aia kekahi hana ma ka waihona: hoʻokomo, holoi, etc.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Inā ʻokoʻa kēia mau mea, aia kekahi ʻano lag. ʻO Lag ka lag o ka replica mai ka haku, ʻo ia hoʻi, ʻokoʻa ka ʻikepili ma waena o nā kikowaena.

ʻEkolu kumu o ka lohi:

ʻO ia ka subsystem disk hiki ʻole ke mālama i nā faila sync kākau.
He mau hewa pūnaewele paha kēia, a i ʻole ka hoʻonui ʻana o ka pūnaewele, ke loaʻa ʻole ka manawa o ka ʻikepili e hiki ai i ka replica a ʻaʻole hiki iā ia ke hana hou.
A me ka mea hana. He mea laha loa ka mea hana. A ʻelua a ʻekolu mau manawa aʻu i ʻike ai, akā hiki nō hoʻi ia.

A eia nā nīnau ʻekolu e ʻae iā mākou e hoʻohana i nā ʻikepili. Hiki iā mākou ke hoʻohālikelike i ka nui o nā mea i hoʻopaʻa ʻia i kā mākou moʻolelo kālepa. Aia kekahi hana pela pg_xlog_location_diff a hiki iā mākou ke hoʻohālikelike i ka lag replication ma nā bytes a me nā kekona. Hoʻohana pū mākou i ka waiwai mai kēia ʻike (VIEWs) no kēia.

'Ōlelo Aʻo: _Ma kahi o pg_xlog_locationdiff (), hiki iā ʻoe ke hoʻohana i ka mea hoʻohana unuhi a unuhi i kahi wahi mai kekahi. ʻoluʻolu.

Me kahi lag, aia i kekona, aia hoʻokahi manawa. Inā ʻaʻohe hana ma ka haku, aia ke kālepa ma kahi o 15 mau minuke aku nei a ʻaʻohe hana, a inā mākou e nānā i kēia lag ma ka replica, e ʻike mākou i kahi lag o 15 mau minuke. Pono kēia e hoʻomanaʻo. A hiki ke alakaʻi i ka stupor ke nānā ʻoe i kēia lag.

ʻO pg_stat_all_tables kekahi ʻike pono. Hōʻike ia i nā helu ma nā papa. Ke loaʻa iā mākou nā papa i ka waihona, aia kekahi hana me ia, kekahi mau hana, hiki iā mākou ke loaʻa kēia ʻike mai kēia ʻike.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

ʻO ka mea mua a mākou e nānā ai, ʻo ia ka nānā ʻana i ka papa kuhikuhi. ʻAʻole hewa ka helu ponoʻī ma hope o kēia mau paukū a ʻaʻole ia e hōʻike e pono mākou e hana i kekahi mea.

Eia nō naʻe, aia kekahi metric lua - seq_tup_read. ʻO kēia ka helu o nā lālani i hoʻihoʻi ʻia mai ka sequential scan. Inā ʻoi aku ka nui o ka helu awelika ma mua o 1, 000, 10, 000, a laila he hōʻailona kēia e pono ai ʻoe e kūkulu i kahi index ma kahi e hiki ai ke komo ma ka index, a i ʻole hiki ke hoʻopaʻa i nā nīnau e hoʻohana ana i nā scan sequential. ʻaʻole hiki kēia.

He laʻana maʻalahi - e ʻōlelo kākou i kahi noi me ka OFFSET nui a me ka LIMIT pono ia. No ka laʻana, ʻimi ʻia nā lālani 100 i loko o ka papaʻaina a ma hope o ka lawe ʻia ʻana o 000 mau lālani i koi ʻia, a hoʻolei ʻia nā lālani i nānā mua ʻia. He hihia ino no hoi keia. A pono e hoʻonui ʻia kēlā mau noi. A eia kahi nīnau SQL maʻalahi kahi āu e ʻike ai a loiloi i nā helu i loaʻa.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Hiki ke loaʻa nā nui papa me ka hoʻohana ʻana i kēia pākaukau a me ka hoʻohana ʻana i nā hana ʻē aʻe pg_total_relation_size(), pg_relation_size().

Ma ka laulā, aia nā metacommands dt и di, hiki iā ʻoe ke hoʻohana i ka PSQL a ʻike pū i ka nui o ka papaʻaina a me ka helu kuhikuhi.

Eia nō naʻe, kōkua ka hoʻohana ʻana i nā hana iā mākou e nānā i ka nui o nā papa, a me ka lawe ʻana i nā indexes, a i ʻole me ka lawe ʻole ʻana i nā indexes, a ua hana i kekahi mau manaʻo e pili ana i ka ulu ʻana o ka waihona, ʻo ia hoʻi ke ʻano o ka ulu ʻana me mākou, me he aha ka ikaika, a ua huki i kekahi mau manaʻo e pili ana i ka hoʻonui ʻana.

Kākau hana. He aha ka mooolelo? E nana kakou i ka hana UPDATE - ka hana o ka hoʻonui ʻana i nā lālani ma ka papaʻaina. ʻO ka ʻoiaʻiʻo, ʻelua mau hana hou (a ʻoi aku paha). Ke hoʻokomo nei kēia i kahi mana lālani hou a e kaha ana i ka mana o ka lālani kahiko me he mea lā. Ma hope, hele mai ka autovacuum a hoʻomaʻemaʻe i kēia mau mana kahiko o nā laina, e kaha i kēia wahi i loaʻa no ka hoʻohana hou ʻana.

Eia kekahi, ʻaʻole e pili ana i ka hoʻonui ʻana i kahi papaʻaina. He hōʻano hou ʻo ia. Inā he nui kāu mau kuhikuhi ma ka papaʻaina, a laila me ka hoʻonui ʻana, pono e hoʻonui hou ʻia nā papa kuhikuhi a pau kahi i hoʻonui ʻia ai nā kahua i ka nīnau. Loaʻa i kēia mau papa kuhikuhi nā mana lālani kahiko e pono e hoʻomaʻemaʻe ʻia.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

A ma muli o kāna hoʻolālā, ʻo UPDATE kahi hana kaumaha. Akā hiki iā lākou ke maʻalahi. ʻAi hot updates. Ua ʻike ʻia lākou ma PostgreSQL version 8.3. A he aha kēia? He hōʻano hou kēia i hiki ʻole ke hana hou i nā kuhikuhi. ʻO ia hoʻi, ua hōʻano hou mākou i ka moʻolelo, akā ʻo ka moʻolelo wale nō ma ka ʻaoʻao (no ka papaʻaina) i hōʻano hou ʻia, a ke kuhikuhi nei nā kuhikuhi i ka moʻolelo like ma ka ʻaoʻao. Aia kekahi ʻano manaʻo hoihoi o ka hana, i ka wā e hiki mai ai kahi ʻūhā, a laila aia kēia mau kaulahao hot hana hou a hoʻomau nā mea a pau me ka hoʻonui ʻole ʻana i nā indexes, a hiki i nā mea āpau me ka liʻiliʻi o nā kumu waiwai.

A ke loaʻa iā ʻoe n_tup_hot_upd nui, maikaʻi loa. ʻO ia ke ʻano o ka hoʻonui ʻia ʻana o ka māmā a ʻoi aku ka liʻiliʻi no mākou ma ke ʻano o nā kumuwaiwai a maikaʻi nā mea āpau.

ALTER TABLE table_name SET (fillfactor = 70);

Pehea e hoʻonui ai i ka leo hot updateov? Hiki iā mākou ke hoʻohana fillfactor. Hoʻoholo ia i ka nui o ka hakahaka manuahi i ka hoʻopiha ʻana i kahi ʻaoʻao i ka papa me ka hoʻohana ʻana i nā INSERT. Ke hele nā mea hoʻokomo i ka papaʻaina, hoʻopiha piha lākou i ka ʻaoʻao, mai waiho i kahi hakahaka i loko. A laila hōʻike ʻia kahi ʻaoʻao hou. Hoʻopiha hou ʻia ka ʻikepili. A ʻo kēia ka hana maʻamau, fillfactor = 100%.

Hiki iā mākou ke hoʻonohonoho i ka fillfactor i 70%. ʻO ia hoʻi, me nā mea hoʻokomo, ua hoʻokaʻawale ʻia kahi ʻaoʻao hou, akā ʻo 70% wale nō o ka ʻaoʻao i hoʻopiha ʻia. A loaʻa iā mākou he 30% i waiho ʻia. I ka wā e pono ai ʻoe e hana i kahi mea hou, e hana ʻia ia ma ka ʻaoʻao hoʻokahi, a kūpono ka mana hou o ka lālani ma ka ʻaoʻao like. A e hana ʻia ka hot_update. He mea maʻalahi kēia i ke kākau ʻana ma nā papa.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Līlani autovacuum. ʻO Autovacuum kahi subsystem kahi liʻiliʻi loa nā helu ma PostgreSQL. Hiki iā mākou ke ʻike ma nā papa ma pg_stat_activity i ka nui o nā vacuums i loaʻa iā mākou i kēia manawa. Eia nō naʻe, paʻakikī loa ka hoʻomaopopo ʻana i ka nui o nā papa ma ka pila i loaʻa iā ia ma ka hele.

'Ōlelo Aʻo: _Mai ka Postgres 10, ʻoi aku ka maikaʻi o ke kūlana me ka nānā ʻana i ka vacuum vacuum - ua ʻike ʻia ka ʻike pg_stat_progressvacuum, ka mea maʻalahi loa i ka pilikia o ka nānā ʻana i ka autovacuum.

Hiki iā mākou ke hoʻohana i kēia nīnau maʻalahi. A hiki iā mākou ke ʻike i ka wā e hana ʻia ai ka vacuum. Akā, pehea a i ka manawa hea e hoʻomaka ai ka vacuum? ʻO kēia nā mana kahiko o nā kaula aʻu i kamaʻilio mua ai. Ua loaʻa ka hōʻano hou, ua hoʻokomo ʻia ka mana hou o ka lālani. Ua puka mai kekahi mana kahiko o ke kaula. Papakaukau pg_stat_user_tables aia kekahi ʻāpana like n_dead_tup. Hōʻike ia i ka helu o nā lālani "make". A i ka manawa i lilo ai ka nui o nā lālani make ma mua o kahi paepae, e hele mai kahi autovacuum i ka papaʻaina.

A pehea e helu ʻia ai kēia paepae? He pākēneka kikoʻī loa kēia o ka huina o nā lālani ma ka pākaukau. Aia kahi ʻāpana autovacuum_vacuum_scale_factor. E wehewehe ana i ka pākēneka. E ʻōlelo kākou he 10% + aia kahi paepae kumu hou o 50 laina. A he aha ka hopena? Ke loaʻa iā mākou nā lālani make ma mua o "10% + 50" o nā lālani a pau i ka papaʻaina, kau mākou i ka papa ma ka autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Eia naʻe, hoʻokahi wahi. Nā paepae kumu no nā ʻāpana av_base_thresh и av_scale_factor hiki ke hāʻawi ʻia i kēlā me kēia. A, no laila, ʻaʻole honua ka paepae, akā hoʻokahi no ka papaʻaina. No laila, no ka helu ʻana, pono ʻoe e hoʻohana i nā mea hoʻopunipuni. A inā makemake ʻoe, hiki iā ʻoe ke nānā i ka ʻike o kā mākou mau hoa hana mai Avito (ʻaʻole kūpono ka loulou ma ka slide a ua hoʻonui ʻia i ka kikokikona).

Ua kākau lākou no munin plugine noonoo ana i keia mau mea. Aia kahi lole wāwae ma nā ʻāpana ʻelua. Akā, noʻonoʻo pono ʻo ia a me ka maikaʻi e hiki ai iā mākou ke loiloi i kahi e pono ai mākou i ka nui o ka vacuum no nā papa ʻaina, kahi liʻiliʻi.

He aha kā mākou e hana ai no ia mea? Inā loaʻa iā mākou kahi laina lōʻihi a ʻaʻole hiki i ka autovacuum ke hoʻokō, a laila hiki iā mākou ke hoʻonui i ka nui o nā limahana hana, a i ʻole e hoʻoikaika i ka ʻenekini.i mea e hoʻomaka mua ai, e hana i ka papaʻaina i nā ʻāpana liʻiliʻi. A pēlā e emi ai ka lālani. - ʻO ka mea nui ma ʻaneʻi e nānā i ka ukana ma nā disks, no ka mea. ʻAʻole manuahi ka mea vacuum, ʻoiai me ka hiki ʻana mai o nā polokalamu SSD / NVMe, ua emi iki ka pilikia.

ʻO pg_stat_all_indexes he mau helu helu ma nā kuhikuhi. ʻAʻole nui ʻo ia. A hiki iā mākou ke loaʻa ka ʻike e pili ana i ka hoʻohana ʻana i nā kuhikuhi mai ia mea. A no ka laʻana, hiki iā mākou ke hoʻoholo i nā index i loaʻa iā mākou keu.

E like me kaʻu i ʻōlelo mua ai, ʻAʻole wale ka hoʻopou hou ʻana i nā papa ʻaina, ʻo ia hoʻi ka hōʻano hou ʻana i nā indexes. No laila, inā he nui kā mākou mau kuhikuhi ma ka papaʻaina, a laila i ka wā e hoʻonui ai i nā lālani i ka papaʻaina, pono e hoʻonui hou ʻia nā kuhikuhi o nā kahua kuhikuhi, a inā loaʻa iā mākou nā index i hoʻohana ʻole ʻia no ka mea ʻaʻohe scan index, a laila kau lākou me mākou ma ke ʻano he ballast. A pono ʻoe e hoʻopau iā lākou. No kēia pono mākou i kahi māla idx_scan. Nānā wale mākou i ka helu o nā scans index. Inā loaʻa nā helu helu helu ʻole i kahi manawa lōʻihi o ka mālama ʻana i nā helu helu (ma ka liʻiliʻi he 2-3 mau pule), a laila ʻoi aku ka maikaʻi o kēia mau kuhikuhi, pono mākou e kāpae iā lākou.

'Ōlelo Aʻo: Ke ʻimi nei i nā ʻōlelo kuhikuhi i hoʻohana ʻole ʻia i ka hihia o ka hoʻoheheʻe ʻana i nā puʻupuʻu, pono ʻoe e nānā i nā nodes āpau o ka pūʻulu, no ka mea ʻAʻole honua nā helu helu, a inā ʻaʻole hoʻohana ʻia ka index ma ka haku, a laila hiki ke hoʻohana ʻia ma nā replicas (inā loaʻa kahi ukana).

ʻElua loulou:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

He mau laʻana nīnau ʻoi aku kēia no ka ʻimi ʻana i nā index i hoʻohana ʻole ʻia.

ʻO ka loulou ʻelua he nīnau hoihoi. Aia kahi loina non-trivial loa i loko. Manaʻo wau no ka loiloi.

He aha nā mea ʻē aʻe e hōʻuluʻulu ʻia e nā indexes?

He ʻino nā ʻōlelo kuhikuhi i hoʻohana ʻole ʻia.
Lawe lākou i kahi ākea.
Hoʻolohi i nā hana hou.
ʻO ka hana ʻē aʻe no ka vacuum.

Inā hoʻoneʻe mākou i nā index i hoʻohana ʻole ʻia, a laila e hoʻomaikaʻi wale mākou i ka waihona.

ʻO ka ʻike aʻe pg_stat_activity. He analogue keia o ka pono ps, ma PostgreSQL wale nō. Ina ps'ohm ke nānā nei ʻoe i nā kaʻina hana i ka ʻōnaehana hana, a laila pg_stat_activity e hōʻike iā ʻoe i ka hana i loko o PostgreSQL.

He aha kā mākou e lawe ai mai laila?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Hiki iā mākou ke ʻike i ka hana holoʻokoʻa e hana nei ma ka waihona. Hiki iā mākou ke hana i kahi hoʻolālā hou. Ua pahū nā mea a pau ma laila, ʻaʻole ʻae ʻia nā pilina hou, ke kahe nei nā hewa i ka noi.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Hiki iā mākou ke holo i kahi nīnau e like me kēia a ʻike i ka huina pākēneka o nā pilina pili i ka palena pili loa a ʻike i ka mea i loaʻa iā mākou nā pilina. A i kēia hihia i hāʻawi ʻia, ʻike mākou i kēlā mea hoʻohana cron_role wehe 508 pili. A ua loaʻa kekahi mea iā ia. Pono ʻoe e hana me ia a ʻike. A he mea hiki loa kēia i kekahi ʻano helu anomali o nā pilina.

Inā loaʻa iā mākou kahi ukana OLTP, pono e wikiwiki nā nīnau, wikiwiki loa, a ʻaʻole pono nā nīnau lōʻihi. Eia naʻe, inā he mau noi lōʻihi, a laila i ka wā pōkole ʻaʻohe mea e hopohopo ai, akā i ka wā lōʻihi, hoʻopōʻino nā nīnau lōʻihi i ka waihona, hoʻonui lākou i ka hopena bloat o nā papa i ka wā e hiki mai ai ka ʻāpana papaʻaina. Pono e hoʻopau ʻia nā nīnau bloat a lōʻihi.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

E ʻoluʻolu: me ia noi, hiki iā mākou ke wehewehe i nā noi lōʻihi a me nā hana. Hoʻohana mākou i ka hana clock_timestamp() e hoʻoholo i ka manawa hana. ʻO nā noi lōʻihi i loaʻa iā mākou, hiki iā mākou ke hoʻomanaʻo iā lākou, hoʻokō iā lākou explain, e nānā i nā hoʻolālā a me ka hoʻonui ʻana. Pana mākou i nā noi lōʻihi o kēia manawa a ola.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

ʻAʻole i hana nā hana maikaʻi ʻole i ke kālepa a me ka hana ʻole i ka hana (aborted).

He aha ka manaʻo? Nui nā mokuʻāina i nā kālepa. A hiki i kekahi o kēia mau moku'āina ke lawe i kēlā me kēia manawa. Aia kahi kahua e wehewehe i nā mokuʻāina state ma keia manao. A hoʻohana mākou iā ia e hoʻoholo i ka mokuʻāina.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

A, e like me kaʻu i ʻōlelo ai ma luna, kēia mau mokuʻāina ʻelua ʻino ka palaualelo a me ka palaualelo (aborted). He aha ia? ʻO kēia ka manawa i wehe ai ka noi i kahi kālepa, hana i kekahi mau hana a hele i kāna ʻoihana. Ke wehe nei ke kālepa. Ke kau nei, ʻaʻohe mea i loaʻa i loko, pono ia i kahi pilina, laka i nā lālani i hoʻololi ʻia a hiki ke hoʻonui i ka bloat o nā papa ʻē aʻe, ma muli o ke ʻano o ka mīkini transactional Postrges. A pono e pana ʻia kēlā mau hana, no ka mea, he mea pōʻino lākou ma ka laulā, i kēlā me kēia hihia.

Inā ʻike ʻoe aia ʻoe ma mua o 5-10-20 o lākou i kāu waihona, a laila pono ʻoe e hopohopo a hoʻomaka e hana i kekahi mea me lākou.

Maʻaneʻi mākou e hoʻohana ai no ka manawa helu clock_timestamp(). Hoʻopuka mākou i nā kālepa, hoʻonui mākou i ka noi.

E like me kaʻu i ʻōlelo ai ma luna nei, ʻo nā loka ka wā e hoʻokūkū ai ʻelua a ʻoi aku paha no ka hoʻokahi a i ʻole kahi hui o nā kumuwaiwai. No keia mea, he kahua ko makou waiting me ka waiwai boolean true ai ole ia, false.

ʻOiaʻiʻo - ʻo ia hoʻi ke kali nei ke kaʻina hana, pono e hana ʻia kekahi mea. Ke kali nei kahi kaʻina hana, a laila ke kali pū nei ka mea nāna i hoʻomaka i ke kaʻina hana. Noho ka mea kūʻai ma ka polokalamu kele pūnaewele a kali pū kekahi.

Pākuʻi: _E hoʻomaka ana mai Postgres 9.6, ke kahua waiting wehe ʻia a pani ʻia e ʻelua mau kahua ʻike wait_event_type и wait_event._

He aha kaʻu e hana ai? Inā ʻike ʻoe i ka ʻoiaʻiʻo no ka manawa lōʻihi, a laila pono ʻoe e kāpae i kēlā mau noi. Paʻi wale mākou i kēlā mau kālepa. Kākau mākou i nā mea hoʻomohala i nā mea e pono e hoʻonui ʻia i ʻole he lāhui no nā kumuwaiwai. A laila hoʻolālā nā mea hoʻomohala i ka noi i hiki ʻole kēia.

A he hihia koʻikoʻi, akā ʻaʻole hiki ke make ka hiki ʻana mai o nā paʻa. Ua hōʻano hou ʻia nā hana ʻelua i ʻelua mau kumuwaiwai, a laila komo hou lākou iā lākou, i mua o nā kumuwaiwai kūʻē. ʻO PostgreSQL i kēia hihia e lawe a pana i ke kālepa pono'ī i hiki i kekahi ke hoʻomau i ka hana. He kūlana make kēia a ʻaʻole maopopo iā ia iho. No laila, koi ʻia ʻo PostgreSQL e hana i nā hana koʻikoʻi.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

A eia nā nīnau ʻelua e ʻae iā ʻoe e hahai i nā laka. Hoʻohana mākou i ka ʻike pg_locks, hiki iā ʻoe ke hahai i nā laka kaumaha.

A ʻo ka loulou mua ka kikokikona noi ponoʻī. He lōʻihi loa.

A ʻo ka loulou ʻelua he ʻatikala ma nā loka. He mea pono ke heluhelu, hoihoi loa ia.

No laila he aha kā mākou e ʻike nei? ʻIke mākou i ʻelua noi. Kalepa me ALTER TABLE he hana keakea. Ua hoʻomaka, akā ʻaʻole i pau, a ke hana nei ka noi i hoʻopuka i kēia kālepa i nā mea ʻē aʻe ma kahi. A ʻo ka lua o ka noi ʻo ia ka hoʻohou. Ke kali nei ʻo ia i ka pau ʻana o ka papa hoʻololi ma mua o ka hoʻomau ʻana i kāna hana.

ʻO kēia ka mea e hiki ai iā mākou ke ʻike i ka mea i hoʻopaʻa ʻia ʻo wai, ʻo wai ka mea e paʻa ana iā wai, a hiki iā mākou ke hana hou i kēia.

ʻO ka module aʻe pg_stat_statements. E like me kaʻu i ʻōlelo ai, he module ia. No ka hoʻohana ʻana, pono ʻoe e hoʻouka i kāna waihona i ka hoʻonohonoho, hoʻomaka hou i ka PostgreSQL, e hoʻokomo i ka module (me hoʻokahi kauoha), a laila e loaʻa iā mākou kahi ʻike hou.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

He aha kā mākou e lawe ai mai laila? Inā mākou e kamaʻilio e pili ana i nā mea maʻalahi, hiki iā mākou ke lawe i ka awelika o ka manawa hoʻokō nīnau. Ke ulu nei ka manawa, ʻo ia hoʻi ke pane mālie nei ʻo PostgreSQL a pono e hana ʻia kekahi mea.

Hiki iā mākou ke ʻike i nā hana kākau ʻoi loa i loko o ka waihona e hoʻololi i ka ʻikepili i nā buffer kaʻana. E ʻike i ka mea nāna e hōʻano hou a holoi paha i ka ʻikepili ma laila.

A hiki iā mākou ke nānā i nā helu ʻokoʻa no kēia mau nīnau.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Mākou pg_stat_statements hoʻohana ʻia e kūkulu i nā hōʻike. Hoʻonohonoho hou mākou i nā helu helu hoʻokahi i ka lā. E hōʻiliʻili kākou. Ma mua o ka hoʻihoʻi ʻana i nā helu i ka manawa aʻe, kūkulu mākou i kahi hōʻike. Eia kahi loulou i ka hōʻike. Hiki iā ʻoe ke nānā.

He aha kā mākou e hana nei? Ke helu nei mākou i nā ʻikepili holoʻokoʻa no nā nīnau a pau. A laila, no kēlā me kēia nīnau, helu mākou i kāna hāʻawi pilikino i kēia ʻikepili holoʻokoʻa.

A he aha kā mākou e ʻike ai? Hiki iā mākou ke ʻike i ka nui o ka manawa hoʻokō o nā noi āpau o kahi ʻano kikoʻī e pili ana i ke kua o nā noi ʻē aʻe. Hiki iā mākou ke nānā i ka hoʻohana ʻana i ka CPU a me I/O e pili ana i ke kiʻi holoʻokoʻa. A i kēia manawa e hoʻomaikaʻi i kēia mau noi. Ke kūkulu nei mākou i nā nīnau kiʻekiʻe e pili ana i kēia hōʻike a ke ʻimi nei mākou i ka meaʻai no ka noʻonoʻo ʻana i ka mea e hoʻomaikaʻi ai.

He aha kā mākou ma hope o nā hiʻohiʻona? Aia nō kekahi mau ʻōlelo aʻu i noʻonoʻo ʻole ai, no ka mea, ua kaupalena ka manawa.

he nui na pgstattuple he module hou mai ka pūʻolo hāʻawi maʻamau. Hiki iā ʻoe ke loiloi bloat papaaina, i kapaia. ʻāpana ʻāpana. A inā nui ka fragmentation, pono ʻoe e wehe, e hoʻohana i nā mea hana like ʻole. A hana pgstattuple hana no ka manawa lōʻihi. A ʻoi aku ka nui o nā papa, ʻoi aku ka lōʻihi o ka hana.

ʻO ka hāʻawi aʻe pg_buffercache. Hāʻawi ia iā ʻoe e nānā i nā buffer kaʻana like: pehea ka ikaika a me ka hoʻohana ʻana i nā ʻaoʻao buffer papa. A ʻae wale ia iā ʻoe e nānā i nā buffer kaʻana like a loiloi i nā mea e hana nei ma laila.

ʻO ka module aʻe pgfincore. Hiki iā ʻoe ke hana i nā hana papa haʻahaʻa haʻahaʻa ma o ke kelepona ʻōnaehana mincore(), ʻo ia hoʻi, hiki iā ʻoe ke hoʻouka i ka papaʻaina i loko o nā buffer kaʻana like, a i ʻole e hoʻokuʻu iā ia. A hiki iā ia, ma waena o nā mea ʻē aʻe, e nānā i ka cache ʻaoʻao o ka ʻōnaehana hana, ʻo ia hoʻi, pehea ka nui o ka papaʻaina i loko o ka cache ʻaoʻao, i nā buffers kaʻana, a hiki iā ʻoe ke loiloi i ka ukana ma ka papaʻaina.

ʻO ka module aʻe pg_stat_kcache. Hoʻohana pū ia i ke kelepona ʻōnaehana getrusage(). A hoʻokō ia ma mua a ma hope o ka hoʻokō ʻia ʻana o ka noi. A i nā helu i loaʻa, hiki iā mākou ke koho i ka nui o kā mākou noi i hoʻohana ʻia ma ka disk I / O, ʻo ia hoʻi, nā hana me ka ʻōnaehana faila a nānā i ka hoʻohana ʻana i ka ʻōnaehana. Eia nō naʻe, he ʻōpiopio ka module (khe-khe) a no kāna hana e pono ai iā PostgreSQL 9.4 a me pg_stat_statements, aʻu i ʻōlelo ai ma mua.

Pono ka hiki ke hoʻohana i ka ʻikepili. ʻAʻole pono ʻoe i nā polokalamu ʻaoʻao ʻekolu. Hiki iā ʻoe ke nānā, ʻike, hana i kekahi mea, hana.
He maʻalahi ka hoʻohana ʻana i nā ʻikepili, ʻo SQL maʻalahi. Ua ʻohi ʻoe i kahi noi, hōʻuluʻulu, hoʻouna, nānā.
Kōkua nā helu helu e pane i nā nīnau. Inā he mau nīnau kāu, e huli ʻoe i nā helu helu - e nānā, e huki i nā hopena, e nānā i nā hopena.
A hoʻokolohua. Nui nā noi, nui nā ʻikepili. Hiki iā ʻoe ke hoʻololi i kekahi mau nīnau i loaʻa. Hiki iā ʻoe ke hana i kāu mana ponoʻī o ka noi i kūpono iā ʻoe ma mua o ka mea mua a hoʻohana.