Jimat satu sen untuk jumlah yang besar dalam PostgreSQL

Meneruskan topik merekodkan aliran data besar yang dibangkitkan oleh artikel sebelumnya tentang partitioning, dalam hal ini kita akan melihat cara yang anda boleh mengurangkan saiz "fizikal" yang disimpan dalam PostgreSQL, dan kesannya terhadap prestasi pelayan.

Kita akan bercakap tentang Tetapan TOAST dan penjajaran data. "Secara purata," kaedah ini tidak akan menjimatkan terlalu banyak sumber, tetapi tanpa mengubah suai kod aplikasi sama sekali.

Walau bagaimanapun, pengalaman kami ternyata sangat produktif dalam hal ini, kerana penyimpanan hampir semua pemantauan mengikut sifatnya kebanyakannya tambahan sahaja dari segi data yang direkodkan. Dan jika anda tertanya-tanya bagaimana anda boleh mengajar pangkalan data untuk menulis ke cakera sebaliknya 200MB / s separuh daripada banyak - sila di bawah kucing.

Rahsia kecil data besar

Mengikut profil pekerjaan perkhidmatan kami, mereka kerap terbang kepadanya dari sarang pakej teks.

Dan sejak Kompleks VLSIpangkalan data yang kami pantau ialah produk berbilang komponen dengan struktur data yang kompleks, kemudian pertanyaan untuk prestasi maksimum menjadi seperti ini "berbilang volum" dengan logik algoritma yang kompleks. Jadi volum setiap contoh individu permintaan atau pelan pelaksanaan yang terhasil dalam log yang datang kepada kami ternyata "secara purata" agak besar.

Mari kita lihat struktur salah satu jadual di mana kita menulis data "mentah" - iaitu, berikut ialah teks asal dari entri log:

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

Tanda biasa (sudah dibelah, sudah tentu, jadi ini adalah templat bahagian), di mana perkara yang paling penting ialah teks. Kadang-kadang agak besar.

Ingat bahawa saiz "fizikal" satu rekod dalam PG tidak boleh menduduki lebih daripada satu halaman data, tetapi saiz "logik" adalah perkara yang sama sekali berbeza. Untuk menulis nilai volumetrik (varchar/text/bytea) pada medan, gunakan teknologi TOAST:

PostgreSQL menggunakan saiz halaman tetap (biasanya 8 KB), dan tidak membenarkan tupel menjangkau berbilang halaman. Oleh itu, adalah mustahil untuk menyimpan nilai medan yang sangat besar secara langsung. Untuk mengatasi had ini, nilai medan yang besar dimampatkan dan/atau dipecah merentasi berbilang garis fizikal. Ini berlaku tanpa disedari oleh pengguna dan mempunyai sedikit kesan pada kebanyakan kod pelayan. Kaedah ini dikenali sebagai TOAST...

Malah, untuk setiap jadual dengan medan "berpotensi besar", secara automatik jadual berpasangan dengan "menghiris" dicipta setiap rekod "besar" dalam segmen 2KB:

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

Iaitu, jika kita perlu menulis rentetan dengan nilai "besar". data, maka rakaman sebenar akan berlaku bukan sahaja ke meja utama dan PKnya, tetapi juga kepada TOAST dan PKnya.

Mengurangkan pengaruh TOAST

Tetapi kebanyakan rekod kami masih tidak begitu besar, harus muat dalam 8KB - Bagaimana saya boleh menjimatkan wang untuk ini?..

Di sinilah atribut datang untuk membantu kami STORAGE pada lajur jadual:

• EXTENDED membenarkan kedua-dua pemampatan dan penyimpanan berasingan. ini pilihan standard untuk kebanyakan jenis data yang mematuhi TOAST. Mula-mula ia cuba melakukan pemampatan, kemudian menyimpannya di luar jadual jika baris masih terlalu besar.
• UTAMA membenarkan pemampatan tetapi bukan penyimpanan berasingan. (Malah, storan berasingan masih akan dilakukan untuk lajur tersebut, tetapi hanya sebagai jalan terakhir, apabila tiada cara lain untuk mengecilkan rentetan supaya muat pada halaman.)

Sebenarnya, inilah yang kita perlukan untuk teks - mampatkannya sebanyak mungkin, dan jika ia tidak muat langsung, masukkan ke dalam TOAST. Ini boleh dilakukan secara langsung dengan cepat, dengan satu arahan:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Bagaimana untuk menilai kesannya

Memandangkan aliran data berubah setiap hari, kami tidak boleh membandingkan nombor mutlak, tetapi dalam istilah relatif bahagian yang lebih kecil Kami menulisnya dalam TOAST - lebih baik. Tetapi terdapat bahaya di sini - semakin besar volum "fizikal" setiap rekod individu, semakin "lebih luas" indeksnya, kerana kita perlu menutup lebih banyak halaman data.

Bahagian sebelum perubahan:

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

Bahagian selepas perubahan:

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

Sebenarnya, kita mula menulis ke TOAST 2 kali kurang kerap, yang memunggah bukan sahaja cakera, tetapi juga CPU:

Saya akan perhatikan bahawa kita juga telah menjadi lebih kecil dalam "membaca" cakera, bukan sahaja "menulis" - kerana apabila memasukkan rekod ke dalam jadual, kita juga perlu "membaca" bahagian pokok setiap indeks untuk menentukannya. kedudukan masa depan dalam diri mereka.

Siapa yang boleh hidup dengan baik di PostgreSQL 11

Selepas mengemas kini kepada PG11, kami memutuskan untuk meneruskan "menala" TOAST dan mendapati bahawa bermula dari versi ini parameter tersedia untuk penalaan toast_tuple_target:

Kod pemprosesan TOAST hanya menyala apabila nilai baris yang akan disimpan dalam jadual adalah lebih besar daripada TOAST_TUPLE_THRESHOLD bait (biasanya 2 KB). Kod TOAST akan memampatkan dan/atau mengalihkan nilai medan keluar daripada jadual sehingga nilai baris menjadi kurang daripada TOAST_TUPLE_TARGET bait (nilai pembolehubah, juga biasanya 2 KB) atau saiznya tidak boleh dikurangkan.

Kami memutuskan bahawa data yang biasa kami miliki adalah sama ada "sangat pendek" atau "sangat panjang", jadi kami memutuskan untuk mengehadkan diri kami kepada nilai minimum yang mungkin:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Mari lihat bagaimana tetapan baharu mempengaruhi pemuatan cakera selepas konfigurasi semula:

Boleh tahan! Purata baris gilir ke cakera telah berkurangan kira-kira 1.5 kali, dan cakera "sibuk" adalah 20 peratus! Tetapi mungkin ini entah bagaimana menjejaskan CPU?

Sekurang-kurangnya ia tidak menjadi lebih teruk. Walaupun, sukar untuk menilai jika volum sedemikian masih tidak dapat meningkatkan beban CPU purata lebih tinggi 5%.

Dengan menukar tempat istilah, jumlah... berubah!

Seperti yang anda ketahui, satu sen menjimatkan satu ruble, dan dengan volum storan kami ia adalah kira-kira 10TB/bulan walaupun sedikit pengoptimuman boleh memberikan keuntungan yang baik. Oleh itu, kami memberi perhatian kepada struktur fizikal data kami - bagaimana sebenarnya medan "bertindan" di dalam rekod setiap satu meja.

Kerana kerana penjajaran data ini lurus ke hadapan mempengaruhi isipadu yang terhasil:

Banyak seni bina menyediakan penjajaran data pada sempadan perkataan mesin. Contohnya, pada sistem x32 86-bit, integer (jenis integer, 4 bait) akan diselaraskan pada sempadan perkataan 4-bait, begitu juga dengan nombor titik terapung ketepatan dua kali ganda (titik terapung ketepatan ganda, 8 bait). Dan pada sistem 64-bit, nilai berganda akan diselaraskan dengan sempadan perkataan 8-bait. Ini adalah satu lagi sebab ketidakserasian.

Disebabkan penjajaran, saiz baris jadual bergantung pada susunan medan. Biasanya kesan ini tidak begitu ketara, tetapi dalam beberapa kes ia boleh membawa kepada peningkatan yang ketara dalam saiz. Sebagai contoh, jika anda mencampurkan medan char(1) dan integer, lazimnya terdapat 3 bait terbuang antara medan tersebut.

Mari kita mulakan dengan 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 байт

Di manakah datangnya beberapa bait tambahan dalam kes pertama? Ia mudah - Bintik kecil 2-bait dijajarkan pada sempadan 4-bait sebelum medan seterusnya, dan apabila ia adalah yang terakhir, tiada apa-apa dan tidak perlu diselaraskan.

Secara teori, semuanya baik-baik saja dan anda boleh menyusun semula medan yang anda suka. Mari kita semak pada data sebenar menggunakan contoh salah satu jadual, bahagian harian yang menduduki 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)

Bahagian selepas menukar susunan lajur - betul-betul medan yang sama, cuma susunan yang berbeza:

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 isipadu bahagian ditentukan oleh bilangan "fakta" dan bergantung hanya pada proses luaran, jadi mari bahagikan saiz timbunan (pg_relation_size) dengan bilangan rekod di dalamnya - iaitu, kita dapat saiz purata rekod tersimpan sebenar:

Tolak 6% volum, Hebat!

Tetapi semuanya, tentu saja, tidak begitu cerah - lagipun, dalam indeks kita tidak boleh mengubah susunan medan, dan oleh itu “secara umum” (pg_total_relation_size) ...

... masih di sini juga disimpan 1.5%tanpa mengubah satu baris kod. Ya, ya!

Saya perhatikan bahawa pilihan di atas untuk mengatur medan bukanlah hakikat bahawa ia adalah yang paling optimum. Kerana anda tidak mahu "koyakkan" beberapa blok medan atas sebab estetik - contohnya, pasangan (pack, recno), iaitu PK untuk jadual ini.

Secara umum, menentukan susunan medan "minimum" adalah tugas "kuat kuasa" yang agak mudah. Oleh itu, anda boleh mendapatkan hasil yang lebih baik daripada data anda berbanding data kami - cubalah!

Sumber: www.habr.com