We invite all Firebird developers and administrators to 1-day seminar in the frames of Firebird 2015 Tour. Firebird 2015 Tour is devoted to the SQL performance optimization: SQL plans, queries and database statistics, queries tuning techniques, etc.
Firebird 2015 Tour is organized by
IBSurgeon and
IBPhoenix, with support of Firebird Developers core team.
Locations and dates of Firebird 2015 Tour (see details in the appropriate sections below):
- April 24 — Seligenstadt, Germany (finished)
- May 19 — Prague, Czech Republic (finished)
- June 5 — Moscow, Russia
Firebird 2015 Tour seminar detailed schedule
Introduction
What is performance? How to measure performance? Errorneus performance measurement — by utilization (CPU/HDD). Bottlenecks.
Performance of SELECT queries
How query is executed:
- Client-server approach
- Prepare
- Plan generation
- Execution
- Fetches
- Buffering
Performance execution statistics counters
- Reads, Writes, Fetches, Time
- Database cache, Disk, CPU
- How garbage impacts on queries execution
- Cache hits (Classic vs SuperServer
Balancing between bottlenecks
- Moved to SSD — CPU increased
- Where CPU is used: fetches, BLOBS reading-allocating, etc.
Access methods/data paths
- NATURAL scan
- INDEX search
- How indices work: B-Trees, record pointers
- Indices direction
- Examples
- Forced INDEX usage
- Bit merge of indices
- Table cardinality, index selectivity
- Turning off indices in WHERE
- Index ORDER
- SORT
- Differences between index ORDER and SORT
- Turning off indices in ORDER BY, GROUP BY
- Plans in stored procedures
- Plans in VIEWs
Queries tuning
- PLAN — how to read and understand it
- Firebird optimizer tweaks
- WHERE FIELD IN (X1, X2, X3..
- LIKE, CONTAINING, STARTING WITH
- JOIN
- Implicit, explicit
- Wrong JOINS
- LEFT, RIGHT
- Self-JOINS
- UPDATE+SELECT
- MAX, MIN, COUNT, AVG, SUM
- IN
- WHERE (X and Y) or (K and N)
Tracking slow and resource-consuming queries through the MON$ and Trace API
- What are MON$ and Trace
- MON$ tables fields — what they mean
- TraceAPI parameters and output
- Tools
Performance of INSERT queries
- How INSERT works
- Database structure: pointer pages, data pages, index pages
- Inserting in dynamics — inserting 1 record leads to changes at several database pages
- Triggers fire — BEFORE/AFTER
- How BLOBs are inserted — database structure, temporary BLOBs
- Transactions start/end
- How indices, triggers and database structure affect INSERT performance
- Indices insert/update performance — VARCHAR (GUID) vs INT64
- Triggers
- Database structure
- Page size — data pages sizes, pointer pages (fixed bug in 254, 3.0)
- Cache size
- BLOBS vs long VARCHARs
- Page size limit
- Transactions commits — 1/1000/5000/100000
- Orphan pages due to rollbacks
- Forced roll back/savepoint rollback (50000 records) – NO_AUTO_UNDO
- Typical errors which lead to slow INSERTs
- INSERTS to External tables and between External to Internal.
- How GBAK does inserts
- Option –O — commit after each table
- Summary
Performance of UPDATE and DELETE queries
- How UPDATE works
- Record versions , delta versions
- Update and indices
- Record chains — depends on OIT-OST
- Important to keep all writeable transactions short
- SAVEPOINTS
- How DELETE works
- Delete stub
- Delete and indices
- How indices, triggers and database structure affect UPDATE and DELETE performance
- Updates in indices (keys)
- Update Conflicts, transactions and their resolution, DELETE, SELECT WITH LOCK
- Real-life example of UPDATEs algorithm without conflicts
- UPDATE+SELECT, MERGE
Multi-user performance optimisation
- Locks, mutexes and resource races
- Lock print analysys
- Selecting Firebird architectures and tuning their parameters for multi-user environments
Locations details for Firebird 2015 Tour
April 24, Seligenstadt, Germany
Seminar will take place in Hotel Columbus (www.hotel-columbus.de/contact.php/), April 24, 2015.
Important! All topics are presented in English!
Seminar's schedule:
- 9-30 — 10-00 Registration
- 10-00 — 17-00 Seminar (with Lunch and Coffee breaks)
Attendee's package includes seminar’s materials (PDFs), lunch and coffee breaks.
Seminar already finished.
May 19, Prague, Czech Republic
Seminar will take place in Hotel Extoll Inn (http://extolinn.cz/en/contact.htm), May 19, 2015.
Important! All topics are presented in English!
Seminar's schedule:
- 9-30 — 10-00 Registration
- 10-00 — 17-00 Seminar (with Lunch and Coffee breaks)
Attendee's package includes seminar’s materials, lunch and coffee breaks.
Seminar already finished.
June 5, Moscow, Russia
Seminar will take place in TechnoPark «Slava» (www.technopark-slava.ru/contacts ), June 5, 2015.
Important! All topics are presented in Russian!
Seminar's schedule:
- 9-30 — 10-00 Registration
- 10-00 — 17-00 Seminar (with Lunch and Coffee breaks)
Attendee's package includes seminar’s materials and coffee breaks.
Registration fee is
EUR 45 (roubles 2800).
Questions?
