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

Enhance MON$STATEMENTS / MON$STATE to represent real CPU consumption [CORE1583] #2003

Closed
firebird-automations opened this issue Nov 10, 2007 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Marcelo Carvalho (marcelorsc)

Votes: 2

The MON$STATE is being shown as Active even if no CPU usage is happenening. In fact it remains Active while the statement is open, but even while it's idle, giving wrong information about statements that could be consuming resources.

An example: If I run a query, the statement is set as active, and the query appears in the MON$STATEMENTS table. Also the MON$TIMESTAMP is set. During some instants the query runs, then it returns the result, with the cursor in the first data register. BUT, if I stay stopped, without retrieval of the data (let's say user left table open and left for lunch...) even not consuming any processor, the query remains Active, and the timestamp unchanged. The MON$STATE only returns to Inactive when the user reaches the last data page, or if the transaction is ended!

The effect is that if I perform some monitoring during such interval, while the query is open but idle (waiting the user request for more data), checking MON$STATE as active and the value of MON$TIMESTAMP it will appear that we have an endless query - while it may be a just normal an valid query. And we will not be able to identify witch query in that instant may be the one that is really consuming the processor...

I would expect that MON$STATE (and MON$TIMESTAMP) would flag the statements really consuming CPU, to help us to identify what tasks may be consuming the server resources (processor!), and that it should be inactive when the statement is idle. It can work well for web apps that retrieve data immediately after the query and in sequence close the transaction, but in scenery where there are many desktop clients, we may have many active statements even if none of them are really consuming the processor resources.

In an ideal world, as a suggestion, we could have FOUR STATES: 0=Inactive; 1=Active during query processing; 2=Idle after processing select, but before reaching end of data and releasing the statement; 3=Active during data retrieval. Of course a second MON$TIMESTAMP should exist to show the time when the last CPU usage started (states 1 or 3, which actually means "using CPU") - this would be the timestamp to be checked to find the statement consuming CPU. Another benefit of this would be to identify time consuming queries (a lot of time in state 1) or fast queries but with excessive result set (too much time in state 3). Notice that it's possible a statement return from state 3 to state 2, then to 3 again, several times, if user gets data in blocks, until the user finishes to retrieve data (returning then to state 0).

Commits: eb9ad2a

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

It is not appropriate to use the tracker to ask "Is this right..." questions, it is to post real issues. Such questions are to be posted to the developer or support lists. Then, when confirmed they many be posted here.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

issuetype: Bug [ 1 ] => Improvement [ 4 ]

description: I'm not sure if it is a misunderstanding of the feature, or really a bug - but it's behavior can make FB monitoring useless. The MON$STATE is being shown as Active even if no CPU usage is happenening. In fact it remains Active while the statement is open, but even while it's idle, giving wrong information about statements that could be consuming resources.

An example: If I run a query, the statement is set as active, and the query appears in the MON$STATEMENTS table. Also the MON$TIMESTAMP is set. During some instants the query runs, then it returns the result, with the cursor in the first data register. BUT, if I stay stopped, without retrieval of the data (let's say user left table open and left for lunch...) even not consuming any processor, the query remains Active, and the timestamp unchanged. The MON$STATE only returns to Inactive when the user reaches the last data page, or if the transaction is ended!

The effect is that if I perform some monitoring during such interval, while the query is open but idle (waiting the user request for more data), checking MON$STATE as active and the value of MON$TIMESTAMP it will appear that we have an endless query - while it may be a just normal an valid query. And we will not be able to identify witch query in that instant may be the one that is really consuming the processor...

Is this the expected behavior?

I would expect that MON$STATE (and MON$TIMESTAMP) would flag the statements really consuming CPU, to help us to identify what tasks may be consuming the server resources (processor!), and that it should be inactive when the statement is idle. It can work well for web apps that retrieve data immediately after the query and in sequence close the transaction, but in scenery where there are many desktop clients, we may have many active statements even if none of them are really consuming the processor resources.

In an ideal world, as a suggestion, we could have FOUR STATES: 0=Inactive; 1=Active during query processing; 2=Idle after processing select, but before reaching end of data and releasing the statement; 3=Active during data retrieval. Of course a second MON$TIMESTAMP should exist to show the time when the last CPU usage started (states 1 or 3, which actually means "using CPU") - this would be the timestamp to be checked to find the statement consuming CPU. Another benefit of this would be to identify time consuming queries (a lot of time in state 1) or fast queries but with excessive result set (too much time in state 3). Notice that it's possible a statement return from state 3 to state 2, then to 3 again, several times, if user gets data in blocks, until the user finishes to retrieve data (returning then to state 0).

Best regards, and congratulations for the really very good job done with FireBird!

  Marcelo Carvalho\.

PS: Sorry if this is not the right place to post this feedback (I don't know if it is a bug or a misunderstanding of a feature). I tryied first post at the sourceforge.firebird-devel but it didn't accept the post (via newsreader). Anyway, I'd like to give this feedback to the development team.

=>

The MON$STATE is being shown as Active even if no CPU usage is happenening. In fact it remains Active while the statement is open, but even while it's idle, giving wrong information about statements that could be consuming resources.

An example: If I run a query, the statement is set as active, and the query appears in the MON$STATEMENTS table. Also the MON$TIMESTAMP is set. During some instants the query runs, then it returns the result, with the cursor in the first data register. BUT, if I stay stopped, without retrieval of the data (let's say user left table open and left for lunch...) even not consuming any processor, the query remains Active, and the timestamp unchanged. The MON$STATE only returns to Inactive when the user reaches the last data page, or if the transaction is ended!

The effect is that if I perform some monitoring during such interval, while the query is open but idle (waiting the user request for more data), checking MON$STATE as active and the value of MON$TIMESTAMP it will appear that we have an endless query - while it may be a just normal an valid query. And we will not be able to identify witch query in that instant may be the one that is really consuming the processor...

I would expect that MON$STATE (and MON$TIMESTAMP) would flag the statements really consuming CPU, to help us to identify what tasks may be consuming the server resources (processor!), and that it should be inactive when the statement is idle. It can work well for web apps that retrieve data immediately after the query and in sequence close the transaction, but in scenery where there are many desktop clients, we may have many active statements even if none of them are really consuming the processor resources.

In an ideal world, as a suggestion, we could have FOUR STATES: 0=Inactive; 1=Active during query processing; 2=Idle after processing select, but before reaching end of data and releasing the statement; 3=Active during data retrieval. Of course a second MON$TIMESTAMP should exist to show the time when the last CPU usage started (states 1 or 3, which actually means "using CPU") - this would be the timestamp to be checked to find the statement consuming CPU. Another benefit of this would be to identify time consuming queries (a lot of time in state 1) or fast queries but with excessive result set (too much time in state 3). Notice that it's possible a statement return from state 3 to state 2, then to 3 again, several times, if user gets data in blocks, until the user finishes to retrieve data (returning then to state 0).

summary: Wrong monitoring - MON$STATEMENTS / MON$STATE set as Active even while there is no CPU usage => Enhance MON$STATEMENTS / MON$STATE to represent real CPU consumption

@firebird-automations
Copy link
Collaborator Author

Commented by: Marcelo Carvalho (marcelorsc)

Hi Sean! Sorry for the wrong procedure. In fact it was a mix between a possible bug (as it was not working as I expected), a confirmation of interpretation of the feature (improper as you said), and also a suggestion (as it became in the end).

@firebird-automations
Copy link
Collaborator Author

Commented by: Marcelo Carvalho (marcelorsc)

Just an ADD-ON... Besides the Yemanov's suggestion for the Three-State flag (prepared, stalled/suspended and active), I thought about another approach...

Instead a new three state flag, just a new field like MON$CPUTIMESTAMP should be enough... It would mark the moment when any CPU consuming stage started for such statement, becoming NULL when idle (prepared or stalled/suspended), allowing us to know IF that statement is consuming CPU and since when... Crossing with the original MON$TIMESTAMP and MON$STATE we can track exactly what's happening.

Besides giving information about what statements are consuming CPUs, having another timestamp to mark the moment when the CPU started to be used (processing) may allow us to know if the statement is in the query processing stage, or in the data retrieval stage. As the original MON$TIMESTAMP marks the beginning of activity (active or stalled/suspended, before finishing), i.e. the total processing time of the statement, the new timestamp would mark only the last activity duration (CPU usage!). If they have the SAME VALUE (it's the first processing round!), the statement is processing the query. But if they have different values, shows that the query was processed, the activity ceased and restarted, than the statement is now in the data retrieval stage.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Data retrieval stage can also imply the CPU consumption. Firebird almost never prepares the dataset in advance, instead it reads records one-by-one along with the fetches initiated by the client side. So, for a complex join or a select from stored procedure every fetch would mean the data processing.

@firebird-automations
Copy link
Collaborator Author

Commented by: Dimitrios Chr. Ioannidis (dchri)

Use of the new Target Final Release Version field.

@firebird-automations
Copy link
Collaborator Author

Modified by: Dimitrios Chr. Ioannidis (dchri)

Target Final Release Version: 2.5.0 [ 10221 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13398 ] => Firebird [ 14678 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 2.5 Alpha 1 [ 10224 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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

2 participants