What is your 4D Server doing just now?

There are plenty of tools, Real Time Monitor shows currently long running queries, Process list shows which process takes a lot of time (long running method), debug log shows the content of the method and so on.

Let say you have your methods unter control but don’t know what the end user is doing, you want to monitor his/her queries.
Not just running now, but for the last minutes?

Something like this:
[]24801016;“Your comment here…”[/]

showing statistic about each query (by field, field combination and query condition, not query value), how often and duration (milliseconds)

Useable on a client in compiled application, when ever you want?

Wondering why 4D is not providing such a tool?

The tool is here since 4D v14 R3. Named Get database measures.
There is even a tech note: http://kb.4d.com/assetid=77205
It shows how to collect these data - and show huge amount of statistics with wonderful charts.
If you did not already, download and try.
This tool is great if you need to do an analyzes for your customer (or manager) and want to show how busy your server is.
How many bytes read/write per table, per index, in memory, on disk. Thousands of data…

Dashboards are great for managers, but for the working part of us, which just want to know “what was the 10 slowest queries” or “what are the most often used queries”, there are way too many details.

So here how to write your own tool. Showing a list of current queries (if you want to know for how long, read documentation http://doc.4d.com/4Dv16R6/4D/16-R6/Get-database-measures.301-3547959.de.html and look for “history”. It returns the last 200 queries per table. Depending of how heavy the table is used, the time range could be short or very long).

To create the tool, we need 3 methods and one form.
One method just to open a window and display the form.

$win:=Open form window(“LiveStat_DisplayData”)
DIALOG(“LiveStat_DisplayData”;*)

Note the * in DIALOG. This means the form stays open and you do not need to block the process, do not need to waste a process (reducing server load, and that’s why we are caring for all this, don’t we?)
If you don’t, remove the *, add a Close window and run it in a new process.

The dialog could show 3 arrays or 1 collection. I’m using the collection here, as I do not want to use process variables without good reason (=wasting memory in every process).
If you are still in v16, you need to use arrays. The code will show how to do that, don’t worry.

A collection could use the new “Form” object, a kind of local form variables. If you don’t know that, read the blog about Form object.

The form contains a button requesting the update. This button calls 2 methods. One to collect the data from the server, one to analyze it.
The 2nd one could be copied directly in the button object method, I kept it separated in case you want to call it repeatly in On Timer or so (even I would not do so, as I want to be able to read it and not getting it updated while I’m analyzing. If needed, I just run the method several time to open several windows, remember, all in the same process, thanks to Form with isolated data).

To collect the data from the server the method MUST have the “execute on server” check box set. Note: check box, not command! No need to start a new process, no need to build my own communication concept, the check box let the client process pause, code is executed on the server, result sent to client process resume. Nothing could be simpler.
Code is also simple:
// require flag to be executed on server
C_OBJECT($0)
$0:=Get database measures(New object(“path”;“DB.tables”))

The optional parameter allows me to filter some data, else the command will return more as we need - wasted bandwidth…

The 2nd method analyzes the data and build the collection used to display the list box.
It loops through the received object, find’s the query statements and stats and fills the collection:
<code 4D>
$data:=$1
$result:=New collection
For each ($tablename;$data.DB.tables)
$table:=$data.DB.tables[$tablename]
If ($table.queries#Null)
For ($queryindex;0;$table.queries.length-1)
$name:=$table.queries[$queryindex].queryStatement
$count:=Num($table.queries[$queryindex].queryCount.value)
$duration:=Num($table.queries[$queryindex].duration.value)
$result.push(New object(“query”;$name;“count”;$count;“duration”;$duration))
End for
End if
End for each
$0:=$result
</code 4D>

As said, if you are still with v16 and/or without object notation, use arrays in the listbox. Works as well, just less fun (and more code):
<code 4D>
$data:=$1
ARRAY TEXT(arr_Query;0)
ARRAY LONGINT(arr_Count;0)
ARRAY LONGINT(arr_duration;0)
ARRAY TEXT($arrNames;0)
ARRAY LONGINT($arrTypes;0)
$db:=OB Get($data;“DB”;Is object)
$tables:=OB Get($db;“tables”;Is object)
OB GET PROPERTY NAMES($tables;$arrNames;$arrTypes)
For ($i;1;Size of array($arrNames))
$table:=OB Get($tables;$arrNames{$i})
ARRAY OBJECT($queries;0)
OB GET ARRAY($table;“queries”;$queries)
For ($queryindex;1;Size of array($queries))
$name:=OB Get($queries{$queryindex};“queryStatement”)
$countobject:=OB Get($queries{$queryindex};“queryCount”;Is object)
$count:=OB Get($countobject;“value”;Is longint)
$countobject:=OB Get($queries{$queryindex};“duration”;Is object)
$duration:=OB Get($countobject;“value”;Is longint)
APPEND TO ARRAY(arr_Query;$name)
APPEND TO ARRAY(arr_Count;$count)
APPEND TO ARRAY(arr_duration;$duration)
End for
End for
</code 4D>

To try it in your applicatio

  • open the enclosed mini application with v17 or newer (if you are 4D Silver Partner you already have a license, else you need a wait a short while or use the code above)
  • if your own development is in v17, just drag&drop the 3 methods and the single form to your application, finished
  • if you are still in v16 or even v15, create 3 methods with the same name and copy just the content.
    • For RetrieveData rewrite the single line with New Object into several lines using OB Set, then pass the object
    • For AnalyzeData comment the block using collection and uncomment the block using arrays.
    • in the form, change data source of the listbox from collection to array and change the 3 columns from this.query to variable arr_Query and so on.

Run LiveStat_Display on the client. (for testing you can of course also do that in developer edition, just run some queries upfront=use your user interface in typical way)

Then, from time to time, take a look on the result. Sort by largest duration or highest count. High duration for many queries could be normal.
High duration for only one query should be a query without index, but used only once, so ok.
For some queries it means repeated query without index, maybe worth to consider a new index.

In the example screenshot above I found a Query by Formula checking a field an an expression (picture size) running 25091 times in the first hour of working day, drastically more often as expected, so a good reason to rewrite the code. The 2nd line was a query on 4 fields, none of them indexed. Only 20 times, but still a reason to add at least one index.
Enjoy…

http://forums.4d.com/4DBB_Main/x_User/3985/files/24801031.zip

Hello,

It looks like the method to the “Update” button is damaged.
Error #-5 (Resource #642)
Error code: -5 (4DRT)
Error #-5 (Resource #642)
component: ‘4DRT’
task -4, name: ‘Design process’

Here is the complier message:
[]24804569;“Your comment here…”[/]

Forgot to say: v17

Awesome post – thanks Thomas!

Get database measures is a terrific tool. You can really dig in and optimize your application, as well as build beautiful dashboards using 3rd party dashboard tools like Grafana.

[]24807689;“Dashboard build with Get database measures and Grafana”[/]

oops, sorry, that file looks damaged, yes.

Here a new upload:
http://forums.4d.com/4DBB_Main/x_User/3985/files/24817493.zip

Nice control monitor!

Thanks. This is very handy as a component.

: Keith CULOTTA

this is very handy as a component.

yes, fully agree.
I was just scared that some readers would not look into it, just because they don’t like components. More a kind of religious decision…

You can use it either as component or just move the 3 methods/1 form into an application. Or use the logic behind and display the result in a web page.