Message queue lengths

[Michael A. Lowry]

The documentation page on the Database page of the Administration perspective describes the Current MQ-Records:

The section Current MQ-Records provides details about the message queues counter in the MQ tables of your database.

Are there any guidelines for how high these numbers should be in a normally-running system?

[Legacy User]

I think it really depends on the DB, AE server power.
I mean with a low-end system, 1000 MQ-Records could make it running "abnormally"
But with high-end super system, even with 10000 MQ-Records, your AE still running normally.
You should follow our AWA System Requirements and Sizing and maintains your DB regularly to have a "healthy" system :smiley:

[Michael A. Lowry]

Thanks, Dat_Nguyen-Tat_9196. None of those documents mentions the Current MQ-Records or how to interpret these numbers.

[Legacy User]

I think it just a number that shows current records. Why do you have to "interpret" it? 
If I want to know how good my DB is, I'll definitely take a look at transaction times instead :)

Transaction Times

Transaction times indicate the overall system performance. To evaluate your system, you can turn on a minimal trace on a WP in the Administration perspectiveSeparate functional area of the Automic Web Interface's (AWI). (set trace flag 16 (minimal) to 1). Run the trace for 2 minutes during a typical load situation. Collect the trace files and evaluate them.

Along with several other information you get the average transaction time calculation over all transaction. This may look like this:

The above illustration shows that the average transaction time of more than 17.000 transaction was 0,0159 seconds. Compare your value with the recommendations in the table below:

Average Transaction Time	Rating
> 0.2 sec	Critical
> 0.05 sec	To be improved
> 0.02 sec	OK
< 0.02 sec	Perfect

[Michael A. Lowry]

Dat_Nguyen-Tat_9196:
I collected a trace file as you suggested. I do not see these average transaction times anywhere in the file. I guess you have a special tool for analyzing these traces. Would you be willing to share this tool, or at least provide instructions for extracting and analyzing the relevant data? Thanks.

[Carsten Schmitz]

Michael A. Lowry

It's not much, but at least something as a guideline:

We had some performance problems a while ago and at that time, and ever since then I also captured the MQ levels.

At the time of the performance problems (which were later traced to an overloaded agent, not the engine itself though), these were the high watermark counts over a time of several hours:

MQDWP: 51
MQCP002: 56
MQWP: 753
MQCP003: 803
MQPWP: 209
MQMEM: 3183

I was subsequently told by Automic that these numbers are no reason to worry.

I've just gone over my log, these are the absolute highest counts I found over that entire period (since roughly May 2017):

MQCP001 1010
MQCP002 277
MQCP003 4259
MQCP004 334
MQCP005 465
MQDWP 116
MQJWP 0 (it's a 10.x without JWP, so no surprises here)
MQLS 15365
MQMEM 6110
MQOWP 739
MQPWP 1771
MQQWP 0 (possibly another unused table)
MQRWP 1194
MQWP 2528

Hope this helps.

[Michael A. Lowry]

In a recent incident, Automic analyzed traces using another similar tool:

Reading E:\temp\4\WPsrv_PROD_trc_001_00.txt ...

U00003492 Server has been started on Host 'myhost1' ('192.168.0.1') with process ID '12345'.
Primary: True   RWP: False   OWP: False   DWP: False
Transactions:               2.010
Transaction Time (ms):    189.731 Average:    94,3935
AE Time (ms):              30.400 Average:    15,1244
DB Time (ms):             159.331 Average:    79,2692
Ratio Database:AE          5,24:1

Dat_Nguyen-Tat_9196: Have you seen my request above? We would appreciate it if you could provide instructions for analyzing the trace files — or better yet, the tool you are using to process the data.

We would also like to have copies of the exact SQL statements the Automation Engine is using to measure DB performance. This way, we can run these statements outside of the AE to verify the results. Thanks in advance.

[Michael Pirson]

Here is some information I had in another thread:

Disclaimer I'm not from the Automic side but I was curious so decided to run a DB4 trace on a start up of a WP and here is what I saw.



SELECT DIVDB_String, DIVDB_Date, DIVDB_Memo FROM DIVDB where DIVDB_PK = 1
INSERT INTO DIVDB (DIVDB_PK,DIVDB_Memo,DIVDB_String,DIVDB_Date) VALUES(1, ?, ?, ?)
SELECT DIVDB_String, DIVDB_Date, DIVDB_Memo FROM DIVDB where DIVDB_PK = 1


The INSERT shows in the startup 1000 times and the SELECT shows up 2000 times.


Bind parameters however is just Umlauts for every field: öäüÖÄÜß{}[] (that exact string from trace >öäüÖÄÜß{}[].....<)


Another thing I found interesting is that it looks like it runs 1000 insert statements regardless of how long it takes. For example here is the results from my startup

CPU/DB: '28209746'/'221 (1000/4.506597 s)


So it took 4.5 seconds to run all 1000 inserts. Its not done by going lets see how many we can do in 1 second. I runs it 1000 times and then divides it how by long it took.

Lastly, the data is then truncated.

[Jeff Muller]

Thanks for the information.

 

Jeff

 

[Carsten Schmitz]

We would also like to have copies of the exact SQL statements the Automation Engine is using to measure DB performance.

@Automic: +1000.

Literally. Do want :)

[Keld Mollnitz]

I have the same need as

Michael A. Lowry

:
"We would appreciate it if you could provide instructions for analyzing the trace files — or better yet, the tool you are using to process the data.

We would also like to have copies of the exact SQL statements the Automation Engine is using to measure DB performance. This way, we can run these statements outside of the AE to verify the results. Thanks in advance."

[Legacy User]

Michael A. Lowry

Sorry, I just back from holiday, 
About your question "provide instructions for analyzing the trace files — or better yet, the tool you are using to process the data.", to be honest, I don't have that information. 

One more place you can go to check your AE system performance: https://downloads.automic.com/tools/performance_index

[Keld Mollnitz]

I have the same need as

Michael A. Lowry

:
"We would appreciate it if you could provide instructions for analyzing the trace files — or better yet, the tool you are using to process the data.

We would also like to have copies of the exact SQL statements the Automation Engine is using to measure DB performance. This way, we can run these statements outside of the AE to verify the results. Thanks in advance."

[Michael A. Lowry]

On 29 August 2017, Michael Lowry wrote:

...We would appreciate it if you could provide instructions for analyzing the trace files — or better yet, the tool you are using to process the data.

We would also like to have copies of the exact SQL statements the Automation Engine is using to measure DB performance. This way, we can run these statements outside of the AE to verify the results. Thanks in advance.

The background for my request is a pattern that has repeated itself many times over the past 5 years:

1. We encounter a problem with the Automation Engine — either very poor performance, or an outright crash or hang of the system.
2. We report the problem to Automic Support, and provide application logs and traces.
3. After some back-and-forth, Automic informs us that the root cause is poor database performance. As proof, they point to messages in application logs that show long transaction times.
4. We consult with our DB team, who insist that the actual transaction times in the DB are very small, only tiny fractions of the times reported by the AE.
   

Obviously there is a discrepancy between the performance reported by the DB, and that reported by the Automation Engine.

Some weeks ago, in a related problem ticket, we requested that Automic Support & Development provide us with SQL queries that we could use to measure AE DB performance independent of the AE itself. Unfortunately, these efforts have not proven successful. Although we initially saw promising signs that Automic would provide such SQL statements, these statements were never provided. Instead, Automic advised us to use the Oracle Database Replay tool to capture & replay actual AE DB activity.

[Keld Mollnitz]

Michael_Lowry
Item 1-4 are the exact same pattern we experience....it proves to me that we are not alone which is nice to know..

[Legacy User]

One of the tools we use is called Log.exe. I'm not quite sure if we sent that out to customers in the past. I would have to take a look at it.
One thing that might also help is to run certain queries that take long through an execution plan to see if 100% are spent on the index and not on the table itself.

[Michael A. Lowry]

Alexander Trenker wrote:

One of the tools we use is called Log.exe. I'm not quite sure if we sent that out to customers in the past. I would have to take a look at it.

We would appreciate any tool that would help us analyze logs and traces more efficiently.

One thing that might also help is to run certain queries that take long through an execution plan to see if 100% are spent on the index and not on the table itself.

Thanks for the suggestion; this is the first time anyone has suggested it. To follow your advice, we would have to know the exact query, including the values of bind parameters. Is there a way to write to full SQL tracing information, but only for statements that take more than a certain amount of time to execute? It would not be feasible to leave tracing enabled all the time, just waiting for a problem

[Keld Mollnitz]

Hi Michael,
did you ever get hold of this tool, log.exe ? I may have a copy that I can send you...

/Keld.

[Torsten Goehler]

I don't understand why log.exe and tracemix pro are not availble for download in the download section. Yes, If I request both from support, they will provide me a download link. But you have to know about the tools, to ask for them.