You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Michael McAllister <mm...@homeaway.com> on 2015/09/22 20:14:08 UTC
Number of regions in SYSTEM.SEQUENCE
Hi
By default SYSTEM.SEQUENCE is installed with 256 regions. In an environment where you don’t have a large number of tables and regions (yet), the end result of this seems to be that with hbase balance_switch=true, you end up with a lot of region servers with nothing but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our cluster.
Have there been any best practices developed as to how to deal with this situation?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D080DC.77AD4930]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Number of regions in SYSTEM.SEQUENCE
Posted by Michael McAllister <mm...@homeaway.com>.
Thanks, that one worked :-)
On Sep 22, 2015, at 3:28 PM, James Heather <ja...@mendeley.com>> wrote:
Try the one you missed:
SYSTEM."SEQUENCE"
i.e., quote the bits separately (but SYSTEM doesn't need quoting), and put it in caps.
James
On 22/09/15 21:18, Michael McAllister wrote:
More failed attempts ...
0: jdbc:phoenix:redacted,> select count(*) from system."sequence";
Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.sequence (state=42M03,code=1012)
0: jdbc:phoenix:redacted,> select count(*) from "system.sequence";
Error: ERROR 1012 (42M03): Table undefined. tableName=system.sequence (state=42M03,code=1012)
0: jdbc:phoenix:redacted,> select count(*) from "SYSTEM.SEQUENCE";
Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.SEQUENCE (state=42M03,code=1012)
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 3:14 PM, James Heather <ja...@mendeley.com>> wrote:
I don't think it's trying to stop you looking inside the table. I think it's complaining that SEQUENCE is a keyword, and shouldn't be appearing there.
You could try quoting it.
James
On 22/09/15 21:11, Michael McAllister wrote:
OK - so the traditional methods of recreating sequences, that makes sense.
Interestingly btw, at least from within Phoenix I can’t see the content of SYSTEM.SEQUENCE. I get the following error:-
0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
I do understand this is a system table, but it would be nice to see inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 2:47 PM, James Heather <ja...@mendeley.com>> wrote:
If no one else will be hitting the table while you complete the operation, and if you don't mind about missing a few sequence values (i.e., having a gap), you should just need the following.
SELECT NEXT VALUE FOR sequencename FROM sometable;
That will tell you the next value the sequence wants to hand out.
DROP SEQUENCE sequencename;
Then reconnect with the property as given below, and
CREATE SEQUENCE sequencename START WITH n;
where n is the value you retrieved in the first step.
The reason this might cause gaps is that client connections will cache sequence values, so the one you retrieve might not actually be the first one that hasn't been used; it'll just be the first one cached by the connection you're using. But if you do it this way, and nothing else is connected in the meantime, then you won't get any duplicates.
As far as I can see, if you're the only connected client, this should do it with no gaps: no other clients will have cached any sequence values, so you'll retrieve the first one your connection has cached (which will be the first one available), and then that's where your sequence will start when you recreate the sequence. But I'm not absolutely certain about that, and you might want to try some experiments.
If the sequence is being used for a primary key column (a sort of auto_increment), then the other option is to
SELECT MAX(id) FROM sometable;
and then add one to this value to determine where the recreated sequence should start. That will ensure no gaps.
James
On 22/09/15 19:47, Michael McAllister wrote:
Mujtaba
Thanks for this information. Seeing as I am using Phoenix 4.2, what is the safe and approved sequence of steps to drop this table and recreate it as you mention? Additionally, how do we ensure we don’t lose sequence data?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> <https://h.a/mikewebex> https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <<m...@apache.org>> wrote:
Since Phoenix 4.5.x default has been changed for phoenix.sequence.saltBuckets to not split sequence table. See this<https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d> commit. For older versions you can drop sequence table and reconnect with setting client side phoenix.sequence.saltBuckets property.
On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister <<m...@homeaway.com>> wrote:
Hi
By default SYSTEM.SEQUENCE is installed with 256 regions. In an environment where you don’t have a large number of tables and regions (yet), the end result of this seems to be that with hbase balance_switch=true, you end up with a lot of region servers with nothing but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our cluster.
Have there been any best practices developed as to how to deal with this situation?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447<tel:512.423.7447> | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> <https://h.a/mikewebex> https://h.a/mikewebex
<image002.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Number of regions in SYSTEM.SEQUENCE
Posted by James Heather <ja...@mendeley.com>.
Try the one you missed:
SYSTEM."SEQUENCE"
i.e., quote the bits separately (but SYSTEM doesn't need quoting), and
put it in caps.
James
On 22/09/15 21:18, Michael McAllister wrote:
> More failed attempts ...
>
> 0: jdbc:phoenix:redacted,> select count(*) from system."sequence";
> Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.sequence
> (state=42M03,code=1012)
> 0: jdbc:phoenix:redacted,> select count(*) from "system.sequence";
> Error: ERROR 1012 (42M03): Table undefined. tableName=system.sequence
> (state=42M03,code=1012)
> 0: jdbc:phoenix:redacted,> select count(*) from "SYSTEM.SEQUENCE";
> Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.SEQUENCE
> (state=42M03,code=1012)
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <ma...@HomeAway.com> | C:
> 512.423.7447 | skype: michael.mcallister.ha
> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>
>
> This electronic communication (including any attachment) is
> confidential. If you are not an intended recipient of this
> communication, please be advised that any disclosure, dissemination,
> distribution, copying or other use of this communication or any
> attachment is strictly prohibited. If you have received this
> communication in error, please notify the sender immediately by reply
> e-mail and promptly destroy all electronic and printed copies of this
> communication and any attachment.
>
>> On Sep 22, 2015, at 3:14 PM, James Heather
>> <james.heather@mendeley.com <ma...@mendeley.com>> wrote:
>>
>> I don't think it's trying to stop you looking inside the table. I
>> think it's complaining that SEQUENCE is a keyword, and shouldn't be
>> appearing there.
>>
>> You could try quoting it.
>>
>> James
>>
>> On 22/09/15 21:11, Michael McAllister wrote:
>>> OK - so the traditional methods of recreating sequences, that makes
>>> sense.
>>>
>>> Interestingly btw, at least from within Phoenix I can’t see the
>>> content of SYSTEM.SEQUENCE. I get the following error:-
>>>
>>> 0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
>>> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
>>> "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
>>>
>>> I do understand this is a system table, but it would be nice to see
>>> inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
>>>
>>> Michael McAllister
>>> Staff Data Warehouse Engineer | Decision Systems
>>> <ma...@HomeAway.com>mmcallister@HomeAway.com | C:
>>> 512.423.7447 | skype: michael.mcallister.ha
>>> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>>>
>>> <Mail Attachment.png>
>>> This electronic communication (including any attachment) is
>>> confidential. If you are not an intended recipient of this
>>> communication, please be advised that any disclosure, dissemination,
>>> distribution, copying or other use of this communication or any
>>> attachment is strictly prohibited. If you have received this
>>> communication in error, please notify the sender immediately by
>>> reply e-mail and promptly destroy all electronic and printed copies
>>> of this communication and any attachment.
>>>
>>>> On Sep 22, 2015, at 2:47 PM, James Heather
>>>> <james.heather@mendeley.com <ma...@mendeley.com>> wrote:
>>>>
>>>> If no one else will be hitting the table while you complete the
>>>> operation, and if you don't mind about missing a few sequence
>>>> values (i.e., having a gap), you should just need the following.
>>>>
>>>> SELECT NEXT VALUE FOR sequencename FROM sometable;
>>>>
>>>> That will tell you the next value the sequence wants to hand out.
>>>>
>>>> DROP SEQUENCE sequencename;
>>>>
>>>> Then reconnect with the property as given below, and
>>>>
>>>> CREATE SEQUENCE sequencename START WITH n;
>>>>
>>>> where n is the value you retrieved in the first step.
>>>>
>>>> The reason this might cause gaps is that client connections will
>>>> cache sequence values, so the one you retrieve might not actually
>>>> be the first one that hasn't been used; it'll just be the first one
>>>> cached by the connection you're using. But if you do it this way,
>>>> and nothing else is connected in the meantime, then you won't get
>>>> any duplicates.
>>>>
>>>> As far as I can see, if you're the only connected client, this
>>>> *should* do it with no gaps: no other clients will have cached any
>>>> sequence values, so you'll retrieve the first one your connection
>>>> has cached (which will be the first one available), and then that's
>>>> where your sequence will start when you recreate the sequence. But
>>>> I'm not absolutely certain about that, and you might want to try
>>>> some experiments.
>>>>
>>>> If the sequence is being used for a primary key column (a sort of
>>>> auto_increment), then the other option is to
>>>>
>>>> SELECT MAX(id) FROM sometable;
>>>>
>>>> and then add one to this value to determine where the recreated
>>>> sequence should start. That will ensure no gaps.
>>>>
>>>> James
>>>>
>>>>
>>>> On 22/09/15 19:47, Michael McAllister wrote:
>>>>> Mujtaba
>>>>>
>>>>> Thanks for this information. Seeing as I am using Phoenix 4.2,
>>>>> what is the safe and approved sequence of steps to drop this table
>>>>> and recreate it as you mention? Additionally, how do we ensure we
>>>>> don’t lose sequence data?
>>>>>
>>>>> Michael McAllister
>>>>> Staff Data Warehouse Engineer | Decision Systems
>>>>> <ma...@HomeAway.com>mmcallister@HomeAway.com | C:
>>>>> 512.423.7447 | skype: michael.mcallister.ha
>>>>> <ma...@hotmail.com> | webex:
>>>>> <https://h.a/mikewebex>https://h.a/mikewebex
>>>>>
>>>>> <Mail Attachment.png>
>>>>> This electronic communication (including any attachment) is
>>>>> confidential. If you are not an intended recipient of this
>>>>> communication, please be advised that any disclosure,
>>>>> dissemination, distribution, copying or other use of this
>>>>> communication or any attachment is strictly prohibited. If you
>>>>> have received this communication in error, please notify the
>>>>> sender immediately by reply e-mail and promptly destroy all
>>>>> electronic and printed copies of this communication and any
>>>>> attachment.
>>>>>
>>>>>> On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mujtaba@apache.org
>>>>>> <ma...@apache.org>> wrote:
>>>>>>
>>>>>> Since Phoenix 4.5.x default has been changed for
>>>>>> phoenix.sequence.saltBuckets to not split sequence table. See
>>>>>> this
>>>>>> <https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d>
>>>>>> commit. For older versions you can drop sequence table and
>>>>>> reconnect with setting client side phoenix.sequence.saltBuckets
>>>>>> property.
>>>>>>
>>>>>> On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister
>>>>>> <mmcallister@homeaway.com <ma...@homeaway.com>> wrote:
>>>>>>
>>>>>> Hi
>>>>>>
>>>>>> By default SYSTEM.SEQUENCE is installed with 256 regions. In
>>>>>> an environment where you don’t have a large number of tables
>>>>>> and regions (yet), the end result of this seems to be that
>>>>>> with hbase balance_switch=true, you end up with a lot of
>>>>>> region servers with nothing but empty SYSTEM.SEQUENCE regions
>>>>>> on them. That mans inefficient use of our cluster.
>>>>>>
>>>>>> Have there been any best practices developed as to how to
>>>>>> deal with this situation?
>>>>>>
>>>>>> Michael McAllister
>>>>>> Staff Data Warehouse Engineer | Decision Systems
>>>>>> <ma...@HomeAway.com>mmcallister@HomeAway.com |
>>>>>> C: 512.423.7447 <tel:512.423.7447> | skype:
>>>>>> michael.mcallister.ha <ma...@hotmail.com> | webex:
>>>>>> <https://h.a/mikewebex>https://h.a/mikewebex
>>>>>>
>>>>>> <image002.png>
>>>>>> This electronic communication (including any attachment) is
>>>>>> confidential. If you are not an intended recipient of this
>>>>>> communication, please be advised that any disclosure,
>>>>>> dissemination, distribution, copying or other use of this
>>>>>> communication or any attachment is strictly prohibited. If
>>>>>> you have received this communication in error, please notify
>>>>>> the sender immediately by reply e-mail and promptly destroy
>>>>>> all electronic and printed copies of this communication and
>>>>>> any attachment.
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Number of regions in SYSTEM.SEQUENCE
Posted by Michael McAllister <mm...@homeaway.com>.
More failed attempts ...
0: jdbc:phoenix:redacted,> select count(*) from system."sequence";
Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.sequence (state=42M03,code=1012)
0: jdbc:phoenix:redacted,> select count(*) from "system.sequence";
Error: ERROR 1012 (42M03): Table undefined. tableName=system.sequence (state=42M03,code=1012)
0: jdbc:phoenix:redacted,> select count(*) from "SYSTEM.SEQUENCE";
Error: ERROR 1012 (42M03): Table undefined. tableName=SYSTEM.SEQUENCE (state=42M03,code=1012)
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D080DC.77AD4930]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 3:14 PM, James Heather <ja...@mendeley.com>> wrote:
I don't think it's trying to stop you looking inside the table. I think it's complaining that SEQUENCE is a keyword, and shouldn't be appearing there.
You could try quoting it.
James
On 22/09/15 21:11, Michael McAllister wrote:
OK - so the traditional methods of recreating sequences, that makes sense.
Interestingly btw, at least from within Phoenix I can’t see the content of SYSTEM.SEQUENCE. I get the following error:-
0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
I do understand this is a system table, but it would be nice to see inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 2:47 PM, James Heather <ja...@mendeley.com>> wrote:
If no one else will be hitting the table while you complete the operation, and if you don't mind about missing a few sequence values (i.e., having a gap), you should just need the following.
SELECT NEXT VALUE FOR sequencename FROM sometable;
That will tell you the next value the sequence wants to hand out.
DROP SEQUENCE sequencename;
Then reconnect with the property as given below, and
CREATE SEQUENCE sequencename START WITH n;
where n is the value you retrieved in the first step.
The reason this might cause gaps is that client connections will cache sequence values, so the one you retrieve might not actually be the first one that hasn't been used; it'll just be the first one cached by the connection you're using. But if you do it this way, and nothing else is connected in the meantime, then you won't get any duplicates.
As far as I can see, if you're the only connected client, this should do it with no gaps: no other clients will have cached any sequence values, so you'll retrieve the first one your connection has cached (which will be the first one available), and then that's where your sequence will start when you recreate the sequence. But I'm not absolutely certain about that, and you might want to try some experiments.
If the sequence is being used for a primary key column (a sort of auto_increment), then the other option is to
SELECT MAX(id) FROM sometable;
and then add one to this value to determine where the recreated sequence should start. That will ensure no gaps.
James
On 22/09/15 19:47, Michael McAllister wrote:
Mujtaba
Thanks for this information. Seeing as I am using Phoenix 4.2, what is the safe and approved sequence of steps to drop this table and recreate it as you mention? Additionally, how do we ensure we don’t lose sequence data?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mu...@apache.org>> wrote:
Since Phoenix 4.5.x default has been changed for phoenix.sequence.saltBuckets to not split sequence table. See this<https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d> commit. For older versions you can drop sequence table and reconnect with setting client side phoenix.sequence.saltBuckets property.
On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister <mm...@homeaway.com>> wrote:
Hi
By default SYSTEM.SEQUENCE is installed with 256 regions. In an environment where you don’t have a large number of tables and regions (yet), the end result of this seems to be that with hbase balance_switch=true, you end up with a lot of region servers with nothing but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our cluster.
Have there been any best practices developed as to how to deal with this situation?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447<tel:512.423.7447> | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> <https://h.a/mikewebex> https://h.a/mikewebex
<image002.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Number of regions in SYSTEM.SEQUENCE
Posted by James Heather <ja...@mendeley.com>.
I don't think it's trying to stop you looking inside the table. I think
it's complaining that SEQUENCE is a keyword, and shouldn't be appearing
there.
You could try quoting it.
James
On 22/09/15 21:11, Michael McAllister wrote:
> OK - so the traditional methods of recreating sequences, that makes
> sense.
>
> Interestingly btw, at least from within Phoenix I can’t see the
> content of SYSTEM.SEQUENCE. I get the following error:-
>
> 0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
> "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
>
> I do understand this is a system table, but it would be nice to see
> inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <ma...@HomeAway.com> | C:
> 512.423.7447 | skype: michael.mcallister.ha
> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>
>
> This electronic communication (including any attachment) is
> confidential. If you are not an intended recipient of this
> communication, please be advised that any disclosure, dissemination,
> distribution, copying or other use of this communication or any
> attachment is strictly prohibited. If you have received this
> communication in error, please notify the sender immediately by reply
> e-mail and promptly destroy all electronic and printed copies of this
> communication and any attachment.
>
>> On Sep 22, 2015, at 2:47 PM, James Heather
>> <james.heather@mendeley.com <ma...@mendeley.com>> wrote:
>>
>> If no one else will be hitting the table while you complete the
>> operation, and if you don't mind about missing a few sequence values
>> (i.e., having a gap), you should just need the following.
>>
>> SELECT NEXT VALUE FOR sequencename FROM sometable;
>>
>> That will tell you the next value the sequence wants to hand out.
>>
>> DROP SEQUENCE sequencename;
>>
>> Then reconnect with the property as given below, and
>>
>> CREATE SEQUENCE sequencename START WITH n;
>>
>> where n is the value you retrieved in the first step.
>>
>> The reason this might cause gaps is that client connections will
>> cache sequence values, so the one you retrieve might not actually be
>> the first one that hasn't been used; it'll just be the first one
>> cached by the connection you're using. But if you do it this way, and
>> nothing else is connected in the meantime, then you won't get any
>> duplicates.
>>
>> As far as I can see, if you're the only connected client, this
>> *should* do it with no gaps: no other clients will have cached any
>> sequence values, so you'll retrieve the first one your connection has
>> cached (which will be the first one available), and then that's where
>> your sequence will start when you recreate the sequence. But I'm not
>> absolutely certain about that, and you might want to try some
>> experiments.
>>
>> If the sequence is being used for a primary key column (a sort of
>> auto_increment), then the other option is to
>>
>> SELECT MAX(id) FROM sometable;
>>
>> and then add one to this value to determine where the recreated
>> sequence should start. That will ensure no gaps.
>>
>> James
>>
>>
>> On 22/09/15 19:47, Michael McAllister wrote:
>>> Mujtaba
>>>
>>> Thanks for this information. Seeing as I am using Phoenix 4.2, what
>>> is the safe and approved sequence of steps to drop this table and
>>> recreate it as you mention? Additionally, how do we ensure we don’t
>>> lose sequence data?
>>>
>>> Michael McAllister
>>> Staff Data Warehouse Engineer | Decision Systems
>>> <ma...@HomeAway.com>mmcallister@HomeAway.com | C:
>>> 512.423.7447 | skype: michael.mcallister.ha
>>> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>>>
>>> <Mail Attachment.png>
>>> This electronic communication (including any attachment) is
>>> confidential. If you are not an intended recipient of this
>>> communication, please be advised that any disclosure, dissemination,
>>> distribution, copying or other use of this communication or any
>>> attachment is strictly prohibited. If you have received this
>>> communication in error, please notify the sender immediately by
>>> reply e-mail and promptly destroy all electronic and printed copies
>>> of this communication and any attachment.
>>>
>>>> On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mujtaba@apache.org
>>>> <ma...@apache.org>> wrote:
>>>>
>>>> Since Phoenix 4.5.x default has been changed for
>>>> phoenix.sequence.saltBuckets to not split sequence table. See this
>>>> <https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d>
>>>> commit. For older versions you can drop sequence table and
>>>> reconnect with setting client side phoenix.sequence.saltBuckets
>>>> property.
>>>>
>>>> On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister
>>>> <mmcallister@homeaway.com <ma...@homeaway.com>> wrote:
>>>>
>>>> Hi
>>>>
>>>> By default SYSTEM.SEQUENCE is installed with 256 regions. In an
>>>> environment where you don’t have a large number of tables and
>>>> regions (yet), the end result of this seems to be that with
>>>> hbase balance_switch=true, you end up with a lot of region
>>>> servers with nothing but empty SYSTEM.SEQUENCE regions on them.
>>>> That mans inefficient use of our cluster.
>>>>
>>>> Have there been any best practices developed as to how to deal
>>>> with this situation?
>>>>
>>>> Michael McAllister
>>>> Staff Data Warehouse Engineer | Decision Systems
>>>> mmcallister@HomeAway.com <ma...@HomeAway.com> | C:
>>>> 512.423.7447 <tel:512.423.7447> | skype: michael.mcallister.ha
>>>> <ma...@hotmail.com> | webex:
>>>> <https://h.a/mikewebex>https://h.a/mikewebex
>>>>
>>>> <image002.png>
>>>> This electronic communication (including any attachment) is
>>>> confidential. If you are not an intended recipient of this
>>>> communication, please be advised that any disclosure,
>>>> dissemination, distribution, copying or other use of this
>>>> communication or any attachment is strictly prohibited. If you
>>>> have received this communication in error, please notify the
>>>> sender immediately by reply e-mail and promptly destroy all
>>>> electronic and printed copies of this communication and any
>>>> attachment.
>>>>
>>>>
>>>
>>
>
Re: Number of regions in SYSTEM.SEQUENCE
Posted by Michael McAllister <mm...@homeaway.com>.
OK - so the traditional methods of recreating sequences, that makes sense.
Interestingly btw, at least from within Phoenix I can’t see the content of SYSTEM.SEQUENCE. I get the following error:-
0: jdbc:phoenix:redacted,> select count(*) from system.sequence;
Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "sequence" at line 1, column 29. (state=42P00,code=604)
I do understand this is a system table, but it would be nice to see inside it. This is from Apache Phoenix 4.2 on HDP 2.2.6.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D080DC.77AD4930]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 2:47 PM, James Heather <ja...@mendeley.com>> wrote:
If no one else will be hitting the table while you complete the operation, and if you don't mind about missing a few sequence values (i.e., having a gap), you should just need the following.
SELECT NEXT VALUE FOR sequencename FROM sometable;
That will tell you the next value the sequence wants to hand out.
DROP SEQUENCE sequencename;
Then reconnect with the property as given below, and
CREATE SEQUENCE sequencename START WITH n;
where n is the value you retrieved in the first step.
The reason this might cause gaps is that client connections will cache sequence values, so the one you retrieve might not actually be the first one that hasn't been used; it'll just be the first one cached by the connection you're using. But if you do it this way, and nothing else is connected in the meantime, then you won't get any duplicates.
As far as I can see, if you're the only connected client, this should do it with no gaps: no other clients will have cached any sequence values, so you'll retrieve the first one your connection has cached (which will be the first one available), and then that's where your sequence will start when you recreate the sequence. But I'm not absolutely certain about that, and you might want to try some experiments.
If the sequence is being used for a primary key column (a sort of auto_increment), then the other option is to
SELECT MAX(id) FROM sometable;
and then add one to this value to determine where the recreated sequence should start. That will ensure no gaps.
James
On 22/09/15 19:47, Michael McAllister wrote:
Mujtaba
Thanks for this information. Seeing as I am using Phoenix 4.2, what is the safe and approved sequence of steps to drop this table and recreate it as you mention? Additionally, how do we ensure we don’t lose sequence data?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
<Mail Attachment.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mu...@apache.org>> wrote:
Since Phoenix 4.5.x default has been changed for phoenix.sequence.saltBuckets to not split sequence table. See this<https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d> commit. For older versions you can drop sequence table and reconnect with setting client side phoenix.sequence.saltBuckets property.
On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister <mm...@homeaway.com>> wrote:
Hi
By default SYSTEM.SEQUENCE is installed with 256 regions. In an environment where you don’t have a large number of tables and regions (yet), the end result of this seems to be that with hbase balance_switch=true, you end up with a lot of region servers with nothing but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our cluster.
Have there been any best practices developed as to how to deal with this situation?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447<tel:512.423.7447> | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: <https://h.a/mikewebex> https://h.a/mikewebex
<image002.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Number of regions in SYSTEM.SEQUENCE
Posted by James Heather <ja...@mendeley.com>.
If no one else will be hitting the table while you complete the
operation, and if you don't mind about missing a few sequence values
(i.e., having a gap), you should just need the following.
SELECT NEXT VALUE FOR sequencename FROM sometable;
That will tell you the next value the sequence wants to hand out.
DROP SEQUENCE sequencename;
Then reconnect with the property as given below, and
CREATE SEQUENCE sequencename START WITH n;
where n is the value you retrieved in the first step.
The reason this might cause gaps is that client connections will cache
sequence values, so the one you retrieve might not actually be the first
one that hasn't been used; it'll just be the first one cached by the
connection you're using. But if you do it this way, and nothing else is
connected in the meantime, then you won't get any duplicates.
As far as I can see, if you're the only connected client, this *should*
do it with no gaps: no other clients will have cached any sequence
values, so you'll retrieve the first one your connection has cached
(which will be the first one available), and then that's where your
sequence will start when you recreate the sequence. But I'm not
absolutely certain about that, and you might want to try some experiments.
If the sequence is being used for a primary key column (a sort of
auto_increment), then the other option is to
SELECT MAX(id) FROM sometable;
and then add one to this value to determine where the recreated sequence
should start. That will ensure no gaps.
James
On 22/09/15 19:47, Michael McAllister wrote:
> Mujtaba
>
> Thanks for this information. Seeing as I am using Phoenix 4.2, what is
> the safe and approved sequence of steps to drop this table and
> recreate it as you mention? Additionally, how do we ensure we don’t
> lose sequence data?
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <ma...@HomeAway.com> | C:
> 512.423.7447 | skype: michael.mcallister.ha
> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>
>
> This electronic communication (including any attachment) is
> confidential. If you are not an intended recipient of this
> communication, please be advised that any disclosure, dissemination,
> distribution, copying or other use of this communication or any
> attachment is strictly prohibited. If you have received this
> communication in error, please notify the sender immediately by reply
> e-mail and promptly destroy all electronic and printed copies of this
> communication and any attachment.
>
>> On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mujtaba@apache.org
>> <ma...@apache.org>> wrote:
>>
>> Since Phoenix 4.5.x default has been changed for
>> phoenix.sequence.saltBuckets to not split sequence table. See this
>> <https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d>
>> commit. For older versions you can drop sequence table and reconnect
>> with setting client side phoenix.sequence.saltBuckets property.
>>
>> On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister
>> <mmcallister@homeaway.com <ma...@homeaway.com>> wrote:
>>
>> Hi
>>
>> By default SYSTEM.SEQUENCE is installed with 256 regions. In an
>> environment where you don’t have a large number of tables and
>> regions (yet), the end result of this seems to be that with hbase
>> balance_switch=true, you end up with a lot of region servers with
>> nothing but empty SYSTEM.SEQUENCE regions on them. That mans
>> inefficient use of our cluster.
>>
>> Have there been any best practices developed as to how to deal
>> with this situation?
>>
>> Michael McAllister
>> Staff Data Warehouse Engineer | Decision Systems
>> mmcallister@HomeAway.com <ma...@HomeAway.com> | C:
>> 512.423.7447 <tel:512.423.7447> | skype: michael.mcallister.ha
>> <ma...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> <image002.png>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this
>> communication, please be advised that any disclosure,
>> dissemination, distribution, copying or other use of this
>> communication or any attachment is strictly prohibited. If you
>> have received this communication in error, please notify the
>> sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any
>> attachment.
>>
>>
>
Re: Number of regions in SYSTEM.SEQUENCE
Posted by Michael McAllister <mm...@homeaway.com>.
Mujtaba
Thanks for this information. Seeing as I am using Phoenix 4.2, what is the safe and approved sequence of steps to drop this table and recreate it as you mention? Additionally, how do we ensure we don’t lose sequence data?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D080DC.77AD4930]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
On Sep 22, 2015, at 1:32 PM, Mujtaba Chohan <mu...@apache.org>> wrote:
Since Phoenix 4.5.x default has been changed for phoenix.sequence.saltBuckets to not split sequence table. See this<https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d> commit. For older versions you can drop sequence table and reconnect with setting client side phoenix.sequence.saltBuckets property.
On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister <mm...@homeaway.com>> wrote:
Hi
By default SYSTEM.SEQUENCE is installed with 256 regions. In an environment where you don’t have a large number of tables and regions (yet), the end result of this seems to be that with hbase balance_switch=true, you end up with a lot of region servers with nothing but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our cluster.
Have there been any best practices developed as to how to deal with this situation?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447<tel:512.423.7447> | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
<image002.png>
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Number of regions in SYSTEM.SEQUENCE
Posted by Mujtaba Chohan <mu...@apache.org>.
Since Phoenix 4.5.x default has been changed for
phoenix.sequence.saltBuckets to not split sequence table. See this
<https://git-wip-us.apache.org/repos/asf?p=phoenix.git;a=blobdiff;f=phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java;h=79776e7f688fc700275d0502e31646afe2bbcb1e;hp=4e8879b1b7a6358db2c1f9ccb4fa169394fec721;hb=18e52cc4ce2384bdc7a9c72d63901058e40f04ae;hpb=b82c5cbccdf4eb944238e69a514841be361bfb6d>
commit. For older versions you can drop sequence table and reconnect with
setting client side phoenix.sequence.saltBuckets property.
On Tue, Sep 22, 2015 at 11:14 AM, Michael McAllister <
mmcallister@homeaway.com> wrote:
> Hi
>
> By default SYSTEM.SEQUENCE is installed with 256 regions. In an
> environment where you don’t have a large number of tables and regions
> (yet), the end result of this seems to be that with hbase
> balance_switch=true, you end up with a lot of region servers with nothing
> but empty SYSTEM.SEQUENCE regions on them. That mans inefficient use of our
> cluster.
>
> Have there been any best practices developed as to how to deal with this
> situation?
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>