Ngirit sen ing volume gedhe ing PostgreSQL

Terus topik ngrekam aliran data gedhe wungu dening artikel sadurunge babagan partisi, ing iki kita bakal katon ing cara sing bisa nyuda ukuran "fisik" saka disimpen ing PostgreSQL, lan pengaruhe ing kinerja server.

Kita bakal ngomong babagan Setelan TOAST lan alignment data. "Rata-rata," cara iki ora bakal nyimpen akeh banget sumber daya, nanging tanpa ngowahi kode aplikasi ing kabeh.

Nanging, pengalaman kita dadi produktif banget ing babagan iki, amarga panyimpenan meh kabeh pemantauan kanthi sifate biasane append-mung ing babagan data sing direkam. Lan yen sampeyan kepingin weruh carane sampeyan bisa mulang database kanggo nulis menyang disk tinimbang 200MB / s setengah minangka akeh - please ing cat.

Rahasia cilik saka data gedhe

Miturut profil proyek layanan kita, padha ajeg mabur marang dheweke saka lairs paket teks.

Lan wiwit Kompleks VLSIdatabase kang kita monitor minangka produk multi-komponen karo struktur data Komplek, banjur pitakon kanggo kinerja maksimum dadi kaya iki "multi-volume" kanthi logika algoritma kompleks. Dadi volume saben conto individu saka panjalukan utawa rencana eksekusi asil ing log sing teka kanggo kita dadi "rata-rata" cukup gedhe.

Ayo ndeleng struktur salah sawijining tabel ing ngendi kita nulis data "mentah" - yaiku, iki teks asli saka entri log:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Tandha khas (wis dipérang, mesthi, dadi iki minangka cithakan bagean), sing paling penting yaiku teks. Kadhangkala cukup voluminous.

Elinga yen ukuran "fisik" saka siji rekaman ing PG ora bisa manggoni luwih saka siji kaca data, nanging ukuran "logis" minangka prakara sing beda. Kanggo nulis nilai volumetrik (varchar/text/bytea) menyang kolom, gunakake teknologi TOAST:

PostgreSQL nggunakake ukuran kaca sing tetep (biasane 8 KB), lan ora ngidini tuple ngubengi pirang-pirang kaca. Mulane, ora bisa langsung nyimpen nilai lapangan sing gedhe banget. Kanggo ngatasi watesan iki, nilai lapangan gedhe dikompres lan / utawa dipérang ing pirang-pirang garis fisik. Iki kedadeyan sing ora diweruhi dening pangguna lan ora duwe pengaruh kanggo umume kode server. Cara iki dikenal minangka TOAST ...

Nyatane, kanggo saben tabel kanthi kolom "potensi gedhe", kanthi otomatis Tabel dipasangake karo "slicing" digawe saben rekaman "gedhe" ing segmen 2KB:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Yaiku, yen kita kudu nulis senar kanthi nilai "gedhe". data, banjur rekaman nyata bakal kelakon ora mung kanggo meja utama lan PK, nanging uga kanggo TOAST lan PK.

Ngurangi pengaruh TOAST

Nanging umume cathetan kita isih ora gedhe, kudu pas menyang 8KB - Kepiye carane bisa ngirit dhuwit iki? ..

Iki ngendi atribut teka kanggo bantuan kita STORAGE ing kolom tabel:

• Ditambahi ngidini komprèsi lan panyimpenan kapisah. Iki pilihan standar kanggo paling jinis data TOAST cecek. Kawitan nyoba kanggo nindakake komprèsi, banjur nyimpen ing njaba meja yen baris isih gedhe banget.
• UTAMA ngidini komprèsi nanging ora panyimpenan kapisah. (Nyatane, panyimpenan kapisah isih bakal ditindakake kanggo kolom kasebut, nanging mung minangka pilihan pungkasan, nalika ora ana cara liya kanggo nyilikake senar supaya pas ing kaca.)

Nyatane, iki persis sing kita butuhake kanggo teks - compress okehe, lan yen ora pas ing kabeh, sijine ing TOAST. Iki bisa ditindakake langsung kanthi cepet, kanthi siji prentah:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Carane ngevaluasi efek

Wiwit aliran data diganti saben dina, kita ora bisa mbandhingake angka absolut, nanging kanthi istilah relatif bagean cilik We wrote mudhun ing TOAST - dadi luwih apik. Nanging ana bebaya ing kene - luwih gedhe volume "fisik" saben rekaman individu, indeks kasebut "luwih akeh", amarga kita kudu nutupi luwih akeh kaca data.

Bagean sadurunge owah-owahan:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Bagean sawise owah-owahan:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Nyatane, kita wiwit nulis kanggo TOAST 2 kaping kurang asring, sing ora mung mbukak disk, nanging uga CPU:

Aku bakal nyathet yen kita uga dadi luwih cilik ing "maca" disk, ora mung "nulis" - amarga nalika nglebokake rekaman menyang tabel, kita uga kudu "maca" bagean saka wit saben indeks kanggo nemtokake sawijining posisi mangsa ing wong.

Sapa sing bisa urip kanthi apik ing PostgreSQL 11

Sawise nganyari menyang PG11, kita mutusake kanggo nerusake "tuning" TOAST lan ngerteni manawa wiwit saka versi iki parameter kasedhiya kanggo nyetel toast_tuple_target:

Kode pangolahan TOAST mung murub nalika nilai baris sing bakal disimpen ing tabel luwih gedhe tinimbang TOAST_TUPLE_THRESHOLD bita (biasane 2 KB). Kode TOAST bakal ngompres lan/utawa mindhah nilai lapangan metu saka tabel nganti nilai baris dadi kurang saka TOAST_TUPLE_TARGET bita (nilai variabel, uga biasane 2 KB) utawa ukurane ora bisa dikurangi.

Kita mutusake manawa data sing biasane diduweni yaiku "cendhek banget" utawa "dawa banget", mula kita mutusake kanggo mbatesi nilai minimal:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ayo ndeleng carane setelan anyar kena pengaruh disk loading sawise reconfiguration:

Ora ala! Rata-rata antrian menyang disk wis suda kira-kira 1.5 kaping, lan disk "sibuk" punika 20 persen! Nanging bisa uga iki kena pengaruh CPU?

Saora-orane ora tambah parah. Sanajan, angel kanggo ngadili yen volume kasebut isih ora bisa ngunggahake beban CPU rata-rata sing luwih dhuwur 5%.

Kanthi ngganti panggonan istilah, jumlah ... owah-owahan!

Sing ngerti, Penny nyimpen ruble, lan karo volume panyimpenan kita bab 10TB / wulan malah optimasi sethitik bisa menehi MediaWiki apik. Mulane, kita mbayar manungsa waé kanggo struktur fisik data kita - carane persis kolom "ditumpuk" nang rekaman saben tabel.

Amarga saka alignment data iki langsung maju mengaruhi volume asil:

Akeh arsitektur nyedhiyakake keselarasan data ing wates tembung mesin. Contone, ing sistem x32 86-dicokot, integer (jinis integer, 4 bait) bakal didadekake siji ing wates tembung 4-bait, minangka bakal pindho tliti nomer floating point (pindho precision floating point, 8 bait). Lan ing sistem 64-bit, nilai dobel bakal didadekake siji karo wates tembung 8-byte. Iki minangka alesan liyane kanggo incompatibility.

Amarga keselarasan, ukuran baris tabel gumantung saka urutan kolom. Biasane efek iki ora katon banget, nanging ing sawetara kasus bisa nambah ukuran sing signifikan. Contone, yen sampeyan nyampur kolom char(1) lan integer, biasane ana 3 bita sing dibuwang ing antarane.

Ayo dadi miwiti karo model sintetik:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

Ngendi saperangan bita ekstra teka saka ing kasus pisanan? Iku prasaja - Smallint 2-byte didadekake siji ing wates 4-byte sadurunge lapangan sabanjuré, lan nalika iku pungkasan, ana apa-apa lan ora perlu kanggo kempal.

Ing teori, kabeh apik lan sampeyan bisa ngatur maneh lapangan sing dikarepake. Ayo mriksa data nyata nggunakake conto salah sawijining tabel, bagean saben dinane 10-15GB.

Struktur awal:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Bagean sawise ngganti urutan kolom - persis lapangan padha, mung beda urutan:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

Volume total bagean kasebut ditemtokake dening jumlah "fakta" lan mung gumantung ing proses eksternal, mula ayo dibagi ukuran tumpukan (pg_relation_size) kanthi jumlah cathetan ing - yaiku, kita entuk ukuran rata-rata saka rekaman disimpen nyata:

Minus 6% volume, Agung!

Nanging kabeh, mesthi, ora dadi rosy - sawise kabeh, ing indeks kita ora bisa ngganti urutan kolom, lan mulane "umum" (pg_total_relation_size) ...

... isih kene uga disimpen 1.5%tanpa ngganti siji baris kode. Ya wis!

Aku nyathet yen pilihan ing ndhuwur kanggo ngatur lapangan dudu kasunyatan sing paling optimal. Amarga sampeyan ora pengin "nyuwek" sawetara blok lapangan amarga alasan estetis - contone, pasangan (pack, recno), yaiku PK kanggo tabel iki.

Umumé, nemtokake susunan "minimal" lapangan minangka tugas "brute force" sing cukup prasaja. Mula, sampeyan bisa entuk asil sing luwih apik saka data sampeyan tinimbang data kita - coba!

Source: www.habr.com