45 načina da ubrzate Firebird bazu podataka

Ovde možete pronaći listu saveta za performanse Firebird baze podataka u različitim oblastima - od podešavanja hardvera/OS-a i konfiguracije Firebird-a do preporuka za optimizaciju SQL-a. Ova lista nije potpuni referentni vodič kako optimizovati Firebird, i podrazumeva da razumete osnove funkcionisanja Firebird-a, kao što su planovi izvršavanja, upravljanje transakcijama i statistike performansi upita.

Primenite ove savete sa oprezom i proverite njihov efekat pre nego što ih stavite u produkciju.

Naša kompanija (IBSurgeon) nudi sveobuhvatnu uslugu optimizacije performansi baze podataka.

1. Stavite bazu podataka na SSD

Stavite svoju bazu podataka na SSD. SSD disk pruža mnogo bolji slučajni IO od tradicionalnih diskova. Slučajni IO je kritičan za čitanje i pisanje podataka raspoređenih kroz veliki fajl baze podataka - većina operacija baze podataka zahteva intenzivan paralelni slučajni IO.

2. Koristite RAID 10

Ako koristite RAID1 ili RAID5, razmotrite RAID10 - brži je 15-25%.

3. Proverite BBU

Ako koristite RAID kontroler, proverite da li ima instaliranu i funkcionalnu jedinicu rezervnog napajanja (BBU) - neki proizvođači ne isporučuju BBU podrazumevano. Bez BBU, kontroler onemogućava keš, a RAID radi veoma sporo, čak sporije od uobičajenih SATA diskova. Obično možete proveriti status BBU u alatu za konfiguraciju RAID-a.

4. Podesite keš za upis na write-back

Ako koristite RAID kontroler sa instaliranim BBU (i serverom sa UPS-om), proverite da li je njegov keš podešen na write-back (ne write-through). «Write-back» omogućava keš za upis kontrolera.

5. Omogućite keš za čitanje

Ako koristite RAID kontroler, proverite da li ima omogućen keš za čitanje.

6. Proverite disk podsistem

Proverite svoje diskove na loše blokove i druge hardverske probleme (uključujući pregrevanje). Hardverski problemi mogu značajno smanjiti IO performanse i dovesti do korupcije baze podataka.

7. Koristite SuperClassic ili Classic u Firebird 2.5

Ako koristite Firebird 2.5 SuperServer sa mnogo konekcija, pokušajte da koristite SuperClassic ili Classic, oni se mogu bolje skalirati korišćenjem svih jezgara CPU-a.

8. Koristite SuperServer 3.0 u Firebird 3.

Ako koristite Classic ili SuperClassic u 2.5, razmotrite migraciju na Firebird 3.0 SuperServer, sada može koristiti više jezgara i kombinovati to sa prednostima deljenog keša.

9. Povećajte keš straničnih bafera

Povećajte veličinu keša straničnih bafera (parametar DefaultDBCachePages) od podrazumevanih vrednosti. Za 2.5 SuperServer preporučujemo 10000 stranica, za 3.0 SuperServer - 50000 stranica, za Classic i SuperClassic - od 256 do 2048 stranica. Međutim, ne postavljajte vrednost keša straničnih bafera previsoko - sinhronizacija keša ima svoju cenu, i ideja da stavite celu bazu podataka u RAM podešavanjem ove vrednosti neće funkcionisati. Koristite unapred optimizovane konfiguracione fajlove Firebird-a ovde: /sr/optimized-firebird-configuration/

10. Povećajte veličinu memorije za operacije sortiranja

Povećajte vrednost parametra TempCacheLimit u firebird.conf - on određuje veličinu keša privremenog prostora za sortiranje. Podrazumevane vrednosti su preniske (8Mb za Classic i 64Mb za SuperServer), koristite najmanje 64Mb za Classic i 1Gb za SuperServer i SuperClassic. Ponovo, koristite optimizovane konfiguracione fajlove iz #9.

11. Isključite Forced Writes (sa oprezom!)

Ako imate intenzivnu aktivnost umetanja ili ažuriranja (možete je proveriti pomoću HQbird MonLogger, za detalje pogledajte stranicu 60 HQbird User Guide), i ako imate instaliran UPS i replikaciju za zaštitu od hardverskih kvarova, razmotrite da podesite Forced Writes na OFF, to može povećati brzinu operacija upisa do 3 puta.

12. Povećajte broj hash slotova za Classic/SuperClassic

Povećajte vrednost parametra LockHashSlots za Classic i SuperClassic sa podrazumevanih 1009 na neki veliki prost broj (30011, na primer), to će smanjiti redove u internom mehanizmu zaključavanja.

13. Koristite CPU Affinity za Super Server 2.5

Ako koristite SuperServer 2.5, podesite parametar CPUAffinity na vrednost jednaku broju baza podataka u upotrebi: SuperServer u 2.5 može koristiti različita CPU jezgra za obradu zahteva za određene baze podataka.

14. Koristite brz disk za privremeni prostor

Podesite prvi deo parametra TempDirectory u firebird.conf na brz disk - SSD ili RAM disk. To će smanjiti vreme velikih sortiranja - na primer kada se baza podataka vraća iz rezervne kopije.

15. Čuvajte rezervne kopije baze podataka na drugom disku

Čuvajte rezervne kopije baze podataka na namenskom fizičkom disku (RAID). To će razdvojiti IO čitanja i upisa tokom pravljenja rezervne kopije, i povećati brzinu pravljenja rezervne kopije i smanjiti opterećenje glavnog diska. Posebno je važno kada se rezervne kopije prave dok korisnici rade sa bazom podataka. Više detalja o hardverskoj konfiguraciji za Firebird možete pronaći u " Firebird Hardware Guide".

16. Deaktivirajte indekse za masovne umetke

Ako umećete ili ažurirate mnogo zapisa (više od 25% tabele), deaktivirajte indekse za tabelu u koju se zapisi umeću i ponovo ih aktivirajte nakon umetanja ili ažuriranja. Operacija ponovnog izgrađivanja indeksa može biti brža od mnogih ažuriranja indeksa.

17. Koristite Global Temporary Tables za brze umetke

Da biste ubrzali umetke i ažuriranja, koristite Global Temporary Tables za masovne umetke velikih skupova zapisa, a zatim prenesite zapise u trajnu tabelu. Može biti veoma efikasno umetnuti zapise u GTT, prethodno ih obraditi i zatim premestiti u trajnu tabelu.

18. Izbegavajte nepotrebne indekse

Koristite manje indeksa za tabele sa intenzivnim umecima i ažuriranjima. Svaki indeks dodaje značajan trošak za operacije umetanja, ažuriranja, brisanja i sakupljanja smeća - može biti 3-4 dodatna čitanja i upisa stranica kada se pojedinačni zapis umeće/ažurira/briše/čisti za svaki indeks.

19. Zamenite UDF-ove pozivima ugrađenih funkcija

Zamenite UDF pozive pozivima ugrađenih funkcija. Mnoge ugrađene funkcije su dodate u nedavnim verzijama Firebird-a, koje nude funkcionalnost ranije dostupnu samo u UDF bibliotekama. Zamenite takve funkcije gde je moguće, jer ugrađene funkcije rade do 3 puta brže od UDF-ova.

20. Koristite transakcije samo za čitanje za operacije čitanja

Koristite transakcije samo za čitanje za operacije koje ne menjaju zapise (tj. SELECT-ove) sa režimom izolacije = read committed. Takve transakcije ne zadržavaju verzije zapisa od sakupljanja smeća, i mogu raditi neograničeno: ne utiču na performanse baze podataka.

21. Koristite kratke transakcije za upis i rešite se SVIH dugotrajnih

Koristite kratke transakcije za upis (za operacije INSERT/UPDATE/DELETE).

Što je transakcija za upis kraća, to bolje. Kratke transakcije zadržavaju proporcionalno manji broj verzija zapisa od sakupljanja smeća nego dugotrajne. Nažalost, čak i jedna dugotrajna transakcija (iz razvojnog alata ostavljenog otvorenim, na primer) može pokvariti dobar efekat svih ostalih kratkih transakcija za upis. Zato je potrebno pratiti dugotrajne transakcije i ispraviti odgovarajuća mesta u izvornom kodu. Koristite HQbird DataGuard alat da primate upozorenja o najstarijoj aktivnoj transakciji u Firebird bazi podataka (koje aplikacije su je pokrenule, koja IP adresa, vremenska oznaka njenog početka), i HQbird MonLogger alat da vidite kompletnu listu dugotrajnih aktivnih transakcija i njihovu IO statistiku. Takođe, ako koristite komponente/biblioteke za pristup bazi podataka koje mogu keširati skupove zapisa, koristite keširana ažuriranja.

22. Izbegavajte duge lance zapisa

Izbegavajte situacije kada jedan zapis ima mnogo verzija zapisa - Firebird radi mnogo sporije sa dugim lancima zapisa. (da vidite koliko verzija zapisa imaju neke tabele, i koji je najduži lanac zapisa možete koristiti HQbird IBAnalyst alat, karticu Tables, sortirajte po “Max Version”). Koristite kombinaciju umetaka i zakazanog brisanja starih zapisa umesto višestrukih ažuriranja istog zapisa.

23. Pravilno koristite PREPARE

Koristite pripremljene izraze za izvršavanje SQL upita gde se menjaju samo parametri - na primer, izvršite prepare pre petlje takvih upita. Prepare može trajati značajno vreme (posebno za velike tabele), a pripremanje upita samo jednom će uveliko povećati ukupne performanse.

24. Ne izvršavajte COMMIT previše često tokom masovnog insert/update operacija

U slučaju masovne INSERT/UPDATE/DELETE operacije, ne izvršavajte commit transakcije nakon svake izmene (to se može desiti ako koristite opciju auto commit u vašem drajveru baze podataka) - izvršite commit transakcija najmanje nakon 1000 operacija ili više. Svaki commit transakcije pokreće nekoliko read/write IO operacija nad bazom podataka, zbog čega česti commit-i smanjuju performanse baze podataka.

25. “Isključite” indekse ako koristite IN sa mnogo konstanti

Ako koristite konstrukciju WHERE fieldX IN (Constant1, Constant2,… ConstantN), i postoji indeks na fieldX, Firebird će koristiti indeks onoliko puta koliko ima konstanti u IN listi. Onemogućite pretragu indeksa pretvaranjem fieldX u izraz +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), ili, za stringove, koristite fieldX||''

26. Zamenite IN sa JOIN

Izbegavajte korišćenje upita sa ugnježdenim WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), to može zbuniti Firebird optimizator. Transformišite ugnježdene IN-ove u join-ove.

27. Koristite LEFT JOIN na ispravan način

Ako koristite LEFT OUTER join-ove, eksplicitno postavite tabele u join od najmanje do najveće.

28. Ograničite preuzimanje SELECT upita

Uvek pokušajte da ograničite veliki izlaz za SELECT upite sa FIRST… SKIP ili ROWS klauzulama. Ako upit nije posebno dizajniran kao izveštaj (koji zahteva da se svi zapisi štampaju/izvezu), obično je dovoljno prikazati top 10-100 zapisa. Preuzmite samo neophodne zapise.

29. Navedite manji broj kolona u SELECT sa ORDER BY/GROUP BY

Smanjite broj kolona i njihovu ukupnu širinu u upitima sa ORDER BY/GROUP BY kako u SELECT delu (tj. polja koja se prikazuju) tako i u ORDER BY klauzuli. Firebird spaja kolone iz SELECT i ORDER BY/GROUP BY klauzula i sortira ih u memoriji (ili, ako memorija nije dovoljna, na disku). Dakle, ako postoji dugi VARCHAR u SELECT-u, veličina fajlova za sortiranje može biti zaista velika (mnogo gigabajta). Smanjenje broja polja samo na ona koja moraju biti sortirana i naknadni join sa velikim poljima koja se prikazuju može uveliko (x3-x10) povećati brzinu upita sa ORDER BY/GROUP BY.

30. Koristite izvedene tabele za optimizaciju SELECT sa ORDER BY/GROUP BY

Drugi način za optimizaciju SQL upita sa sortiranjem je korišćenje izvedenih tabela da bi se izbegle nepotrebne operacije sortiranja. Umesto

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

koristite sledeću modifikaciju:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. Čuvajte kratke stringove u VARCHAR, velike u BLOB-ovima

Za čuvanje kratkih karakter podataka koristite VARCHAR-ove, za čuvanje dugih tekstova koristite BLOB-ove. Varchar-ovi su brži za male delove podataka jer se čuvaju u zapisu, a ceo zapis se čita tokom istog IO ciklusa, i ako je veličina zapisa manja od 2/3 veličine stranice baze podataka, ceo zapis se čuva na istoj stranici baze podataka. BLOB-ovi se čuvaju izvan zapisa i zahtevaju dodatni krug IO za čitanje, i pokazuju prednost pri čitanju i pisanju dugih stringova.

32. Isključite BLOB kolone iz velikih SELECT-ova

Isključite BLOB kolone iz velikih SELECT-ova. Koristite neku vrstu odloženog povezivanja sa pod-upitima da selektivno prikažete informacije iz BLOB-ova (na primer, prikažite sadržaj dokumenta).

33. Koristite BIGINT za primarne i jedinstvene ključeve

Koristite BIGINT tip za automatski inkrementirane primarne i jedinstvene ključeve i za identifikatore svih tipova. Operacije sa BIGINT-om su najbrže, a BIGINT ima dovoljan kapacitet da čuva skoro sve opsege podataka.

34. Ne koristite VARCHAR-ove za ključeve

Ne koristite VARCHAR za identifikatore osim ako je zaista neophodno - operacije sa njima su daleko manje efikasne nego sa celobrojnim kolonama. Posebno izbegavajte GUID-ove kao identifikatore - zbog slučajne distribucije GUID vrednosti INSERT/UPDATE operacije sa Primary/Unique Keys GUID-ovima mogu biti 20 puta sporije nego sa celim brojevima.

35. Ponovo izračunajte statistiku indeksa

Redovno ponovo izračunavajte statistiku indeksa. Ažurirajte statistiku indeksa za tabele sa čestim ili masovnim izmenama komandom SET STATISTICS, to omogućava Firebird optimizatoru da izabere bolje SQL planove. HQbird Firebird DataGuard može automatski izvršiti takvo ponovno izračunavanje statistike indeksa prema željenom rasporedu (obično jednom nedeljno).

36. Koristite connection pool

Ako su konekcije ka Firebird bazi podataka kratke (tipično za veb sajtove), koristite connection pool - na primer, u PHP-u koristite funkciju ibase_pconnect umesto ibase_connect

37. Koristite LINGER opciju u Firebird 3.0

Ako su konekcije ka bazi podataka kratke i koristite Firebird 3+, koristite LINGER opciju da zadržite keš aktivnim tokom određenog vremena, ona će zadržati često korišćene stranice u kešu čak i ako ne bude drugih konekcija. Na primer, ALTER DATABASE SET LINGER TO 60 će zadržati keš 60 sekundi nakon završetka poslednje konekcije.

38. Koristite HASH JOIN-ove

U Firebird 3.0, u slučaju spajanja velikih i malih tabela, HASH JOIN može biti mnogo brži od normalnog join-a koji koristi «nested loop» sa indeksom. Da bi Firebird optimizator koristio HASH join, koristite +0 u join uslovu: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Proverite rezultat optimizacije pre nego što ga stavite u produkciju!

39. Označite odgovarajuće PSQL funkcije kao DETERMINISTIC

Označite vaše PSQL funkcije (u Firebird 3+) koje nemaju parametre i vraćaju konstantne vrednosti ključnom rečju DETERMINISTIC. Determinističke funkcije se izračunavaju i keširaju u okviru trenutnog upita.

40. Koristite analitičke (window) funkcije u Firebird 3.0

Ako izvršavate SELECT sa istovremenim prikazom neke kolone i agregatne funkcije za nju, koristite window (analitičke) funkcije - to je brže od pod-upita ili 2 upita. Na primer:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

zamenite sa

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Koristite prekidač -se za gbak

Koristite prekidač -se da povećate brzinu gbak backup-a i/ili restore-a do 20%, na primer

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

Najbrži način za obradu zapisa preuzetih kursorom u PSQL je klauzula ‘where current of <>’. Brža je od ‘where rb$db_key = :v_db_key’ i mnogo brža od pretrage sa primarnim ili jedinstvenim ključem.

43. Izbegavajte česte upite ka monitoring tabelama

Ne izvršavajte upite ka Firebird monitoring tabelama (MON$) previše često - takvi upiti troše značajne resurse i mogu uveliko smanjiti performanse glavne poslovne logike. Preporučujemo izvršavanje MON$ upita ne češće od jednom u minuti. Za kontinuirano praćenje Firebird upita/transakcija/konekcija, koristite HQbird PerfMon alat koji podržava Trace API (pogledajte stranu 66 HQbird User Guide za detalje).

44. Koristite NO_AUTO_UNDO opciju za masovne insert/update operacije

Ako izvršavate mnogo DML (Update/Insert/Delete) komandi u okviru iste transakcije, Firebird spaja undo-log svake komande sa undo-log-om transakcije. Da biste ubrzali masovne DML operacije, započnite transakciju sa «NO AUTO UNDO» opcijom, kako ne biste spajali undo-log-ove svake komande sa undo-log-om transakcije.

45. Ne koristite SRP autentifikaciju u Firebird 3 ako vam nije potrebna

Ne koristite SRP autentifikaciju korisnika (Firebird 3.0+) ako vam zaista nije potrebna - konekcija sa SRP autentifikacijom se uspostavlja sporije od regularne konekcije.

Umesto rezimea

Optimizacija performansi zahteva razmatranje više faktora i može biti zaista nezgodna. Ako ste probali sve gore navedeno, razmislite o angažovanju profesionalne usluge optimizacije performansi baze podataka.

Kontaktirajte nas

Imate li pitanja? Ne ustručavajte se da nas kontaktirate putem email!