{"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":"Kursim t\u00eb parave p\u00ebr sasi t\u00eb m\u00ebdha n\u00eb PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Duke vazhdimin e tem\u00ebs s\u00eb regjistrimit t\u00eb flukseve t\u00eb m\u00ebdha t\u00eb t\u00eb dh\u00ebnave artikulli i m\u00ebparsh\u00ebm mbi ndarjen<\/a><\/noindex>, n\u00eb k\u00ebt\u00eb do t\u00eb shqyrtojm\u00eb m\u00ebnyrat me t\u00eb cilat mund t\u00eb reduktosh \"p\u00ebrmas\u00ebn fizike\" t\u00eb t\u00eb dh\u00ebnave t\u00eb ruajtura<\/b> n\u00eb PostgreSQL, dhe ndikimin e saj n\u00eb performanc\u00ebn e serverit.<\/p>\n

Do t\u00eb flasim p\u00ebr caktimet TOAST dhe rregullimin e t\u00eb dh\u00ebnave<\/b>. \"N\u00eb mesatare\", k\u00ebto metoda do t\u00eb lejojn\u00eb kursimin e jo shum\u00eb burimeve, por pa modifikime n\u00eb kodin e aplikacionit.<\/p>\n

\"Kursim
\nMegjithat\u00eb, p\u00ebrvoja jon\u00eb ka rezultuar shum\u00eb produktive n\u00eb k\u00ebt\u00eb fush\u00eb, pasi ruajtja e pothuajse \u00e7do monitorimi p\u00ebr natyr\u00ebn e saj \u00ebsht\u00eb n\u00eb shumic\u00eb t\u00eb rasteve append-only<\/b> p\u00ebr sa i p\u00ebrket t\u00eb dh\u00ebnave t\u00eb shkruara. Dhe n\u00ebse je kurioz, si mund ta m\u00ebsosh baz\u00ebn t\u00eb shkruaj\u00eb n\u00eb disk n\u00eb vend t\u00eb 200MB\/s<\/b> gjysm\u00eb m\u00eb pak - t\u00eb lutem, vazhdo m\u00eb tej.
\n<\/a><\/noindex><\/p>\n

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

\nSipas profilit t\u00eb pun\u00ebs t\u00eb sh\u00ebrbimit ton\u00eb<\/a><\/noindex>, ai p\u00ebrballonte rregullisht nga log\u00ebt paqet tekstuale<\/b>.<\/p>\n

Dhe p\u00ebrsa i p\u00ebrket kompleksit SBIS<\/a><\/noindex>, t\u00eb cilat DB-t\u00eb tona monitorojn\u00eb, - \u00ebsht\u00eb nj\u00eb produkt me shum\u00eb komponent\u00eb me struktura t\u00eb nd\u00ebrlikuara t\u00eb t\u00eb dh\u00ebnave, k\u00ebshtu q\u00eb p\u00ebr t\u00eb arritur performanc\u00ebn maksimale<\/b> kan\u00eb rezultuar t\u00eb jen\u00eb gjithashtu \"multi-volume\" me logjik\u00eb algjebrike t\u00eb nd\u00ebrlikuar<\/a><\/noindex>. Pra, volumi i \u00e7do ekzemplar t\u00eb ve\u00e7ant\u00eb t\u00eb k\u00ebrkes\u00ebs ose p\u00ebrfundimit t\u00eb planit t\u00eb ekzekutimit n\u00eb logun q\u00eb na vjen \u00ebsht\u00eb \"n\u00eb mesatare\" mjaft i madh.<\/p>\n

Le t\u00eb shohim struktur\u00ebn e nj\u00eb nga tabelat, n\u00eb t\u00eb cil\u00ebn ne shkruajm\u00eb \"t\u00eb dh\u00ebna t\u00eb pap\u00ebrpunuara\" - do t\u00eb thot\u00eb pik\u00ebrisht tekstin 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 (sigurisht e ndar\u00eb, prandaj kjo \u00ebsht\u00eb - nj\u00eb shabllon seksioni), ku m\u00eb e r\u00ebnd\u00ebsishmja \u00ebsht\u00eb - teksti. Ndonj\u00ebher\u00eb mjafton shum\u00eb voluminoz.<\/p>\n
Le t\u00eb kujtojm\u00eb se \"p\u00ebrmasat fizike\" e nj\u00eb regjistrimi n\u00eb PG nuk mund t\u00eb z\u00eb m\u00eb shum\u00eb se nj\u00eb faqe t\u00eb dh\u00ebnash, por \"p\u00ebrmasat logjike\" jan\u00eb nj\u00eb \u00e7\u00ebshtje tjet\u00ebr. P\u00ebr t\u00eb regjistruar nj\u00eb vler\u00eb t\u00eb madhe n\u00eb fush\u00eb (varchar\/text\/bytea) p\u00ebrdoret teknologjia TOAST<\/a><\/noindex>:<\/p>\n
PostgreSQL p\u00ebrdor nj\u00eb madh\u00ebsi fikse faqeje (rreth 8 KB) dhe nuk lejon q\u00eb tuple t\u00eb z\u00ebn\u00eb disa faqe. Prandaj, ruajtja e vlerave shum\u00eb t\u00eb m\u00ebdha t\u00eb fushave nuk \u00ebsht\u00eb e mundur. 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 pak n\u00eb kodin e serverit. Ky metod\u00eb njihet si TOAST …<\/p><\/blockquote>\n
\nRealistikisht, p\u00ebr \u00e7do tabel\u00eb me \"fusha potencialisht t\u00eb m\u00ebdha\" automatikisht krijohet nj\u00eb tabel\u00eb marr\u00ebse me \"cop\u00ebzimin\"<\/a><\/noindex> e \u00e7do regjistrimi \"t\u00eb madh\" n\u00eb seksione 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
\nPra, n\u00ebse na duhen t\u00eb regjistrojm\u00eb nj\u00eb rresht me nj\u00eb \"vler\u00eb t\u00eb madhe\" data<\/code>, regjistrimi real do t\u00eb ndodhi jo vet\u00ebm n\u00eb tabel\u00ebn kryesore dhe PK e saj, por edhe n\u00eb TOAST dhe PK e tij<\/b>.<\/p>\n
Ulimi i ndikimit t\u00eb TOAST<\/h4>\n
\nPor shumica e regjistrimeve tona megjithat\u00eb nuk jan\u00eb kaq t\u00eb m\u00ebdha, n\u00eb 8KB duhet t\u00eb ndihmohen<\/b> - si t\u00eb kursejm\u00eb p\u00ebr k\u00ebt\u00eb?..<\/p>\n
K\u00ebtu na vjen n\u00eb ndihm\u00eb atribute STORAGE<\/code><\/a><\/noindex> n\u00eb kolon\u00ebn e tabel\u00ebs:<\/p>\n
\n