1

Topic: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Daily on peak loading I receive OutOfMemory
Process Firebird grows to 60 Gb
Help to find the reason. Even at presence  on connection, instead of on process 522*50 - 26
The maximum quantity of users 522
The virtual server (Xenon Platinum 8160 (2.1GHz) 2 processors, storage 64 Gb, OS WinSrv20
Basis
Page Size 16384
ODS Version 12.0
Oldest Tr. 6576715
Oldest Active Tr. 6576716
Oldest Snapshot Tr. 6576716
Next Tr. 6625664
Page Buffers 65536
SQL Dialect 3
Shutdown Mode Online
Sweep Interval 20000
Read Only No
Forced Writes Yes
Reserve Space No
Created At 20.05.2018 10:49
Pages 1520344
Size, KB 24325504
Size, MB 23755,38
Backup State Normal
Non-indexed Reads 110509853
Indexed Reads 291856415
Records Inserted 170457
Records Updated 234478
Records Deleted 27895
Records Backed Out 15489
Records Purged 110627
Records Expunged 116110
Page Reads 7929658
Page Writes 299662
Page Fetches 867207569
Page Marks 4309822
Configuration
#########################################
#
# Firebird version 3.0 configuration file
#
# ----------------------------
# Database Paths/Directories
#
# DatabaseAccess = None
# DatabaseAccess = Restrict C:\DataBase
# DatabaseAccess = Restrict C:\DataBase; D:\Mirror
# DatabaseAccess = Restrict/db
# DatabaseAccess = Restrict/db;/mnt/mirrordb
# DatabaseAccess = Full
#
#DatabaseAccess = Full
#RemoteAccess = true
#ExternalFileAccess = None
#UdfAccess = Restrict UDF
#TempDirectories =
#AuditTraceConfigFile =
#MaxUserTraceLogSize = 10
#DefaultDbCachePages = 2048
#FNV
DefaultDbCachePages = 64K
#DatabaseGrowthIncrement = 128M
#FileSystemCacheThreshold = 64K
#FNV
FileSystemCacheThreshold = 129K
#FileSystemCacheSize = 0
#RemoteFileOpenAbility = 0
#TempBlockSize = 1M
#FNV
TempBlockSize = 2M
TempCacheLimit = 64M
#FNV - a cache of sortings - one on process
#TempCacheLimit = 2G
#AuthServer = Srp
AuthServer = Legacy_Auth, Srp, Win_Sspi
#AuthClient = Srp, Win_Sspi, Legacy_Auth
AuthClient = Legacy_Auth, Srp, Win_Sspi
#UserManager = Srp
UserManager = Legacy_UserManager, Srp
#TracePlugin = fbtrace
#WireCryptPlugin = Arc4
#KeyHolderPlugin =
#AllowEncryptedSecurityDatabase = false
#Providers = Remote, Engine12, Loopback
#DeadlockTimeout = 10
#MaxUnflushedWrites = 100
#MaxUnflushedWriteTime = 5
#BugcheckAbort = 0
#RelaxedAliasChecking = 0
#ConnectionTimeout = 180
#WireCrypt = Enabled (for client) / Required (for server)
#FNV
WireCrypt = disabled
#WireCompression = false
#DummyPacketInterval = 0
#RemoteServiceName = gds_db
#RemoteServicePort = 3050
#FNV
#RemoteServiceName = gds_db_64
#RemoteServicePort = 3051
#RemoteAuxPort = 0
#TcpRemoteBufferSize = 8192
#TcpNoNagle = 1
#IPv6V6Only = 0
#RemoteBindAddress =
#LockMemSize = 1M
#LockAcquireSpins = 0
#LockHashSlots = 8191
#EventMemSize = 64K
#CpuAffinityMask = 0
#GCPolicy = combined
#SecurityDatabase = $ (dir_secDb)/security3.fdb
#GuardianOption = 1
#ProcessPriorityLevel = 0
#IpcName = FIREBIRD
#RemotePipeName = interbas
#Redirection = 0
ServerMode = Super
Here indexes VMMap at 380

2

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope;
Search for curves UDF/UDR
Well also it will be updated it is desirable to 3.0.3. There some memory leaks have been corrected

3

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

To look in MON$MEMORY_USAGE, on what objects and in what amount storage is arranged. Whether coincides (about) value of level DATABASE (MON$STAT_GROUP = 0) to indications of system monitors. Whether there are peaks at level  (MON$STAT_GROUP = 1), transactions (MON$STAT_GROUP = 2), etc. If are not present skills it to analyze to preempt all table in excel and to lay out here.

4

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Simonov Denis, on 3.0.3 did not pass -  load distribution between processors
At 3.0.2 more uniform

5

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

dimitr,

6

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope,

7

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

dimitr,

8

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

dimitr,

9

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

dimitr,

10

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Simonov Denis
https://cloud.mail.ru/public/6UHM/hJBFfAeam

11

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope wrote:

Simonov Denis, on 3.0.3 did not pass -  load distribution between processors
At 3.0.2 more uniform

Like yet Friday

12

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

It is a lot of connections on 100-200 mbytes everyone, and  requests there unit. Or there is any memory leak in , or it is all  under  meta data. And under  procedure/trigger. How much "branchy" requests with .. Caused PSQL-modules? Type in one  a call of ten more, in which too most.

13

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

14

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Denis wrote:

fnvhope;
Search for curves UDF/UDR
Well also it will be updated it is desirable to 3.0.3. There some memory leaks

have been corrected
Simonov Denis, to you was possible to look at tests? Tests are correct, indicative (the table contains about 55 thousand records)? Can give the reference to the information on leak?

15

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope;
http://tracker.firebirdsql.org/browse/CORE-5416

16

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope wrote:

Recovery of basis from  with a sign - only meta data gives basis in the size less than 66 MB

That is why some think, what meta data on a disk and in storage ( meta data) one and too? It at all so.
By the way here one more variant http://tracker.firebirdsql.org/browse/CORE-5611 the answer to a question why a lot of storage is eaten. It is a pity in  correction not .

17

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Denis wrote:

that is why some think, what meta data on a disk and in storage ( meta data) one and too? It at all so.

Meta data on a disk is though any starting point
http://www.sql.ru/forum/784504/uznat-te … metadannyh

18

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Could not repeat - there are no contents of files, and the description of the test was not found (I do not eliminate the immaturity)
@call run_test_w64_local2_d3.bat
@call run_test_w32_local2_d3.bat

19

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope;
Well so

dimitr wrote:

in 2.5 it is possible to try to remove the size of storage for object database, to subtract from it the storage occupied with all objects attachment this database, to subtract the size page . Residual as a first approximation will be in the size  meta data.

Where here about the size of meta data on a disk? Speech about that how approximately to calculate through MON $

20

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Denis wrote:

it is passed...
Where here about the size of meta data on a disk? Speech about that how approximately to calculate through MON $

Beforehand somehow to initiate loading in a cache 500 , 400 triggers, 150 tables, 100 domains, 600 indexes, and it it was necessary to make all before passage on 3.0.: (Alas, did not think, a minus to me.

21

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope;
To a word. Anything large in number of your procedures, triggers and tables is not present. Industrial DB normally contain thousand triggers and procedures, i.e. in 10 and more times than at you. Another matter that in the core they on the classic, but moved on the superclassic much, and a part already moved on a superserver 3.0. And while such problem (with a storage consuming) it is not watched.

22

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope;
On a fig? While there are no acknowledgement that it  meta data so much .
It is necessary to make exactly one connection and to track as on it storage grows, and to look that did when storage essentially grew.
If a lot of storage is seized at a connection, means it is necessary to look triggers ON CONNECT and at start,  transactions.
And before passage to the new version it it is necessary to do the load testing.

23

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

kdv wrote:

fnvhope;
To a word. Anything large in number of your procedures, triggers and tables is not present. Industrial DB normally contain thousand triggers and procedures, i.e. in 10 and more times than at you. Another matter that in the core they on the classic, but moved on the superclassic much, and a part already moved on a superserver 3.0. And while such problem (with a storage consuming) it is not watched.

Absence of problems also caused this subject in others at a forum. Before we worked on superserver 2.5 x32 and did not know burning, except productivity. If the size of a cache  m. Twice there is more than size on a disk it would explain storage usage. While simply added to the server of 12 more Gb of storage, reduced the size of a cache of pages from 4 Gb to 1.

24

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

Simonov Denis;
How to force the server to load  in a cache? If  recursive how many copies of objects of its representation in storage are? One.? It is so much, how many calls? It is so much how many recursion levels?

25

Re: Again about storage (Firebird 3.0.2.32703 SuperServer x64)

fnvhope wrote:

Before we worked on superserver 2.5 x32 and did not know burning, except productivity.

Still, it on kernels not .

fnvhope wrote:

If the size of a cache  m. Twice there is more than size on a disk

At  2.5 cache of meta data the general, at 3.0 - as at the classic, separate on each connection.

fnvhope wrote:

How to force the server to load  in a cache?

To make to object/objects prepare. The recursion is a performance of the code if in each recursive piece before the next nested call storage is guzzled, means will be n such pieces at n recursive calls. The in itself recursion in any way does not eat off storage.