Hello.
Nami abdi Vanya sareng abdi pamekar Java. Éta kajadian yén kuring damel pisan sareng PostgreSQL - nyetél pangkalan data, ngaoptimalkeun struktur, kinerja, sareng maén DBA sakedik dina sabtu minggu.
Anyar-anyar ieu kuring parantos ngabereskeun sababaraha pangkalan data dina layanan mikro kami sareng nyerat perpustakaan java , anu ngagampangkeun padamelan ieu, ngahémat waktos sareng ngabantosan abdi ngahindarkeun sababaraha kasalahan umum anu dilakukeun ku pamekar. Perpustakaan ieu anu bakal urang bahas ayeuna.
Bantahan
Versi utama PostgreSQL anu kuring damel nyaéta 10. Sadaya patarosan SQL anu kuring anggo ogé diuji dina versi 11. Versi anu dirojong minimum nyaéta 9.6.
prasajarah
Eta sadayana dimimitian ampir sataun katukang jeung kaayaan anu aneh keur kuring: kreasi kalapa tina hiji indéks kaluar tina biru réngsé kalawan kasalahan. Indéks sorangan, sakumaha biasa, tetep dina database dina kaayaan teu valid. Analisis log nunjukkeun kakurangan . Tur kaluar urang balik ... Digging deeper, Kuring manggihan sakabeh kebat masalah dina konfigurasi database na, rolling up sleeves kuring, mimiti ngalereskeun aranjeunna kalayan Kilauan dina panon kuring.
Masalah hiji - konfigurasi standar
Meureun dulur geulis bosen metafora ngeunaan Postgres, nu bisa dijalankeun dina maker kopi, tapi ... konfigurasi standar bener raises sababaraha patarosan. Sahenteuna, éta patut nengetan pangropéa_work_mem, temp_file_limit, statement_timeout и lock_timeout.
Dina hal urang pangropéa_work_mem éta standar 64 MB, jeung temp_file_limit hal sabudeureun 2 GB - urang ngan saukur teu boga cukup memori pikeun nyieun hiji indéks dina tabel badag.
Ku kituna, dina pg-indéks-kaséhatan Kuring ngumpulkeun runtuyan , dina pamanggih kuring, parameter nu kudu ngonpigurasi pikeun tiap database.
Masalah dua - duplikat indéks
basis data urang hirup dina drive SSD, sarta kami nganggo HA-konfigurasi kalawan sababaraha puseur data, master host na n-jumlah réplika. Spasi piringan mangrupikeun sumber anu berharga pikeun urang; teu kurang pentingna ti kinerja sarta konsumsi CPU. Ku alatan éta, di hiji sisi, urang peryogi indexes pikeun bacaan gancang, sarta di sisi séjén, urang teu hayang ningali indexes perlu dina database, sabab dahar nepi spasi tur ngalambatkeun turun data ngamutahirkeun.
Tur ayeuna, sanggeus malikkeun sagalana sarta sanggeus katempo cukup , Kuring mutuskeun pikeun ngatur hiji "hébat" ngabersihan. Tétéla pamekar henteu resep maca dokuméntasi database. Aranjeunna teu resep pisan. Kusabab ieu, aya dua kasalahan anu umum - indéks anu didamel sacara manual dina konci primér sareng indéks "manual" anu sami dina kolom anu unik. Kanyataanna nyaéta aranjeunna henteu diperyogikeun - Postgres bakal ngalakukeun sadayana nyalira. Indéks sapertos kitu tiasa dihapus sacara aman, sareng diagnostik parantos muncul pikeun tujuan ieu .
Masalah tilu - indéks intersecting
Paling pamekar novice nyieun indexes dina kolom tunggal. Laun, sanggeus tuntas sakecap bisnis ieu, jalma ngawitan ngaoptimalkeun queries maranéhanana sarta nambahan indexes leuwih kompleks nu ngawengku sababaraha kolom. Ieu kumaha indéks dina kolom muncul A, A + B, A + B + C teras salajengna. Dua kahiji tina indéks ieu bisa aman dialungkeun kaluar, sabab éta prefiks tina katilu. Ieu ogé ngahemat seueur rohangan disk sareng aya diagnostik pikeun ieu .
Masalah opat - konci asing tanpa indéks
Postgres ngidinan Anjeun pikeun nyieun konstrain konci asing tanpa nangtukeun indéks backing. Dina loba kaayaan ieu teu jadi masalah, sarta bisa jadi teu manifest sorangan ... Keur waktu keur ...
Éta sami sareng kami: éta ngan ukur dina waktos anu sanés padamelan, dijalankeun dumasar kana jadwal sareng ngabersihan pangkalan data pesenan uji, mimiti "ditambahkeun" ka kami ku host master. CPU jeung IO indit ka runtah, requests kalem handap sarta timed kaluar, jasa éta lima ratus. Analisis gancang nunjukkeun yén patarosan sapertos:
delete from <table> where id in (…)Dina hal ieu, tangtosna, aya hiji indéks ku id dina tabel target, sarta saeutik pisan rékaman dihapus nurutkeun kaayaan. Éta sigana sadayana kedah dianggo, tapi, sayangna, éta henteu.
Nu éndah datang ka nyalametkeun ngajelaskeun analisis sarta ngomong yén salian mupus rékaman dina tabel target, aya ogé cék integritas rujukan, sarta dina salah sahiji tabel patali cék ieu gagal. scan sequential alatan kurangna indéks cocog. Kituna diagnostics dilahirkeun .
Masalah lima - nilai null dina indéks
Sacara standar, Postgres kalebet nilai null dina indéks btree, tapi biasana henteu diperyogikeun di dinya. Ku alatan éta, kuring rajin nyobian ngalungkeun nulls ieu (diagnostics ), nyieun indéks parsial dina kolom nullable dumasar jenis where <A> is not null. Ku cara kieu kuring tiasa ngirangan ukuran salah sahiji indéks kami tina 1877 MB ka 16 KB. Sareng dina salah sahiji jasa, ukuran database turun total ku 16% (ku 4.3 GB dina jumlah mutlak) kusabab pangaluaran nilai nol tina indéks. Hemat pisan dina rohangan disk kalayan modifikasi anu saderhana pisan. 🙂
Masalah genep - kurangna konci primér
Alatan sipat mékanisme nu kaayaan kawas ieu mungkin lamun ukuran tabel anjeun tumuwuh gancang alatan angka nu gede ngarupakeun rékaman maot. Kuring naively yakin yén ieu moal ngancem urang, sarta yén ieu moal lumangsung dina basa urang, sabab urang, wow!!!, mangrupakeun pamekar normal ... Kumaha bodo tur naif kuring ...
Hiji poé, hiji migrasi éndah nyandak tur diropéa sakabéh rékaman dina tabel badag tur aktip dipaké. Simkuring meunang +100 GB kana ukuran tabel kaluar tina bulao. Ieu éra damn, tapi misadventures kami teu mungkas dinya. Saatos autovacuum on tabel ieu réngsé 15 jam engké, janten jelas yén lokasi fisik moal balik. Kami henteu tiasa ngeureunkeun jasa sareng ngadamel VACUUM FULL, janten kami mutuskeun pikeun nganggo . Lajeng tétéla éta pg_repack teu nyaho kumaha carana ngolah tabel tanpa konci primér atawa konstrain uniqueness séjén, sarta tabel kami teu boga konci primér. Kituna diagnostics dilahirkeun .
Dina versi perpustakaan 0.1.5 Kamampuhan pikeun ngumpulkeun data tina bloat tabel sareng indéks sareng ngabalesana dina waktos anu pas parantos ditambah.
Masalah tujuh jeung dalapan - indexes cukup jeung indexes henteu kapake
Dua diagnostik di handap ieu nyaéta: и – mucunghul dina bentuk ahir maranéhanana rélatif anyar. Intina nyaéta aranjeunna henteu ngan ukur tiasa dicandak sareng ditambah.
Salaku geus I wrote, urang ngagunakeun konfigurasi kalawan sababaraha réplika, sarta beban bacaan dina host béda téh fundamentally béda. Hasilna, kaayaan tétéla yén sababaraha tabel sarta indéks dina sababaraha sarwa praktis teu dipaké, sarta pikeun analisis anjeun kudu ngumpulkeun statistik tina sakabeh host dina kluster. Ieu ogé diperlukeun dina unggal host dina kluster; Anjeun teu bisa ngalakukeun ieu ngan dina master.
Pendekatan ieu ngamungkinkeun urang pikeun ngahemat sababaraha puluhan gigabyte ku ngaleungitkeun indéks anu henteu pernah dianggo, ogé nambihan indéks anu leungit kana tabel anu jarang dianggo.
dina kacindekan
Tangtosna, pikeun ampir sadaya diagnostik anjeun tiasa ngonpigurasikeun . Ku cara ieu, anjeun tiasa gancang ngalaksanakeun cék dina aplikasi anjeun, nyegah kasalahan anyar muncul, teras ngalereskeun anu lami.
Sababaraha diagnostik tiasa dilakukeun dina tés fungsional saatos ngaluncurkeun migrasi database. Sareng ieu panginten salah sahiji fitur anu paling kuat dina perpustakaan kuring. Hiji conto pamakéan bisa kapanggih dina .
Masuk akal pikeun ngalakukeun cék pikeun indéks anu henteu dianggo atanapi leungit, ogé pikeun bloat, ngan ukur dina database nyata. Nilai anu dikumpulkeun tiasa dirékam dina atawa dikirim ka sistem monitoring.
Abdi ngarep pisan éta pg-indéks-kaséhatan bakal mangpaat tur di paménta. Anjeun ogé tiasa nyumbang kana pamekaran perpustakaan ku ngalaporkeun masalah anu anjeun mendakan sareng nyarankeun diagnostik anyar.
sumber: www.habr.com