Ngaoptimalkeun pamundut database nganggo conto jasa B2B pikeun tukang

Kumaha tumuwuh 10 kali jumlah queries kana database tanpa pindah ka server leuwih produktif tur ngajaga fungsionalitas sistem? Kuring bakal ngabejaan ka maneh kumaha urang diurus turunna dina kinerja database urang, kumaha urang dioptimalkeun queries SQL pikeun ngalayanan saloba pamaké sabisa na teu nambahan biaya sumberdaya komputasi.

Kuring ngadamel jasa pikeun ngatur prosés bisnis di perusahaan konstruksi. Sakitar 3 rébu perusahaan damel sareng kami. Langkung ti 10 rébu jalma damel sareng sistem kami unggal dinten salami 4-10 jam. Éta ngarengsekeun sagala rupa masalah perencanaan, béwara, peringatan, validasi ... Kami nganggo PostgreSQL 9.6. Kami gaduh sakitar 300 tabel dina pangkalan data sareng dugi ka 200 juta patarosan (10 rébu anu béda) nampi unggal dinten. Rata-rata urang gaduh 3-4 sarébu requests per detik, dina moments paling aktif leuwih ti 10 sarébu requests per detik. Kalolobaan patarosan anu OLAP. Aya langkung seueur tambihan, modifikasi sareng ngahapus, hartosna beban OLTP relatif hampang. Kuring nyayogikeun sadaya nomer ieu supados anjeun tiasa ngira-ngira skala proyék kami sareng ngartos kumaha mangpaat pangalaman kami pikeun anjeun.

Gambar hiji. Liris

Nalika urang ngamimitian ngembangkeun, urang teu bener pikir ngeunaan jenis beban bakal tumiba dina database jeung naon anu bakal urang pigawé lamun server dieureunkeun narik. Nalika ngarancang pangkalan data, kami nuturkeun saran umum sareng nyobian henteu némbak diri dina suku, tapi ngalangkungan saran umum sapertos "ulah nganggo pola éta. Nilai Atribut Éntitas urang teu lebet. Urang dirancang dumasar kana prinsip normalisasi, ngahindarkeun redundansi data jeung teu paduli ngeunaan nyepetkeun queries tangtu. Pas pangguna munggaran sumping, kami mendakan masalah kinerja. Sakumaha biasa, kami henteu siap pikeun ieu. Masalah kahiji tétéla basajan. Sakumaha aturan, sagalana geus direngsekeun ku nambahkeun hiji indéks anyar. Tapi aya waktos nalika patches basajan lirén damel. Nyadar yén kami kakurangan pangalaman sareng janten beuki hese pikeun urang ngartos naon anu nyababkeun masalah, kami nyéwa spesialis anu ngabantosan kami nyetél server anu leres, nyambungkeun ngawaskeun, sareng nunjukkeun ka kami dimana milarian. statistik.

Gambar dua. Statistik

Janten urang gaduh sakitar 10 rébu patarosan anu béda-béda anu dieksekusi dina database kami per dinten. Tina 10 rébu ieu, aya monster anu dieksekusi 2-3 juta kali kalayan waktos palaksanaan rata-rata 0.1-0.3 mdet, sareng aya patarosan kalayan waktos palaksanaan rata-rata 30 detik anu disebut 100 kali sadinten.

Teu mungkin pikeun ngaoptimalkeun sadaya 10 rébu patarosan, ku kituna kami mutuskeun pikeun terang dimana arahkeun usaha kami pikeun ningkatkeun kinerja pangkalan data kalayan leres. Saatos sababaraha iterasi, urang mimiti ngabagi pamundut kana jinis.

requests TOP

Ieu mangrupikeun patarosan anu paling beurat anu paling seueur waktos (total waktos). Ieu mangrupikeun patarosan anu sering disebat atanapi patarosan anu nyandak waktos anu lami pisan pikeun dieksekusi (pamundut anu panjang sareng sering dioptimalkeun dina iterations mimiti gelut pikeun kagancangan). Hasilna, server spends paling waktos on palaksanaan maranéhanana. Leuwih ti éta, hal anu penting pikeun misahkeun requests luhur ku total waktu palaksanaan sarta misah ku waktu IO. Métode pikeun ngaoptimalkeun pamundut sapertos rada béda.

Prakték biasa sadaya perusahaan nyaéta damel sareng pamundut TOP. Aya sababaraha di antarana; optimizing malah hiji query bisa ngosongkeun nepi 5-10% sumberdaya. Sanajan kitu, salaku proyék matures, optimizing queries TOP janten hiji tugas beuki non-trivial. Sadaya metodeu saderhana parantos didamel, sareng pamundut anu paling "beurat" nyandak "ngan" 3-5% sumberdaya. Lamun queries TOP dina total nyokot kirang ti 30-40% tina waktu, teras paling dipikaresep anjeun geus usaha sangkan aranjeunna dianggo gancang sarta éta waktu pikeun ngaléngkah ka optimizing queries ti grup salajengna.
Tetep ngajawab sual sabaraha patarosan luhur kudu kaasup dina grup ieu. Kuring biasana nyandak sahanteuna 10, tapi teu leuwih ti 20. Kuring nyobian pikeun mastikeun yén waktu kahiji jeung panungtung dina grup TOP béda ku teu leuwih ti 10 kali. Nyaéta, upami waktos palaksanaan pamundut turun drastis tina tempat ka-1 dugi ka ka-10, teras kuring nyandak TOP-10, upami turunna langkung bertahap, maka kuring ningkatkeun ukuran grup ka 15 atanapi 20.

Patani tengah

Ieu kabeh requests nu datang langsung saatos TOP, iwal panungtungan 5-10%. Biasana, dina optimizing queries ieu perenahna kasempetan pikeun greatly ningkatkeun kinerja server. Paménta ieu tiasa beuratna dugi ka 80%. Tapi sanajan dibagikeun maranéhanana geus ngaleuwihan 50%, éta waktu kasampak di aranjeunna leuwih taliti.

Buntut

Sakumaha didadarkeun, queries ieu datangna dina tungtungna sarta nyandak 5-10% waktu. Anjeun tiasa hilap ngeunaan éta ngan upami anjeun henteu nganggo alat analisa pamundut otomatis, teras ngaoptimalkeunana ogé tiasa murah.

Kumaha meunteun unggal kelompok?

Kuring make query SQL nu mantuan nyieun hiji assessment misalna pikeun PostgreSQL (Kuring yakin yén query sarupa bisa ditulis pikeun loba DBMSs séjén)

Query SQL pikeun ngira-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

Hasil tina query nyaéta tilu kolom, nu masing-masing ngandung persentase waktu nu diperlukeun pikeun ngolah queries ti grup ieu. Jero pamundut aya dua angka (bisi kuring éta 20 jeung 800) nu misahkeun requests ti hiji grup ti nu sejen.

Ieu kumaha biasa tina requests kasarna ngabandingkeun dina waktu optimasi karya dimimitian jeung ayeuna.

Diagram nunjukeun yen pangsa tina requests TOP geus sharply turun, tapi "patani tengah" geus ngaronjat.
Dina awalna, requests TOP kaasup blunders blatant. Kana waktu, kasakit budak leutik ngiles, pangsa tina requests TOP turun, sarta beuki loba usaha kudu dilakukeun pikeun nyepetkeun requests hésé.

Pikeun meunangkeun téks requests kami nganggo pamundut handap

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

Ieu daptar téknik anu paling sering dianggo anu ngabantosan urang nyepetkeun patarosan TOP:

• Ngadesain ulang sistem, contona, ngerjakeun deui logika bewara nganggo calo pesen tinimbang patarosan périodik kana pangkalan data.
• Nambahkeun atawa ngarobah indéks
• Nulis deui patarosan ORM kana SQL murni
• Rewriting puguh data loading logika
• Caching ngaliwatan denormalisasi data. Contona, urang boga sambungan tabel Pangiriman -> Invoice -> Request -> Aplikasi. Hartina, unggal pangiriman pakait sareng hiji aplikasi ngaliwatan tabel séjén. Pikeun henteu ngaitkeun sadaya tabel dina unggal pamundut, kami duplikat tautan kana pamundut dina tabel Pangiriman.
• Caching tabel statik sareng buku rujukan sareng jarang ngarobah tabel dina mémori program.

Kadang-kadang parobihan éta ngagaduhan desain ulang anu pikaresepeun, tapi aranjeunna nyayogikeun 5-10% tina beban sistem sareng diyakinkeun. Kana waktu, knalpot jadi leuwih leutik sarta leuwih leutik, sarta redesign beuki serius diperlukeun.

Teras we ngalihkeun perhatian urang ka grup kadua requests - grup tani tengah. Aya seueur deui patarosan di jerona sareng sigana bakal peryogi seueur waktos pikeun nganalisis sadayana grup. Sanajan kitu, paling queries tétéla basajan pisan pikeun ngaoptimalkeun, sarta loba masalah anu diulang puluhan kali dina variasi béda. Di handap ieu conto sababaraha optimizations has nu urang dilarapkeun ka puluhan queries sarupa jeung unggal grup queries dioptimalkeun unloaded database ku 3-5%.

• Gantina mariksa ayana rékaman ngagunakeun COUNT sarta scan tabel pinuh, EXISTS mimiti dipaké
• Ngaleungitkeun DISTINCT (teu aya resep umum, tapi sakapeung anjeun tiasa gampang ngaleungitkeun ku ngagancangkeun pamundut ku 10-100 kali).

  Contona, tinimbang query pikeun milih sadaya supir ti tabel badag pangiriman (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  dijieun query dina tabel PERSON relatif leutik

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Ieu bakal sigana nu urang dipaké hiji subquery correlated, tapi méré speedup leuwih ti 10 kali.

• Dina loba kasus, COUNT ieu ditinggalkeun sakabehna na
  diganti ku itungan nilai perkiraan
• tibatan

  UPPER(s) LIKE JOHN%’ 
  

  pamakean

  s ILIKE “John%”
  

Unggal pamundut husus ieu kadang speeded up ku 3-1000 kali. Sanajan kinerja impressive, mimitina eta seemed kami yén euweuh titik di optimizing query nu nyokot 10 mdet pikeun ngalengkepan, mangrupa salah sahiji ratus queries heaviest 3rd, sarta nyokot up hundredths of a persen tina sakabéh waktu beban database. Tapi ku nerapkeun resep sarua ka grup queries tina tipe sarua, urang meunang deui sababaraha persen. Pikeun henteu miceunan waktos sacara manual marios sadaya ratusan patarosan, kami nyerat sababaraha skrip saderhana anu ngagunakeun ekspresi biasa pikeun milarian patarosan anu sami. Hasilna, sacara otomatis milarian grup patarosan ngamungkinkeun kami pikeun ningkatkeun kinerja kami kalayan usaha anu sederhana.

Hasilna, urang parantos damel dina hardware anu sami salami tilu taun ayeuna. Beban poean rata-rata sakitar 30%, dina puncakna ngahontal 70%. Jumlah pamundut, kitu ogé jumlah pamaké, geus ngaronjat kurang leuwih 10 kali. Sareng sadaya ieu berkat ngawaskeun konstan tina grup anu sami tina pamundut TOP-MEDIUM. Pas pamundut anyar muncul dina grup TOP, urang langsung nganalisis eta jeung nyoba ngagancangkeun eta. Urang marios grup MEDIUM saminggu sakali nganggo skrip analisis query. Lamun urang datang di sakuliah queries anyar nu urang geus nyaho kumaha carana ngaoptimalkeun, urang gancang ngarobah éta. Kadang-kadang urang manggihan métode optimasi anyar nu bisa dilarapkeun ka sababaraha queries sakaligus.

Numutkeun ramalan kami, server ayeuna bakal tahan kanaékan jumlah pamaké ku sejen 3-5 kali. Leres, kami gaduh hiji deui ace dina leungeun baju urang - kami masih henteu acan ngalihkeun patarosan PILIH kana eunteung, sakumaha anu disarankeun. Tapi kami henteu ngalakukeun ieu sacara sadar, sabab kami hoyong ngabéréskeun heula kamungkinan optimasi "pinter" sateuacan ngaktipkeun "artileri beurat".
Tinjauan kritis kana padamelan anu dilakukeun tiasa nyarankeun ngagunakeun skala vertikal. Mésér server anu langkung kuat tibatan ngabuang waktos spesialis. Pangladén panginten henteu langkung seueur, khususna saprak urang henteu acan béak wates skala vertikal. Sanajan kitu, ngan jumlah requests ngaronjat 10 kali. Salila sababaraha taun, pungsionalitas sistem parantos ningkat sareng ayeuna langkung seueur jinis pamundut. Hatur nuhun kana cache, pungsionalitas anu aya dilaksanakeun dina pangsaeutikna requests, sarta requests leuwih efisien. Ieu ngandung harti yén anjeun aman tiasa kalikeun ku 5 séjén pikeun meunangkeun koefisien akselerasi nyata. Janten, dumasar kana perkiraan anu paling konservatif, urang tiasa nyarios yén akselerasi éta 50 kali atanapi langkung. Vertikal ayun server bakal ngarugikeun 50 kali leuwih. Utamana tempo yén sakali optimasi dilumangsungkeun gawéna sepanjang waktos, sarta tagihan pikeun server disewa datang unggal bulan.

sumber: www.habr.com