[firebird-support] FB3 Memory usage

[Si Carter [e-mail] [firebird-support]]

Hi,

I recently installed FB v3.0.1.32609 on Windows server 2008.

This is a dual installation as I still have 2.5.3 installed.

I noticed that my fb3 service is using in excess of 4gb memory, this in
itself is not a problem as the server has plenty of ram. It's probably my
lack of understanding but it appears I have memory allocated to what
appears to be orphaned connections.

Given this sql

SELECT ma.MON$ATTACHMENT_NAME, ma.MON$USER, a.MON$STAT_ID,
a.MON$STAT_GROUP, a.MON$MEMORY_USED, a.MON$MEMORY_ALLOCATED,
a.MON$MAX_MEMORY_USED, a.MON$MAX_MEMORY_ALLOCATED
FROM MON$MEMORY_USAGE a
LEFT JOIN MON$ATTACHMENTS ma ON (ma.MON$STAT_ID = a.MON$STAT_ID)
ORDER BY ma.MON$ATTACHMENT_NAME

it produces the following results, STAT_ID 1 is the one I am more concerned
about at this point.

[null] [null] 138 2 3248 0 4272 0
[null] [null] 115 2 44048 0 44048 0
[null] [null] 218 3 22104 8112 87984 73688
[null] [null] 116 3 22384 8112 91760 73688
[null] [null] 95 3 21192 24336 2121800 2121568
[null] [null] 1 0 58128176 81557240 1685336336 2771125200

Previously it was using in excess of 5gb of ram, a restart bought it back
to normal, but it soon escalated to 3gb (within seconds) and now to 4gb ram.

The database has only 4 normal connections, plus Cache Writer and Garbage
Collector, so it's not under unusual usage at the mo.

Other stats:

Oldest transaction 735636
Oldest active 735637
Oldest snapshot 735120
Next transaction 735644

Sorry if this is a duplicate question, have tried searching but not had
many answers.

regards

Si

[Dmitry Yemanov [e-mail] [firebird-support]]

Post by Si Carter ***@gmail.com [firebird-support]
I recently installed FB v3.0.1.32609 on Windows server 2008.
This is a dual installation as I still have 2.5.3 installed.
I noticed that my fb3 service is using in excess of 4gb memory

How did you get that number?

Post by Si Carter ***@gmail.com [firebird-support]
Given this sql
SELECT ma.MON$ATTACHMENT_NAME, ma.MON$USER, a.MON$STAT_ID,
a.MON$STAT_GROUP, a.MON$MEMORY_USED, a.MON$MEMORY_ALLOCATED,
a.MON$MAX_MEMORY_USED, a.MON$MAX_MEMORY_ALLOCATED
FROM MON$MEMORY_USAGE a
LEFT JOIN MON$ATTACHMENTS ma ON (ma.MON$STAT_ID = a.MON$STAT_ID)
ORDER BY ma.MON$ATTACHMENT_NAME
it produces the following results, STAT_ID 1 is the one I am more
concerned about at this point.
[null] [null] 138 2 3248 0 4272 0
[null] [null] 115 2 44048 0 440480
[null] [null] 218 3 22104 8112 87984 73688
[null] [null] 116 3 22384 8112 91760 73688
[null] [null] 95 3 21192 24336 2121800 2121568
[null] [null] 1 0 58128176 81557240 1685336336 2771125200

I see that the database utilizes only 81MB of memory (with a 2.8GB peak
somewhen in the past).

Post by Si Carter ***@gmail.com [firebird-support]
Previously it was using in excess of 5gb of ram, a restart bought it
back to normal, but it soon escalated to 3gb (within seconds) and now to
4gb ram.

I don't see 4GB here, sorry.

Post by Si Carter ***@gmail.com [firebird-support]
The database has only 4 normal connections, plus Cache Writer and
Garbage Collector, so it's not under unusual usage at the mo.

Number of connections does not directly affect memory usage.


Dmitry

[Si Carter [e-mail] [firebird-support]]

Fair point, I only took a snapshot of the "orphaned" stats from one of the
databases, if it happens again, will gather all data for all databases.

But as far as your concerned it's normal to have orphaned records within
there?

regards

Si

Post by Dmitry Yemanov ***@users.sourceforge.net [firebird-support]

Post by Si Carter ***@gmail.com [firebird-support]
I recently installed FB v3.0.1.32609 on Windows server 2008.
This is a dual installation as I still have 2.5.3 installed.
I noticed that my fb3 service is using in excess of 4gb memory

How did you get that number?

Post by Si Carter ***@gmail.com [firebird-support]
Given this sql
SELECT ma.MON$ATTACHMENT_NAME, ma.MON$USER, a.MON$STAT_ID,
a.MON$STAT_GROUP, a.MON$MEMORY_USED, a.MON$MEMORY_ALLOCATED,
a.MON$MAX_MEMORY_USED, a.MON$MAX_MEMORY_ALLOCATED
FROM MON$MEMORY_USAGE a
LEFT JOIN MON$ATTACHMENTS ma ON (ma.MON$STAT_ID = a.MON$STAT_ID)
ORDER BY ma.MON$ATTACHMENT_NAME
it produces the following results, STAT_ID 1 is the one I am more
concerned about at this point.
[null] [null] 138 2 3248 0 4272 0
[null] [null] 115 2 44048 0 440480
[null] [null] 218 3 22104 8112 87984 73688
[null] [null] 116 3 22384 8112 91760 73688
[null] [null] 95 3 21192 24336 2121800 2121568
[null] [null] 1 0 58128176 81557240 1685336336 2771125200

I see that the database utilizes only 81MB of memory (with a 2.8GB peak
somewhen in the past).

Post by Si Carter ***@gmail.com [firebird-support]
Previously it was using in excess of 5gb of ram, a restart bought it
back to normal, but it soon escalated to 3gb (within seconds) and now to
4gb ram.

I don't see 4GB here, sorry.

Post by Si Carter ***@gmail.com [firebird-support]
The database has only 4 normal connections, plus Cache Writer and
Garbage Collector, so it's not under unusual usage at the mo.

Number of connections does not directly affect memory usage.
Dmitry

[Si Carter [e-mail] [firebird-support]]

The memory usage crept up to just under 3gb again, and I took a snapshot of
the data using the previous sql, from all 4 databases. Between the 4
databases there are over 1500 orphaned records, and between the 4 databases
there are 30 identifiable connections.

I have placed the raw data in a csv file (http://tinyurl.com/zkqf9bo) as
again, it's odd the mem usage keeps creeping up and there are so many
orphaned records. However if orphaned records are normal it could be that
I am barking up the wrong tree.

regards

Si

Post by Si Carter ***@gmail.com [firebird-support]
Fair point, I only took a snapshot of the "orphaned" stats from one of the
databases, if it happens again, will gather all data for all databases.
But as far as your concerned it's normal to have orphaned records within
there?
regards
Si

Post by Dmitry Yemanov ***@users.sourceforge.net [firebird-support]

Post by Si Carter ***@gmail.com [firebird-support]
I recently installed FB v3.0.1.32609 on Windows server 2008.
This is a dual installation as I still have 2.5.3 installed.
I noticed that my fb3 service is using in excess of 4gb memory

How did you get that number?

Post by Si Carter ***@gmail.com [firebird-support]
Given this sql
SELECT ma.MON$ATTACHMENT_NAME, ma.MON$USER, a.MON$STAT_ID,
a.MON$STAT_GROUP, a.MON$MEMORY_USED, a.MON$MEMORY_ALLOCATED,
a.MON$MAX_MEMORY_USED, a.MON$MAX_MEMORY_ALLOCATED
FROM MON$MEMORY_USAGE a
LEFT JOIN MON$ATTACHMENTS ma ON (ma.MON$STAT_ID = a.MON$STAT_ID)
ORDER BY ma.MON$ATTACHMENT_NAME
it produces the following results, STAT_ID 1 is the one I am more
concerned about at this point.
[null] [null] 138 2 3248 0 4272 0
[null] [null] 115 2 44048 0 440480
[null] [null] 218 3 22104 8112 87984 73688
[null] [null] 116 3 22384 8112 91760 73688
[null] [null] 95 3 21192 24336 2121800 2121568
[null] [null] 1 0 58128176 81557240 1685336336 2771125200

I see that the database utilizes only 81MB of memory (with a 2.8GB peak
somewhen in the past).

Post by Si Carter ***@gmail.com [firebird-support]
Previously it was using in excess of 5gb of ram, a restart bought it
back to normal, but it soon escalated to 3gb (within seconds) and now to
4gb ram.

I don't see 4GB here, sorry.

Post by Si Carter ***@gmail.com [firebird-support]
The database has only 4 normal connections, plus Cache Writer and
Garbage Collector, so it's not under unusual usage at the mo.

Number of connections does not directly affect memory usage.
Dmitry

[Dmitry Yemanov [e-mail] [firebird-support]]

Post by Si Carter ***@gmail.com [firebird-support]
The memory usage crept up to just under 3gb again

Once again, where do you get that number from? Task manager? Perfmon?
top? Screenshot proof would be appreciated.

Post by Si Carter ***@gmail.com [firebird-support]
The memory usage crept up to just under 3gb again, and I took a snapshot
of the data using the previous sql, from all 4 databases. Between the 4
databases there are over 1500 orphaned records, and between the 4
databases there are 30 identifiable connections.

There are no "orphaned" records, it's just a result of your outer join
-- you also get non-identified transaction- and statement-level counters
(mon$stat_group > 1).

And because of that, you shouldn't sum all the values in the column -
the counters are already aggregated
statement->transaction->attachment->database, so you just get wrongly
multiplied results.

Post by Si Carter ***@gmail.com [firebird-support]
I have placed the raw data in a csv file (http://tinyurl.com/zkqf9bo)

The current memory usage is just 350MB but there was a peak of 3.4GB
(3GB of them were used for db1.fdb).

What is your TempCacheLimit setting in firebird.conf? If it's set too
high, the peaks could be explained by heavy sorts.


Dmitry

[Si Carter [e-mail] [firebird-support]]

Hi Dmitry,

Take your point on there being no orphaned records, default for
TempCacheLimit is 8 MB (see below dragged from conf file). And proof in
terms of screen shots are below. You may also see on there FB2.5 which has
many more websites on it.

fwiw, I am just trying to understand why the mem *appears* so high before I
port another 10 to 15 databases over to v3.

regards

Si

Straight from conf file:


#
# The maximum amount of the temporary space that can be cached
# in memory.
#
# For Classic servers, this setting is defaulted to 8 MB.
# Although it can be increased, the value applies to each client
# connection/server instance and thus consumes a lot of memory.
#
# Type: integer
#
#TempCacheLimit = 64M


[image: Inline images 3]
[image: Inline images 2]

Post by Dmitry Yemanov ***@users.sourceforge.net [firebird-support]

Post by Si Carter ***@gmail.com [firebird-support]
The memory usage crept up to just under 3gb again

Once again, where do you get that number from? Task manager? Perfmon?
top? Screenshot proof would be appreciated.

Post by Si Carter ***@gmail.com [firebird-support]
The memory usage crept up to just under 3gb again, and I took a snapshot
of the data using the previous sql, from all 4 databases. Between the 4
databases there are over 1500 orphaned records, and between the 4
databases there are 30 identifiable connections.

There are no "orphaned" records, it's just a result of your outer join
-- you also get non-identified transaction- and statement-level counters
(mon$stat_group > 1).
And because of that, you shouldn't sum all the values in the column -
the counters are already aggregated
statement->transaction->attachment->database, so you just get wrongly
multiplied results.

Post by Si Carter ***@gmail.com [firebird-support]
I have placed the raw data in a csv file (http://tinyurl.com/zkqf9bo)

The current memory usage is just 350MB but there was a peak of 3.4GB
(3GB of them were used for db1.fdb).
What is your TempCacheLimit setting in firebird.conf? If it's set too
high, the peaks could be explained by heavy sorts.
Dmitry

[Non-text portions of this message have been removed]