Hi Ming,
We are sorry for the delayed response. Please see my responses embedded. *From:* Liu, Ming (Ming) [mailto:ming....@esgyn.cn] *Sent:* Saturday, February 27, 2016 8:05 PM *To:* user@trafodion.incubator.apache.org *Subject:* RMS questions Hi, all, I am trying to gather query’s run-time statistics using RMS command ‘get statistics’. It works fine, but I have some questions below: As I understand, RMS will save stats for a given query in shared memory, so it cannot save all the history. It only save CURRENT running queries’ stats. Is this true?*[Selva] * RMS uses the shared segment to provide near real time statistics of the query. The metrics are captured at the relevant components in near real time and updated in the shared segment directly while the query is being executed. RMS doesn’t poll for the metrics collection, it is the infrastructure to provide real time statistics. For a long-running query, I can start another session using ‘get statistics for qid xxx ‘ to periodically get the stats. For short-running query (finish in ms), it seems hard for me to start another session find out qid and run the ‘get statistics’. I think there is a small time window that one can still get stats for a query after it finished. *[Selva] * For short running queries, you can get the statistics after the query is completed before the next query is run in the same session using the command “get statistics for qid <qid> current”. If the query is issued from a non-interactive application, then you might be able to get some kind of summary info from Trafodion repository if it is enabled. What is that time window, 30 seconds?*[Selva] *Generally, the statistics is retained till the statement is deallocated. The server deallocates the statement only when user initiates SQLDrop or Statement.close or the connection is closed or the statement object on the client side is somehow garbage collected and triggers resource deallocation on the server side. RMS extends the statistics life time a bit more till a next statement is prepared or executed in the same session after the statement is deallocated In case of non-interactive application, this time period could be very short. If I have a busy system with TPS like 3000 queries/s, can RMS save all of them by 30 seconds? That seems huge, and memory is limited. If it works like a ring buffer or cache (aging out oldest entries), what is the strategy RMS keep stats or aging who out? *[Selva] *As I said earlier, RMS is an infrastructure that aids in providing the real time statistics and it is not statistics gathering tool. In Trafodion, Type 4 JDBC applications and ODBC applications use the common infrastructure DCS to execute the queries. DCS is capable providing the summary info or the detailed query statistics based on the configuration settings in DCS. What will happen if all active queries will run out of RMS memory? I know we can enlarge the size of that memory, but not know exact how, any instructions? With the instruction, how can one calculate the required memory size if s/he know how many queries s/he want to save. *[Selva] *Default size of RMS shared segment is 64 MB. We have been able to manage within this space for hundreds of concurrent queries because RMS kicks in garbage collection every 10 minutes to gc any orphaned statistics info. Statistics can become orphaned if the server component went away abruptly or the server component itself failed to deallocate resources. Of course a badly written application that doesn’t deallocate statements can make RMS shared segment to become full. RMS relies on the trusted DCS components /type 2 JDBC driver to put some capacity limit on the connection to avoid this. You can increase the RMS shared segment by adding MX_RTS_STATS_SEG_SIZE=<n in MB> in $MY_SQROOT/etc/ms.env in all nodes and restarting the Trafodion instance. You can issue “get statistics for rms all” to confirm the size of RMS shared segment and to get heath info of RMS itself. Maybe we can only save stats for ‘slow queries’? Many questions, thanks in advance for any help.*[Selva] * I sincerely wish my responses are in order and useful. Thanks, Ming
