{"id":79039,"date":"2020-04-23T19:43:26","date_gmt":"2020-04-23T17:43:26","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql"},"modified":"2020-04-23T19:43:26","modified_gmt":"2020-04-23T17:43:26","slug":"ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","title":{"rendered":"Kurseni qindarka n\u00eb v\u00ebllime t\u00eb m\u00ebdha n\u00eb PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Duke vazhdojm\u00eb tem\u00ebn e regjistrimit t\u00eb flukseve t\u00eb m\u00ebdha t\u00eb t\u00eb dh\u00ebnave, e ngritur n\u00eb artikullin e m\u00ebparsh\u00ebm mbi seksionimin<\/a><\/noindex>, n\u00eb k\u00ebt\u00eb do t\u00eb shqyrtojm\u00eb m\u00ebnyrat me t\u00eb cilat mund t\u00eb ulet \"masa fizike\" e t\u00eb dh\u00ebnave t\u00eb ruajtura<\/b> n\u00eb PostgreSQL, dhe ndikimi i tyre n\u00eb performanc\u00ebn e serverit.<\/p>\n

B\u00ebhet fjal\u00eb p\u00ebr konfigurimet TOAST dhe-alignimin e t\u00eb dh\u00ebnave<\/b>. \"N\u00eb mesatar\u00eb\" k\u00ebto metoda do t\u00eb kursejn\u00eb jo shum\u00eb burime, por \u2014 krejt\u00ebsisht pa modifikimin e Kodit t\u00eb aplikacionit.<\/p>\n

\"Kurseni
\nMegjithat\u00eb, p\u00ebrvoja jon\u00eb ka rezultuar mjaft produktive n\u00eb k\u00ebt\u00eb drejtim, pasi depozita e pothuajse \u00e7do monitorimi nga natyra e saj \u00ebsht\u00eb pjes\u00ebrisht append-only<\/b> nga pik\u00ebpamja e t\u00eb dh\u00ebnave t\u00eb regjistruara. Dhe n\u00ebse ju intereson se si mund t\u00eb m\u00ebsoni baz\u00ebn p\u00ebr t\u00eb shkruar n\u00eb disk n\u00eb vend t\u00eb 200MB\/s<\/b> gjysm\u00eb m\u00eb pak \u2014 ju lutemi ndiqni m\u00eb posht\u00eb.
\n<\/a><\/noindex><\/p>\n

Sekretet e vogla t\u00eb t\u00eb dh\u00ebnave t\u00eb m\u00ebdha<\/h2>\n

\nN\u00eb p\u00ebrputhje me profilin e pun\u00ebs t\u00eb sh\u00ebrbimit ton\u00eb<\/a><\/noindex>, ai rregullisht merr nga log-et paketa teksti<\/b>.<\/p>\n

Dhe pasi kompleksi SBIS<\/a><\/noindex>, t\u00eb cilat Baza t\u00eb Dh\u00ebnash ne monitorojm\u00eb \u2014 \u00ebsht\u00eb nj\u00eb produkt me shum\u00eb komponente me struktura t\u00eb komplikuara t\u00eb t\u00eb dh\u00ebnave, at\u00ebher\u00eb edhe k\u00ebrkesat p\u00ebr t\u00eb arritur performanc\u00ebn maksimale<\/b> po rezultojn\u00eb mjaft \"multivolume\" me logjik\u00eb algoritmike t\u00eb komplikuar<\/a><\/noindex>. Pra, edhe volumi i \u00e7do instanc\u00eb t\u00eb ve\u00e7ant\u00eb t\u00eb k\u00ebrkes\u00ebs ose planit rezultues t\u00eb ekzekutimit n\u00eb log-un q\u00eb vjen tek ne \u00ebsht\u00eb \"n\u00eb mesatar\u00eb\" mjaft i madh.<\/p>\n

Le t\u00eb shohim struktur\u00ebn e nj\u00ebrit nga tabelat, n\u00eb t\u00eb cil\u00ebn ne shkruajm\u00eb t\u00eb dh\u00ebnat \"e pap\u00ebrpunuara\" \u2014 dometh\u00ebn\u00eb k\u00ebtu \u00ebsht\u00eb teksti origjinal nga regjistrimi i logut:<\/p>\n

CREATE TABLE rawdata_orig(\n  pack -- PK\n    uuid NOT NULL\n, recno -- PK\n    smallint NOT NULL\n, dt -- \u00e7el\u00ebsi i seksionit\n    date\n, data -- m\u00eb e r\u00ebnd\u00ebsishmja\n    text\n, PRIMARY KEY(pack, recno)\n);<\/code><\/pre>\n
\nNj\u00eb tabel\u00eb tipike e till\u00eb (e seksionuar pa dyshim, prandaj kjo \u00ebsht\u00eb nj\u00eb model seksioni), ku m\u00eb e r\u00ebnd\u00ebsishmja \u00ebsht\u00eb\u2014tekstin. Ndonj\u00ebher\u00eb mjaft voluminoze.<\/p>\n
Le t\u00eb kujtojm\u00eb se \"masa fizike\" e nj\u00eb regjistrimi n\u00eb PG nuk mund t\u00eb z\u00ebr\u00eb m\u00eb shum\u00eb se nj\u00eb faqe t\u00eb dh\u00ebnash, por \"masa logjike\" \u2014 \u00ebsht\u00eb nj\u00eb \u00e7\u00ebshtje krejt tjet\u00ebr. P\u00ebr t\u00eb shkruar nj\u00eb vler\u00eb voluminoze n\u00eb fush\u00eb (varchar\/text\/bytea) p\u00ebrdoret teknologjia TOAST<\/a><\/noindex>:<\/p>\n
PostgreSQL p\u00ebrdor nj\u00eb madh\u00ebsi fiks t\u00eb faqes (zakonisht 8 KB) dhe nuk lejon q\u00eb tuple t\u00eb z\u00ebn\u00eb m\u00eb shum\u00eb se nj\u00eb faqe. Prandaj, nuk \u00ebsht\u00eb e mundur t\u00eb ruani direkt vlera shum\u00eb t\u00eb m\u00ebdha t\u00eb fushave. P\u00ebr t\u00eb tejkaluar k\u00ebt\u00eb kufizim, vlerat e m\u00ebdha t\u00eb fushave kompresohen dhe\/ose ndahen n\u00eb disa rreshta fizik\u00eb. Kjo ndodh pa u v\u00ebn\u00eb re nga p\u00ebrdoruesi dhe ndikon n\u00eb shumic\u00ebn e kodit t\u00eb serverit n\u00eb m\u00ebnyr\u00eb t\u00eb par\u00ebnd\u00ebsishme. K\u00ebta metoda njihet si TOAST \u2026<\/p><\/blockquote>\n
\nN\u00eb fakt, p\u00ebr \u00e7do tabel\u00eb me \u00abfusha potencialisht t\u00eb m\u00ebdha\u00bb automatikisht krijohet nj\u00eb tabel\u00eb p\u00ebrkat\u00ebse me \u00abcop\u00ebza\u00bb<\/a><\/noindex> t\u00eb \u00e7do regjistrimi \u00abt\u00eb madh\u00bb me segmente prej 2KB:<\/p>\n
TOAST(\n  chunk_id\n    integer\n, chunk_seq\n    integer\n, chunk_data\n    bytea\n, PRIMARY KEY(chunk_id, chunk_seq)\n);<\/code><\/pre>\n
\nDometh\u00ebn\u00eb, n\u00ebse na duhet t\u00eb shkruajm\u00eb nj\u00eb rresht me nj\u00eb vler\u00eb \u00abt\u00eb madhe\u00bb data<\/code>, regjistrimi real do t\u00eb ndodhi jo vet\u00ebm n\u00eb tabel\u00ebn kryesore dhe PK-n\u00eb e saj, por edhe n\u00eb TOAST dhe PK-n\u00eb e tij<\/b>.<\/p>\n
T\u00eb zvog\u00eblojm\u00eb ndikimin e TOAST<\/h4>\n
\nPor shumica e regjistrimeve tona nuk jan\u00eb kaq t\u00eb m\u00ebdha, n\u00eb 8KB duhet t\u00eb p\u00ebrfshihen<\/b> \u2014 si mund ta kursejm\u00eb k\u00ebt\u00eb?..<\/p>\n
K\u00ebtu vjen n\u00eb ndihm\u00eb atributi STORAGE<\/code><\/a><\/noindex> i kolon\u00ebs s\u00eb tabel\u00ebs:<\/p>\n
\n