Simpen hiji Penny dina volume badag dina PostgreSQL

Nuluykeun topik ngarekam aliran data badag diangkat ku artikel saméméhna ngeunaan partitioning, dina ieu urang bakal ningali cara anu anjeun tiasa ngurangan ukuran "fisik" tina disimpen dina PostgreSQL, sarta dampak maranéhanana dina kinerja server.

Urang bakal ngobrol ngeunaan Setélan TOAST sareng alignment data. "Rata-rata," métode ieu moal ngahemat loba teuing sumberdaya, tapi tanpa ngarobah kodeu aplikasi pisan.

Tapi, pangalaman urang tétéla jadi pohara produktif dina hal ieu, saprak neundeun ampir sagala ngawaskeun ku alam na lolobana append-hijina dina hal kacatet data. Sareng upami anjeun heran kumaha anjeun tiasa ngajarkeun pangkalan data pikeun nyerat kana disk 200MB / s satengah saloba - mangga handapeun ucing.

Rahasia saeutik data badag

Ku profil pakasaban jasa urang, aranjeunna rutin ngapung ka anjeunna ti lairs bungkusan téks.

Sareng saprak VLSI kompléksnu database urang ngawas mangrupakeun produk multi-komponén kalawan struktur data kompléks, lajeng queries pikeun kinerja maksimum tétéla rada kawas kieu "multi-volume" kalawan logika algorithmic kompléks. Janten volume unggal conto individu tina pamundut atanapi rencana palaksanaan anu hasilna dina log anu sumping ka urang tétéla "rata-rata" lumayan ageung.

Hayu urang tingali struktur salah sahiji tabel dimana urang nyerat data "atah" - nyaéta, ieu mangrupikeun téks asli tina éntri log:

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

Hiji tanda has (geus sectioned, tangtosna, jadi ieu téh bagian template), dimana hal pangpentingna nyaéta téks. Kadang rada voluminous.

Émut yén ukuran "fisik" tina hiji catetan dina PG henteu tiasa ngeusian langkung ti hiji halaman data, tapi ukuran "logis" mangrupikeun masalah anu béda. Pikeun nulis nilai volumetrik (varchar/text/bytea) kana widang, paké téhnologi TOAST:

PostgreSQL nganggo ukuran halaman anu tetep (biasana 8 KB), sareng henteu ngijinkeun tuple nganjang sababaraha halaman. Ku alatan éta, teu mungkin pikeun langsung nyimpen nilai widang kacida gedéna. Pikeun ngatasi watesan ieu, nilai lapangan ageung dikomprés sareng / atanapi dibagi kana sababaraha garis fisik. Ieu kajadian unnoticed ku pamaké sarta ngabogaan saeutik dampak dina paling kode server. Metoda ieu katelah TOAST ...

Kanyataanna, pikeun unggal tabel kalawan "berpotensi badag" widang, otomatis méja dipasangkeun kalayan "slicing" dijieun unggal catetan "badag" dina bagéan 2KB:

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

Hartina, lamun urang kudu nulis string kalawan nilai "badag". data, mangka rékaman nyata bakal lumangsung teu ukur kana tabel utama jeung PK na, tapi ogé pikeun roti bakar jeung PK na.

Ngurangan pangaruh TOAST

Tapi sabagéan ageung catetan urang masih henteu ageung, kedah pas kana 8KB - Kumaha carana abdi tiasa nyimpen duit dina ieu?..

Ieu dimana atribut datang ka bantuan urang STORAGE dina kolom tabel:

• DITULU ngamungkinkeun duanana komprési jeung neundeun misah. Ieu pilihan baku pikeun kalolobaan tipe data patuh TOAST. Ieu mimitina nyoba nedunan komprési, lajeng nyimpen eta di luar tabel lamun baris masih badag teuing.
• utama ngamungkinkeun komprési tapi teu misah gudang. (Saleresna, panyimpen anu misah bakal tetep dilaksanakeun pikeun kolom sapertos kitu, tapi ngan ukur salaku Resort panungtungan, lamun teu aya cara séjén pikeun ngaleutikan senar supados pas dina kaca.)

Nyatana, ieu mangrupikeun anu urang peryogikeun pikeun téks - niiskeun eta saloba mungkin, sarta lamun teu cocog pisan, nempatkeun eta dina roti bakar. Ieu tiasa dilakukeun langsung dina laleur, kalayan hiji paréntah:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kumaha evaluate pangaruh

Kusabab aliran data robah unggal poé, urang teu bisa ngabandingkeun angka mutlak, tapi dina istilah relatif babagi leutik Kami nyerat éta dina TOAST - langkung saé. Tapi aya bahaya di dieu - langkung ageung volume "fisik" unggal catetan individu, indéksna "langkung lega", sabab urang kedah nutupan langkung seueur halaman data.

Bagian saméméh parobahan:

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

Bagian sanggeus parobahan:

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

Kanyataanna, urang mimiti nulis ka TOAST 2 kali kirang sering, anu ngabongkar teu ukur disk, tapi ogé CPU:

Kuring bakal perhatikeun yén urang ogé parantos janten langkung alit dina "maca" disk, sanés ngan ukur "nulis" - sabab nalika nyelapkeun rékaman kana méja, urang ogé kedah "maca" bagian tina tangkal unggal indéks pikeun nangtukeun na. posisi hareup maranéhanana.

Saha anu tiasa hirup saé dina PostgreSQL 11

Saatos ngamutahirkeun ka PG11, kami mutuskeun pikeun neraskeun "tuning" TOAST sareng perhatikeun yén mimitian ti versi ieu parameter toast_tuple_target:

Kode processing TOAST ngan seuneu lamun nilai baris disimpen dina tabel leuwih badag batan TOAST_TUPLE_THRESHOLD bait (biasana 2 KB). Kodeu TOAST bakal niiskeun jeung/atawa mindahkeun nilai widang kaluar tina tabel nepi ka nilai baris jadi kirang ti TOAST_TUPLE_TARGET bait (nilai variabel, ogé biasana 2 KB) atawa ukuranana teu bisa ngurangan.

Kami mutuskeun yén data anu biasana urang gaduh boh "pondok pisan" atanapi "panjang pisan", janten kami mutuskeun pikeun ngawatesan diri kana nilai minimum anu mungkin:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Hayu urang tingali kumaha setelan anyar mangaruhan loading disk sanggeus reconfiguration:

Lumayan! Rata-rata antrian ka disk geus turun kira-kira 1.5 kali, sarta disk "sibuk" nyaeta 20 persen! Tapi meureun ieu kumaha bae mangaruhan CPU?

Sahenteuna teu meunang nu mana wae nu goréng. Sanajan, hese nangtoskeun upami volume sapertos kitu masih teu tiasa ngangkat beban CPU rata-rata langkung luhur 5%.

Ku ngarobah tempat istilah, jumlah ... robah!

Anjeun terang, Penny a ngaheéat ruble, sarta kalawan volume gudang urang éta ngeunaan 10TB/bulan malah optimasi saeutik bisa méré untung alus. Ku alatan éta, urang nengetan struktur fisik data urang - kumaha kahayang Widang "tumpuk" di jero rékaman unggal méja.

Kusabab kusabab alignment data ieu lempeng ka hareup mangaruhan volume hasilna:

Loba arsitéktur nyadiakeun alignment data dina wates kecap mesin. Contona, dina sistem 32-bit x86, integer (tipe integer, 4 bait) bakal Blok dina wates kecap 4-bait, sakumaha ogé ganda precision angka floating point (double precision floating point, 8 bait). Sareng dina sistem 64-bit, nilai ganda bakal dijajarkeun kana wates kecap 8-bait. Ieu alesan sejen pikeun incompatibility.

Kusabab alignment, ukuran baris tabel gumantung kana urutan widang. Biasana pangaruh ieu teu pisan noticeable, tapi dina sababaraha kasus eta bisa ngakibatkeun kanaékan signifikan dina ukuran. Contona, upami anjeun nyampur char (1) sareng widang integer, biasana bakal aya 3 bait anu dibuang antara aranjeunna.

Hayu urang mimitian ku model sintétik:

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 байт

Dimana datangna sababaraha bait tambahan dina kasus nu pertama? Ieu basajan - Smallint 2-bait dijajarkeun dina wates 4-bait saméméh widang salajengna, sarta lamun éta panungtungan, aya nanaon jeung teu kudu align.

Sacara tiori, sadayana henteu kunanaon sareng anjeun tiasa nyusun ulang widang anu anjeun pikahoyong. Hayu urang pariksa dina data nyata ngagunakeun conto salah sahiji tabel, bagian poéan nu ngawengku 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)

Bagian sanggeus ngarobah urutan kolom - persis widang anu sarua, ngan béda 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)

Jumlah volume bagian ditangtukeun ku jumlah "fakta" sareng ngan ukur gumantung kana prosés éksternal, janten hayu urang ngabagi ukuran tumpukan (pg_relation_size) ku jumlah rékaman di dinya - nyaeta, urang meunang ukuran rata-rata rékaman disimpen sabenerna:

Minus 6% volume, Hebat!

Tapi sagalana, tangtosna, teu jadi rosy - sanggeus kabeh, dina indexes urang teu bisa ngarobah urutan widang, sarta ku kituna "sacara umum" (pg_total_relation_size) ...

...di dieu oge disimpen 1.5%tanpa ngarobah hiji garis tunggal kode. Enya enya!

Kuring dicatet yén pilihan di luhur pikeun ngatur sawah teu kanyataan yén éta téh paling optimal. Kusabab anjeun henteu hoyong "nyobek" sababaraha blok lapangan kusabab alesan estetika - contona, pasangan (pack, recno), nu PK pikeun tabel ieu.

Sacara umum, nangtukeun "minimum" susunan sawah mangrupakeun tugas "brute force" cukup basajan. Ku alatan éta, anjeun bisa meunangkeun hasil malah leuwih hadé tina data anjeun ti kami - coba eta!

sumber: www.habr.com