help with fragmentation scheme...

Main Page | Report this Page

Computers Forum Index » Computer - Databases - Informix » help with fragmentation scheme...

Page 1 of 1

help with fragmentation scheme...

Author

Message

Floyd Wellershaus...

Posted: Mon Oct 26, 2009 2:12 pm

Guest

We have to fragment a table now because of nearing the page limit size. Yes we could just change the page size but think at 240million rows, it's probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and joined to another field(stprofil_token).
The other field has values that are spread througout the token range. So if we fragmented on the token field, it would be good to eliminate the proper fragment, but that would have to happen many times until it found the right record that contains the token/stprofil_token value.

So would the best thing be to try and find a good split of data between those 2 fields ?
Like see if I can fragment with an expression like:
token >;=x and token ;=a and stprofil_token

Jarrod Teale...

Posted: Tue Oct 27, 2009 12:27 am

Guest

Floyd,
If you fragment on a range expression like you have below, I am pretty
sure that you have issues with fragment elimination.

It's only my opinion but if there isn't a performance problem, then I'd
just round robin it across multiple spaces. I suspect that the
expression you have below would hit sometimes and cause slow performance
other times - finding out when could be difficult..

Jarrod

________________________________

From: informix-list-bounces at (no spam) iiug.org
[mailto:informix-list-bounces at (no spam) iiug.org] On Behalf Of Floyd Wellershaus
Sent: Tuesday, 27 October 2009 3:13 a.m.
To: informix-list at (no spam) iiug.org
Subject: help with fragmentation scheme

We have to fragment a table now because of nearing the page limit size.
Yes we could just change the page size but think at 240million rows,
it's probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and
joined to another field(stprofil_token).
The other field has values that are spread througout the token range. So
if we fragmented on the token field, it would be good to eliminate the
proper fragment, but that would have to happen many times until it found
the right record that contains the token/stprofil_token value.

So would the best thing be to try and find a good split of data between
those 2 fields ?
Like see if I can fragment with an expression like:
token <=x and token >y and stprofil_token <=a and stprofil_token > b ??

Thanks.
floyd

Andrew Clarke...

Posted: Tue Oct 27, 2009 2:43 am

Guest

Quote:

We have to fragment a table now because of nearing the page limit size. Yes
we could just change the page size but think at 240million rows, it's
probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and joined
to another field(stprofil_token). The other field has values that are
spread througout the token range. So if we fragmented on the token field,
it would be good to eliminate the proper fragment, but that would have to
happen many times until it found the right record that contains the
token/stprofil_token value.

So would the best thing be to try and find a good split of data between
those 2 fields ? Like see if I can fragment with an expression like:
token >;=x and token ;=a and stprofil_token

A look at the schema and domains of possible candidate fields would help.

I know you mention a "token" but to me a token is something that gets me
through the turnstile at Luna Park. What's the type and possible spread of
that field?

For the fields that might be suitable for fragmenting, give a rough estimate
of the typical shape of the data (eg a char(2) might match "[A-Z][A-Z0-9]" or
it might be more "[AB][A-Z0-9]") and also give a rough estimate of the number
of unique values for that field (eg if it's an American state field, the whole
world knows there's about 50 possible values)

A nice clean set of SELECT samples will help too

As Jarrod mentioned, you might just be better off using round-robin and
enabling simple parallelism using the PDQ parameter in the config file.

Finally, once a candidate scheme looks promising, test-drive it on a sample
database using real SELECTs, and check the query plan that pops out using SET
EXPLAIN

Art Kagel...

Posted: Tue Oct 27, 2009 5:15 am

Guest

Quote:

We have to fragment a table now because of nearing the page limit size. Yes
we could just change the page size but think at 240million rows, it's
probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and joined
to another field(stprofil_token).
The other field has values that are spread througout the token range. So if
we fragmented on the token field, it would be good to eliminate the proper
fragment, but that would have to happen many times until it found the right
record that contains the token/stprofil_token value.

So would the best thing be to try and find a good split of data between
those 2 fields ?
Like see if I can fragment with an expression like:
token <=x and token >y and stprofil_token <=a and stprofil_token > b ??

Thanks.
floyd

_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

david at (no spam) smooth1.co.uk...

Posted: Tue Oct 27, 2009 8:02 pm

Guest

On 27 Oct, 02:36, Art Kagel <art.ka... at (no spam) gmail.com> wrote:

Quote:

Yes, you can fragment on such a complex formula. Just be careful that you
cover all of the possible permutations or have a REMAINDER fragment.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a... at (no spam) iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Mon, Oct 26, 2009 at 10:12 AM, Floyd Wellershaus <fl... at (no spam) fwellers.com>wrote:

We have to fragment a table now because of nearing the page limit size. Yes
we could just change the page size but think at 240million rows, it's
probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and joined
to another field(stprofil_token).
The other field has values that are spread througout the token range. So if
we fragmented on the token field, it would be good to eliminate the proper
fragment, but that would have to happen many times until it found the right
record that contains the token/stprofil_token value.

So would the best thing be to try and find a good split of data between
those 2 fields ?
Like see if I can fragment with an expression like:
token <=x and token >y and stprofil_token <=a and stprofil_token > b ??

Thanks.
floyd

_______________________________________________
Informix-list mailing list
Informix-l... at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

On the Informix course i did the advice was to ALWAYS have a remainder
expression, even if the values in the column are known now
they can change in the future.

You need to deceide if you want queries to most commonly access

a). one fragment (complete fragment elimination),
b) some fragments (partial fragment elimination) with or without
parallelism
c) all fragments with parallelism.

This really depends upon what types of queries that will be done and
how many cpus/ how much parallel io you can use per query.

Jack Parker...

Posted: Wed Oct 28, 2009 4:21 am

Guest

Quote:

Yes, you can fragment on such a complex formula. Just be careful that you
cover all of the possible permutations or have a REMAINDER fragment..

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a... at (no spam) iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any
entity
with which I am affiliated nor those of the entities themselves.

On Mon, Oct 26, 2009 at 10:12 AM, Floyd Wellershaus
fl... at (no spam) fwellers.com>wrote:

We have to fragment a table now because of nearing the page limit size.
Yes
we could just change the page size but think at 240million rows, it's
probably a good thing to fragment the table anyway.

Most queries join that table to other tables based on the token and
joined
to another field(stprofil_token).
The other field has values that are spread througout the token range. So
if
we fragmented on the token field, it would be good to eliminate the
proper
fragment, but that would have to happen many times until it found the
right
record that contains the token/stprofil_token value.

So would the best thing be to try and find a good split of data between
those 2 fields ?
Like see if I can fragment with an expression like:
token <=x and token >y and stprofil_token <=a and stprofil_token > b ??

Thanks.
floyd

_______________________________________________
Informix-list mailing list
Informix-l... at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Beau Nanaz...

Posted: Sun Nov 01, 2009 6:00 am

Guest

Floyd,

I see nothing wrong with your fragmentation scheme, except, perhaps, the
way you worded it. For clarity, I would have expressed it as:
token >x and token <=y and stprofil_token > a and stprofil_token <=b
for token between (x and y) and stprofil between (a and b).

In any case, trying to translate into an actual distribution, this declares
- 3 ranges for token (token <=y, y < token <=x, token >x)
- 3 ranges for stprofil (stprofil <= a, a < stprofil <= b, stprofil > b)

To cover all possibilities, you need 9 partitions, plus (as some have
pointed out) a remainder partition whether or nor you believe you'll
need it. I believe you can set different extent parameters for each
partition in release 11+; surely Art will supply the exact version. Wink

Thus, you can set a small extent size for the remainder partition.

I would be dubious of Jack's suggestion, however. While it is not
essential that an index have a similar fragmentation scheme as its
table, the size that Floyd describes tells me that some fragment
elimination at the index level might be prudent. For example, the index
might be fragmented only on token (assuming the index is a composite of
<token, stprofil>) - 3 index partitions + remainder.

And while the latest releases of IDS allow multiple fragments of a table
to reside in the same DBspace, that is a convenience you should not
necessarily indulge if you can afford the additional spindles. You
don't want the parallel threads fighting each other for that blessed
disk head.

As an aside, as long as we are discussing useful new features of IDS, I
recall requesting the ability to set extent sizes for an index. Did
that ever see the light of day?

-- Jacob

Jack Parker wrote:

Quote:

I would imagine that you have an index on the column in question, which
would obviate the need for fragment elimination.

cheers
j.

Sane ego te vocavi. Forsitan capedictum tuum desit.

-----Original Message-----
From: informix-list-bounces at (no spam) iiug.org
[mailto:informix-list-bounces at (no spam) iiug.org]On Behalf Of david at (no spam) smooth1.co.uk
Sent: Tuesday, October 27, 2009 3:03 PM
To: informix-list at (no spam) iiug.org
Subject: Re: help with fragmentation scheme

On 27 Oct, 02:36, Art Kagel <art.ka... at (no spam) gmail.com> wrote:
Yes, you can fragment on such a complex formula. Just be careful that you
cover all of the possible permutations or have a REMAINDER fragment.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a... at (no spam) iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any
entity
with which I am affiliated nor those of the entities themselves.

On Mon, Oct 26, 2009 at 10:12 AM, Floyd Wellershaus
fl... at (no spam) fwellers.com>wrote:
We have to fragment a table now because of nearing the page limit size.
Yes
we could just change the page size but think at 240million rows, it's
probably a good thing to fragment the table anyway.
Most queries join that table to other tables based on the token and
joined
to another field(stprofil_token).
The other field has values that are spread througout the token range. So
if
we fragmented on the token field, it would be good to eliminate the
proper
fragment, but that would have to happen many times until it found the
right
record that contains the token/stprofil_token value.
So would the best thing be to try and find a good split of data between
those 2 fields ?
Like see if I can fragment with an expression like:
token <=x and token >y and stprofil_token <=a and stprofil_token > b ??
Thanks.
floyd
_______________________________________________
Informix-list mailing list
Informix-l... at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

On the Informix course i did the advice was to ALWAYS have a remainder
expression, even if the values in the column are known now
they can change in the future.

You need to deceide if you want queries to most commonly access

a). one fragment (complete fragment elimination),
b) some fragments (partial fragment elimination) with or without
parallelism
c) all fragments with parallelism.

This really depends upon what types of queries that will be done and
how many cpus/ how much parallel io you can use per query.

_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Page 1 of 1

All times are GMT The time now is Wed Nov 25, 2009 10:06 pm Computers Forum Index