You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by Юрий <ju...@gmail.com> on 2019/01/15 13:57:00 UTC
SQL views for IO statistics
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
Re: SQL views for IO statistics
Posted by Denis Magda <dm...@apache.org>.
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 Юрий <ju...@gmail.com> 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 <dm...@apache.org>:
>
> > Wouldn't disk_read and memory_read be better naming?
> >
> > -
> > Denis
> >
> >
> > On Wed, Jan 16, 2019 at 7:38 AM Юрий <ju...@gmail.com>
> 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 <dm...@apache.org>:
> > >
> > > > 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 Юрий <ju...@gmail.com>
> > > 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
>
Re: SQL views for IO statistics
Posted by Юрий <ju...@gmail.com>.
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 <dm...@apache.org>:
> Wouldn't disk_read and memory_read be better naming?
>
> -
> Denis
>
>
> On Wed, Jan 16, 2019 at 7:38 AM Юрий <ju...@gmail.com> 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 <dm...@apache.org>:
> >
> > > 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 Юрий <ju...@gmail.com>
> > 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
Re: SQL views for IO statistics
Posted by Denis Magda <dm...@apache.org>.
Wouldn't disk_read and memory_read be better naming?
-
Denis
On Wed, Jan 16, 2019 at 7:38 AM Юрий <ju...@gmail.com> 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 <dm...@apache.org>:
>
> > 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 Юрий <ju...@gmail.com>
> 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
>
Re: SQL views for IO statistics
Posted by Юрий <ju...@gmail.com>.
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 <dm...@apache.org>:
> 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 Юрий <ju...@gmail.com> 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
Re: SQL views for IO statistics
Posted by Denis Magda <dm...@apache.org>.
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 Юрий <ju...@gmail.com> 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
>