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 • Good Design and Correctly Specified Hardware counts more than 2 minutes of button pushing….
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 • These are not guidelines these are rules • Think of them as natural laws like gravity • 1) • 2) Do not touch an option until you know (not think you know) what it does Test, test and test again after you have set it – it must not damage something else
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) • 2) • 3) Those that will generally help – or must be set correctly Those options that may help The options you must not touch
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 • 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 – -cl – -ch suggested size of cache lower bound (min. allocated memory) Hard upper bound (max. memory to be allocated)
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 • To see the options Select * from sysoptions order by 2 • To change the options SET [ temporary ] OPTION [ userid. | PUBLIC. ] option-name = [ option-value ]
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 • Main_Cache_Memory_MB Def: 8 Mb Def: 4 MB Def: 0 – Used to set the size of the main cache – This must be set correctly • Temp_Cache_Memory_MB – Used to set the size of the temp. cache • Load_Memory_MB – 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 • 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 Operating System ASIQ Server ASIQ Overhead ASIQ Main Cache ASIQ Temp cache
Memory – 3 • Virtual Memory limits for IQ operating systems: – – – – SOLARIS (2. 6 & 2. 7) SOLARIS 2. 7 64 bit DUNIX (4. 0 D & 4. 0 E) AIX (4. 2. 1, 4. 3. 2) HPUX (11. 0) NT Windows 2000 (NT 5) SGI IRIX 64 3 GB 32 GB 27 GB (64 bit !) 2. 75 GB 2. 7 GB 2 GB (Cache limits) 4 GB 128 GB • You probably will not have this much real 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 • 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 ASCII Flat File Main Cache Load Memory Building Simple Index Pages Single Thread Per HNG, LF and FP Single Thread Multi Thread Pass 2 Temp. Cache Single Thread Per HG (maybe 2) 1 Sorting Groups Building B-Trees Row Delimited! Or it is Single Thread Pass 1 Raw IO Buffers Intermediate Buffers 2 1 3 Write of Completed HG B-tree G-Arrays and Bitmaps to Main Cache IO Operations to Temp Store indicate Not Enough Temp. Cache 2 Write of completed HNG, LF and FP Pages to Main IQ Store 3 Write of completed HG B-trees, GArrays and Bitmaps to Main IQ Store Temp. IQ Store
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 Pass 1 times
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_Load Def OFF
Low Fast Load - 1 Def 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 LF_Bitmap_Cache_kb Memory = (LF_Bitmap_Cache_kb * 1024) * column_distinct_count
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_Limit_MB Memory_Snapshot_First Memory_Snapshot_Increment Def 0 • These 3 flags, if set, will simulate an out of memory condition • These are DO NOT TOUCH flags
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 Def 20 Hash_Pinnable_Cache_Percent Def 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 Sort_Pinnable_Cache_Percent
Pining Bit Maps Bit_Vector_Pinnable_Cache_Percent Def 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 • • • 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 IQ 12 can be considered to be cursors
Query Governor ? • Query_Rows_Returned_Limit Def 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_Result Def 10000 – Deny execution of a query if the result set of a Cartesian join exceeds this number • Max_IQ_Threads_Per_Connection Def 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 • Garray_Fill_Factor_Percent Def 25% – This defines how much data is inserted into new G-Array pages – the default is 25% space • Garray_Page_Split_Pad_Percent Def 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_Size Def 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 • 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 • Sort_Phase 1_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 – 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 3 rd party tools and will slow performance
Number of Users • User_Resource_Reservation Def 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 • There 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_Check Btree_Consistency_Check LF_Consistency_Check set to 4 set to 1 set to 4 • The results appear in the message log for the database
DML Options • There a series of options numbered DML_Option 1 thru 27 (I think) and CORE_OPTION 1 to 13 • These are, in the main, magic numbers for joins • It would be better not to touch them • DML_Option 10 switches secure statistics On (see section on Query Tree Disassembly)
CIS Option 512 – throw a warning if non IQ-M table used 2048 – force IQ-M execution only 0 CIS off (but not the ASA optimiser!) 1 CIS 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_Sort_Percent Def. 20 60 • Prefetch_Garray_Percent – 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_Buffer_Limit Def. 20 0 • Prefetch_Buffer_Percent – 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 • Cache_Threads_Percent Def 5 – 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 • • 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_Refetch Def ON
Checkpoint & Recovery - ASA • Checkpoint_Time Def 60 (minutes) – This is the time between checkpoints – Setting it bigger will speed up applications (to a small amount, maybe 5%) • Recovery_Time Def 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 Def 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_Predicate_Workunit_Pages
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. 5 M 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 => 155 K rows/Work Unit – This is maybe too small if the table is large
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 • 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 (? ) • Remember the LRU MRU ? • The wash area and sweeper thread numbers can be controlled by – Sweeper_Threads_Percent Def 10 – Wash_Area_Buffers_Percent Def 20 • 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 • • • 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 • In 12. 4. 2 there 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 Temp_Extract_Name (1 thru 8) default blank Temp_Extract_Size (1 thru 8) default 0 (128 GB) Temp_Extract_Binary default OFF Temp_Extract_Swap default OFF Temp_Extract_Column_Delimiter default ‘, ’ Temp_Extract_Row_Delimiter default ‘n’ Temp_Extract_Null_As_Zero default OFF Temp_Extract_Quote default ‘’ Temp_Extract_Quotes default OFF Temp_Extract_Quotes_all default OFF Note: A second select statement will overwrite the first output
Tunable Options - End
Скачать презентацию Tuneable Options Tuneable Options This section
170e215132c30bd75aa228a16133b66d.ppt