[PHOENIX-6709] Create new command to update cache size of an existing sequence.

• Type: New Feature
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Assignee: Unassigned
• Reporter: Rushabh Shah
• Created: 09/May/22 21:33
• Updated: 10/May/22 16:42

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