1 / 48

Tuneable Options

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

abby
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 6 Tuneable Options

  2. 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….

  3. 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

  4. 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

  5. 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

  6. 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…

  7. 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 • -c suggested size of cache • -cl lower bound (min. allocated memory) • -ch Hard upper bound (max. memory to be allocated)

  8. 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

  9. 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 ]

  10. 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

  11. Memory – 1 • Main_Cache_Memory_MB Def: 8 Mb • Used to set the size of the main cache • This must be set correctly • Temp_Cache_Memory_MB Def: 4 MB • Used to set the size of the temp. cache • Load_Memory_MB Def: 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

  12. 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

  13. Memory – 3 • Virtual Memory limits for IQ operating systems: • SOLARIS (2.6 & 2.7) 3 GB • SOLARIS 2.7 64 bit 32 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 • NT 2 GB (Cache limits) • Windows 2000 (NT5) 4 GB • SGI IRIX 64 128 GB • You probably will not have this much real memory !

  14. 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.

  15. 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

  16. 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

  17. 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

  18. 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

  19. Low Fast Load - 1 LF_Bitmap_Cache_kb 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 Memory = (LF_Bitmap_Cache_kb * 1024) * column_distinct_count

  20. 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

  21. Memory – Do Not Touch Memory_Limit_MB Def 0 Memory_Snapshot_First Def 0 Memory_Snapshot_IncrementDef 0 • These 3 flags, if set, will simulate an out of memory condition • These are DO NOT TOUCH flags

  22. 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

  23. Pining Hashes and Sorts Sort_Pinnable_Cache_Percent 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

  24. 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!

  25. 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

  26. 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_ResultDef 100000000 • 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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_Check set to 4 Btree_Consistency_Check set to 1 LF_Consistency_Check set to 4 • The results appear in the message log for the database

  33. 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)

  34. 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

  35. Prefetch - 1 • Prefetch_Sort_PercentDef. 20 • Prefetch_Garray_Percent Def. 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

  36. Prefetch - 2 • Prefetch_Buffer_Limit Def. 20 • Prefetch_Buffer_Percent Def. 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!)

  37. 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

  38. 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_RefetchDef ON

  39. 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)

  40. FP Parallelism - 1 FP_Predicate_Workunit_Pages 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

  41. 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

  42. 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

  43. 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

  44. Wash Day (?) • Remember the LRU MRU ? • The wash area and sweeper thread numbers can be controlled by • Sweeper_Threads_PercentDef 10 • Wash_Area_Buffers_PercentDef 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

  45. 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…

  46. 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

  47. Extraction Options - 1 Temp_Extract_Name (1 thru 8) default blank Temp_Extract_Size (1 thru 8) default 0 (128GB) 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

  48. Tunable Options - End

More Related