Hi Igniters!
As part of IEP-27 <https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics> we already gathering IO statistics and expose it through JMX. User who use only SQL should have access to the statistics also. So let's discuss about how such SQL view should looks. My proposal it is two SQL views: 1) STATIO_CACHE_GRP cache_grp_name - Name of cache group physical_read - Number of physical read of pages logical_read - Number of logical read of pages The view can be filtered by name, like SELECT * from IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' 2) STATIO_IDX cache_grp_name - Name of cache group idx_name - Name of index physical_read - Common number of physical reads of pages for the index logical_read - Common number of logical reads of pages for the index leaf_logical_read - Number of logical reads of index leaf pages leaf_physical_read - Number of physical reads of index leaf pages inner_logical_read - Number of logical reads of index inner pages inner_physical_read - Number of physical reads of index leaf pages The view can be filtered by cache group name or by index name, like SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' We also have time of start gathering statistics, but I'm not sure that it should be exposed here. WDYT about proposed format for the SQL views? -- Живи с улыбкой! :D |
Yury,
How do we differentiate between logical and physical reads? Also, it looks counter-intuitive when "CACHE" is used in the name of the views for SQL table related statistics. It's still hard to explain the user the relations between caches and tables. Hopefully, this will be fixed in 3.0 with renaming but as for the statistics can we use anything neutral for the view names? - Denis On Tue, Jan 15, 2019 at 5:57 AM Юрий <[hidden email]> wrote: > Hi Igniters! > > As part of IEP-27 > < > https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics > > > we > already gathering IO statistics and expose it through JMX. > > User who use only SQL should have access to the statistics also. So let's > discuss about how such SQL view should looks. > > My proposal it is two SQL views: > 1) STATIO_CACHE_GRP > > cache_grp_name - Name of cache group > physical_read - Number of physical read of pages > logical_read - Number of logical read of pages > > > The view can be filtered by name, like SELECT * from > IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' > 2) STATIO_IDX > > cache_grp_name - Name of cache group > > idx_name - Name of index > physical_read - Common number of physical reads of pages for > the index > logical_read - Common number of logical reads of pages for > the index > > leaf_logical_read - Number of logical reads of index leaf pages > > leaf_physical_read - Number of physical reads of index leaf pages > > inner_logical_read - Number of logical reads of index inner pages > > inner_physical_read - Number of physical reads of index leaf pages > > > The view can be filtered by cache group name or by index name, like > SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' > > We also have time of start gathering statistics, but I'm not sure that it > should be exposed here. > > > WDYT about proposed format for the SQL views? > > > > > > > -- > Живи с улыбкой! :D > |
Denis,
Physical reads is load page from storage to memory. Logical reads is read page which already in memory. We gather IO statistics on CACHE_GROUP level due to Ignite use one page to keep all caches related to one cache group. Unfortunately gathering on table level will be expensive due to the reason. That's way name of view contains words cache and groups. ср, 16 янв. 2019 г. в 17:52, Denis Magda <[hidden email]>: > Yury, > > How do we differentiate between logical and physical reads? > > Also, it looks counter-intuitive when "CACHE" is used in the name of the > views for SQL table related statistics. It's still hard to explain the user > the relations between caches and tables. Hopefully, this will be fixed in > 3.0 with renaming but as for the statistics can we use anything neutral for > the view names? > > - > Denis > > > On Tue, Jan 15, 2019 at 5:57 AM Юрий <[hidden email]> wrote: > > > Hi Igniters! > > > > As part of IEP-27 > > < > > > https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics > > > > > we > > already gathering IO statistics and expose it through JMX. > > > > User who use only SQL should have access to the statistics also. So > let's > > discuss about how such SQL view should looks. > > > > My proposal it is two SQL views: > > 1) STATIO_CACHE_GRP > > > > cache_grp_name - Name of cache group > > physical_read - Number of physical read of pages > > logical_read - Number of logical read of pages > > > > > > The view can be filtered by name, like SELECT * from > > IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' > > 2) STATIO_IDX > > > > cache_grp_name - Name of cache group > > > > idx_name - Name of index > > physical_read - Common number of physical reads of pages > for > > the index > > logical_read - Common number of logical reads of pages > for > > the index > > > > leaf_logical_read - Number of logical reads of index leaf pages > > > > leaf_physical_read - Number of physical reads of index leaf pages > > > > inner_logical_read - Number of logical reads of index inner pages > > > > inner_physical_read - Number of physical reads of index leaf pages > > > > > > The view can be filtered by cache group name or by index name, like > > SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' > > > > We also have time of start gathering statistics, but I'm not sure that it > > should be exposed here. > > > > > > WDYT about proposed format for the SQL views? > > > > > > > > > > > > > > -- > > Живи с улыбкой! :D > > > -- Живи с улыбкой! :D |
Wouldn't disk_read and memory_read be better naming?
- Denis On Wed, Jan 16, 2019 at 7:38 AM Юрий <[hidden email]> wrote: > Denis, > > Physical reads is load page from storage to memory. > Logical reads is read page which already in memory. > > We gather IO statistics on CACHE_GROUP level due to Ignite use one page to > keep all caches related to one cache group. Unfortunately gathering on > table level will be expensive due to the reason. That's way name of view > contains words cache and groups. > > ср, 16 янв. 2019 г. в 17:52, Denis Magda <[hidden email]>: > > > Yury, > > > > How do we differentiate between logical and physical reads? > > > > Also, it looks counter-intuitive when "CACHE" is used in the name of the > > views for SQL table related statistics. It's still hard to explain the > user > > the relations between caches and tables. Hopefully, this will be fixed in > > 3.0 with renaming but as for the statistics can we use anything neutral > for > > the view names? > > > > - > > Denis > > > > > > On Tue, Jan 15, 2019 at 5:57 AM Юрий <[hidden email]> > wrote: > > > > > Hi Igniters! > > > > > > As part of IEP-27 > > > < > > > > > > https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics > > > > > > > we > > > already gathering IO statistics and expose it through JMX. > > > > > > User who use only SQL should have access to the statistics also. So > > let's > > > discuss about how such SQL view should looks. > > > > > > My proposal it is two SQL views: > > > 1) STATIO_CACHE_GRP > > > > > > cache_grp_name - Name of cache group > > > physical_read - Number of physical read of pages > > > logical_read - Number of logical read of pages > > > > > > > > > The view can be filtered by name, like SELECT * from > > > IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' > > > 2) STATIO_IDX > > > > > > cache_grp_name - Name of cache group > > > > > > idx_name - Name of index > > > physical_read - Common number of physical reads of pages > > for > > > the index > > > logical_read - Common number of logical reads of pages > > for > > > the index > > > > > > leaf_logical_read - Number of logical reads of index leaf > pages > > > > > > leaf_physical_read - Number of physical reads of index leaf pages > > > > > > inner_logical_read - Number of logical reads of index inner > pages > > > > > > inner_physical_read - Number of physical reads of index leaf pages > > > > > > > > > The view can be filtered by cache group name or by index name, > like > > > SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' > > > > > > We also have time of start gathering statistics, but I'm not sure that > it > > > should be exposed here. > > > > > > > > > WDYT about proposed format for the SQL views? > > > > > > > > > > > > > > > > > > > > > -- > > > Живи с улыбкой! :D > > > > > > > > -- > Живи с улыбкой! :D > |
Denis,
As I understand logical and physical IO operations are standard terms which use other DB vendors, for example Oracle - http://www.dba-oracle.com/t_oracle_logical_io_physical_io.htm , sometime logical IO operation called as 'page/buffer hit'. So I think current naming is ok. WDYT? ср, 16 янв. 2019 г. в 18:44, Denis Magda <[hidden email]>: > Wouldn't disk_read and memory_read be better naming? > > - > Denis > > > On Wed, Jan 16, 2019 at 7:38 AM Юрий <[hidden email]> wrote: > > > Denis, > > > > Physical reads is load page from storage to memory. > > Logical reads is read page which already in memory. > > > > We gather IO statistics on CACHE_GROUP level due to Ignite use one page > to > > keep all caches related to one cache group. Unfortunately gathering on > > table level will be expensive due to the reason. That's way name of view > > contains words cache and groups. > > > > ср, 16 янв. 2019 г. в 17:52, Denis Magda <[hidden email]>: > > > > > Yury, > > > > > > How do we differentiate between logical and physical reads? > > > > > > Also, it looks counter-intuitive when "CACHE" is used in the name of > the > > > views for SQL table related statistics. It's still hard to explain the > > user > > > the relations between caches and tables. Hopefully, this will be fixed > in > > > 3.0 with renaming but as for the statistics can we use anything neutral > > for > > > the view names? > > > > > > - > > > Denis > > > > > > > > > On Tue, Jan 15, 2019 at 5:57 AM Юрий <[hidden email]> > > wrote: > > > > > > > Hi Igniters! > > > > > > > > As part of IEP-27 > > > > < > > > > > > > > > > https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics > > > > > > > > > we > > > > already gathering IO statistics and expose it through JMX. > > > > > > > > User who use only SQL should have access to the statistics also. So > > > let's > > > > discuss about how such SQL view should looks. > > > > > > > > My proposal it is two SQL views: > > > > 1) STATIO_CACHE_GRP > > > > > > > > cache_grp_name - Name of cache group > > > > physical_read - Number of physical read of pages > > > > logical_read - Number of logical read of pages > > > > > > > > > > > > The view can be filtered by name, like SELECT * from > > > > IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' > > > > 2) STATIO_IDX > > > > > > > > cache_grp_name - Name of cache group > > > > > > > > idx_name - Name of index > > > > physical_read - Common number of physical reads of > pages > > > for > > > > the index > > > > logical_read - Common number of logical reads of > pages > > > for > > > > the index > > > > > > > > leaf_logical_read - Number of logical reads of index leaf > > pages > > > > > > > > leaf_physical_read - Number of physical reads of index leaf > pages > > > > > > > > inner_logical_read - Number of logical reads of index inner > > pages > > > > > > > > inner_physical_read - Number of physical reads of index leaf > pages > > > > > > > > > > > > The view can be filtered by cache group name or by index name, > > like > > > > SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' > > > > > > > > We also have time of start gathering statistics, but I'm not sure > that > > it > > > > should be exposed here. > > > > > > > > > > > > WDYT about proposed format for the SQL views? > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > Живи с улыбкой! :D > > > > > > > > > > > > > -- > > Живи с улыбкой! :D > > > -- Живи с улыбкой! :D |
Yury,
Thanks for the extra details. Agree that we should reuse logical and physical terms if there are already in use by similar technologies. - Denis On Thu, Jan 17, 2019 at 12:52 AM Юрий <[hidden email]> wrote: > Denis, > > As I understand logical and physical IO operations are standard terms which > use other DB vendors, for example Oracle - > http://www.dba-oracle.com/t_oracle_logical_io_physical_io.htm , sometime > logical IO operation called as 'page/buffer hit'. > > So I think current naming is ok. > > WDYT? > > > > > > ср, 16 янв. 2019 г. в 18:44, Denis Magda <[hidden email]>: > > > Wouldn't disk_read and memory_read be better naming? > > > > - > > Denis > > > > > > On Wed, Jan 16, 2019 at 7:38 AM Юрий <[hidden email]> > wrote: > > > > > Denis, > > > > > > Physical reads is load page from storage to memory. > > > Logical reads is read page which already in memory. > > > > > > We gather IO statistics on CACHE_GROUP level due to Ignite use one page > > to > > > keep all caches related to one cache group. Unfortunately gathering on > > > table level will be expensive due to the reason. That's way name of > view > > > contains words cache and groups. > > > > > > ср, 16 янв. 2019 г. в 17:52, Denis Magda <[hidden email]>: > > > > > > > Yury, > > > > > > > > How do we differentiate between logical and physical reads? > > > > > > > > Also, it looks counter-intuitive when "CACHE" is used in the name of > > the > > > > views for SQL table related statistics. It's still hard to explain > the > > > user > > > > the relations between caches and tables. Hopefully, this will be > fixed > > in > > > > 3.0 with renaming but as for the statistics can we use anything > neutral > > > for > > > > the view names? > > > > > > > > - > > > > Denis > > > > > > > > > > > > On Tue, Jan 15, 2019 at 5:57 AM Юрий <[hidden email]> > > > wrote: > > > > > > > > > Hi Igniters! > > > > > > > > > > As part of IEP-27 > > > > > < > > > > > > > > > > > > > > > https://cwiki.apache.org/confluence/display/IGNITE/IEP-27%3A+Page+IO+statistics > > > > > > > > > > > we > > > > > already gathering IO statistics and expose it through JMX. > > > > > > > > > > User who use only SQL should have access to the statistics also. > So > > > > let's > > > > > discuss about how such SQL view should looks. > > > > > > > > > > My proposal it is two SQL views: > > > > > 1) STATIO_CACHE_GRP > > > > > > > > > > cache_grp_name - Name of cache group > > > > > physical_read - Number of physical read of pages > > > > > logical_read - Number of logical read of pages > > > > > > > > > > > > > > > The view can be filtered by name, like SELECT * from > > > > > IGNITE.STATIO_CACHE_GRP where cache_grp_name='cache1' > > > > > 2) STATIO_IDX > > > > > > > > > > cache_grp_name - Name of cache group > > > > > > > > > > idx_name - Name of index > > > > > physical_read - Common number of physical reads of > > pages > > > > for > > > > > the index > > > > > logical_read - Common number of logical reads of > > pages > > > > for > > > > > the index > > > > > > > > > > leaf_logical_read - Number of logical reads of index leaf > > > pages > > > > > > > > > > leaf_physical_read - Number of physical reads of index leaf > > pages > > > > > > > > > > inner_logical_read - Number of logical reads of index inner > > > pages > > > > > > > > > > inner_physical_read - Number of physical reads of index leaf > > pages > > > > > > > > > > > > > > > The view can be filtered by cache group name or by index name, > > > like > > > > > SELECT * from IGNITE.STATIO_IDX where idx_name='cache1_name_idx' > > > > > > > > > > We also have time of start gathering statistics, but I'm not sure > > that > > > it > > > > > should be exposed here. > > > > > > > > > > > > > > > WDYT about proposed format for the SQL views? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > Живи с улыбкой! :D > > > > > > > > > > > > > > > > > > -- > > > Живи с улыбкой! :D > > > > > > > > -- > Живи с улыбкой! :D > |
Free forum by Nabble | Edit this page |