Optimizations for MON$ snapshots

[arkinform]

We have huge databases (hundreds of gigabytes) and very complex client-server medical applications with hundreds of database connections. Applications allow users to work using multiple tabs in many active transactions with different data in parallel. Recently we gathered some statistics and it showed that we usually have up to 10000 (10 thousands) records in MON$STATEMENTS table on the most loaded databases. There are obvious performance issues here.

For example, let's look at the MON$STATEMENTS table. Each MON$STATEMENTS record contains 2 blobs (MON$SQL_TEXT and MON$EXPLAINED_PLAN) and on each MON$ snapshot all these blobs should be created, in our case 20 thousands of blobs. The most curious thing here is that we query MON$STATEMENTS table very rarely. So, each time when some abstract DBA executes simple query with MON$ATTACHMENTS or MON$TRANSACTIONS, for example, to kill connection, all these blobs are created in vain. I am sure there are a lot of similar production cases when users have regular scheduled tasks to get information from some MON$ tables for monitoring, killing old connections, etc., and most of them do not involve MON$STATEMENTS and other rarely used MON$ tables.

Also, in our specific case the performance issue is getting worse because of regular MON$CONTEXT_VARIABLES queries from applications on each client application start, and we cannot remove it easily. All that causes significant slowdown from time to time. HQBird even had to implement special 5.x build for our most loaded databases to remove MON$STATEMENTS and MON$COMPILED_STATEMENTS data collection completely from MON$ snapshots and the problem has been gone or at least has been significantly mitigated.

So, I would like Firebird developers to consider several solutions to optimize or at least to control MON$ snapshot processing:

1. The easiest one. Implement special firebird.conf option to set default monitoring "level" (for example, "minimal" and "full"). If "minimal" level is specified MON$ snapshots collect data only for MON$DATABASE, MON$ATTACHMENTS, MON$TRANSACTIONS, MON$CONTEXT_VARIABLES tables and all other MON$ tables remain empty. If a user wants to collect more, special RDB$SET_CONTEXT variable can be specified in the transaction to change monitoring "level". After that the user will be able to query other MON$ tables. This is an explicit manual approach, but it will allow us to resolve mentioned above performance issues related to monitoring tables keeping other MON$ tables accessible on demand.

2. The monitoring "level" approach can be enhanced using automatic "level up" within the transaction. For example, if the user runs a query only with MON$ATTACHMENTS and MON$TRANSACTION tables, then "minimal" snapshot is collected. If the user in the same transaction executes query with any MON$ table outside of "minimal" scope then a "full" snapshot is collected from scratch. In that approach, previous active monitoring queries should continue to use the previous "minimal" snapshot because data in the new "full" snapshot can differ from the previous "minimal" snapshot. Yes, that approach makes monitoring data not fully "stable" within the transaction, but it allows to avoid manual monitoring "level" management.

Of course, there are other more sophisticated approaches to optimize MON$ processing, but I would like to focus this task more on any feasible short-term approach.

[sim1984]

We have already discussed this. The snapshot rule for MON$ tables should be the same as for others and depends on the transaction isolation level. For READ COMMITTED - snapshot at the statement level, the snapshot is made only for those tables that are involved in the SQL query. For SNAPSHOT - snapshot at the transaction level, the snapshot is always made for all monitoring tables.