You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2016/02/28 05:04:53 UTC
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?
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.
What is that time window, 30 seconds?
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?
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.
Maybe we can only save stats for 'slow queries'?
Many questions, thanks in advance for any help.
Thanks,
Ming
Re: 答复: 答复: RMS questions
Posted by Carol Pearson <ca...@gmail.com>.
ODB does taxes? Are you sure you're not talking about emacs? I think it
has a file-taxes-mode (maybe one per US state?), all written in e-lisp and
with embedded tax tables (*ducks*)
But seriously, Gunnar's right. The documentation that we have in the
various guides for Trafodion is very detailed. And if something is
missing, we should go add it so people have decent references for what
Trafodion can do. If someone out there wants to take on helping to
organize/extend our documentation, that would be a great place to
contribute.
-Carol P.
---------------------------------------------------------------
Email: carol.pearson234@gmail.com
Twitter: @CarolP222
---------------------------------------------------------------
On Wed, Mar 2, 2016 at 3:05 PM, Gunnar Tapper <ta...@gmail.com>
wrote:
> There is a tremendous amount of documentation in the SQL Reference Guide.
> For example, it contains the basic set of CQDs is documented in this
> manual. Same for the other guides. For example, take a look at the odb User
> Guide. It's so function rich that I am surprised that odb can't do your
> taxes. :)
>
> G.
>
> On Wed, Mar 2, 2016 at 3:52 PM, Liu, Ming (Ming) <mi...@esgyn.cn>
> wrote:
>
>> Very nice document Gunnar, I didn’t realize this. Read it through, it is
>> very informational and helpful.
>>
>> Look very good for me, maybe Selva can review it as Gunnar suggested.
>>
>>
>>
>> Thanks,
>>
>> Ming
>>
>>
>>
>> *发件人:* Gunnar Tapper [mailto:tapper.gunnar@gmail.com]
>> *发送时间:* 2016年3月3日 6:42
>> *收件人:* user@trafodion.incubator.apache.org
>> *主题:* Re: 答复: RMS questions
>>
>>
>>
>> Hi,
>>
>>
>>
>> Also, RMS is documented in the Trafodion SQL Reference Manual:
>> http://trafodion.apache.org/docs/sql_reference/index.html#displaying_sql_runtime_statistics
>>
>>
>>
>> Selva: Please let me know if this documentation needs to be updated. If
>> so, send me the info and I'll incorporate it into this guide.
>>
>>
>>
>> Thanks,
>>
>>
>>
>> Gunnar
>>
>>
>>
>> On Wed, Mar 2, 2016 at 3:37 PM, Liu, Ming (Ming) <mi...@esgyn.cn>
>> wrote:
>>
>> This is very clear and good answer, solve all my confusion! I am now
>> understanding how it works. Also understand the relationship among ODBC
>> collected stats , repository and RMS. Thanks a lot Selva!
>>
>>
>>
>> *发件人**:* Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
>> *发送时间**:* 2016年3月3日 3:35
>> *收件人**:* user@trafodion.incubator.apache.org
>> *主题**:* RE: RMS questions
>>
>>
>>
>> Hi Ming,
>>
>>
>>
>> We are sorry for the delayed response.
>>
>>
>>
>> Please see my responses embedded.
>>
>>
>>
>> *From:* Liu, Ming (Ming) [mailto:ming.liu@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
>>
>>
>>
>>
>>
>>
>>
>> --
>>
>> Thanks,
>>
>>
>>
>> Gunnar
>>
>> *If you think you can you can, if you think you can't you're right.*
>>
>
>
>
> --
> Thanks,
>
> Gunnar
> *If you think you can you can, if you think you can't you're right.*
>
Re: 答复: 答复: RMS questions
Posted by Gunnar Tapper <ta...@gmail.com>.
There is a tremendous amount of documentation in the SQL Reference Guide.
For example, it contains the basic set of CQDs is documented in this
manual. Same for the other guides. For example, take a look at the odb User
Guide. It's so function rich that I am surprised that odb can't do your
taxes. :)
G.
On Wed, Mar 2, 2016 at 3:52 PM, Liu, Ming (Ming) <mi...@esgyn.cn> wrote:
> Very nice document Gunnar, I didn’t realize this. Read it through, it is
> very informational and helpful.
>
> Look very good for me, maybe Selva can review it as Gunnar suggested.
>
>
>
> Thanks,
>
> Ming
>
>
>
> *发件人:* Gunnar Tapper [mailto:tapper.gunnar@gmail.com]
> *发送时间:* 2016年3月3日 6:42
> *收件人:* user@trafodion.incubator.apache.org
> *主题:* Re: 答复: RMS questions
>
>
>
> Hi,
>
>
>
> Also, RMS is documented in the Trafodion SQL Reference Manual:
> http://trafodion.apache.org/docs/sql_reference/index.html#displaying_sql_runtime_statistics
>
>
>
> Selva: Please let me know if this documentation needs to be updated. If
> so, send me the info and I'll incorporate it into this guide.
>
>
>
> Thanks,
>
>
>
> Gunnar
>
>
>
> On Wed, Mar 2, 2016 at 3:37 PM, Liu, Ming (Ming) <mi...@esgyn.cn>
> wrote:
>
> This is very clear and good answer, solve all my confusion! I am now
> understanding how it works. Also understand the relationship among ODBC
> collected stats , repository and RMS. Thanks a lot Selva!
>
>
>
> *发件人**:* Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
> *发送时间**:* 2016年3月3日 3:35
> *收件人**:* user@trafodion.incubator.apache.org
> *主题**:* RE: RMS questions
>
>
>
> Hi Ming,
>
>
>
> We are sorry for the delayed response.
>
>
>
> Please see my responses embedded.
>
>
>
> *From:* Liu, Ming (Ming) [mailto:ming.liu@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
>
>
>
>
>
>
>
> --
>
> Thanks,
>
>
>
> Gunnar
>
> *If you think you can you can, if you think you can't you're right.*
>
--
Thanks,
Gunnar
*If you think you can you can, if you think you can't you're right.*
答复: 答复: RMS questions
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
Very nice document Gunnar, I didn’t realize this. Read it through, it is very informational and helpful.
Look very good for me, maybe Selva can review it as Gunnar suggested.
Thanks,
Ming
发件人: Gunnar Tapper [mailto:tapper.gunnar@gmail.com]
发送时间: 2016年3月3日 6:42
收件人: user@trafodion.incubator.apache.org
主题: Re: 答复: RMS questions
Hi,
Also, RMS is documented in the Trafodion SQL Reference Manual: http://trafodion.apache.org/docs/sql_reference/index.html#displaying_sql_runtime_statistics
Selva: Please let me know if this documentation needs to be updated. If so, send me the info and I'll incorporate it into this guide.
Thanks,
Gunnar
On Wed, Mar 2, 2016 at 3:37 PM, Liu, Ming (Ming) <mi...@esgyn.cn>> wrote:
This is very clear and good answer, solve all my confusion! I am now understanding how it works. Also understand the relationship among ODBC collected stats , repository and RMS. Thanks a lot Selva!
发件人: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com<ma...@esgyn.com>]
发送时间: 2016年3月3日 3:35
收件人: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
主题: RE: RMS questions
Hi Ming,
We are sorry for the delayed response.
Please see my responses embedded.
From: Liu, Ming (Ming) [mailto:ming.liu@esgyn.cn<ma...@esgyn.cn>]
Sent: Saturday, February 27, 2016 8:05 PM
To: user@trafodion.incubator.apache.org<ma...@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
--
Thanks,
Gunnar
If you think you can you can, if you think you can't you're right.
Re: 答复: RMS questions
Posted by Gunnar Tapper <ta...@gmail.com>.
Hi,
Also, RMS is documented in the Trafodion SQL Reference Manual:
http://trafodion.apache.org/docs/sql_reference/index.html#displaying_sql_runtime_statistics
Selva: Please let me know if this documentation needs to be updated. If so,
send me the info and I'll incorporate it into this guide.
Thanks,
Gunnar
On Wed, Mar 2, 2016 at 3:37 PM, Liu, Ming (Ming) <mi...@esgyn.cn> wrote:
> This is very clear and good answer, solve all my confusion! I am now
> understanding how it works. Also understand the relationship among ODBC
> collected stats , repository and RMS. Thanks a lot Selva!
>
>
>
> *发件人:* Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
> *发送时间:* 2016年3月3日 3:35
> *收件人:* user@trafodion.incubator.apache.org
> *主题:* RE: RMS questions
>
>
>
> Hi Ming,
>
>
>
> We are sorry for the delayed response.
>
>
>
> Please see my responses embedded.
>
>
>
> *From:* Liu, Ming (Ming) [mailto:ming.liu@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
>
>
>
--
Thanks,
Gunnar
*If you think you can you can, if you think you can't you're right.*
答复: RMS questions
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
This is very clear and good answer, solve all my confusion! I am now understanding how it works. Also understand the relationship among ODBC collected stats , repository and RMS. Thanks a lot Selva!
发件人: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
发送时间: 2016年3月3日 3:35
收件人: user@trafodion.incubator.apache.org
主题: RE: RMS questions
Hi Ming,
We are sorry for the delayed response.
Please see my responses embedded.
From: Liu, Ming (Ming) [mailto:ming.liu@esgyn.cn<ma...@esgyn.cn>]
Sent: Saturday, February 27, 2016 8:05 PM
To: user@trafodion.incubator.apache.org<ma...@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
RE: RMS questions
Posted by Selva Govindarajan <se...@esgyn.com>.
Hi Ming,
We are sorry for the delayed response.
Please see my responses embedded.
*From:* Liu, Ming (Ming) [mailto:ming.liu@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