Life with big Firebird databases

Alexey Kovyazinwww.IBSurgeon.com

2

• Tools and consulting• Platinum Sponsor of Firebird Project• Founded in 2002: 12 years of

Firebird and InterBase recoveries and consulting

• Based in Moscow, Russia• Firebase is our Brazil partner (since

2006)

3Big databases

Series of performance tests with databases 9Gb... 30Gb... 1.7Tb

http://ib-aid.com/en/articles/firebird-performance-degradation-tests-myths-and-truth/

4Non-optimized performance results

5

Optimized test results

6

What we did

• SuperServer → Classic or SuperClassic• Tuned Firebird config parameters

Optimized config are available here:http://ib-aid.com/en/optimized-firebird-configuration/

7The difference between big and small databases

• Small– Optimize your SQLs– In case of any trouble

do backup/restore (gbak)

• Big– Where is the problem?– Why it happens?– Backup/restore takes 1

days... Will it solve the problem or it will be waste of time?

8

Maintenance tasks

• Backups• Performance monitoring

– Transactions– SQLs and plans– Database structure

9

Backups

• Usual backup: new backup rewrites old backup

Firebird database

New backup

Old backup

10

Wrong!• If backup fails, there will be no good old

backup, no new backup, and corrupted database

Firebird database

New backup

Old backup

11Correct schema

Firebird database

New backup

Old backupFinished backup

Database space for backups:(Number_of_stored_backups+1)*Size

12

Gbak vs Nbackup

• NBACKUP+ fast- does not check data- not reliable till 2.5.2- delta problems

• Gbak+ checks data- slow (and speed degrades)- test restore needed

13

Big databases backup schema

Production server Backup server

Nbackup gbak

Requirements● delta monitoring● health monitoring

Requirements:● gbak backup ● test restore

14

Big databases backup schema

Production server Backup server

Nbackup gbak

FBDataGuard 1 FBDataGuard 2

15

FBDataGuard

• Backups (in correct way)• Health checks of the database• Performance parameters monitoring

– Transactions– Users, Indices, database statistics

• Corruption monitoring• Email alerts

16

Health monitoring

• Critical metadata check and backup– Can be used for recovery with FirstAID

• Database limitations checks– Transactions limits, format limits, space limits

• Errors– Firebird.log

17

Indices maintenance

• Indices health– Idea of statistics – Recalculation of indices statistics

• Manual• Automatic

18

Demo

19

Performance monitoring

20

Tasks and Tools

• SQL queries and plans: FBScanner and FBPerfMon

• Transactions: FBDataGuard (alerts, database statistics) and IBTM (visualization)

• Database structure analysys: IBAnalyst

21Recommended setup for

monitoring

Firebird Production server

Users

FBScanner remote server

Administrator has:1) FBScanner Analyzer2) MON$Logger3) TraceAPI4) IBTM5) IBAnalyst

MON$

TraceAPI

FBDataGuard& Trace API

22Examples of problem resolution

Long running active transaction — prevent performance problem1.Administrator got email from FBDataGuard about

transaction gap2.IBTM shows long running active transaction3.MON$ logger identified source of transaction, and

admin disconnected client

23

Transactions monitoring

• Transaction markers (NEXT, OIT, OST, OAT) dynamics is good indicator of problems– Long running active transactions (stuck OAT)– Forced Rollback (stuck OIT)– Sweep and autosweep success

24

25MON$

26Examples of problem resolutionBad performance — fix performance problem– User reported problem with performance in application A– Quick check with MON$logger does not show the

problem– Admin analysed logs from FBScanner (permanent

monitoring) and found bad query, reported to developer, with SQL plan and fresh gstat statistics from FBDataGuard

– Developer checked gstat and plan, found that new index is needed

– Admin applied index, performance is good again

27

SQL Queries and Plans

• For all Firebird versions — FBScanner– Works as a proxy– Connection string changes required– Can be installed on remote server

• For Firebird 2.5 — FBPerfMon– Mon$ support– TraceAPI support

28Trace

29

FBScanner Log

30Database structure

31

Conclusion

• To sleep well and live long you need:– Relialble backups– Continious monitoring and alerts– Quick identification of problem– Full details how to fix it

32

Backups

Health monitoring

Indices maintenance

Transactions dynamics

SQL Queries & plans

Database structure analysys

Database

Typical tasks

33

FBDataGuard

IBTM

FBScanner & FBPerfMon

IBAnalyst

34

Only at FDD 2014

IBSurgeon Optimization Pack 5-in-1 (FBDataGuard+FBScanner+FBPerfMon +IBTM +IBAnalyst)

• EUR 299 R$400 per server• EUR 1290 R$850 Unlimited Subscription 1 year• EUR 349 R$400 FirstAID 50 database

35

• Thank you!• Questions?

Web: www.ib-aid.com www.ibsurgeon.comEmail: support@ib-aid.com