Issue Details (XML | Word | Printable)

Key: CORE-1583
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Marcelo Carvalho
Votes: 2
Watchers: 2

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

Enhance MON$STATEMENTS / MON$STATE to represent real CPU consumption

Created: 09/Nov/07 10:02 PM   Updated: 19/Jan/16 05:02 AM
Component/s: None
Affects Version/s: 2.1 Beta 2
Fix Version/s: 2.5 Beta 1

Environment: FB (Beta 2) under Windows XP Pro. Access via a Delphi 2006 application using IBX, and also via IBConsole (from IB6.02) and Flame Robin.

Target: 2.5.0
QA Status: No test

 Description  « Hide
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).

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 10/Nov/07 02:29 PM
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.

Marcelo Carvalho added a comment - 14/Nov/07 01:33 AM
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).

Marcelo Carvalho added a comment - 14/Nov/07 01:44 AM
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.

Dmitry Yemanov added a comment - 14/Nov/07 02:17 AM
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.

Dimitrios Chr. Ioannidis added a comment - 15/Nov/07 10:02 AM
Use of the new Target Final Release Version field.