Tuneable options
This presentation is the property of its rightful owner.
Sponsored Links
1 / 48

Tuneable Options PowerPoint PPT Presentation


  • 81 Views
  • Uploaded on
  • Presentation posted in: General

6. Tuneable Options. Tuneable Options. This section describes some of the options that can be played with, and can affect performance The effect can be to reduce performance - so be careful Generally the use of options is very over-rated

Download Presentation

Tuneable Options

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Tuneable options

6

Tuneable Options


Tuneable options1

Tuneable Options

  • This section describes some of the options that can be played with, and can affect performance

  • The effect can be to reduce performance - so be careful

  • Generally the use of options is very over-rated

  • Good Design and Correctly Specified Hardware counts more than 2 minutes of button pushing….


Warning

Warning

  • As above these options can do more harm than good

  • Also be aware of the scope of the option – you probably do not want to change the behaviour of the entire server - just to speed up one query


2 rules or laws if you like

2 Rules or laws if you like

  • These are not guidelines these are rules

  • Think of them as natural laws like gravity

  • 1)Do not touch an option until you know (not think you know) what itdoes

  • 2)Test, test and test again after you have set it – it must not damage something else


The options

The Options

  • There is not enough time I this course to go through all of the options in the server

  • We will cover three sections of options

  • 1)Those that will generally help – or must be set correctly

  • 2)Those options that may help

  • 3)The options you must not touch


Options

Options

  • Some options are better mentioned in the other sections of this course – so some are missing

  • Of the “Do Not Touch” options – this is not a joke Do No Touch them

  • There are options that do nothing (they are disconnected, but are yet to be removed)

  • Play with these as much as you like…


Cache sizes

Cache Sizes

  • Forget, for the moment, the Main and Temp caches

  • There is, also, the Catalog Cache (for the ASA database)

  • There are 3 start-up parameters that control the size of the memory to be used for this cache

    • -csuggested size of cache

    • -cllower bound (min. allocated memory)

    • -chHard upper bound (max. memory to be allocated)


Sql anywhere options

SQL Anywhere Options

  • A number of the options in the sysoptions table are only used by Adaptive Server Anywhere

  • These are only peripherally of interest to IQ-M

  • We will only cover those that are of interest


See and change the options

See and change the options

  • To see the options

    Select * from sys.sysoptions order by 2

  • To change the options

    SET [ temporary ] OPTION [ userid. | PUBLIC.] option-name = [ option-value ]


Scope etc

Scope Etc.

  • There are two scope ranges for the options

    • User wide (set for the specific user-id in the command)

    • Server wide (set for all users - across the entire server)

  • There are two time ranges

    • Temporary

      • For user based scope this last until the user signs off

      • For Server wide the option lasts until reboot of the server

    • Permanent is permanent until the option is re-set

    • All options can be set “temporary”, but there is no meaning for this if the option requires a server reboot


Memory 1

Memory – 1

  • Main_Cache_Memory_MBDef:8 Mb

    • Used to set the size of the main cache

    • This must be set correctly

  • Temp_Cache_Memory_MBDef:4 MB

    • Used to set the size of the temp. cache

  • Load_Memory_MBDef: 0

    • Used to limit the “per user” memory used for loads (not inserts) – default is much too big for most systems

  • All these require a server re-boot as memory is not dynamically re-addressable in IQ-M


Memory 2

Memory - 2

Operating System

  • This is the memory picture for an IQ machine:

  • Allow between 60 and 100 MB for the O/S and ASIQ Server

  • The bottom three boxes are controlled by the three options on the last page

  • Do not exceed the virtual (or real) memory limit for the system

ASIQ Server

ASIQ Overhead

ASIQ Main Cache

ASIQ Temp cache


Memory 3

Memory – 3

  • Virtual Memory limits for IQ operating systems:

    • SOLARIS (2.6 & 2.7)3 GB

    • SOLARIS 2.7 64 bit32 GB

    • DUNIX (4.0D & 4.0E)27 GB (64 bit !)

    • AIX (4.2.1, 4.3.1, 4.3.2)2.75 GB

    • HPUX (11.0)2.7 GB

    • NT2 GB (Cache limits)

    • Windows 2000 (NT5)4 GB

    • SGI IRIX 64128 GB

  • You probably will not have this much real memory !


Cache memory

Cache Memory

  • Ever since IQ 10 (or before) hit the streets we have said

    • You probably need more Main Cache than Temp.

  • WRONG !

  • Start at 50/50 then move to more Temp. Cache

  • There are only a very few limited areas where you need more Main than Temp.


Loading

Loading

  • Back a few slides ago we mentioned Load_Memory_MB

  • I think that it is about time to shed some light on the IQ-M Load process

  • Even although there is only 1 parameter to affect the load/insert (and delete!) processes, we need to understand it


Loading the big picture

Loading – The Big Picture

Main Cache

ASCII Flat File

Load Memory

Building Simple Index Pages

Single Thread

Per HNG, LF and FP

Single

Thread

Pass 2

Multi

Thread

Temp. Cache

1

Sorting Groups

Building B-Trees

Single Thread

Per HG (maybe 2)

Row Delimited!

Or it is Single Thread

Raw IO Buffers

Intermediate Buffers

2

Pass 1

1

3

IO Operations to Temp Store indicate Not Enough Temp. Cache

Write of Completed HG B-tree G-Arrays and Bitmaps to Main Cache

2

Write of completed HNG, LF and FP Pages to Main IQ Store

3

Main IQ Store

Write of completed HG B-trees, G-Arrays and Bitmaps to Main IQ Store

Temp. IQ Store


Load clause

Load Clause

  • Row Delimited By

    • Always,Always use this sub-clause

    • During the data read from disk, before the index creator threads are started the conversion process can be either parallel or serial

    • Without Row Delimited By the process will always be serial

    • This will triple the Load Pass1 times


Append load

Append Load

  • By default the system will always attempt to fill “deleted slots” in the table structure

  • This can dramatically increase the load times as the system has to “search” for the “holes” in the columns (rowids with row_existence off)

  • By setting the following option the server will “append” to the rowids – and ignore spaces in the table

    Append_LoadDef OFF


Low fast load 1

Low Fast Load - 1

LF_Bitmap_Cache_kbDef 4

  • By default, during a load, each distinct value for each LF index in a load has a heap that is 4 Kbytes in size

  • If the distinct count for all LF indexes that are being loaded is greater than 20,000 (the manual says 10,000 – this is too low) then reduce this option as the system will start to use too much memory

  • Memory usage can be checked using the IQ Monitor

    Memory = (LF_Bitmap_Cache_kb * 1024) * column_distinct_count


Low fast load 2

Low Fast Load - 2

  • Remember this is in HEAP memory, not in the caches

  • So the heap can grow very large

  • For a Writer Node, this is not too much of a problem, but Simplex units may suffer from this


Memory do not touch

Memory – Do Not Touch

Memory_Limit_MB Def 0Memory_Snapshot_FirstDef 0Memory_Snapshot_IncrementDef 0

  • These 3 flags, if set, will simulate an out of memory condition

  • These are DO NOT TOUCH flags


Pinnable memory

Pinnable Memory

  • For a majority of the time IQ-M does not need to lock (or pin) “objects” into memory

  • However for Sorts, Hashes and for certain bitmap operations the server need to be able to lock (or pin) pages into the caches

  • The larger the number that can be locked into memory

    • The faster the operation runs

    • But the rest of the server users suffer from a “lack” of memory


Pining hashes and sorts

Pining Hashes and Sorts

Sort_Pinnable_Cache_PercentDef 20

Hash_Pinnable_Cache_PercentDef 20

  • The above are the maximum percentage of a currently available sort/hash buffers that a sort/hash object will try to pin

  • If you are running big sorts – or large loads, with a small number of users then these can be increased


Pining bit maps

Pining Bit Maps

Bit_Vector_Pinnable_Cache_PercentDef 40

  • This is the percentage of a user’s temp memory allocation that any one persistent bit-vector (bit map – usually LF or HG) can pin in memory

  • We have played with this and (apart from increasing memory usage) it does not have any appreciable impact on performance! Sorry!


Cursors

Cursors

  • Force_No_Scroll_Cursors Def OFF

  • This defines all cursors as scrolling

  • The overhead on a large result set is huge

  • Unless you need scrolling cursors then switch this ON

  • Note: all result sets in IQ12 can be considered to be cursors


Query governor

Query Governor ?

  • Query_Rows_Returned_LimitDef 0

    • This sets up a counter to prevent queries executing that may return more than this many rows the Default is No Limit

  • Query_Temp_Space_limit Def 0

    • This will prevent the execution of a query that may use more than this amount of temp space the Default in No Limit

  • Max_Cartesian_ResultDef 100000000

    • Deny execution of a query if the result set of a Cartesian join exceeds this number

  • Max_IQ_Threads_Per_ConnectionDef 50

    • Thread constrainer. We cannot see any reason to play with this unless the server has some very, very complex queries running on it


G array hg options

G-Array (HG) Options

  • Garray_Fill_Factor_PercentDef 25%

    • This defines how much data is inserted into new G-Array pages – the default is 25% space

  • Garray_Page_Split_Pad_PercentDef 25

    • When there is a page split in a G-Array then the least filled page will be 25% full (This is the default)

  • Garray_Prefetch_SizeDef 3

    • When a G-Array is being scanned (be careful this is not as often as you might think) then the disk engine will retrieve this many pages (not blocks) in one IO


High group delete

High Group Delete

  • HG_Delete_Memory_MB Def 10

    • This option does nothing

    • This is an example of an option that was used (actually only for testing), then discarded from the code – but not removed from sysoptions

    • Be very aware of situations like this


High group insert

High Group Insert

  • Sort_Phase1_Helpers Def 1

    • This option specifies the number of sort helper threads for each sort object during a HG load

    • This can increase the parallelism of the load, and can improve the load performance

    • Beware this can eat memory if the HG index data is wide

    • Do not increase this if you have a large number of indexes, and a small number of processors

    • The option can only be set as 1 or 2


Row count or counts

Row Count or Counts

  • Row_Count

    • Specifies how many rows are returned in a query

    • Default is 0 – return all rows

    • This is very useful for checking SQL, without running the whole query, but beware of sorts, order by and group by phrases

  • Row_Counts

    • This is used by ASA to count the outgoing rows from the server to the client

    • This can affect certain 3rd party tools and will slow performance


Number of users

Number of Users

  • User_Resource_ReservationDef 1

  • This was the option “Optimise_for_this_many_users”

  • This tells the system to consider splitting Temp. Cache according to an “expected” number of IQ users on the system

  • The optimiser will always plan the usage of Temp. Cache for queries based on the larger of (this option and the actual number of current cursors)

  • We have found that you should leave this at the default 1 or change to 2 – and probably only increase it up to maybe 5

    • Unless you have a serious amount of Temp. cache memory


Checking the database

Checking the Database

  • There are a number of options that check parts of the database

  • If you think that the db is corrupt – yet check db does not show errors, then these can be set to on

  • They will slow down the server – a lot!

    HG_Consistency_Checkset to 4

    Btree_Consistency_Checkset to 1

    LF_Consistency_Checkset to 4

  • The results appear in the message log for the database


Dml options

DML Options

  • There are a series of options numbered DML_Option 1 thru 27(I think) andCORE_OPTION 1 to 13

  • These are, in the main, magic numbers for joins

  • It would be better not to touch them

  • DML_Option10 switches secure statistics On (see section on Query Tree Disassembly)


Cis option

CIS Option

512 – throw a warning if non IQ-M table used

2048 – force IQ-M execution only

0CIS off (but not the ASA optimiser!)

1CIS on

  • Only use this option if you are going to use CIS

  • There is an overhead when evaluating database objects in the parser if CIS is turned on – even if it is not used


Prefetch 1

Prefetch - 1

  • Prefetch_Sort_PercentDef.20

  • Prefetch_Garray_PercentDef.60

    • Garray Percent is the percentage of buffers (allocated to a client) that are used for read-ahead during HG searches (and loads)

    • Sort Percent is the percentage of user buffers that can be used for sort buckets (over the whole system) This is a temp cache variable

    • These are the only prefetch options that will/may speed up loading, as the HG is the only index that has a large READ component when loading, and also an HG has a large sort component in Pass 2


Prefetch 2

Prefetch - 2

  • Prefetch_Buffer_LimitDef. 20

  • Prefetch_Buffer_PercentDef.0

    • For all other prefetch conditions (excepting the last slide) these options are used

    • The percentage is the percentage of cache that could be made available for prefetching data

    • Prefetch is not a big thing in IQ-M because generally the indexes are not big

    • For the larger FACT table the indexes may grow to require a degree of prefetch

    • Try and see! (Sorry!)


Prefetch 3

Prefetch – 3

  • Cache_Threads_PercentDef5

    • This is the percentage of total system threads used for prefetch data

    • On a system using prefetch, it may be worth increasing this option

  • The option Prefetch is only used by the catalogue server and refers to the prefetch of results row over the SA to IQ internal bridge

  • Leave this ON, unless you want the great possibility of very slow performance


Re fetch

Re-fetch

  • Not the same as Prefetch

  • After any DML statement (INSERT,UPDATE, DELETE)

  • By default the query FETCH is repeated

  • For performance reasons (unless you need this information) switch it off

    Auto_RefetchDefON


Checkpoint recovery asa

Checkpoint & Recovery - ASA

  • Checkpoint_TimeDef 60 (minutes)

    • This is the time between checkpoints

    • Setting it bigger will speed up applications (to a small amount, maybe 5%)

  • Recovery_TimeDef 2 (minutes)

    • This is the maximum time the server will take to recover (in the event of system failure)

    • It is a a heuristic value (a guess!)

    • No performance improvement – other than systems that are taken down regularly (may also be relevant in a Multiplex)


Fp parallelism 1

FP Parallelism - 1

FP_Predicate_Workunit_PagesDef 500

  • You will notice in the Query Plan that there are times that the Optimiser will resort to using the FP or default index for aggregation

  • By decreasing the above parameter the degree of parallelism during a default index aggregation can be increased

  • Use with care – you can flood the server – do not reduce below 100-200


Fp parallelism 2

FP Parallelism - 2

  • Given a Work Unit of 500

  • Assume you have an FP on an INT column

    • INT = 4 bytes, Page = 64 kbytes => 7.5M rows/Work Unit

    • This may reduce parallelism if the table is small

  • Assume you have an FP in a char(200)

    • Char(200) = Page = 64 kbytes => 155K rows/Work Unit

    • This is maybe too small if the table is large


More parallelism

More parallelism

  • Group By operations can now be run in parallel

  • This is very useful – the speed up for Group By operations that rely on multiple keys is in the region of 20%

  • Set Parallel_GBH_Enable to ON to run groups by’s in parallel

  • By default the optimizer will use a fairly complex algorithm to calculate the degree of parallelism

  • The degree can be set by the option Parallel_GBH_Units to the number of threads required to perform the group by


Flatten subquery

Flatten_Subquery

  • This is a new option to 12.4.2 and it allows the optimiser to flatten EXISTS and NOT EXISTS

  • If Set an EXISTS clause is flattened to a join

  • If Set a NOT EXISTS clause is flattened to an anti-join

  • If not set an EXISTS (and NOT EXISTS) are handled by a filter


Wash day

Wash Day (?)

  • Remember the LRU MRU ?

  • The wash area and sweeper thread numbers can be controlled by

    • Sweeper_Threads_PercentDef10

    • Wash_Area_Buffers_PercentDef20

  • The threads is the percentage of currently used threads on the server (so this is variable)

  • The buffer percentage is the percentage of the WHOLE cache that is inside the wash area

  • Use IQ Monitor to see when these need to changed


A great brake for the server

A Great Brake for the Server

  • So your server is running too fast

  • Set Avoid_Vertical_Predicates to ON

  • This will only use FP indexes for vertical filtering

  • No fast indexes at all (Server wide!)

  • Maybe not a good one to play with…


Extracting data 1

Extracting Data - 1

  • In 12.4.2 there are a series of options that can be used to direct the results of a select clause to a file

  • The following can be set:

    • The output filename

    • The type of output (binary [swapped] or ASCII)

    • Delimiter strings (column and row)

    • Null handling


Extraction options 1

Extraction Options - 1

Temp_Extract_Name (1 thru 8)defaultblank

Temp_Extract_Size (1 thru 8)default0 (128GB)

Temp_Extract_BinarydefaultOFF

Temp_Extract_SwapdefaultOFF

Temp_Extract_Column_Delimiterdefault‘,’

Temp_Extract_Row_Delimiterdefault‘\n’

Temp_Extract_Null_As_ZerodefaultOFF

Temp_Extract_Quotedefault‘’

Temp_Extract_QuotesdefaultOFF

Temp_Extract_Quotes_alldefaultOFF

Note:A second select statement will overwrite the first output


Tunable options end

Tunable Options - End


  • Login