Re: About index inline size of primary key
Hello! I actually think that you should be conservative with that setting, since large inline size will make your B+-tree taller which may lead to worse performance. Setting it to some good default would make sense. It depends on structure and selectivity of your primary keys. Regards, -- Ilya Kasnacheev пн, 18 мая 2020 г. в 16:53, 18624049226 <18624049...@163.com>: > Hi Ilya, > > Then I think this property should be ready in the production planning > stage. > So the problem is, if there are many tables in the system and many tables > have combined primary keys, should this attribute be configured with a > relatively large value at the beginning, such as 40 and 50? What's the > negative impact? Instead of waiting for notifications from the log. > 在 2020/5/18 下午9:45, Ilya Kasnacheev 写道: > > Hello! > > I think this is correct. Moreover, setting this property on a part of > cluster may lead to problems of its own. It is recommended to set ot before > deploying a cluster. > > Regards, > -- > Ilya Kasnacheev > > > пн, 18 мая 2020 г. в 16:40, 18624049226 <18624049...@163.com>: > >> Hi Ilya, >> >> Thank you very much for your reply! >> I wonder if the existing primary key index will not be rebuilt after this >> property is configured? Will only affect newly created tables in the future? >> 在 2020/5/18 下午9:25, Ilya Kasnacheev 写道: >> >> Hello! >> >> Yes, it will have global impact on all indexes on primary keys, and all >> indexes created without INLINE SIZE clause. >> >> Regards, >> -- >> Ilya Kasnacheev >> >> >> чт, 14 мая 2020 г. в 16:53, 38797715 <38797...@qq.com>: >> >>> Hi, >>> >>> I see this property. >>> If this property is configured, it has a global impact? What is the >>> influence range of this parameter? >>> 在 2020/5/14 下午9:41, Stephen Darlington 写道: >>> >>> Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE >>> property: >>> >>> ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 >>> >>> Regards, >>> Stephen >>> >>> On 14 May 2020, at 14:23, 38797715 <38797...@qq.com> wrote: >>> >>> Hi, >>> >>> Today, I see the following information in the log: >>> [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed >>> columns of a row cannot be fully inlined into index what may lead to >>> slowdown due to additional data page reads, increase index inline size >>> if needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with >>> recommended size (be aware it will be used by default for all indexes >>> without explicit inline size)) [cacheName=NEW, tableName=NEW, >>> idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, >>> curSize=10, recommendedInlineSize=33] >>> >>> I know that the create index statement has an inline_ size clause, but I >>> want to ask, how to adjust the inline size of primary key? >>> >>> >>> >>>
Re: About index inline size of primary key
Hi Ilya,
Then I think this property should be ready in the production planning stage.
So the problem is, if there are many tables in the system and many
tables have combined primary keys, should this attribute be configured
with a relatively large value at the beginning, such as 40 and 50?
What's the negative impact? Instead of waiting for notifications from
the log.
在 2020/5/18 下午9:45, Ilya Kasnacheev 写道:
Hello!
I think this is correct. Moreover, setting this property on a part of
cluster may lead to problems of its own. It is recommended to set ot
before deploying a cluster.
Regards,
--
Ilya Kasnacheev
пн, 18 мая 2020 г. в 16:40, 18624049226 <18624049...@163.com
<mailto:18624049...@163.com>>:
Hi Ilya,
Thank you very much for your reply!
I wonder if the existing primary key index will not be rebuilt
after this property is configured? Will only affect newly created
tables in the future?
在 2020/5/18 下午9:25, Ilya Kasnacheev 写道:
Hello!
Yes, it will have global impact on all indexes on primary keys,
and all indexes created without INLINE SIZE clause.
Regards,
--
Ilya Kasnacheev
чт, 14 мая 2020 г. в 16:53, 38797715 <38797...@qq.com
<mailto:38797...@qq.com>>:
Hi,
I see this property.
If this property is configured, it has a global impact? What
is the influence range of this parameter?
在 2020/5/14 下午9:41, Stephen Darlington 写道:
Exactly as the warning says, with
the IGNITE_MAX_INDEX_PAYLOAD_SIZE property:
./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33
Regards,
Stephen
On 14 May 2020, at 14:23, 38797715 <38797...@qq.com
<mailto:38797...@qq.com>> wrote:
Hi,
Today, I see the following information in the log:
[2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing]
Indexed columns of a row cannot be fully inlined into index
what may lead to slowdown due to additional data page
reads, increase index inline size if needed (set system
property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended
size (be aware it will be used by default for all indexes
without explicit inline size)) [cacheName=NEW,
tableName=NEW, idxName=_key_PK, idxCols=(CO_NUM, CUST_ID),
idxType=PRIMARY KEY, curSize=10, recommendedInlineSize=33]
I know that the create index statement has an inline_ size
clause, but I want to ask, how to adjust theinline size of
primary key?
Re: About index inline size of primary key
Hello! I think this is correct. Moreover, setting this property on a part of cluster may lead to problems of its own. It is recommended to set ot before deploying a cluster. Regards, -- Ilya Kasnacheev пн, 18 мая 2020 г. в 16:40, 18624049226 <18624049...@163.com>: > Hi Ilya, > > Thank you very much for your reply! > I wonder if the existing primary key index will not be rebuilt after this > property is configured? Will only affect newly created tables in the future? > 在 2020/5/18 下午9:25, Ilya Kasnacheev 写道: > > Hello! > > Yes, it will have global impact on all indexes on primary keys, and all > indexes created without INLINE SIZE clause. > > Regards, > -- > Ilya Kasnacheev > > > чт, 14 мая 2020 г. в 16:53, 38797715 <38797...@qq.com>: > >> Hi, >> >> I see this property. >> If this property is configured, it has a global impact? What is the >> influence range of this parameter? >> 在 2020/5/14 下午9:41, Stephen Darlington 写道: >> >> Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE >> property: >> >> ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 >> >> Regards, >> Stephen >> >> On 14 May 2020, at 14:23, 38797715 <38797...@qq.com> wrote: >> >> Hi, >> >> Today, I see the following information in the log: >> [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed >> columns of a row cannot be fully inlined into index what may lead to >> slowdown due to additional data page reads, increase index inline size >> if needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with >> recommended size (be aware it will be used by default for all indexes >> without explicit inline size)) [cacheName=NEW, tableName=NEW, >> idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, >> curSize=10, recommendedInlineSize=33] >> >> I know that the create index statement has an inline_ size clause, but I >> want to ask, how to adjust the inline size of primary key? >> >> >> >>
Re: About index inline size of primary key
Hi Ilya, Thank you very much for your reply! I wonder if the existing primary key index will not be rebuilt after this property is configured? Will only affect newly created tables in the future? 在 2020/5/18 下午9:25, Ilya Kasnacheev 写道: Hello! Yes, it will have global impact on all indexes on primary keys, and all indexes created without INLINE SIZE clause. Regards, -- Ilya Kasnacheev чт, 14 мая 2020 г. в 16:53, 38797715 <38797...@qq.com <mailto:38797...@qq.com>>: Hi, I see this property. If this property is configured, it has a global impact? What is the influence range of this parameter? 在 2020/5/14 下午9:41, Stephen Darlington 写道: Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE property: ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 Regards, Stephen On 14 May 2020, at 14:23, 38797715 <38797...@qq.com <mailto:38797...@qq.com>> wrote: Hi, Today, I see the following information in the log: [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed columns of a row cannot be fully inlined into index what may lead to slowdown due to additional data page reads, increase index inline size if needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended size (be aware it will be used by default for all indexes without explicit inline size)) [cacheName=NEW, tableName=NEW, idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, curSize=10, recommendedInlineSize=33] I know that the create index statement has an inline_ size clause, but I want to ask, how to adjust theinline size of primary key?
Re: About index inline size of primary key
Hello! Yes, it will have global impact on all indexes on primary keys, and all indexes created without INLINE SIZE clause. Regards, -- Ilya Kasnacheev чт, 14 мая 2020 г. в 16:53, 38797715 <38797...@qq.com>: > Hi, > > I see this property. > If this property is configured, it has a global impact? What is the > influence range of this parameter? > 在 2020/5/14 下午9:41, Stephen Darlington 写道: > > Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE > property: > > ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 > > Regards, > Stephen > > On 14 May 2020, at 14:23, 38797715 <38797...@qq.com> wrote: > > Hi, > > Today, I see the following information in the log: > [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed > columns of a row cannot be fully inlined into index what may lead to > slowdown due to additional data page reads, increase index inline size if > needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended > size (be aware it will be used by default for all indexes without > explicit inline size)) [cacheName=NEW, tableName=NEW, idxName=_key_PK, > idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, curSize=10, recommendedInl > ineSize=33] > > I know that the create index statement has an inline_ size clause, but I > want to ask, how to adjust the inline size of primary key? > > > >
Re: About index inline size of primary key
Hi, I see this property. If this property is configured, it has a global impact? What is the influence range of this parameter? 在 2020/5/14 下午9:41, Stephen Darlington 写道: Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE property: ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 Regards, Stephen On 14 May 2020, at 14:23, 38797715 <38797...@qq.com <mailto:38797...@qq.com>> wrote: Hi, Today, I see the following information in the log: [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed columns of a row cannot be fully inlined into index what may lead to slowdown due to additional data page reads, increase index inline size if needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended size (be aware it will be used by default for all indexes without explicit inline size)) [cacheName=NEW, tableName=NEW, idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, curSize=10, recommendedInlineSize=33] I know that the create index statement has an inline_ size clause, but I want to ask, how to adjust theinline size of primary key?
Re: About index inline size of primary key
Exactly as the warning says, with the IGNITE_MAX_INDEX_PAYLOAD_SIZE property: ./ignite.sh -J-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=33 Regards, Stephen > On 14 May 2020, at 14:23, 38797715 <38797...@qq.com> wrote: > > Hi, > > Today, I see the following information in the log: > > [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed > columns of a row cannot be fully inlined into index what may lead to slowdown > due to additional data page reads, increase index inline size if needed (set > system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended size (be aware > it will be used by default for all indexes without explicit inline size)) > [cacheName=NEW, tableName=NEW, idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), > idxType=PRIMARY KEY, curSize=10, recommendedInlineSize=33] > > I know that the create index statement has an inline_ size clause, but I want > to ask, how to adjust the inline size of primary key? >
About index inline size of primary key
Hi, Today, I see the following information in the log: [2020-05-14T16:42:04,346][WARN][query-#7759][IgniteH2Indexing] Indexed columns of a row cannot be fully inlined into index what may lead to slowdown due to additional data page reads, increase index inline size if needed (set system property IGNITE_MAX_INDEX_PAYLOAD_SIZE with recommended size (be aware it will be used by default for all indexes without explicit inline size)) [cacheName=NEW, tableName=NEW, idxName=_key_PK, idxCols=(CO_NUM, CUST_ID), idxType=PRIMARY KEY, curSize=10, recommendedInlineSize=33] I know that the create index statement has an inline_ size clause, but I want to ask, how to adjust the inline size of primary key?
Re: Is there a way to re-set the index inline size at runtime?
thank you very much! Thanks, Ken Cheng On Mon, Sep 23, 2019 at 8:29 PM Igor Belyakov wrote: > Hi, > > Increasing index inline size can improve performance in cases when you're > using variable-size columns, as described here: > https://apacheignite-sql.readme.io/docs/create-index#section-index-inlining > > Also modifying inline size will require entire index rebuild, which is > equals to index recreation. Due to this, you should use DROP/CREATE > commands for changing index inline size. > > Regards, > Igor > > On Sun, Sep 22, 2019 at 6:12 PM Ken Cheng wrote: > >> per my understanding increase the index inline size can improve query >> performance, so is there a way to increase the index inline size at >> runtime? >> my guess is that drop and re-create index will cost the whole cluster a >> lot. >> is that true? >> >> Thanks, >> Ken Cheng >> >
Re: Is there a way to re-set the index inline size at runtime?
Hi, Increasing index inline size can improve performance in cases when you're using variable-size columns, as described here: https://apacheignite-sql.readme.io/docs/create-index#section-index-inlining Also modifying inline size will require entire index rebuild, which is equals to index recreation. Due to this, you should use DROP/CREATE commands for changing index inline size. Regards, Igor On Sun, Sep 22, 2019 at 6:12 PM Ken Cheng wrote: > per my understanding increase the index inline size can improve query > performance, so is there a way to increase the index inline size at > runtime? > my guess is that drop and re-create index will cost the whole cluster a > lot. > is that true? > > Thanks, > Ken Cheng >
Is there a way to re-set the index inline size at runtime?
per my understanding increase the index inline size can improve query performance, so is there a way to increase the index inline size at runtime? my guess is that drop and re-create index will cost the whole cluster a lot. is that true? Thanks, Ken Cheng
Re: Index inline size
Depends on the use case. Sometimes you want to save the memory as much as possible, and then you would use a lower inline size. However, in most cases you actually need a higher value because that will greatly improve the performance. Starting 2.7 there are warnings with a recommended size (calculated based on your actual data) and a way to set it. A couple of ways to change inline size are missing in the warnings though - check https://issues.apache.org/jira/browse/IGNITE-11355. Stan colinc wrote > The documentation that you referenced states that the > IGNITE_MAX_INDEX_PAYLOAD_SIZE system property defines the default max - > and > that this defaults to 10. > > Since it's only a maximum value, is there any reason why it can't be a bit > higher - say 100? Or is it strongly encouraged to keep indexed fields > shorter than this? > > Regards, > Colin. > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Index inline size
The documentation that you referenced states that the IGNITE_MAX_INDEX_PAYLOAD_SIZE system property defines the default max - and that this defaults to 10. Since it's only a maximum value, is there any reason why it can't be a bit higher - say 100? Or is it strongly encouraged to keep indexed fields shorter than this? Regards, Colin. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Index inline size
Prasad,
By default *QuerySqlField#inlineSize *is equal to -1, which means, that it
will be chosen automatically.
*CacheConfiguration#setSqlIndexMaxInlineSize *specifies the maximal
automatically calculated
inline size for a cache.
But if *QuerySqlField#inlineSize *is not -1, then it will be used
regardless of the configured maximum.
It should only be less than 2048. Otherwise 2048 will be used.
Judging by the warning message, value 10 is used for the inline size.
Did you specify it manually or was it calculated automatically?
Index inlining documentation:
https://apacheignite-sql.readme.io/docs/create-index#section-index-inlining
Denis
ср, 26 дек. 2018 г. в 18:27, Prasad Bhalerao :
> Hi,
>
> I have set sqlIndexMaxInline size in cache configuration level as follows.
>
> cacheCfg.setSqlIndexMaxInlineSize(100);
>
> But still I am getting following warning message in log.
>
> WARN o.a.i.i.p.q.h2.database.H2TreeIndex - Indexed
> columns of a row cannot be fully inlined into index what may lead to
> slowdown due to additional data page reads, increase index inline size if
> needed (use INLINE_SIZE option for CREATE INDEX command,
> QuerySqlField.inlineSize for annotated classes, or QueryIndex.inlineSize
> for explicit QueryEntity configuration) [cacheName=USERACCOUNTDATA,
> tableName=USER_ACCOUNT_CACHE, idxName=USER_ACCOUNT_IDX4,
> idxCols=(SUBSCRIPTIONID, UNITID, _KEY, AFFINITYID), idxType=SECONDARY,
> curSize=10, recommendedInlineSize=83]
>
> 1) Is it necessary to set inline size using @QuerySqlField annotation?
>
> 2) How do I set inline size in case of group index in following case?
> Do I need to set inline property inside each @QuerySqlField annotation?
>
> public class Person implements Serializable {
> /** Indexed in a group index with "salary". */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(
> name = "age_salary_idx", order = 0, descending = true)})
> private int age;
>
> /** Indexed separately and in a group index with "age". */
> @QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
> name = "age_salary_idx", order = 3)})
> private double salary;
> }
>
>
>
>
>
>
>
> Thanks,
> Prasad
>
Index inline size
Hi,
I have set sqlIndexMaxInline size in cache configuration level as follows.
cacheCfg.setSqlIndexMaxInlineSize(100);
But still I am getting following warning message in log.
WARN o.a.i.i.p.q.h2.database.H2TreeIndex - Indexed
columns of a row cannot be fully inlined into index what may lead to
slowdown due to additional data page reads, increase index inline size if
needed (use INLINE_SIZE option for CREATE INDEX command,
QuerySqlField.inlineSize for annotated classes, or QueryIndex.inlineSize
for explicit QueryEntity configuration) [cacheName=USERACCOUNTDATA,
tableName=USER_ACCOUNT_CACHE, idxName=USER_ACCOUNT_IDX4,
idxCols=(SUBSCRIPTIONID, UNITID, _KEY, AFFINITYID), idxType=SECONDARY,
curSize=10, recommendedInlineSize=83]
1) Is it necessary to set inline size using @QuerySqlField annotation?
2) How do I set inline size in case of group index in following case?
Do I need to set inline property inside each @QuerySqlField annotation?
public class Person implements Serializable {
/** Indexed in a group index with "salary". */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(
name = "age_salary_idx", order = 0, descending = true)})
private int age;
/** Indexed separately and in a group index with "age". */
@QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
name = "age_salary_idx", order = 3)})
private double salary;
}
Thanks,
Prasad
