Get best of the week

Hemat banyak uang untuk volume besar di PostgreSQL

Melanjutkan topik rekaman aliran besar data, yang diangkat oleh artikel sebelumnya tentang partisi , dalam hal ini kami mempertimbangkan cara-cara di mana Anda dapat mengurangi ukuran "fisik" yang disimpan dalam PostgreSQL, dan pengaruhnya terhadap kinerja server.

Ini tentang pengaturan TOAST dan perataan data . "Rata-rata", metode ini akan menghemat tidak terlalu banyak sumber daya, tetapi tanpa modifikasi pada kode aplikasi.


Namun, pengalaman kami ternyata sangat produktif dalam hal ini, karena repositori dari hampir semua pemantauan pada dasarnya bersifat tambahan-hanya dalam hal data yang direkam. Dan jika Anda tertarik bagaimana Anda bisa mengajarkan database untuk menulis ke disk, bukannya 200MB / s setengahnya - saya minta potongan.

Rahasia Kecil Data Besar


Menurut profil layanan kami , ia secara teratur menerima paket teks dari log .

Dan karena kompleks VLSI , yang databasenya kami pantau, adalah produk multikomponen dengan struktur data yang kompleks, pertanyaan untuk mencapai kinerja maksimum diperoleh dengan "multi-volume" dengan logika algoritmik yang kompleks . Jadi volume setiap instance individu dari permintaan atau rencana eksekusi yang dihasilkan dalam log yang datang kepada kami ternyata “rata-rata” cukup besar.

Mari kita lihat struktur dari salah satu tabel di mana kita menulis data "mentah" - yaitu, inilah teks asli 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)
);

Piring yang khas (sudah dipartisi, tentu saja, karena itu merupakan templat bagian), di mana yang paling penting adalah teks. Terkadang cukup produktif.

Ingat bahwa ukuran "fisik" dari satu rekaman di PG tidak dapat menempati lebih dari satu halaman data, tetapi ukuran "logis" adalah masalah yang sama sekali berbeda. Untuk menulis nilai volume (varchar / teks / bytea) ke dalam bidang, teknologi TOAST digunakan :
PostgreSQL menggunakan ukuran halaman tetap (biasanya 8 KB), dan tidak mengizinkan tupel untuk menjangkau beberapa halaman. Oleh karena itu, tidak mungkin untuk secara langsung menyimpan nilai bidang yang sangat besar. Untuk mengatasi batasan ini, nilai bidang yang besar dikompresi dan / atau dipecah menjadi beberapa garis fisik. Ini terjadi tanpa disadari oleh pengguna dan sedikit memengaruhi sebagian besar kode server. Metode ini dikenal sebagai TOAST ...

Bahkan, untuk setiap tabel dengan bidang "berpotensi besar" , tabel berpasangan secara otomatis dibuat dengan "mengiris" dari setiap catatan "besar" di segmen 2KB:

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

Artinya, jika kita harus menulis baris dengan nilai "besar" data, maka catatan nyata akan terjadi tidak hanya di tabel utama dan PK-nya, tetapi juga di TOAST dan PK-nya .

Kurangi efek TOAST


Tetapi sebagian besar catatan di sini masih tidak begitu besar, mereka harus muat dalam 8KB - bagaimana Anda menghemat ini? ..

Di sini atribut STORAGEdi kolom tabel datang ke bantuan kami :
  • DIPERPANJANGKAN memungkinkan kompresi dan penyimpanan terpisah. Ini adalah opsi standar untuk sebagian besar tipe data yang kompatibel TOAST. Pertama, upaya dilakukan untuk melakukan kompresi, kemudian disimpan di luar tabel jika barisnya masih terlalu besar.
  • MAIN memungkinkan kompresi, tetapi tidak memisahkan penyimpanan. (Pada kenyataannya, penyimpanan terpisah, akan dilakukan untuk kolom seperti itu, tetapi hanya sebagai upaya terakhir , ketika tidak ada cara lain untuk mengurangi baris sehingga cocok pada halaman.)
Faktanya, ini adalah persis apa yang kita butuhkan untuk teks - peras sebanyak mungkin, dan bahkan jika itu tidak cocok sama sekali - masukkan ke dalam TOAST . Anda dapat melakukan ini secara langsung "on the fly", dengan satu perintah:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Bagaimana cara mengevaluasi efeknya


Karena aliran data berubah setiap hari, kami tidak dapat membandingkan angka absolut, tetapi secara relatif, semakin kecil proporsi yang kami catat di TOAST, semakin baik. Tetapi ada bahaya - semakin kita memiliki volume "fisik" dari setiap catatan individu, indeks itu "lebih luas", karena kita harus membahas lebih banyak halaman data.

Bagian sebelum perubahan :
heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Bagian setelah perubahan :
heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Bahkan, kami mulai menulis di TOAST 2 kali lebih jarang , yang tidak hanya memuat disk, tetapi juga CPU:



Saya perhatikan bahwa kami juga mulai "membaca" disk lebih sedikit, tidak hanya "menulis" - karena ketika Anda memasukkan catatan ke beberapa tabel, Anda juga harus "mengurangi" bagian dari pohon dari masing-masing indeks untuk menentukan posisi masa depan di dalamnya.

Siapa di PostgreSQL 11 hidup dengan baik


Setelah memutakhirkan ke PG11, kami memutuskan untuk melanjutkan "tuning" TOAST dan memperhatikan bahwa mulai dengan versi ini, parameter menjadi tersedia untuk konfigurasi toast_tuple_target:
Kode pemrosesan TOAST dipicu hanya ketika nilai baris yang akan disimpan dalam tabel lebih besar dari TOAST_TUPLE_THRESHOLD byte (biasanya 2 Kb). Kode TOAST akan memampatkan dan / atau memindahkan nilai bidang dari tabel hingga nilai baris kurang dari TOAST_TUPLE_TARGET byte (variabel, biasanya 2 KB juga) atau menjadi tidak mungkin untuk mengurangi ukuran.
Kami memutuskan bahwa data yang biasanya kami miliki adalah "sangat pendek" atau segera "sangat panjang", jadi kami memutuskan untuk membatasi diri pada nilai serendah mungkin:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Mari kita lihat bagaimana pengaturan baru mempengaruhi pemuatan disk setelah migrasi:


Tidak buruk! Line-up rata-rata ke disk menurun sekitar 1,5 kali, dan "hunian" disk - sebesar 20 persen! Tapi mungkin ini entah bagaimana mempengaruhi CPU?


Setidaknya, itu tidak menjadi lebih buruk. Meskipun, sulit untuk menilai apakah volume seperti itu masih tidak dapat menaikkan rata-rata beban CPU di atas 5% .

Dari perubahan posisi, jumlah ... berubah!


Seperti yang Anda ketahui, satu sen menghemat rubel, dan dengan volume penyimpanan kami sekitar 10TB / bulan, bahkan optimasi kecil dapat memberikan keuntungan yang baik. Oleh karena itu, kami menarik perhatian pada struktur fisik data kami - bagaimana tepatnya "bidang" diletakkan di dalam catatan setiap tabel.

Karena karena penyelarasan data, ini secara langsung mempengaruhi volume yang dihasilkan :
Banyak arsitektur menyediakan penyelarasan data melintasi batas-batas kata mesin. Misalnya, pada sistem 32-bit x86, bilangan bulat (tipe bilangan bulat, menempati 4 byte) akan disejajarkan di perbatasan kata 4-byte, serta angka floating-point presisi ganda (tipe presisi ganda, 8 byte). Dan pada sistem 64-bit, nilai ganda akan disejajarkan di perbatasan kata 8-byte. Ini adalah alasan lain ketidakcocokan.

Karena perataan, ukuran baris tabel tergantung pada urutan bidang. Biasanya efek ini tidak terlalu terlihat, tetapi dalam beberapa kasus dapat menyebabkan peningkatan ukuran yang signifikan. Misalnya, jika Anda menempatkan bidang tipe char (1) dan integer dicampur, di antara mereka, sebagai aturan, 3 byte akan sia-sia.

Mari kita mulai dengan model sintetis:

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

Dari mana asal pasangan byte ekstra dalam kasus pertama? Semuanya sederhana - smallint 2-byte selaras dengan batas 4-byte sebelum bidang berikutnya, dan ketika itu adalah yang terakhir, tidak ada dan tidak perlu untuk menyelaraskannya.

Secara teori, semuanya baik-baik saja dan Anda dapat mengatur ulang bidang yang Anda inginkan. Mari kita periksa data nyata pada contoh salah satu tabel, bagian harian yang membutuhkan 10-15GB.

Struktur sumber:

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 setelah mengubah urutan kolom adalah bidang yang sama persis , hanya urutannya yang berbeda :

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 bagian ditentukan oleh jumlah "fakta" dan hanya bergantung pada proses eksternal, jadi kami membagi ukuran tumpukan ( pg_relation_size) dengan jumlah catatan di dalamnya - yaitu, kami mendapatkan ukuran rata - rata dari catatan tersimpan aktual :


Minus 6% dari volume , luar biasa!

Tapi semuanya, tentu saja, tidak begitu cerah - karena dalam indeks kita tidak dapat mengubah urutan bidang , dan karena itu "secara umum" ( pg_total_relation_size) ...


... setelah semua, mereka menghemat 1,5% di sini , tanpa mengubah satu baris kode. Ya ya!



Saya perhatikan bahwa pengaturan bidang di atas bukan fakta yang paling optimal. Karena beberapa blok bidang tidak ingin "dihancurkan" karena alasan estetika - misalnya, pasangan (pack, recno), yang merupakan PK untuk tabel ini.

Secara umum, definisi pengaturan bidang "minimum" adalah tugas "lengkap" yang cukup sederhana. Karena itu, Anda bisa mendapatkan hasil pada data Anda lebih baik daripada kami - coba saja!

More articles:


All Articles