About index inline size of primary key

classic Classic list List threaded Threaded
8 messages Options
38797715 38797715
Reply | Threaded
Open this post in threaded view
|

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?

stephendarlington stephendarlington
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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?



38797715 38797715
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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?



ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:

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 <[hidden email]> 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?



18624049226 18624049226
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:

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 <[hidden email]> 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?



ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:

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 <[hidden email]>:

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 <[hidden email]> 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?



18624049226 18624049226
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:

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 <[hidden email]>:

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 <[hidden email]> 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?



ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]>:

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 <[hidden email]>:

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 <[hidden email]>:

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 <[hidden email]> 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?