Question about chunks extendables
Roger asked why an extendable chunk (12.10.FC1 on RHEL 6.4) grew by varying amounts (mostly 512MB, sometimes 256MB or 1GB) and whether the growth increment can be fixed in MB. Answers: use the SQL Admin API 'modify space sp_sizes' to set create and extend sizes (in MB or percent), and read current values from sysmaster:sysdbspaces (create_size/extend_size) or oncheck -pr. John Miller explained these are only minimums — if the requesting object needs more space, the extension is larger, so the increments aren't constant. SP_THRESHOLD=0 only disables proactive provisioning. Roger accepted the explanation.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
My environment is:
IBM Informix Dynamic Server Version 12.10.FC1
S.O. RHEL 6.4 64 bits
I created a dbspace with one chunk and I enabled the automatic chunk extention
with the commands:
onspaces -c -d tmp5 -p /tmpdbs/tmp5.dbs -s 52428800 -o 0 -t -k 4echo 'EXECUTE FUNCTION task("modify chunk extendable on", "48");' | dbaccess
sysadmin
But I don't know how the engine allocates the aditional space. In the extract
of the online log:
11:01:19 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:01:25 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:01:36 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:01:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:01:57 Chunk 48 in space 'tmp5' has been extended by 262144 kb.
11:02:14 Chunk 48 in space 'tmp5' has been extended by 1048576 kb.
11:02:35 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:02:55 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:03:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:04:52 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:05:04 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:05:42 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
11:06:43 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
It show that the value of growth is not constant. What is the rule that the
engine uses to allocate additional space ? A percentage, a value ?
Why it is not constant ?
Is it possible to establish a value in MB ?
Please, provide me any clues acording your experience.
Thanks in advance.
Roger
Hello, Roger:
Official docs does not say anything about the rule of sizing/increasing, but
this link may help you:
http://pic.dhe.ibm.com/infocenter/informix/v121/topic/com.ibm.admin.doc/ids_admi
n_1365.htm?resultof=%22%65%78%70%61%6e%64%61%62%6c%65%22%20%22%65%78%70%61%6e%64
%22%20%22%63%68%75%6e%6b%22%20
You may confirm your actual SP_THRESHOLD value, in order to check for
something further.
I think the engine is trying to be smart enough, and avoid several allocations
during time, when the ammount requested is not big enough, it might be
doubling it, until it fits your current needs... .maybe someone could let us
understand it better... (our IBMers friends are always around us, here).....
Hope it helps.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Informix Senior DBA - Orizon Brasil
BRIUG website administrator
Informix independent consultant
> To: ids@iiug.org
> From: rvilca@luzdelsur.com.pe
> Subject: Question about chunks extendables [32791]
> Date: Tue, 25 Mar 2014 12:30:08 -0400
>
> Hi,
>
> My environment is:
>
> IBM Informix Dynamic Server Version 12.10.FC1
> S.O. RHEL 6.4 64 bits
>
> I created a dbspace with one chunk and I enabled the automatic chunk
extention
> with the commands:
>
> onspaces -c -d tmp5 -p /tmpdbs/tmp5.dbs -s 52428800 -o 0 -t -k 4> echo 'EXECUTE FUNCTION task("modify chunk extendable on", "48");' | dbaccess
> sysadmin
>
> But I don't know how the engine allocates the aditional space. In the extract
> of the online log:
>
> 11:01:19 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:25 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:36 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:57 Chunk 48 in space 'tmp5' has been extended by 262144 kb.
> 11:02:14 Chunk 48 in space 'tmp5' has been extended by 1048576 kb.
> 11:02:35 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:02:55 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:03:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:04:52 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:05:04 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:05:42 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:06:43 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
>
> It show that the value of growth is not constant. What is the rule that the
> engine uses to allocate additional space ? A percentage, a value ?
> Why it is not constant ?
>
> Is it possible to establish a value in MB ?
>
> Please, provide me any clues acording your experience.
>
> Thanks in advance.
> Roger
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Please check out the SQL ADMIN API command modify chunk sp_size.
It is not constant because you have the minimum size set by the chunk,
together with
the storage provision sizes, and the requestor's object size request.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/25/2014 09:30:08 AM:
> From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
> To: ids@iiug.org,
> Date: 03/25/2014 09:30 AM
> Subject: Question about chunks extendables [32791]
> Sent by: ids-bounces@iiug.org
>
> Hi,
>
> My environment is:
>
> IBM Informix Dynamic Server Version 12.10.FC1
> S.O. RHEL 6.4 64 bits
>
> I created a dbspace with one chunk and I enabled the automatic
chunkextention
> with the commands:
>
> onspaces -c -d tmp5 -p /tmpdbs/tmp5.dbs -s 52428800 -o 0 -t -k 4> echo 'EXECUTE FUNCTION task("modify chunk extendable on", "48");' |
dbaccess> sysadmin
>
> But I don't know how the engine allocates the aditional space. In the
extract
> of the online log:
>
> 11:01:19 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:25 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:36 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:01:57 Chunk 48 in space 'tmp5' has been extended by 262144 kb.
> 11:02:14 Chunk 48 in space 'tmp5' has been extended by 1048576 kb.
> 11:02:35 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:02:55 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:03:48 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:04:52 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:05:04 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:05:42 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
> 11:06:43 Chunk 48 in space 'tmp5' has been extended by 524288 kb.
>
> It show that the value of growth is not constant. What is the rule that
the
> engine uses to allocate additional space ? A percentage, a value ?
> Why it is not constant ?
>
> Is it possible to establish a value in MB ?
>
> Please, provide me any clues acording your experience.
>
> Thanks in advance.
> Roger
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thanks Alexandre, I have set the value of SP_THRESHOLD = 0 The automatic extent works, but according the documentation, the value = 0 effectively disables this proactive storage provisioning trigger" This is the reason for which the increment value is not constant? Roger
Thanks John,
Based on the link:
http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.adref.
doc%2Fids_sapi_106.htm
I have the examples:
The following command sets the minimum create size to 60 megabytes and the
minimum extend size to 10 megabytes for dbspace3:
EXECUTE FUNCTION task("modify space sp_sizes", "dbspace3", "60000", "10000");
The following command sets the minimum create size to 20 percent and the
minimum extend size 1.5 percent for dbspace8:
EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", "20", "1.5");
seemingly, the API only permit modify the values, but, is it posible to know
the current values ?
Roger
You can check sysmaster:sysdbspaces for the columns create_size, and
extend_size
or run
oncheck -pr
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/25/2014 12:43:18 PM:
> From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
> To: ids@iiug.org,
> Date: 03/25/2014 12:44 PM
> Subject: Re: Question about chunks extendables [32797]
> Sent by: ids-bounces@iiug.org
>
> Thanks John,
>
> Based on the link:
>
> http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%
> 2Fcom.ibm.adref.doc%2Fids_sapi_106.htm
>
> I have the examples:
>
> The following command sets the minimum create size to 60 megabytes and
the
> minimum extend size to 10 megabytes for dbspace3:
> EXECUTE FUNCTION task("modify space sp_sizes", "dbspace3", "60000","10000");
>
> The following command sets the minimum create size to 20 percent and the
> minimum extend size 1.5 percent for dbspace8:
> EXECUTE FUNCTION task("modify space sp_sizes", "dbspace8", "20", "1.5");>
> seemingly, the API only permit modify the values, but, is it posible to
know
> the current values ?
>
> Roger
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The values shows create_size = 10.00000000000 and extend_size = 10000.00000000, but in the online log appears, by example: "Chunk 48 in space 'tmp5' has been extended by 524288 kb." The value 524288 Kb is 1% de 50 GB (initial size of the chunk). The engine seems to have a space allocation algorithm that I can not guess.
The currently configured minimum extend size is set at 10MB, but if the user of chunk 48 is requesting 50MB then the extend size will be 50MB. What you are configuring is the minimum size. If a user of the space request a larger chunk of space, then the minimum will not be honored. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/25/2014 06:43:17 PM: > From: "ROGER VILCA" <rvilca@luzdelsur.com.pe> > To: ids@iiug.org, > Date: 03/25/2014 06:43 PM > Subject: Re: Question about chunks extendables [32799] > Sent by: ids-bounces@iiug.org > > The values shows create_size = 10.00000000000 and extend_size = > 10000.00000000, > but in the online log appears, by example: "Chunk 48 in space 'tmp5'has been > extended by 524288 kb." > > The value 524288 Kb is 1% de 50 GB (initial size of the chunk). > The engine seems to have a space allocation algorithm that I can not guess. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks John Best regards Roger