45 начина да убрзате Firebird базу података
Овде можете пронаћи листу савета за перформансе Firebird базе података у различитим областима - од хардвера/ОС-а и подешавања Firebird конфигурације до препорука за оптимизацију SQL-а. Ова листа није комплетан референтни водич како оптимизовати Firebird, и претпоставља да разумете основе функционисања Firebird-а, као што су планови извршавања, управљање трансакцијама и статистика перформанси упита.
Молимо вас да ове савете примењујете са опрезом и проверите њихов ефекат пре него што их примените у продукцији.
Наша компанија (IBSurgeon) нуди свеобухватну услугу оптимизације перформанси базе података.
1. Поставите базу на SSD
Поставите вашу базу на SSD. SSD диск пружа много боље насумично IO од традиционалних дискова. Насумично IO је критично за читање и писање података распоређених кроз велику датотеку базе - већина операција са базом захтева интензивно паралелно насумично IO.
2. Користите RAID 10
Ако користите RAID1 или RAID5, размотрите RAID10 - он је 15-25% бржи.
3. Проверите BBU
Ако користите RAID контролер, проверите да ли има инсталирану и исправну Backup Battery Unit (BBU) - неки произвођачи не испоручују BBU подразумевано. Без BBU-а, контролер онемогућава кеш, и RAID ради веома споро, чак спорије од обичних SATA дискова. Обично можете проверити статус BBU-а у алату за конфигурацију RAID-а.
4. Подесите write cache на write-back
Ако користите RAID контролер са инсталираним BBU-ом (и сервер са UPS-ом), проверите да је његов кеш подешен на write-back (не write-through). „Write-back“ омогућава write кеш контролера.
5. Омогућите read cache
Ако користите RAID контролер, проверите да ли је омогућен read cache.
6. Проверите диск подсистем
Проверите ваше дискове на лоше блокове и друге хардверске проблеме (укључујући прегревање). Хардверски проблеми могу значајно смањити IO перформансе и довести до оштећења базе података.
7. Користите SuperClassic или Classic у Firebird 2.5
Ако користите Firebird 2.5 SuperServer са много конекција, покушајте да користите SuperClassic или Classic, они могу боље да скалирају користећи сва језгра процесора.
8. Користите SuperServer 3.0 у Firebird 3.
Ако користите Classic или SuperClassic у 2.5, размотрите миграцију на Firebird 3.0 SuperServer, сада може да користи више језгара и комбинује то са предностима дељеног кеша.
9. Повећајте page buffers cache
Повећајте величину page buffers кеша (параметар DefaultDBCachePages) од подразумеваних вредности. За 2.5 SuperServer препоручујемо 10000 страница, за 3.0 SuperServer - 50000 страница, за Classic и SuperClassic - од 256 до 2048 страница. Међутим, не постављајте вредност page buffers кеша превисоко - синхронизација кеша има своју цену, и идеја да се цела база стави у RAM подешавањем ове вредности неће функционисати. Користите пре-оптимизоване Firebird конфигурационе датотеке овде: /sr/optimized-firebird-configuration/
10. Повећајте меморију за операције сортирања
Повећајте вредност параметра TempCacheLimit у firebird.conf - он одређује величину кеша привременог простора за сортирање. Подразумеване вредности су прениске (8Mb за Classic и 64Mb за SuperServer), користите најмање 64Mb за Classic и 1Gb за SuperServer и SuperClassic. Поново, користите оптимизоване конфигурационе датотеке из #9.
11. Подесите Forced Writes на Off (са опрезом!)
Ако имате интензивну insert или update активност (можете је проверити са HQbird MonLogger, за детаље погледајте страницу 60 HQbird User Guide), и ако имате UPS и репликацију инсталиране за заштиту од хардверских кварова, размотрите подешавање Forced Writes на OFF, то може повећати брзину write операција до 3 пута.
12. Повећајте број hash слотова за Classic/SuperClassic
Повећајте вредност параметра LockHashSlots за Classic и SuperClassic са подразумеваних 1009 на неки велики прост број (30011, на пример), то ће смањити редове у интерном механизму закључавања.
13. Користите CPU Affinity за Super Server 2.5
Ако користите SuperServer 2.5, подесите параметар CPUAffinity на вредност једнаку броју база података у употреби: SuperServer у 2.5 може користити различита CPU језгра за обраду захтева за одређене базе података.
14. Користите брзи диск за привремени простор
Подесите први део параметра TempDirectory у firebird.conf на брзи диск - SSD или RAM диск. То ће смањити време великих сортирања - на пример када се база обнавља.
15. Чувајте резервне копије базе на другом диску
Чувајте резервне копије базе на наменском физичком диску (RAID). То ће раздвојити read и write IO током прављења резервне копије, и повећати брзину прављења резервне копије и смањити оптерећење главног диска. Посебно је важно када се резервне копије праве док корисници раде са базом. Више детаља о хардверској конфигурацији за Firebird можете пронаћи у „Firebird Hardware Guide".
16. Деактивирајте индексе за масовне insert-е
Ако убацујете или ажурирате много записа (више од 25% табеле), деактивирајте индексе за табелу у коју се записи убацују и реактивирајте их након insert-а или update-а. Операција поновне изградње индекса може бити бржа од многих ажурирања индекса.
17. Користите Global Temporary Tables за брзе insert-е
Да бисте убрзали insert-е и update-е, користите Global Temporary Tables за масовне insert-е великих скупова записа, а затим пребаците записе у сталну табелу. Може бити веома ефикасно убацити записе у GTT, обрадити их и затим преместити у перзистентну табелу.
18. Избегавајте непотребне индексе
Користите мање индекса за табеле са интензивним insert-има и update-има. Сваки индекс додаје значајан overhead за insert, update, delete и операције garbage collection-а - може бити 3-4 додатна читања и уписа страница када се појединачни запис убацује/ажурира/брише/чисти за сваки индекс.
19. Замените UDF-ове уграђеним функцијама
Замените UDF позиве уграђеним функцијама. Многе уграђене функције су додате у новијим верзијама Firebird-а, које нуде функционалност која је раније била доступна само у UDF библиотекама. Замените такве функције где је могуће, јер уграђене функције раде до 3 пута брже од UDF-ова.
20. Користите read-only трансакције за read операције
Користите read-only трансакције за операције које не мењају записе (тј. SELECT) са режимом изолације = read committed. Такве трансакције не задржавају верзије записа од garbage collection-а, и могу трајати неограничено: не утичу на перформансе базе.
21. Користите кратке write трансакције и ослободите се СВИХ дуготрајних
Користите кратке writeable трансакције (за операције INSERT/UPDATE/DELETE).
Што је краћа writeable трансакција, то је боље. Кратке трансакције задржавају пропорционално мањи број верзија записа од garbage collection-а него дуготрајне. Нажалост, чак и једна дуготрајна трансакција (остављена отворена из развојног алата, на пример) може покварити добар ефекат свих других кратких writeable трансакција. Зато морате пратити дуготрајне трансакције и поправити одговарајућа места у изворном коду. Користите HQbird DataGuard алат за примање упозорења о најстаријој активној трансакцији у Firebird бази (која апликација ју је покренула, која IP адреса, временска ознака њеног почетка), и HQbird MonLogger алат за преглед комплетне листе дуготрајних активних трансакција и њихове IO статистике. Такође, ако користите компоненте/библиотеке за приступ бази које могу кеширати скупове записа, користите cached updates.
22. Избегавајте дуге ланце записа
Избегавајте ситуације када један запис има много верзија - Firebird ради много спорије са дугим ланцима записа. (да видите колико верзија записа неке табеле имају, и који је најдужи ланац записа можете користити HQbird IBAnalyst алат, картица Tables, сортирање на „Max Version"). Користите комбинацију insert-а и планираног брисања старих записа уместо вишеструких update-а истог записа.
23. Правилно коришћење PREPARE
Користите припремљене изјаве за извршавање SQL упита где се мењају само параметри - на пример, припремите пре петље таквих упита. Припрема може потрајати значајно време (посебно за велике табеле), а припрема упита само једном ће значајно повећати укупне перформансе.
24. Немојте често радити COMMIT током масовних INSERT/UPDATE операција
У случају масовних INSERT/UPDATE/DELETE операција, немојте комитовати трансакцију после сваке измене (то се може десити ако користите аутоматско комитовање у вашем драјверу базе података) - комитујте трансакције најмање после 1000 операција или више. Свако комитовање трансакције покреће неколико read/write IO операција против базе података, због чега често комитовање смањује перформансе базе.
25. “Искључите” индексе ако користите IN са много константи
Ако користите конструкцију WHERE fieldX IN (Constant1, Constant2,… ConstantN), и постоји индекс на fieldX, Firebird ће користити индекс онолико пута колико има константи у IN листи. Онемогућите претрагу индекса претварањем fieldX у израз +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), или, за стрингове, користите fieldX||''
26. Замените IN са JOIN
Избегавајте коришћење упита са угњежденим WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), то може збунити Firebird оптимизатор. Трансформишите угњеждене IN у join-ове.
27. Користите LEFT JOIN на правилан начин
Ако користите LEFT OUTER join-ове, експлицитно поређајте табеле у join-у од најмање до највеће.
28. Ограничите преузимање SELECT упита
Увек покушајте да ограничите велики излаз за SELECT упите са FIRST… SKIP или ROWS клаузулама. Ако упит није специфично дизајниран као извештај (који захтева све записе за штампање/извоз), обично је довољно приказати првих 10-100 записа. Преузимајте само потребне записе.
29. Наведите мање колона у SELECT са ORDER BY/GROUP BY
Смањите број колона и њихову укупну ширину у упитима са ORDER BY/GROUP BY и у SELECT делу (тј. поља која ће се приказати) и у ORDER BY клаузули. Firebird спаја колоне из SELECT и ORDER BY/GROUP BY клаузула и сортира их у меморији (или, ако нема довољно меморије, на диску). Дакле, ако постоји дугачак VARCHAR у SELECT-у, величина датотека за сортирање може бити веома велика (више гигабајта). Смањење броја поља само на она која морају бити сортирана и каснији join са великим пољима за приказ може значајно (x3-x10) повећати брзину упита са ORDER BY/GROUP BY.
30. Користите изведене табеле за оптимизацију SELECT са ORDER BY/GROUP BY
Други начин за оптимизацију SQL упита са сортирањем је коришћење изведених табела да би се избегле непотребне операције сортирања. Уместо
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2користите следећу модификацију:
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_KEY31. Чувајте кратке стрингове у VARCHAR, велике у BLOB-овима
За чување кратких карактерних података користите VARCHAR, за чување дугих текстова користите BLOB-ове. VARCHAR-ови су бржи за мале делове података јер се чувају у запису, а цео запис се чита током истог IO циклуса, и ако је величина записа мања од 2/3 величине странице базе, цео запис се чува на истој страници базе. BLOB-ови се чувају ван записа и захтевају додатни круг IO-а за читање, а показују предност при читању и писању дугих стрингова.
32. Искључите BLOB колоне из великих SELECT-ова
Искључите BLOB колоне из великих SELECT-ова. Користите врсту касног везивања са под-упитима за селективно приказивање информација из BLOB-ова (на пример, прикажите садржај документа).
33. Користите BIGINT за примарне и јединствене кључеве
Користите BIGINT тип за аутоматски инкрементиране примарне и јединствене кључеве и за идентификаторе свих врста. Операције са BIGINT-ом су најбрже, а BIGINT има довољно капацитета за чување скоро свих опсега података.
34. Немојте користити VARCHAR за кључеве
Немојте користити VARCHAR за идентификаторе осим ако је то заиста неопходно - операције са њима су далеко мање ефикасне него са целобројним колонама. Посебно избегавајте GUID-ове као идентификаторе - због насумичне дистрибуције GUID вредности, INSERT/UPDATE операције са Primary/Unique Keys GUID-овима могу бити 20 пута спорије него са целим бројевима.
35. Поново израчунајте статистику индекса
Редовно поново израчунавајте статистику индекса. Ажурирајте статистику индекса за табеле са честим или масовним изменама командом SET STATISTICS, то омогућава Firebird оптимизатору да изабере боље SQL планове. HQbird Firebird DataGuard може аутоматски извршити такво поновно израчунавање статистике индекса према жељеном распореду (обично једном недељно).
36. Користите connection pool
Ако су конекције ка Firebird бази кратке (типично за веб сајтове), користите connection pool - на пример, у PHP-у користите функцију ibase_pconnect уместо ibase_connect
37. Користите LINGER опцију у Firebird 3.0
Ако су конекције кратке и користите Firebird 3+, користите LINGER опцију да бисте задржали кеш активним током одређеног времена, то ће задржати често коришћене странице у кешу чак и ако нема других конекција. На пример, ALTER DATABASE SET LINGER TO 60 ће задржати кеш 60 секунди након завршетка последње конекције.
38. Користите HASH JOIN-ове
У Firebird 3.0, у случају спајања великих и малих табела, HASH JOIN може бити много бржи од нормалног join-а који користи „nested loop" са индексом. Да бисте натерали Firebird оптимизатор да користи HASH join, користите +0 у услову join-а: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Проверите резултат оптимизације пре него што га ставите у продукцију!
39. Означите одговарајуће PSQL функције као DETERMINISTIC
Означите ваше PSQL функције (у Firebird 3+) које немају параметре и враћају константне вредности кључном речи DETERMINISTIC. Детерминистичке функције се израчунавају и кеширају у оквиру текућег упита.
40. Користите аналитичке (window) функције у Firebird 3.0
Ако покрећете SELECT са истовременим излазом неке колоне и агрегатне функције за њу, користите window (аналитичке) функције - то је брже од под-упита или 2 упита. На пример:
Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employeeзамените са
Select id, department, salary, salary / sum(salary) OVER () percentage from employee41. Користите прекидач -se за gbak
Користите прекидач -se да повећате брзину gbak backup-а и/или рестаурације до 20%, на пример
gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk42. WHERE CURRENT OF
Најбржи начин за обраду записа преузетих курсором у PSQL-у је клаузула ‘where current of <>’. Бржа је од ‘where rb$db_key = :v_db_key’ и много бржа од претраге са примарним или јединственим кључем.
43. Избегавајте честе упите ка мониторинг табелама
Немојте покретати упите ка Firebird мониторинг табелама (MON$) пречесто - такви упити троше значајне ресурсе и могу значајно смањити перформансе главне пословне логике. Препоручујемо покретање MON$ упита не чешће од једном у минуту. За континуирано праћење Firebird упита/трансакција/прикључака, користите HQbird PerfMon алат који подржава Trace API (погледајте страницу 66 HQbird User Guide за детаље).
44. Користите NO_AUTO_UNDO опцију за масовне insert/update операције
Ако покрећете много DML (Update/Insert/Delete) команди у оквиру исте трансакције, Firebird спаја undo-лог сваке команде са undo-логом трансакције. Да бисте убрзали масовне DML операције, започните трансакцију са опцијом «NO AUTO UNDO», како не бисте спајали undo-логове сваке команде са undo-логом трансакције.
45. Немојте користити SRP аутентификацију у Firebird 3 ако вам није потребна
Немојте користити SRP аутентификацију корисника (Firebird 3.0+) ако вам заиста није потребна - конекција са SRP аутентификацијом се успоставља спорије него регуларна конекција.
Уместо резимеа
Оптимизација перформанси захтева разматрање више фактора и може бити заиста захтевна. Ако сте испробали све горе наведено, размислите о ангажовању професионалне услуге оптимизације перформанси база података.
Контактирајте нас
Имате питања? Не оклевајте да нас контактирате путем имејла!