ประหยัดเงินเมื่อซื้อปริมาณมากใน PostgreSQL

สานต่อหัวข้อการบันทึกกระแสข้อมูลขนาดใหญ่โดย บทความก่อนหน้านี้เกี่ยวกับการแบ่งพาร์ติชันในนี้เราจะดูวิธีที่คุณสามารถทำได้ ลดขนาด "ทางกายภาพ" ของการจัดเก็บ ใน PostgreSQL และผลกระทบต่อประสิทธิภาพของเซิร์ฟเวอร์

เราจะพูดถึง การตั้งค่า TOAST และการจัดตำแหน่งข้อมูล. “โดยเฉลี่ย” วิธีการเหล่านี้จะไม่ประหยัดทรัพยากรมากเกินไป แต่ไม่ต้องแก้ไขโค้ดแอปพลิเคชันเลย

อย่างไรก็ตามประสบการณ์ของเรากลับกลายเป็นว่ามีประโยชน์มากในเรื่องนี้ เนื่องจากการจัดเก็บข้อมูลการติดตามเกือบทุกชนิดโดยธรรมชาติของมัน ส่วนใหญ่ผนวกเท่านั้น ในแง่ของข้อมูลที่บันทึกไว้ และหากคุณสงสัยว่าจะสอนฐานข้อมูลให้เขียนลงดิสก์แทนได้อย่างไร 200MB / วินาที ครึ่งหนึ่ง - โปรดอยู่ใต้แมว

ความลับเล็กๆ น้อยๆ ของข้อมูลขนาดใหญ่

ตามโปรไฟล์งาน บริการของเราพวกมันบินไปหาเขาจากถ้ำเป็นประจำ แพ็คเกจข้อความ.

และตั้งแต่ วีแอลเอสไอคอมเพล็กซ์ฐานข้อมูลที่เราตรวจสอบเป็นผลิตภัณฑ์ที่มีหลายองค์ประกอบซึ่งมีโครงสร้างข้อมูลที่ซับซ้อน จากนั้นจึงทำการสืบค้น เพื่อประสิทธิภาพสูงสุด กลายเป็นแบบนี้ “หลายวอลุ่ม” พร้อมตรรกะอัลกอริทึมที่ซับซ้อน. ดังนั้นปริมาณของแต่ละอินสแตนซ์ของคำขอหรือแผนการดำเนินการผลลัพธ์ในบันทึกที่มาถึงเราจึงกลายเป็น "โดยเฉลี่ย" ค่อนข้างมาก

ลองดูโครงสร้างของตารางใดตารางหนึ่งที่เราเขียนข้อมูล "ดิบ" นั่นคือนี่คือข้อความต้นฉบับจากรายการบันทึก:

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

เครื่องหมายทั่วไป (แน่นอนว่ามีการแบ่งส่วนแล้ว ดังนั้นนี่คือเทมเพลตส่วน) โดยที่สิ่งที่สำคัญที่สุดคือข้อความ บางครั้งก็ค่อนข้างใหญ่โต

โปรดจำไว้ว่าขนาด "ทางกายภาพ" ของหนึ่งบันทึกใน PG ไม่สามารถครอบครองข้อมูลได้มากกว่าหนึ่งหน้า แต่ขนาด "ตรรกะ" เป็นเรื่องที่แตกต่างไปจากเดิมอย่างสิ้นเชิง หากต้องการเขียนค่าปริมาตร (varchar/text/bytea) ลงในเขตข้อมูล ให้ใช้ เทคโนโลยีโทสต์:

PostgreSQL ใช้ขนาดหน้าคงที่ (โดยทั่วไปคือ 8 KB) และไม่อนุญาตให้สิ่งอันดับขยายหลายหน้า ดังนั้นจึงเป็นไปไม่ได้ที่จะจัดเก็บค่าฟิลด์ที่มีขนาดใหญ่มากโดยตรง เพื่อเอาชนะข้อจำกัดนี้ ค่าฟิลด์ขนาดใหญ่จะถูกบีบอัดและ/หรือแยกออกเป็นหลายเส้น สิ่งนี้เกิดขึ้นโดยที่ผู้ใช้ไม่มีใครสังเกตเห็น และมีผลกระทบเพียงเล็กน้อยต่อโค้ดเซิร์ฟเวอร์ส่วนใหญ่ วิธีการนี้เรียกว่า TOAST...

ที่จริงแล้ว สำหรับทุกตารางที่มีเขตข้อมูล "อาจมีขนาดใหญ่" โดยอัตโนมัติ ตารางที่จับคู่กับ "การแบ่งส่วน" จะถูกสร้างขึ้น แต่ละบันทึก "ใหญ่" ในส่วน 2KB:

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

นั่นคือถ้าเราต้องเขียนสตริงที่มีค่า "มาก" dataแล้วการบันทึกจริงก็จะเกิดขึ้น ไม่เพียงแต่กับโต๊ะหลักและ PK เท่านั้น แต่ยังรวมไปถึง TOAST และ PK ของมันด้วย.

ลดอิทธิพลของ TOAST

แต่บันทึกส่วนใหญ่ของเราก็ยังไม่ใหญ่โตขนาดนั้น ควรพอดีกับ 8KB - จะประหยัดเงินเรื่องนี้ได้อย่างไร?..

นี่คือจุดที่คุณลักษณะมาช่วยเหลือเรา STORAGE ที่คอลัมน์ตาราง:

• ขยาย อนุญาตทั้งการบีบอัดและการจัดเก็บแยกกัน นี้ ตัวเลือกมาตรฐาน สำหรับประเภทข้อมูลที่สอดคล้องกับ TOAST ส่วนใหญ่ ขั้นแรกจะพยายามทำการบีบอัด จากนั้นจึงจัดเก็บไว้นอกตารางหากแถวยังมีขนาดใหญ่เกินไป
• MAIN อนุญาตการบีบอัดแต่ไม่แยกการจัดเก็บ (อันที่จริงจะยังคงมีการจัดเก็บแยกต่างหากสำหรับคอลัมน์ดังกล่าว แต่เพียงเท่านั้น เป็นทางเลือกสุดท้ายเมื่อไม่มีวิธีอื่นในการย่อสตริงให้พอดีกับหน้า)

อันที่จริงนี่คือสิ่งที่เราต้องการสำหรับข้อความ - บีบอัดให้มากที่สุด และถ้ามันไม่พอดีเลย ให้ใส่ลงใน TOAST. ซึ่งสามารถทำได้ทันทีด้วยคำสั่งเดียว:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

วิธีการประเมินผลกระทบ

เนื่องจากกระแสข้อมูลเปลี่ยนแปลงทุกวัน เราจึงไม่สามารถเปรียบเทียบตัวเลขสัมบูรณ์ได้ แต่ในแง่ความสัมพันธ์ หุ้นน้อยลง เราเขียนมันลงใน TOAST - ยิ่งดีเท่าไร แต่มีอันตรายอยู่ที่นี่ - ยิ่งปริมาณ "ทางกายภาพ" ของแต่ละบันทึกมากขึ้น ดัชนีก็จะ "กว้างขึ้น" เนื่องจากเราต้องครอบคลุมหน้าข้อมูลมากขึ้น

ส่วน ก่อนการเปลี่ยนแปลง:

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

ส่วน หลังจากการเปลี่ยนแปลง:

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

อันที่จริงเรา เริ่มเขียนถึง TOAST น้อยลง 2 เท่าซึ่งไม่ได้โหลดเฉพาะดิสก์เท่านั้น แต่ยังรวมถึง CPU ด้วย:

ฉันจะสังเกตว่าเรา "อ่าน" ดิสก์น้อยลงเช่นกัน ไม่ใช่แค่ "เขียน" - เนื่องจากเมื่อแทรกบันทึกลงในตาราง เราต้อง "อ่าน" ส่วนหนึ่งของแผนผังของแต่ละดัชนีด้วยเพื่อกำหนด ตำแหน่งในอนาคตในพวกเขา

ใครบ้างที่สามารถใช้ชีวิตได้ดีบน PostgreSQL 11

หลังจากอัปเดตเป็น PG11 เราตัดสินใจที่จะ "ปรับแต่ง" TOAST ต่อไป และสังเกตเห็นว่าตั้งแต่เวอร์ชันนี้เป็นต้นไป พารามิเตอร์จะพร้อมสำหรับการปรับแต่ง toast_tuple_target:

รหัสประมวลผล TOAST จะเริ่มทำงานเฉพาะเมื่อค่าแถวที่จะเก็บไว้ในตารางมีขนาดใหญ่กว่า TOAST_TUPLE_THRESHOLD ไบต์ (ปกติคือ 2 KB) รหัส TOAST จะบีบอัดและ/หรือย้ายค่าฟิลด์ออกจากตารางจนกว่าค่าแถวจะน้อยกว่า TOAST_TUPLE_TARGET ไบต์ (ค่าตัวแปรซึ่งปกติคือ 2 KB) หรือไม่สามารถลดขนาดได้

เราตัดสินใจว่าข้อมูลที่เรามักจะมีคือ "สั้นมาก" หรือ "ยาวมาก" ดังนั้นเราจึงตัดสินใจจำกัดตัวเองให้มีค่าต่ำสุดที่เป็นไปได้:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

มาดูกันว่าการตั้งค่าใหม่ส่งผลต่อการโหลดดิสก์อย่างไรหลังจากกำหนดค่าใหม่:

ไม่เลว! เฉลี่ย คิวไปยังดิสก์ลดลง ประมาณ 1.5 เท่าและดิสก์ "ไม่ว่าง" คือ 20 เปอร์เซ็นต์! แต่บางทีนี่อาจส่งผลต่อ CPU บ้าง?

อย่างน้อยก็ไม่ได้เลวร้ายไปกว่านี้ แม้ว่าจะเป็นการยากที่จะตัดสินได้ว่าแม้ปริมาณดังกล่าวก็ยังไม่สามารถเพิ่มภาระ CPU โดยเฉลี่ยให้สูงขึ้นได้ 5%.

โดยการเปลี่ยนสถานที่ของเงื่อนไข ผลรวม... เปลี่ยน!

ดังที่คุณทราบ เงินหนึ่งเพนนีช่วยประหยัดรูเบิลได้ และด้วยปริมาณพื้นที่เก็บข้อมูลของเรา ก็ช่วยประหยัดได้ 10TB/เดือน การเพิ่มประสิทธิภาพเพียงเล็กน้อยก็สามารถให้ผลกำไรที่ดีได้ ดังนั้นเราจึงใส่ใจกับโครงสร้างทางกายภาพของข้อมูลของเราว่าเป็นอย่างไร ช่อง "ซ้อนกัน" ภายในบันทึก แต่ละตาราง

เพราะเพราะว่า การจัดตำแหน่งข้อมูล นี่มันตรงไปตรงมา ส่งผลต่อปริมาณผลลัพธ์:

สถาปัตยกรรมจำนวนมากจัดให้มีการจัดตำแหน่งข้อมูลบนขอบเขตคำของเครื่อง ตัวอย่างเช่น ในระบบ x32 แบบ 86 บิต จำนวนเต็ม (ชนิดจำนวนเต็ม 4 ไบต์) จะถูกจัดเรียงบนขอบเขตคำขนาด 4 ไบต์ เช่นเดียวกับตัวเลขทศนิยมที่มีความแม่นยำสองเท่า (จุดลอยตัวที่มีความแม่นยำสองเท่า 8 ไบต์) และในระบบ 64 บิต ค่าสองเท่าจะสอดคล้องกับขอบเขตคำขนาด 8 ไบต์ นี่เป็นอีกสาเหตุของความไม่ลงรอยกัน

เนื่องจากการจัดแนว ขนาดของแถวตารางจะขึ้นอยู่กับลำดับของฟิลด์ โดยปกติแล้วเอฟเฟกต์นี้จะไม่สังเกตเห็นได้ชัดเจนมากนัก แต่ในบางกรณีอาจทำให้ขนาดเพิ่มขึ้นอย่างมาก ตัวอย่างเช่น หากคุณผสมช่อง char(1) และช่องจำนวนเต็ม โดยปกติแล้วจะต้องใช้พื้นที่ 3 ไบต์โดยเปล่าประโยชน์ระหว่างช่องเหล่านั้น

เริ่มจากแบบจำลองสังเคราะห์กันก่อน:

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

ไบต์พิเศษสองสามไบต์มาจากไหนในกรณีแรก? มันง่ายมาก - Smallint ขนาด 2 ไบต์จัดชิดบนขอบเขตขนาด 4 ไบต์ ก่อนสนามถัดไปและเมื่อเป็นสนามสุดท้ายก็ไม่มีอะไรและไม่จำเป็นต้องจัดตำแหน่ง

ตามทฤษฎีแล้ว ทุกอย่างเรียบร้อยดี และคุณสามารถจัดเรียงฟิลด์ใหม่ได้ตามที่คุณต้องการ มาตรวจสอบข้อมูลจริงโดยใช้ตัวอย่างของตารางตัวใดตัวหนึ่งซึ่งส่วนรายวันครอบคลุมพื้นที่ 10-15GB

โครงสร้างเริ่มต้น:

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)

ส่วนหลังจากเปลี่ยนลำดับคอลัมน์ - อย่างแน่นอน ฟิลด์เดียวกันเพียงลำดับที่แตกต่างกัน:

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)

ปริมาตรรวมของส่วนนี้ถูกกำหนดโดยจำนวน "ข้อเท็จจริง" และขึ้นอยู่กับกระบวนการภายนอกเท่านั้น ดังนั้นมาแบ่งขนาดของฮีปกัน (pg_relation_size) ตามจำนวนบันทึกในนั้น - นั่นคือเราได้รับ ขนาดเฉลี่ยของบันทึกที่จัดเก็บจริง:

ลบปริมาตร 6%, ยอดเยี่ยม!

แต่แน่นอนว่าทุกอย่างไม่ได้เป็นสีดอกกุหลาบเลย - ท้ายที่สุดแล้ว ในดัชนีเราไม่สามารถเปลี่ยนลำดับของฟิลด์ได้และดังนั้น "โดยทั่วไป" (pg_total_relation_size) ...

...ยังอยู่ที่นี่เหมือนกัน ประหยัด 1.5%โดยไม่ต้องเปลี่ยนโค้ดแม้แต่บรรทัดเดียว ใช่ ๆ!

ฉันทราบว่าตัวเลือกข้างต้นสำหรับการจัดเรียงฟิลด์ไม่ใช่ข้อเท็จจริงที่ว่ามันเหมาะสมที่สุด เพราะคุณไม่ต้องการ "ฉีก" ทุ่งนาบางส่วนด้วยเหตุผลด้านความสวยงาม - ตัวอย่างเช่นสองสามอัน (pack, recno)ซึ่งก็คือ PK ของตารางนี้

โดยทั่วไป การพิจารณาการจัดเรียงฟิลด์ "ขั้นต่ำ" นั้นเป็นงาน "กำลังดุร้าย" ที่ค่อนข้างง่าย ดังนั้น คุณจะได้รับผลลัพธ์ที่ดียิ่งขึ้นจากข้อมูลของคุณมากกว่าของเรา - ลองเลย!

ที่มา: will.com