|
Home > Archive > IQ Server > December 2005 > MINIMIZE_STORAGE question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MINIMIZE_STORAGE question
|
|
|
| There is this information about MINIMIZE_STORAGE option in
documentation
....
Tables with few columns benefit when MINIMIZE_STORAGE is ON.
Tables with many columns generally benefit from turning this
option OFF....
Where is problem with MINIMIZE_STORAGE=ON and tables with
many columns? I would like to do some optimization on my IQ
database.
| |
|
| > There is this information about MINIMIZE_STORAGE option in
> documentation
> ...
> Tables with few columns benefit when MINIMIZE_STORAGE is
> ON. Tables with many columns generally benefit from
> turning this option OFF....
>
> Where is problem with MINIMIZE_STORAGE=ON and tables with
> many columns? I would like to do some optimization on my
> IQ database.
Does it mean only problems with loading data and refformat
from 2 byte FP to Flat FP? I think If I have data in
database I get best query performance for all types of
tables.
| |
| David Louie 2005-12-08, 9:24 am |
| > There is this information about MINIMIZE_STORAGE option in
> documentation
> ...
> Tables with few columns benefit when MINIMIZE_STORAGE is
> ON. Tables with many columns generally benefit from
> turning this option OFF....
>
> Where is problem with MINIMIZE_STORAGE=ON and tables with
> many columns? I would like to do some optimization on my
> IQ database.
The Migrating from Sybase to Sybase IQ white paper states:
"The database option "Minimize Storage" should be set "ON"
before creating any tables. This option will create the
"optimized" Fast Projection (FP) index on all columns with
less than 65,536 values. Optimized FP index dramatic reduce
storage and will make queries run faster"
No mention of adverse affects on tables with columns with >
65536 values.
Mike Kane from IQ tech manager send us the following
recommendations:
set option public. minimize_storage='on
'; --
minimize use
use
of storage for newly created columns, will maximize
compression
set option public. query_temp_space_lim
it=0;
-- set no
limit
to amount of temp space a query can use
set option public.iqmsg_length_mb=0;
-- length of
iqmsg wrapping 0=no wrap, 2047 = max length before wrap
set option public. query_plan_as_html_d
irectory='';
-- null
directory for query plan output will write to default
(.iqmsg) set
option public. USER_RESOURCE_RESERV
ATION=1; -- memory
allocation for
open cursors - do not change
set option public. force_no_scroll_curs
ors='on';
--
prevents IQ from buffering result set before returning to
client
set option public. OS_File_Cache_Buffer
ing='on';
-- use
file system cache
set option public.disk_striping='on';
-- stripe
write across dbspaces
set option public. disk_striping_packed
='on';
-- better
space
usage during data mods
set option public. notify_modulus=10000
00;
-- generate
message every xx rows inserted/loaded
set option public.append_load = 'on';
-- append
load,
to not fill in delete rows, IQ 12.4.2 and later
/* the following options will open up more memory, and speed
things up -
You can make them larger, but not too large or the OS will
start
swapping */
set option public. Main_Cache_Memory_MB
= 400; -- MB
Memory to
allocate for Main Memory, default = 16MB
set option public. Temp_Cache_Memory_MB
= 200; -- MB
Memory to
allocate for Temp Memory, default = 12MB
Hope this helps
David Louie
| |
| David Walrath 2005-12-10, 3:24 am |
|
The recommendation is really not because of the cost due to
switching from one FP type to another (although that may be
an issue in some databases), but because of the memory
overhead of loading many 1-byte and 2-byte FP columns in parallel.
Each 1-byte and 2-byte FP requires a hash table to be locked in
memory in the main cache. With many columns and limited cache
this could cause significant load problems (and also slower
queries if you do a select count(*)).
As a rough estimate, take the data type width, multiply by the
estimated distinct count, and round up to the nearest page size.
Repeat for each column that will be less than 65K distincts.
So a 200-byte char column with 60,000 distincts will need at least
12 MBytes of dedicated main cache. Many wide char/varchar columns
can add up quickly; this is on top of the main dbspace needs
of other indexes.
So if you have GBytes of main cache and not a large number of columns
(remember IQ can have 1,000s of columns in a table) you are probably
OK.
maca wrote:
> There is this information about MINIMIZE_STORAGE option in
> documentation
> ...
> Tables with few columns benefit when MINIMIZE_STORAGE is ON.
> Tables with many columns generally benefit from turning this
> option OFF....
>
> Where is problem with MINIMIZE_STORAGE=ON and tables with
> many columns? I would like to do some optimization on my IQ
> database.
|
|
|
|
|