Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MON$ATTACHMENT performance problem [CORE6129] #6378

Open
firebird-automations opened this issue Aug 26, 2019 · 6 comments
Open

MON$ATTACHMENT performance problem [CORE6129] #6378

firebird-automations opened this issue Aug 26, 2019 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sisa Róbert (robert.sisa)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

Run this query the second time in the same transaction and you'll see that it contants is already cached!

@firebird-automations
Copy link
Collaborator Author

Commented by: Sisa Róbert (robert.sisa)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sisa Róbert (robert.sisa)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

In ON CONNECT trigger, determine process name/IP and store it in SESSION level context variable or in a table.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant