[
https://issues.apache.org/jira/browse/PHOENIX-6709?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rushabh Shah updated PHOENIX-6709:
----------------------------------
Description:
Create a new command something like:
ALTER SEQUENCE <sequence-name> SET CACHE_SIZE=1000
We faced this issue in our production cluster. We create sequence with default
caching size (i.e 100). We have a use case that runs around 440k increment
calls per 5 mins which around 1500 increment ops per second. This is consuming
all the active handler threads. Depending on the load, this causes saturation
of handler threads for hours prevent any other operation on system tables.
We decided to increase the cache size for that particular sequence to 1000
assuming this will reduce the rpc count by 90% and the change did help.
We ran the following query:
UPSERT INTO SYSTEM."SEQUENCE"(TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME,
CACHE_SIZE) SELECT TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME, 1000 FROM
SYSTEM."SEQUENCE" WHERE SEQUENCE_SCHEMA='<sequence-schema>' AND
SEQUENCE_NAME='<sequence-name>';
Instead of this upsert query we can run some new phoenix command like:
ALTER SEQUENCE <sequence-name> SET CACHE_SIZE=1000
[~gjacoby]
was:
Create a new command something like:
UPDATE SEQUENCE <sequence-name> SET CACHE_SIZE=1000
We faced this issue in our production cluster. We create sequence with default
caching size (i.e 100). We have a use case that runs around 440k increment
calls per 5 mins which around 1500 increment ops per second. This is consuming
all the active handler threads. Depending on the load, this causes saturation
of handler threads for hours prevent any other operation on system tables.
We decided to increase the cache size for that particular sequence to 1000
assuming this will reduce the rpc count by 90% and the change did help.
We ran the following query:
UPSERT INTO SYSTEM."SEQUENCE"(TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME,
CACHE_SIZE) SELECT TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME, 1000 FROM
SYSTEM."SEQUENCE" WHERE SEQUENCE_SCHEMA='<sequence-schema>' AND
SEQUENCE_NAME='<sequence-name>';
Instead of this upsert query we can run some new phoenix command like:
UPDATE SEQUENCE <sequence-name> SET CACHE_SIZE=1000
[~gjacoby]
> Create new command to update cache size of an existing sequence.
> ----------------------------------------------------------------
>
> Key: PHOENIX-6709
> URL: https://issues.apache.org/jira/browse/PHOENIX-6709
> Project: Phoenix
> Issue Type: New Feature
> Components: core
> Reporter: Rushabh Shah
> Priority: Major
>
> Create a new command something like:
> ALTER SEQUENCE <sequence-name> SET CACHE_SIZE=1000
> We faced this issue in our production cluster. We create sequence with
> default caching size (i.e 100). We have a use case that runs around 440k
> increment calls per 5 mins which around 1500 increment ops per second. This
> is consuming all the active handler threads. Depending on the load, this
> causes saturation of handler threads for hours prevent any other operation on
> system tables.
> We decided to increase the cache size for that particular sequence to 1000
> assuming this will reduce the rpc count by 90% and the change did help.
> We ran the following query:
> UPSERT INTO SYSTEM."SEQUENCE"(TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME,
> CACHE_SIZE) SELECT TENANT_ID, SEQUENCE_SCHEMA, SEQUENCE_NAME, 1000 FROM
> SYSTEM."SEQUENCE" WHERE SEQUENCE_SCHEMA='<sequence-schema>' AND
> SEQUENCE_NAME='<sequence-name>';
> Instead of this upsert query we can run some new phoenix command like:
> ALTER SEQUENCE <sequence-name> SET CACHE_SIZE=1000
> [~gjacoby]
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
