Hi.
pdqpriority=30.
I'm going to change fragment filter and test it.
Thank you.
Post by Art KagelI should also have asked what versions of Informix you are using and on
what platforms the two servers are runnning?
Note that there have been performance problems in earlier releases when
partitioning on certain data types, and DATETIME columns specifically.
ROUND ROBIN partitioning is meant to minimize INSERT contention among
large numbers of users. For query performance, this is the worst
performing type of fragmentation. When selecting a partitioning scheme,
you have to decide what your goals are and select a schema that will
achieve that. Certainly, the DATETIME based scheme satisfies the goal of
eliminating the problems with exceeding the 16million page limit for a
single partition, but if you cannot get fragment elimination because of
that, you need to revisit the partitioning.
Also note that the storage options of your indexes will also impact
performance. By default all indexes that do not have an IN clause are
partitioned the same way as your data. If the query does not have that
DATETIME column as a filter or join condition, then all of the partitions
of the index will need to be searched, and the search will have to look at
the multiple partitions serially if you don't have PDQPRIORITY set to a
positive value.
If you do not have any indexes on the filter columns then Informix will
have to perform a sequential scan of the entire table which may indeed take
longer on a partitioned table than on a non-partitioned table. I would
strongly urge you to create indexes on any column that is used as a filter
or join clause. If you are using version 11.70 or later (so 12.10 today)
the server supports multi-index searches, so you can have a single column
index on each column used as a filter or join column. Make sure to create
proper distributions also. If you are relying on Informix's Auto Update
Statistics note that it does not gather any distributions on non-indexed
columns so you are not presenting the optimizer with any information about
your data that it can use to optimize queries. You should be following the
update statistics protocols described in the Performance Guide or just use
my dostats utility which implements those protocols best.
Dostats is included in the package utils2_ak which you can download from
the IIUG Software Repository (www.iiug.org/software).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. 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 Thu, Aug 15, 2013 at 11:33 AM, Juan Francisco González Navarro <
Post by Juan Francisco González NavarroI have to answer yes to your first question. This tables was fragment by
a field with datetime data type which is not used in any query but it's
usefull to split our information human logical. This table is huge and was
fragment to avoid reach number of rows size limit per table and was split
by year.
If i've understand you right i have to partion this table by a field use
in the sql as filter, isn't it?
Also i'm using in this table fragmentation strategy comparasions like <=
and >= in a simple query. This simple comparations is vs a field without
any search index or key.
I'm going to alter fragmentation strategy on this table. What about round
robin?
Regards.
Post by Art KagelReview the fragmentation scheme and the SET EXPLAIN output for the
queries that seem to be slow and determine if the fragmentation scheme is
- Are you partitioned on columns that are not used as filters in
typical queries?
- Does the fragmentation scheme list the partitions most likely to
be accessed most frequently first or last?
- Does the partitioning scheme use a complex function or simple
comparisons?
- Do the comparisons eliminate options quickly (so list the <= part
of a range before the >= part).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. 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 Wed, Aug 14, 2013 at 3:25 PM, Juan Francisco González Navarro <
Post by Juan Francisco González NavarroHello:We have two database split in two servers. First one with no
fragment setup runs quickly a query that retrieve 500000 rows in the other
hand the server with fragment setup is too slow.Table and indexes are
fragment by sql.Any advice? Regards
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list