MEMORY USAGE IN
FIREBIRD
Alexey Kovyazin
IBSurgeon
www.ib-aid.com
2
• Replication, Recovery and
Optimization for Firebird
and InterBase since 2002
• Platinum Sponsor of
Firebird Foundation
• Based in Moscow, Russia
www.ibase.ru
www.ib-aid.com
20/05/2018 www.ib-aid.com
3
Agenda
Part 1: Understanding memory usage in Firebird
• Memory types in Windows and Linux
• How Firebird uses memory
• File cache and Firebird
Part 2: Tuning memory usage in Firebird database
• Firebird.conf parameters for memory tuning
• Optimal firebird parameters
20/05/2018 www.ib-aid.com
4
PART 1: UNDERSTANDING
MEMORY USAGE IN
FIREBIRD
20/05/2018 www.ib-aid.com
5
3 main types of memory in OS
Core
Memory, occupied
Paged memory by processes
File cache Frequently used
files
20/05/2018 www.ib-aid.com
6
For Firebird
Core
firebird.exe,
fbserver.exe,
fb_inet_server.exe,
Paged memory /opt/bin/firebird,
/opt/bin/fb_inet_server
File cache Database.fdb
20/05/2018 www.ib-aid.com
7
Competing for memory (Windows)
Core
Paged memory
Processes
Swap
File cache
Database file
8
RAM on Windows
• By default, Windows memory manager has
the following %% for memory types
• 50% paged memory
Use RAMMap to see the
• 41% file cache real memory
• 9% kernel consumption
• It can be tuned in Windows settings/registry
20/05/2018 www.ib-aid.com
9
RAMMap
20/05/2018 www.ib-aid.com
10
RAM on Linux
• Core – 10%
• Processes - on demand
• File cache – takes all available space
20/05/2018 www.ib-aid.com
11
RAM on Linux: monitoring
• smem
• nmon
• Cacti
20/05/2018 www.ib-aid.com
12
Memory consumption Parts
Firebird.exe
of
Paged memory Firebird.exe Firebird
others.exe process
File cache
20/05/2018 www.ib-aid.com
13
Memory usage inside Firebird process
1. Page cache (buffers) We can tune
2. Memory for sorting sizes of these
parts
3. Lock table
4. Metadata cache
• Depends on number of tables, stored procedures, triggers, etc
5. Undo лог
• Depends on the number of changes in the frames of transactions
20/05/2018 www.ib-aid.com
14
Where is the file cache?
20/05/2018 www.ib-aid.com
15
RAMMap shows actual memory usage
20/05/2018 www.ib-aid.com
16
Conclusions
• OS has specific default distribution between memory
types
• The goal of memory management is to fit into frames of
OS limits and memory use patterns
• In the second part we will consider how to tune Firebird
memory usage
20/05/2018 www.ib-aid.com
17
PART 2: TUNING MEMORY
USAGE IN FIREBIRD
DATABASE
Alexey Kovyazin
www.ib-aid.com
20/05/2018 www.ib-aid.com
18
Memory consumption Parts
Firebird.exe
of
Paged memory Firebird.exe Firebird
others.exe process
File cache
20/05/2018 www.ib-aid.com
19
Memory usage inside Firebird process
1. Page cache (buffers) We can tune
2. Memory for sorting sizes of these
parts
3. Lock table
4. Metadata cache
• Depends on number of tables, stored procedures, triggers, etc
5. Undo log
• Depends on the number of changes in the frames of transactions
20/05/2018 www.ib-aid.com
20
Page cache (buffers)
• Contains frequently changed database
pages
• Page cache depends on Firebird
architecture
• Classic and SuperClassic – for each connection
• SuperServer – for all connections
20/05/2018 www.ib-aid.com
21
How to calculate page cache size
• Page cache size
• Page Buffers X Database page size
• Default values
• SuperServer 2.5: 2048 pages x 4096 byte = 8 Mb
• Classic/SuperClassic 2.5: 75 x 4096 = 0,29 Mb
• Default values are low, they must be increased.
20/05/2018 www.ib-aid.com
22
How to change page cache
• Parameter DefaultDBCachePages in firebird.conf
• gfix –buffers NNN
• Connection parameter
20/05/2018 www.ib-aid.com
23
Recommended size of page cache for
Firebird 2.5
• Classic/SuperClassic – from 512 to 2048 pages, rarely
4096
• Example for Classic: 100 users*2048*16k~=3,2Gb
• SuperServer – the empirical limit is 10000 pages
20/05/2018 www.ib-aid.com
24
Recommended size of page cache for
Firebird 3.0
• Classic/SuperClassic – from 512 to 2048 pages, rarely
4096
• SuperServer – theoreticaly all memory can be used for
the page cache – but not recommended!
• For Firebird SuperServer 3.0 empirical value is ~40% of
RAM
• For dedicated Firebird server
20/05/2018 www.ib-aid.com
25
Memory for sorting
• Memory for sorting
• firebird.conf parameter TempCacheLimit
• For Classic – for each connection
• For SuperClassic/SuperServer – for all connections
20/05/2018 www.ib-aid.com
26
Memory for sorting: values
• TempCacheLimit - specified in bytes
• By default
• Classic - 8Mb
• SuperServer/SuperClassic – 64Mb
• Default values are low and must be increased
• Memory is allocated on demand, by default with 1Mb
step.
• It is managed by TempBlockSize parameter
20/05/2018 www.ib-aid.com
27
Temp files for sorting
• fb_sort_xxx files are created
• in %Temp% folder
• In /tmp/firebird
• or folders specified in TempDirectories parameter
• You can monitor number and size of temporary files
manually, or automatically with HQbird, and increase
values accordingly
20/05/2018 www.ib-aid.com
28
Recommendations for TempCacheLimit
• Increase default value
• Monitor temp sort files to see actual sizes of sorted arrays
• Remember about OS memory manager – total size of
paged memory should be less than 50% of Windows
20/05/2018 www.ib-aid.com
29
Lock table
• Lock table size
• Initial parameter is set in LockMemSize
• Default – 1Мб
• Automatically increased
• We recommend to set lock table size to the value
you can see after the day of work
20/05/2018 www.ib-aid.com
30
Example: lock table for database with
1036 users is 68Mb
LOCK_HEADER BLOCK
Version: 145, Active owner: 0, Length: 82048576, Used: 68650352
Flags: 0x0001
Enqs: 92366796464, Converts: 65879210, Rejects: 35657793, Blocks:
1021497258
Deadlock scans: 11, Deadlocks: 0, Scan interval: 10
Acquires: 96127236234, Acquire blocks: 29354592388, Spin count: 0
Mutex wait: 30.5%
Hash slots: 49009, Hash lengths (min/avg/max): 0/ 3/ 14
Remove node: 0, Insert queue: 0, Insert prior: 0
Owners (1036): forward: 6621216, backward: 49994472
Free owners (66): forward: 27637856, backward: 58344720
Free locks (18591): forward: 63254664, backward: 57867608
Free requests (188245): forward: 22698880, backward: 23113112
Lock Ordering: Enabled
31
Memory parameters in firebird.conf
• DefaultDBCachePages – number of page buffers
• TempCacheLimit – memory size for sorting
• TempBlockSize – size of memory block for TempCacheLimit
• LockMemSize – initial size of lock table
• FileSystemCacheThreshold – file cache threshold
• FileSystemCacheSize – for Windows, limit of of the file
cache
20/05/2018 www.ib-aid.com
32
Condition to enable file cache for Firebird
• Page cache < FileSystemCacheThreshold
• With default parameters file cache is always on, can be
disabled if you increase page buffers without increasing
FileSystemCacheThreshold
• File Cache is critically important for Classic and
SuperClassic!
20/05/2018 www.ib-aid.com
33
When can disable File Cache?
• You can try to switch the file
cache off for SuperServer
only in the following cases:
• Read Only database
• Database which fits into page
buffers with low % of writes
• For databases on SSD with
very low % of writes
• Test it!
20/05/2018 www.ib-aid.com
34
General recommendations for RAM on
Windows
• Total size of all processes (for Firebird page
buffers+TempCacheLimit, etc) < Paged Memory
(50% of RAM by default)
• File Cache must be enabled
• For Classic and SuperClassic without exceptions
• For SuperServer for large databases (more than RAM size)
20/05/2018 www.ib-aid.com
35
General recommendations for Linux
• Linux usually does not have strict allocation about paged
memory, and allows file cache to grow high
• Recommended % for paged memory limit is up to 50%,
but usually is less
• File Cache must be enabled in all cases
20/05/2018 www.ib-aid.com
36
Optimized Firebird configuration files
• https://ib-aid.com/en/optimized-firebird-configuration/
20/05/2018 www.ib-aid.com
37
Thank you!
Contact us:
• https://www.ib-aid.com
• support@ib-aid.com
20/05/2018 www.ib-aid.com
38
Swap file tuning
• In case of balanced settings for paged
memory and file cache, and RAM > 32Gb,
swap file can be limited to 16Gb.
• Put swap file on the separate SSD – not on
SSD with database!
• Keep an eye on the SSD health!
20/05/2018 www.ib-aid.com
