Issue Details (XML | Word | Printable)

Key: CORE-6129
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Sisa Róbert
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

MON$ATTACHMENT performance problem

Created: 26/Aug/19 12:52 PM   Updated: 28/Aug/19 10:20 AM
Component/s: Scripts
Affects Version/s: 3.0.4
Fix Version/s: None

Environment: The problem appears in IBExpert and my application too. It run on x64 server.

QA Status: No test


 Description  « Hide
I run this query in live environment:

SELECT 0 FROM MON$ATTACHMENTS

Result:
fetch 94 records and it run for 1.2 second (!). It cause performance problems in my program because i have to monitoring the attachments in several trriggers.

It looks like a bug about MON$ATTACHMENTS reading.
Please inform me about this problem, if it will permanent i have to create a table and clone the MON$ATTACHMENTS content for the quick access.

Thanks

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Roman Simakov added a comment - 26/Aug/19 01:24 PM
Run this query the second time in the same transaction and you'll see that it contants is already cached!

Sisa Róbert added a comment - 26/Aug/19 01:56 PM
Cache is not the proper solution because the query runs in the DB before connect trigger and my application use lot of transactions.
Also i think more than 1 second is too long time for fetch only 94 records, max 10-20 milliseconds is acceptable.

Dmitry Yemanov added a comment - 26/Aug/19 02:01 PM
You should never expect 10-20 ms under load, polling all attachments is an expensive operation. Querying MON$ tables in triggers is a bad practice that should be avoided. Please tell us what you're trying to achieve with that.

Sisa Róbert added a comment - 26/Aug/19 02:24 PM
I use database level logging and i log the attached process name/IP from the MON$ATTACHMENT table. Also i have to check sometimes that a stored attachment ID is a live attachment (i have an own record locking mechanism that store the attachment IDs).

Also I have a table to register all connection i think i have to create an own field to store these information about the attachment to the fast access.

Attila Molnár added a comment - 28/Aug/19 07:00 AM
In ON CONNECT trigger, determine process name/IP and store it in SESSION level context variable or in a table.

Dmitry Yemanov added a comment - 28/Aug/19 10:19 AM
CLIENT_ADDRESS, CLIENT_HOST, CLIENT_PID, CLIENT_PROCESS are available via RDB$GET_CONTEXT('SYSTEM'), you don't need to get them from MON$ATTACHMENTS.