Kepiye carane tuwuh 10 kaping pitakon menyang database tanpa pindhah menyang server sing luwih produktif lan njaga fungsi sistem? Aku bakal pitutur marang kowe carane kita urusan karo Kurangé populasi ing kinerja database kita, carane kita optimized query SQL kanggo ngawula minangka akeh kedhaftar sabisa lan ora nambah biaya sumber daya komputerisasi.
Aku nggawe layanan kanggo ngatur proses bisnis ing perusahaan konstruksi. Udakara 3 ewu perusahaan kerja bareng karo kita. Luwih saka 10 ewu wong nggarap sistem kita saben dina sajrone 4-10 jam. Iki ngrampungake macem-macem masalah perencanaan, kabar, peringatan, validasi ... Kita nggunakake PostgreSQL 9.6. Kita duwe udakara 300 tabel ing database lan nganti 200 yuta pitakon (10 ewu sing beda) ditampa saben dina. Rata-rata kita duwe 3-4 ewu panjaluk per detik, ing wektu sing paling aktif luwih saka 10 ewu panjaluk per detik. Umume pitakon yaiku OLAP. Ana tambahan, modifikasi lan pambusakan sing luwih sithik, tegese beban OLTP relatif entheng. Aku nyedhiyakake kabeh nomer kasebut supaya sampeyan bisa netepake ukuran proyek kita lan ngerti kepiye pengalaman kita bisa migunani kanggo sampeyan.
Gambar siji. Lirik
Nalika kita miwiti pembangunan, kita ora mikir babagan apa jenis beban bakal tiba ing database lan apa sing bakal ditindakake yen server mandheg narik. Nalika ngrancang basis data, kita ngetutake rekomendasi umum lan nyoba ora njupuk awake dhewe ing sikil, nanging ngluwihi saran umum kaya "aja nggunakake pola kasebut. kita ora mlebu. Kita ngrancang adhedhasar prinsip normalisasi, ngindhari redundansi data lan ora peduli babagan nyepetake pitakon tartamtu. Sanalika pangguna pisanan teka, kita nemoni masalah kinerja. Minangka biasanipun, kita padha rampung unprepared kanggo iki. Masalah pisanan dadi prasaja. Minangka aturan, kabeh wis ditanggulangi kanthi nambah indeks anyar. Nanging ana wektu nalika patches prasaja mandheg digunakake. Sadhar yen kita ora duwe pengalaman lan dadi saya angel ngerti apa sing nyebabake masalah kasebut, kita nyewa spesialis sing mbantu nyiyapake server kanthi bener, nyambungake pemantauan, lan nuduhake menyang ngendi goleki. .
Gambar loro. Statistik
Dadi kita duwe kira-kira 10 ewu pitakon beda sing dieksekusi ing basis data saben dina. Saka 10 ewu iki, ana monster sing dieksekusi kaping 2-3 yuta kanthi wektu eksekusi rata-rata 0.1-0.3 ms, lan ana pitakon kanthi wektu eksekusi rata-rata 30 detik sing diarani kaping 100 dina.
Ora bisa ngoptimalake kabeh 10 ewu pitakon, mula kita mutusake kanggo nemtokake ngendi kanggo ngarahake upaya supaya bisa nambah kinerja database kanthi bener. Sawise sawetara iterasi, kita wiwit mbagi panjalukan dadi jinis.
panjalukan TOP
Iki minangka pitakon paling abot sing mbutuhake wektu paling akeh (total wektu). Iki minangka pitakon sing diarani asring banget utawa pitakon sing mbutuhake wektu sing suwe kanggo dieksekusi (pitakon sing dawa lan kerep dioptimalake ing iterasi pisanan perang kanggo kacepetan). Akibaté, server mbuwang paling wektu ing eksekusi. Kajaba iku, penting kanggo misahake panjalukan ndhuwur kanthi total wektu eksekusi lan kanthi kapisah dening wektu IO. Cara kanggo ngoptimalake pitakon kasebut rada beda.
Praktek umume kabeh perusahaan yaiku nggarap panjaluk TOP. Ana sawetara; ngoptimalake malah siji pitakon bisa mbebasake 5-10% sumber daya. Nanging, nalika proyek diwasa, ngoptimalake pitakon TOP dadi tugas sing ora pati penting. Kabeh cara prasaja wis digarap, lan panjalukan paling "abot" njupuk "mung" 3-5% saka sumber daya. Yen pitakon TOP kanthi total njupuk kurang saka 30-40% wektu, mesthine sampeyan wis ngupayakake supaya bisa cepet lan wektune kanggo ngoptimalake pitakon saka grup sabanjure.
Iku tetep kanggo njawab pitakonan saka carane akeh pitakonan ndhuwur kudu kalebu ing grup iki. Aku biasane njupuk paling 10, nanging ora luwih saka 20. Aku nyoba kanggo mesthekake yen wektu pisanan lan pungkasan ing grup TOP beda-beda dening ora luwih saka 10 kaping. Yaiku, yen wektu eksekusi query mudhun banget saka posisi 1 nganti 10, banjur aku njupuk TOP-10, yen drop luwih bertahap, banjur nambah ukuran grup dadi 15 utawa 20.
Petani tengahan
Iki kabeh panjalukan sing teka langsung sawise TOP, kajaba 5-10% pungkasan. Biasane, ing ngoptimalake pitakon kasebut ana kesempatan kanggo nambah kinerja server. Panjaluk kasebut bisa nganti 80%. Nanging sanajan bagean kasebut wis ngluwihi 50%, mula wektune kanggo ndeleng kanthi luwih teliti.
buntut
Kaya sing wis kasebut, pitakon kasebut teka ing pungkasan lan njupuk 5-10% wektu. Sampeyan bisa lali babagan mung yen sampeyan ora nggunakake alat analisis pitakon otomatis, banjur ngoptimalake uga bisa murah.
Kepiye carane ngevaluasi saben klompok?
Aku nggunakake query SQL sing mbantu nggawe taksiran kuwi kanggo PostgreSQL (Aku yakin query padha bisa ditulis kanggo akeh DBMS liyane)
Query SQL kanggo ngira ukuran grup TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Asil pitakon yaiku telung kolom, saben kolom ngemot persentase wektu sing dibutuhake kanggo ngolah pitakon saka grup iki. Ing panyuwunan kasebut ana rong nomer (ing kasusku yaiku 20 lan 800) sing misahake panjaluk saka siji klompok saka liyane.
Iki carane saham panjalukan kira-kira mbandhingake nalika kerja optimasi diwiwiti lan saiki.
Diagram kasebut nuduhake yen bagean saka panjalukan TOP wis suda banget, nanging "petani tengah" tambah akeh.
Ing wiwitan, panjaluk TOP kalebu kesalahan sing jelas. Swara wektu, penyakit kanak-kanak ilang, bagean saka panjalukan TOP suda, lan liyane lan liyane efforts kanggo nyepetake panjalukan angel.
Kanggo njaluk teks panjalukan kita nggunakake panjalukan ing ngisor iki
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Iki minangka dhaptar teknik sing paling umum digunakake sing mbantu nyepetake pitakon TOP:
- Ngrancang ulang sistem, contone, ngolah maneh logika kabar nggunakake makelar pesen tinimbang pitakon periodik menyang database
- Nambah utawa ngganti indeks
- Nulis ulang pitakon ORM menyang SQL murni
- Nulis maneh logika loading data males
- Caching liwat denormalisasi data. Contone, kita duwe sambungan tabel Delivery -> Invoice -> Request -> Application. Yaiku, saben pangiriman digandhengake karo aplikasi liwat tabel liyane. Supaya ora ngubungake kabeh tabel ing saben panyuwunan, kita duplikat pranala menyang panyuwunan ing tabel Pangiriman.
- Caching tabel statis karo buku referensi lan arang ngganti tabel ing memori program.
Kadhangkala owah-owahan dadi desain ulang sing nyengsemaken, nanging nyedhiyakake 5-10% saka beban sistem lan dibenerake. Sajrone wektu, knalpot dadi luwih cilik lan luwih cilik, lan desain ulang sing luwih serius dibutuhake.
Banjur kita nguripake manungsa waé menyang klompok kapindho panjalukan - klompok petani tengah. Ana akeh pitakon liyane lan kayane butuh wektu akeh kanggo nganalisa kabeh grup. Nanging, akeh pitakon dadi gampang banget kanggo dioptimalake, lan akeh masalah sing diulang kaping pirang-pirang ing macem-macem variasi. Ing ngisor iki conto sawetara optimasi khas sing ditrapake kanggo puluhan pitakon sing padha lan saben klompok pitakon sing dioptimalake mbongkar database kanthi 3-5%.
- Tinimbang mriksa anané cathetan nggunakake COUNT lan scan tabel lengkap, EXISTS wiwit digunakake
- Nyingkirake DISTINCT (ora ana resep umum, nanging kadhangkala sampeyan bisa nyingkirake kanthi nyepetake panyuwunan kaping 10-100).
Contone, tinimbang pitakon kanggo milih kabeh pembalap saka tabel kiriman gedhe (KIRIMAN)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDnggawe pitakon ing PERSON tabel sing relatif cilik
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Katon yen kita nggunakake subquery sing gegandhengan, nanging menehi kacepetan luwih saka 10 kaping.
- Ing akeh kasus, COUNT ditinggalake lan
- tinimbang
UPPER(s) LIKE JOHN%’nggunakake
s ILIKE “John%”
Saben panyuwunan tartamtu kadhangkala dicepetake kaping 3-1000. Senadyan kinerja nyengsemaken, ing kawitan kita ketoke ora ana gunane kanggo ngoptimalake pitakonan sing njupuk 10 ms kanggo ngrampungake, iku salah siji saka 3 atus pitakonan paling abot, lan njupuk munggah satus persen wektu mbukak database sakabèhé. Nanging kanthi nggunakake resep sing padha menyang klompok pitakon saka jinis sing padha, kita menang maneh sawetara persen. Supaya ora mbuwang wektu kanthi manual mriksa kabeh atusan pitakon, kita nulis sawetara skrip prasaja sing nggunakake ekspresi reguler kanggo nemokake pitakon saka jinis sing padha. Akibaté, kanthi otomatis nggoleki klompok pitakon ngidini kita nambah kinerja kanthi usaha sing sithik.
Akibaté, kita wis nggarap hardware sing padha telung taun saiki. Beban saben dina rata-rata udakara 30%, ing puncak tekan 70%. Jumlah panjalukan, uga jumlah pangguna, wis tambah kira-kira 10 kali. Lan kabeh iki thanks kanggo pancet ngawasi klompok padha panjalukan TOP-MEDIUM. Sanalika panjaluk anyar katon ing grup TOP, kita langsung nganalisa lan nyoba nyepetake. Kita mriksa klompok MEDIUM sepisan seminggu nggunakake skrip analisis pitakon. Yen kita nemokake pitakon anyar sing wis ngerti carane ngoptimalake, kita cepet ngganti. Kadhangkala kita nemokake cara optimasi anyar sing bisa ditrapake ing sawetara pitakon sekaligus.
Miturut ramalan kita, server saiki bakal tahan nambah jumlah pangguna kanthi kaping 3-5. Bener, kita duwe siji ace maneh - kita isih durung nransfer pitakon PILIH menyang pangilon, kaya sing disaranake. Nanging kita ora nindakake iki kanthi sengaja, amarga kita pengin ngilangi kemungkinan optimalisasi "pinter" sadurunge nguripake "artileri abot".
Deleng kritis ing karya sing ditindakake bisa uga menehi saran nggunakake skala vertikal. Tuku server sing luwih kuat tinimbang mbuwang wektu spesialis. Server bisa uga ora larang regane, utamane amarga kita durung kesel watesan skala vertikal. Nanging, mung jumlah panjalukan tambah 10 kaping. Sajrone pirang-pirang taun, fungsi sistem saya tambah akeh lan saiki ana luwih akeh jinis panjaluk. Fungsi sing ana, amarga caching, ditindakake ing panjalukan sing luwih sithik, lan uga ing panjaluk sing luwih efisien. Iki tegese sampeyan bisa kanthi aman Multiply dening 5 liyane kanggo njaluk koefisien akselerasi nyata. Dadi, miturut prakiraan paling konservatif, kita bisa ngomong sing akselerasi 50 kaping utawa luwih. Ngayunake server kanthi vertikal bakal biaya 50 kaping luwih. Utamane ngelingi yen optimasi wis ditindakake, kerjane kabeh wektu, lan tagihan kanggo server sing disewakake saben wulan.
Source: www.habr.com