Hemat satu sen untuk volume besar di PostgreSQL

Melanjutkan topik pencatatan aliran data besar yang diangkat oleh artikel sebelumnya tentang partisi, dalam hal ini kita akan melihat cara-cara yang Anda bisa mengurangi ukuran "fisik" dari penyimpanan di PostgreSQL, dan dampaknya terhadap kinerja server.

Kita akan membicarakannya Pengaturan TOAST dan penyelarasan data. “Rata-rata,” metode ini tidak akan menghemat terlalu banyak sumber daya, tetapi tanpa mengubah kode aplikasi sama sekali.

Namun, pengalaman kami ternyata sangat produktif dalam hal ini, karena penyimpanan hampir semua pemantauan pada dasarnya bersifat demikian kebanyakan hanya tambahan dalam kaitannya dengan data yang direkam. Dan jika Anda bertanya-tanya bagaimana Anda bisa mengajarkan database untuk menulis ke disk 200MB / s setengahnya - tolong di bawah kucing.

Rahasia kecil dari data besar

Berdasarkan profil pekerjaan layanan kami, mereka secara teratur terbang ke arahnya dari sarangnya paket teks.

Dan sejak Kompleks VLSIyang databasenya kami pantau adalah produk multi-komponen dengan struktur data yang kompleks, lalu kueri untuk performa maksimal ternyata seperti ini "multi-volume" dengan logika algoritmik yang kompleks. Jadi volume setiap permintaan atau rencana eksekusi yang dihasilkan dalam log yang datang kepada kami ternyata “rata-rata” cukup besar.

Mari kita lihat struktur salah satu tabel tempat kita menulis data "mentah" - yaitu, berikut adalah 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)
);

Tandanya khas (sudah dibelah tentunya jadi ini template bagiannya), dimana yang terpenting adalah teksnya. Terkadang cukup banyak.

Ingatlah bahwa ukuran "fisik" dari satu catatan di PG tidak dapat menempati lebih dari satu halaman data, tetapi ukuran "logis" adalah masalah yang sama sekali berbeda. Untuk menulis nilai volumetrik (varchar/teks/bytea) ke suatu bidang, gunakan Teknologi roti panggang:

PostgreSQL menggunakan ukuran halaman tetap (biasanya 8 KB), dan tidak mengizinkan tupel menjangkau beberapa halaman. Oleh karena itu, tidak mungkin menyimpan nilai bidang yang sangat besar secara langsung. Untuk mengatasi keterbatasan ini, nilai bidang yang besar dikompresi dan/atau dibagi menjadi beberapa garis fisik. Hal ini terjadi tanpa disadari oleh pengguna dan berdampak kecil pada sebagian besar kode server. Cara ini dikenal dengan TOAST...

Faktanya, untuk setiap tabel dengan bidang "berpotensi besar", secara otomatis tabel berpasangan dengan "mengiris" dibuat setiap rekaman "besar" dalam segmen 2 KB:

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

Artinya, jika kita harus menulis string dengan nilai “besar”. data, maka perekaman sebenarnya akan terjadi tidak hanya pada meja utama dan PK nya saja, namun juga pada TOAST dan PK nya.

Mengurangi pengaruh TOAST

Namun sebagian besar catatan kami masih belum sebesar itu, harus muat dalam 8KB - Bagaimana saya bisa menghemat uang untuk ini?..

Di sinilah atribut membantu kita STORAGE di kolom tabel:

• DITERIMA memungkinkan kompresi dan penyimpanan terpisah. Ini pilihan standar untuk sebagian besar tipe data yang sesuai dengan TOAST. Pertama-tama ia mencoba melakukan kompresi, lalu menyimpannya di luar tabel jika barisnya masih terlalu besar.
• UTAMA memungkinkan kompresi tetapi bukan penyimpanan terpisah. (Faktanya, penyimpanan terpisah akan tetap dilakukan untuk kolom tersebut, tetapi hanya sebagai upaya terakhir, bila tidak ada cara lain untuk mengecilkan string agar pas di halaman.)

Faktanya, inilah yang kita perlukan untuk teks - kompreslah sekencang-kencangnya, dan jika tidak muat sama sekali, masukkan ke dalam TOAST. Hal ini dapat dilakukan langsung dengan cepat, dengan satu perintah:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Bagaimana cara mengevaluasi efeknya

Karena aliran data berubah setiap hari, kita tidak bisa membandingkan angka absolut, tapi secara relatif bagian yang lebih kecil Kami menuliskannya di TOAST - jauh lebih baik. Namun ada bahaya di sini - semakin besar volume “fisik” dari masing-masing catatan, semakin “luas” indeksnya, karena kita harus mencakup 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%)

Faktanya, kita mulai menulis ke TOAST 2 kali lebih jarang, yang tidak hanya membongkar disk, tetapi juga CPU:

Saya perhatikan bahwa kita juga menjadi lebih kecil dalam "membaca" disk, tidak hanya "menulis" - karena ketika memasukkan catatan ke dalam tabel, kita juga harus "membaca" bagian dari pohon setiap indeks untuk menentukannya posisi masa depan di dalamnya.

Siapa yang bisa hidup dengan baik di PostgreSQL 11

Setelah memperbarui ke PG11, kami memutuskan untuk melanjutkan "penyetelan" TOAST dan memperhatikan bahwa mulai dari versi ini parameter tersedia untuk penyetelan toast_tuple_target:

Kode pemrosesan TOAST hanya diaktifkan 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 field keluar dari tabel hingga nilai baris menjadi kurang dari TOAST_TUPLE_TARGET byte (nilai variabel, biasanya juga 2 KB) atau ukurannya tidak dapat diperkecil.

Kami memutuskan bahwa data yang biasanya kami miliki adalah “sangat pendek” atau “sangat panjang”, jadi kami memutuskan untuk membatasi diri pada nilai seminimal mungkin:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Mari kita lihat bagaimana pengaturan baru memengaruhi pemuatan disk setelah konfigurasi ulang:

Tidak buruk! Rata-rata antrian ke disk telah berkurang sekitar 1.5 kali, dan disk “sibuk” adalah 20 persen! Tapi mungkin ini mempengaruhi CPU?

Setidaknya keadaannya tidak menjadi lebih buruk. Meskipun demikian, sulit untuk menilai apakah volume seperti itu masih tidak dapat meningkatkan rata-rata beban CPU lebih tinggi 5%.

Dengan mengubah tempat suku-sukunya, jumlahnya... berubah!

Seperti yang Anda ketahui, satu sen berarti menghemat satu rubel, dan dengan volume penyimpanan kami, hal tersebut akan menghemat satu sen 10TB/bulan optimasi sedikit saja bisa memberikan keuntungan yang lumayan. Oleh karena itu, kami memperhatikan struktur fisik data kami - bagaimana tepatnya bidang "ditumpuk" di dalam catatan masing-masing tabel.

Karena karena penyelarasan data ini lurus ke depan mempengaruhi volume yang dihasilkan:

Banyak arsitektur menyediakan penyelarasan data pada batasan kata mesin. Misalnya, pada sistem x32 86-bit, bilangan bulat (tipe bilangan bulat, 4 byte) akan disejajarkan pada batas kata 4-byte, begitu pula bilangan titik mengambang presisi ganda (titik mengambang presisi ganda, 8 byte). Dan pada sistem 64-bit, nilai ganda akan disejajarkan dengan batas kata 8 byte. Ini adalah alasan lain ketidakcocokan.

Karena perataan, ukuran baris tabel bergantung pada urutan bidang. Biasanya efek ini tidak terlalu terlihat, namun dalam beberapa kasus dapat menyebabkan peningkatan ukuran yang signifikan. Misalnya, jika Anda mencampur kolom char(1) dan integer, biasanya akan ada 3 byte yang terbuang di antara keduanya.

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 datangnya beberapa byte tambahan dalam kasus pertama? Itu mudah - Smallint 2-byte disejajarkan pada batas 4-byte sebelum bidang berikutnya, dan jika itu adalah bidang terakhir, tidak ada apa-apa dan tidak perlu menyelaraskan.

Secara teori, semuanya baik-baik saja dan Anda dapat mengatur ulang bidang sesuai keinginan. Mari kita periksa data nyata menggunakan contoh salah satu tabel, bagian hariannya menempati 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 setelah mengubah urutan kolom - tepatnya bidang yang sama, 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)

Total volume bagian ditentukan oleh jumlah "fakta" dan hanya bergantung pada proses eksternal, jadi mari kita bagi ukuran heap (pg_relation_size) dengan jumlah catatan di dalamnya - yaitu, kita dapatkan ukuran rata-rata dari rekaman aktual yang disimpan:

Dikurangi 6% volumenya, Besar!

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

...masih di sini juga menghemat 1.5%tanpa mengubah satu baris kode pun. Ya ya!

Saya perhatikan bahwa opsi di atas untuk mengatur bidang bukanlah yang paling optimal. Karena Anda tidak ingin "merobek" beberapa blok bidang karena alasan estetika - misalnya pasangan (pack, recno), yang merupakan PK untuk tabel ini.

Secara umum, menentukan susunan “minimum” bidang adalah tugas “brute force” yang cukup sederhana. Oleh karena itu, Anda bisa mendapatkan hasil yang lebih baik dari data Anda dibandingkan data kami - cobalah!

Sumber: www.habr.com