Introduction to the Mimer SQL monitor
The Mimer SQL monitor application can be used as a helper tool if you are experiencing performance issues on the database server, to find out if one or more specific SQL statements are causing the situation. It can also be used if you’re curious about what SQL statements that are run, and facts about when, how often, by whom, how expensive they are performance-wise and what resources they are using.
The SQL monitor application provides statistical numbers that require some understanding of how the database server operates in order to be interpreted accurately. What they represent and how they are to be interpreted is described in the System Management Handbook in the Mimer SQL Documentation Set, found at our documentation page.
Below, this article will provide some example scenarios where the Mimer SQL monitor application might help, and how to use it. It presents examples where the statistics provided can be used to draw conclusions on the usage of the database server.
Mimer SQL Monitoring examples
Scenario 1 – solving performance issues
The case:
During the past week, every day after lunch there’s a slight performance decrease on your database server for about an hour, and then it goes back to normal. During the night the database server was restarted for another reason, so today all the SQL monitoring statistics are at a clean slate, which you plan to take advantage of.
Referring to the case above, when the clock strikes 1 PM, i.e. when the latency usually starts, the SQL monitor application is attached to the database server, using the system administrator login. It will immediately start continuously monitoring the system.
To see what is happening during this specific hour, the application is asked to take a snapshot of all the executed SQL statements every 30 seconds 20 times, stopping after 10 minutes. As the load is quite heavy, only the top 5 statements with the most table operations are monitored.
> sqlmonitor mydatabase --username=SYSADM --password=sysadm_password --interval=30 --stop=20 --top=5 Mimer SQL Monitor Version x.y.z Copyright (C) Mimer Information Technology AB. All rights reserved. ============================ SNAPSHOT 2020-01-01 13:00:00 ---------------------------- Table Elapsed oper time ations (total (total) secs) SQL ID ---------------------------- x x.xxxxxx 67 x x.xxxxxx 54 x x.xxxxxx 73 x x.xxxxxx 64 x x.xxxxxx 82 ----------------------------------- Table Current Elapsed oper table op time ations erations (delta (delta) /second secs) SQL ID ----------------------------------- --2020-01-01 13:00:30-------------- x x.xxxxxx x.xxxxxx 102 x x.xxxxxx x.xxxxxx 111 x x.xxxxxx x.xxxxxx 67 x x.xxxxxx x.xxxxxx 107 x x.xxxxxx x.xxxxxx 54 ----------------------------------- --2020-01-01 13:01:00-------------- x x.xxxxxx x.xxxxxx 107 x x.xxxxxx x.xxxxxx 102 x x.xxxxxx x.xxxxxx 111 x x.xxxxxx x.xxxxxx 54 x x.xxxxxx x.xxxxxx 67 ----------------------------------- --2020-01-01 13:01:30-------------- x x.xxxxxx x.xxxxxx 107 x x.xxxxxx x.xxxxxx 111 x x.xxxxxx x.xxxxxx 102 x x.xxxxxx x.xxxxxx 32 x x.xxxxxx x.xxxxxx 54 -----------------------------------
You find that the SQL statements with id 102, 107 and 111 keep reappearing during monitoring, and they perform a lot more table operations than the other statements that are executed. You now ask the application to take a snapshot of these statements specifically.
> sqlmonitor mydatabase --username=SYSADM --password=sysadm_password --sqlid=102 --sqlid=107 --sqlid=111 Mimer SQL Monitor Version x.y.z Copyright (C) Mimer Information Technology AB. All rights reserved. ============================================================== SNAPSHOT 2020-01-01 13:11:00 -------------------------------------------------------------- SQL ID SQL statement -------------------------------------------------------------- 102 select x from y 107 update x set y = z where a = b 111 insert into z (y) values (x) ============================================================== Trans- Table action Elapsed oper Prepare Execute Server record time ations count count requests count (total (total) (total) (total) (total) (total) secs) SQL ID -------------------------------------------------------------- x x x x x x.xxxxxx 102 x x x x x x.xxxxxx 107 x x x x x x.xxxxxx 111 ==============================================================
Based on the figures you saw while monitoring continuously, the total number of table operations for each of these statements, since the server restart the past night, approximately correspond to what would be expected of about 10 minutes of execution. This should mean that the statements weren’t executed earlier that day, they just started executing at 1 PM.
The solution:
You now have enough information to ask your colleague about these SQL statements. They recognize the SQL statements shown by the SQL monitor as being part of the major batch job they added last week, that they thought had been configured to be run every night at 1 AM. You reconfigure the batch job to actually run at 1 AM instead of 1 PM, and the performance drops during the day will stop occurring.
Scenario 2 – benchmarking an application
The case:
You have written an application program that executes as a Mimer SQL program ident on your database server. As your network speed is the bottleneck of your system, you would like to know how many server requests are performed by the application when it runs.
In this case you should start the SQL monitor in benchmarking mode. You order the results by server requests, and choose detail level 2 in order to see the number of server requests which is hidden by default. You specify the program ident your application uses in order to only get the SQL statements the application program executes and nothing else. The SQL monitor waits for your input.
> sqlmonitor --program=ExampleProgram --benchmark --order=server_requests –detail=2 Mimer SQL Monitor Version x.y.z Copyright (C) Mimer Information Technology AB. All rights reserved. Username: xyz Password: Program password: Press [ENTER] to take the first snapshot.
You provide input to the SQL monitor and it takes an initial snapshot of the SQL statements run by the program ident ExampleProgram on your database server so far. The SQL monitor then again waits for your input.
============================================================== SNAPSHOT 2020-01-01 12:00:00 -------------------------------------------------------------- Trans- Table action Elapsed oper Prepare Execute Server record time ations count count requests count (total (total) (total) (total) (total) (total) secs) SQL ID -------------------------------------------------------------- x x x x x x.xxxxxx 67 x x x x x x.xxxxxx 66 ============================================================== Press [ENTER] to take another snapshot.
You run your application program, and then ask the SQL monitor to continue taking another snapshot for comparison. It now presents the SQL statements’ number of server requests between then and now, while you executed your application program. The number is presented for each executed SQL statement in column five.
======================================================================= DELTA ----------------------------------------------------------------------- Trans- Table Current action Elapsed oper table op Prepare Execute Server record time ations erations count count requests count (delta (delta) /second (delta) (delta) (delta) (delta) secs) SQL ID ----------------------------------------------------------------------- --2001-01-01 12:05:00-------------------------------------------------- x x x x x x.xxxxxx 67 x x x x x x.xxxxxx 66
Note! In the example above, the user xyz must have execute privilege on the program ExampleProgram for SQL monitor to allow the monitoring operation, or it will present an authorization failure.
Links
For details and further information on the SQL monitor application, see the System Management Handbook in the Mimer SQL Documentation Set or PDF-file.
