{"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\/cs\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","title":{"rendered":"\u0160et\u0159\u00edme na velk\u00fdch objemech v PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Pokra\u010duj\u00edc\u00ed t\u00e9ma z\u00e1pisu velk\u00fdch datov\u00fdch proud\u016f, kter\u00e9 bylo vzneseno v p\u0159edchoz\u00edm \u010dl\u00e1nku o partitioningu<\/a><\/noindex>, se zde zam\u011b\u0159\u00edme na zp\u016fsoby, jak\u00fdmi lze sn\u00ed\u017eit \"fyzickou\" velikost ulo\u017een\u00e9ho<\/b> v PostgreSQL a jejich vliv na v\u00fdkonnost serveru.<\/p>\n

Bude \u0159e\u010d o nastaven\u00ed TOAST a zarovn\u00e1n\u00ed dat<\/b>. \"V pr\u016fm\u011bru\" tyto metody umo\u017en\u00ed u\u0161et\u0159it ne p\u0159\u00edli\u0161 mnoho zdroj\u016f, ale \u2014 v\u016fbec bez modifikace k\u00f3du aplikace.<\/p>\n

\"\u0160et\u0159\u00edme
\nNicm\u00e9n\u011b, na\u0161e zku\u0161enost byla v tomto ohledu velmi produktivn\u00ed, proto\u017ee \u00falo\u017ei\u0161t\u011b t\u00e9m\u011b\u0159 jak\u00e9hokoli monitorov\u00e1n\u00ed je ze sv\u00e9 podstaty v\u011bt\u0161inou append-only<\/b> pokud jde o zapisovan\u00e1 data. A pokud v\u00e1s zaj\u00edm\u00e1, jak m\u016f\u017eete nau\u010dit datab\u00e1zi zapisovat na disk m\u00edsto 200MB\/s<\/b> o polovinu m\u00e9n\u011b \u2014 pros\u00edm pod kat.
\n<\/a><\/noindex><\/p>\n

Mal\u00e9 tajemstv\u00ed velk\u00fdch dat<\/h2>\n

\nPodle profilu pr\u00e1ce na\u0161\u00ed slu\u017eby<\/a><\/noindex>, p\u0159ich\u00e1zej\u00ed mu pravideln\u011b z log\u016f textov\u00e9 bal\u00ed\u010dky<\/b>.<\/p>\n

A jeliko\u017e komplex SBIS<\/a><\/noindex>, jeho\u017e datab\u00e1ze monitorujeme, je mnohokomponentn\u00ed produkt se slo\u017eit\u00fdmi datov\u00fdmi strukturami, tak i dotazy pro dosa\u017een\u00ed maxim\u00e1ln\u00ed v\u00fdkonnosti<\/b> se st\u00e1vaj\u00ed docela \"mnogotominov\u00fdmi\" s komplikovanou algoritmickou logikou. Tak\u017ee objem ka\u017ed\u00e9 samostatn\u00e9 instance dotazu nebo v\u00fdsledn\u00e9ho pl\u00e1nu proveden\u00ed v p\u0159ich\u00e1zej\u00edc\u00edm logu se ukazuje b\u00fdt \"v pr\u016fm\u011bru\" dostate\u010dn\u011b velk\u00fd.<\/a><\/noindex>Pod\u00edvejme se na strukturu jedn\u00e9 z tabulek, do kter\u00e9 zapisujeme \"syrov\u00e1\" data \u2014 tedy p\u0159esn\u011b p\u016fvodn\u00ed text ze z\u00e1pisu logu:<\/p>\n

CREATE TABLE rawdata_orig(\n pack -- PK\n uuid NOT NULL\n, recno -- PK\n smallint NOT NULL\n, dt -- kl\u00ed\u010d sekce\n date\n, data -- to nejd\u016fle\u017eit\u011bj\u0161\u00ed\n text\n, PRIMARY KEY(pack, recno)\n);<\/p>\n

Typick\u00e1 tabulka (ji\u017e partitionovan\u00e1, samoz\u0159ejm\u011b, proto je to \u2014 \u0161ablona sekce), kde to nejd\u016fle\u017eit\u011bj\u0161\u00ed \u2014 je text. N\u011bkdy dost objemn\u00fd.<\/code><\/pre>\n
\nP\u0159ipome\u0148me si, \u017ee \"fyzick\u00e1\" velikost jednoho z\u00e1znamu v PG nem\u016f\u017ee zab\u00edrat v\u00edce ne\u017e jednu str\u00e1nku dat, ale \"logick\u00e1\" velikost \u2014 to je \u00fapln\u011b jin\u00e1 v\u011bc. Pro ulo\u017een\u00ed objemn\u00e9 hodnoty do pole (varchar\/text\/bytea) se pou\u017e\u00edv\u00e1<\/p>\n
technologie TOAST technologie TOAST<\/a><\/noindex>:<\/p>\n
PostgreSQL pou\u017e\u00edv\u00e1 pevnou velikost str\u00e1nky (obvykle 8 kB) a neumo\u017e\u0148uje, aby k tuples zab\u00edraly v\u00edce str\u00e1nek. Proto nen\u00ed mo\u017en\u00e9 p\u0159\u00edmo uchov\u00e1vat velmi velk\u00e9 hodnoty pol\u00ed. Aby se toto omezen\u00ed p\u0159ekonalo, velk\u00e9 hodnoty pol\u00ed se komprimuj\u00ed a\/nebo d\u011bl\u00ed na n\u011bkolik fyzick\u00fdch \u0159\u00e1dk\u016f. To se d\u011bje neznateln\u011b pro u\u017eivatele a m\u00e1 jen mal\u00fd dopad na v\u011bt\u0161inu k\u00f3du serveru. Tato metoda je zn\u00e1m\u00e1 jako TOAST \u2026<\/p><\/blockquote>\n
\nVe skute\u010dnosti je pro ka\u017edou tabulku s \"potenci\u00e1ln\u011b velk\u00fdmi\" poli automaticky vytv\u00e1\u0159ena p\u00e1rov\u00e1 tabulka s \"kr\u00e1jen\u00edm\"<\/a><\/noindex> ka\u017ed\u00e9ho \"velk\u00e9ho\" z\u00e1znamu segmenty po 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
\nTo znamen\u00e1, \u017ee pokud mus\u00edme zapisovat \u0159\u00e1dek s \"velkou\" hodnotou data<\/code>, skute\u010dn\u00fd z\u00e1znam se provede nejen do hlavn\u00ed tabulky a jej\u00edho PK, ale tak\u00e9 do TOASTu a jeho PK<\/b>.<\/p>\n
Sni\u017eujeme vliv TOASTu<\/h4>\n
\nAle v\u011bt\u0161ina z\u00e1znam\u016f zdaleka nen\u00ed tak velk\u00e1, m\u011bly by se vej\u00edt do 8KB<\/b> \u2014 jak na tom u\u0161et\u0159it?..<\/p>\n
Zde n\u00e1m pom\u00e1h\u00e1 atribut STORAGE<\/code><\/a><\/noindex> u sloupce tabulky:<\/p>\n
\n