1 / 42

Everything They Never Told You About Your ONCONFIG File

Everything They Never Told You About Your ONCONFIG File. Presenter Art S. Kagel Session: A06 Advanced DataTools Corp. Mon May 16, 5:00pm. Everything They Never Told You About Your ONCONFIG File.

arvid
Download Presentation

Everything They Never Told You About Your ONCONFIG File

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. Everything They Never Told You About Your ONCONFIG File Presenter Art S. Kagel Session: A06 Advanced DataTools Corp. Mon May 16, 5:00pm

  2. Everything They Never Told You About Your ONCONFIG File I will go through many of the ONCONFIG parameters concentrating on those that are less well defined I will present my recommendations for setting the parameter or for determining an appropriate setting. I will present some undocumented parameters and environment settings. 2

  3. Security Configuration Parameters • ADMIN_MODE_USERS • List of users who can connect to the server while it is in administration mode (onmode -j) • ADMIN_USER_MODE_WITH_DBSA • When set to 1 ADMIN_MODE_USERS takes effect • When set to 0 only Informix can connect to the server in administration mode • I can't think of a good reason to let any other user than informix on the server during maintenance. • The exception would be if you utilize DBSA users other than informix to manage DBSA access. Then the DBSA users should be granted access during admin mode operations.. 3

  4. Security Configuration Parameters • DBCREATE_PERMISSION • List of users authorized to create new databases • On production and QA servers ONLY informix should be listed • On development servers only Data Architects and Development Team Leads should be able to do this besides informix 4

  5. Security Configuration Parameters • IFX_EXTEND_ROLE • The default setting, 1, restricts the ability to register UDR's to users granted that privilege • Setting to 0 allows anyone to register new UDRs. • This should be kept set to '1' and privilege granted only to the database owner and DBA users in each database. 5

  6. Security Configuration Parameters • UNSECURE_ONSTAT • Removes the restriction preventing non-DBSA users from executing onstat commands that might reveal privileged data to a non-privileged user. (ex: onstat -g sql) • On production and QA servers this should be set to '0' • On Development and Test servers you can make an argument for developers to be able to monitor all server activity to determine whether performance problems are related to their work or other activity. 6

  7. Security Configuration Parameters • SECURITY_LOCALCONNECTION • If set to 1 informix checks whether the connected user's OS authentication name matches the user name included in the connection string. • Prevents someone on a trusted host from connecting to the database as a more privileged user. • If set to 2 informix also checks to see that the response port for a query is the same port assigned to the user when he connected. • Prevents spoofing to gain control of another user's session privileges. 7

  8. Security Configuration Parameters • DB_LIBRARY_PATH • Exclusive list of paths from which extension libraries can be loaded. • If not set extensions can be loaded from anywhere. • Prevents users who do not have privileges to add a library to legitimate library paths from registering new UDR/UDT code or replacing legitimate libraries with bogus ones. • Don't forget to include $INFORMIXDIR/extend! 8

  9. Alarm Event Configuration Parameters • ALARMPROGRAM • The alarm program can be a script or a binary executable 9

  10. Alarm Event Configuration Parameters • ALARM_ALL_EVENTS • By default (0) only event severity 2 or higher are forwarded to the ALARMPROGRAM • Set to 1 to be able to filter for 'normal' events such as “log backup completed” • You will want to set this if you are using the ALARMPROGRAM to interface to a 'syslog'-like service or otherwise record server events outside of the Informix message log 10

  11. Alarm Event Configuration Parameters • STORAGE_FULL_ALARM <secs>,<severity> • Secs – how frequently to send a message if dbspaces are full • Severity – Severity level to send to the ALARMPROGRAM on storage space events 11

  12. Alarm Event Configuration Parameters • SYSALARMPROGRAM • Path of a script or binary task to execute if there is an 'af' event (server failure) • Defaults to evidence.sh • Server freezes while the SYSALARMPROGRAM is executing to gather static information for IBM Tech Support • If you need fast restart capability, you should disable this as evidence.sh makes shared memory dumps 12

  13. Tblspace Configuration Parameters • TBLSPACE_STATS • Low impact • High information content • Enables detailed IO statistics by partition • Turn this one on 13

  14. Tblspace Configuration Parameters • TBLTBLFIRST • TBLTBLNEXT • First and Next Extent sizing for the rootdb dbspace at server initialization time only • Specified in KB, must be a multiple of the default pagesize • Default is 250 pages and 5 pages respectively • You can control TBLSpace extent sizing for other dbspaces using onspaces at dbspace create time (11.50+). 14

  15. Temporary Dbspace Configuration Parameters • DBSPACETEMP • Unless you have TEMPTAB_NOLOG set, this should include one or more low activity non-temp dbspaces to use for creating logged temp tables instead of using rootdbs. • If you are using RAW chunks, consider placing temp dbspaces in COOKED files to take advantage of the OS filesystem cache's performance characteristics to speed sorts and other temp IO. 15

  16. System Configuration Parameters • FULL_DISK_INIT • Prevents oninit -i from initializing rootdbs if there are pages in it that look like they may belong to a valid instance. • To intentionally trash an instance, set FULL_DISK_INIT to '1' • Oninit -i will set it back to '0' once the new instance has been initiallized, so you can't accidentally trash your new instance 16

  17. System Configuration Parameters • MSG_DATE • Prepends current date and time to message log lines • Some overhead increase, largest effect if you are using AIO VPs for chunk IO also 17

  18. System Configuration Parameters PRELOAD_DLL_FILE The PRELOAD_DLL_FILE configuration parameter specifies the path name for a shared library file that is preloaded when the database server is started. You must specify a separate occurrence of this parameter for each library file that you want to preload. Pre-loading shared libraries is required for C language UDRs if they are to be permitted to migrate between CPU VPs and are to be parallelized 18

  19. System Configuration Parameters SMX_COMPRESS -1/0/1-9 Specifies the level of compression used to send data to a remote server -1 – no compression ever 0 – only compress if the remote uses compression 1-9 – increasing compression levels It's not clear whether this affects distributed queries, mach11 replication or both Should have a major positive impact over slow networks and WANs 19

  20. CPU Parameters • VP_MEMORY_CACHE_KB • Allows CPU VPs to keep some virtual memory they get from the central memory pool • Reduces contention for the memory pool latch • If the central pool is depleted, CPU VPs will return some of this cache back for other VPs to use • VPs will still go to the central pool to process larger queries then return any overage when the query is complete 20

  21. AIO and Cleaner Parameters • VPCLASS aio • Even if you are using AUTO_AIOVPS, use this to: • Set a minimum level that experience supports • Disable process aging of aio vps with the 'noage' option • Can also use processor affinity to isolate AIO VPS from the CPU VPs and other processes • Noage is particularly important running under AIX and HPUX which both aggressively lower the priority of long-running processes 21

  22. AIO and Cleaner Parameters • CLEANERS • Set to the larger of: • The number of chunks times 1.5 • The total number of lru queues in all buffer pools • You do not have to count chunks for dbspaces that are not actively updated during peak activity periods. Ex: Chunks of dbspaces that only have historical tables resident. 22

  23. Network Configuration Parameters • NETTYPE ipcshm • If shared memory connections are only for maintenance:NETTYPE ipcshm,2,50,CPU • If shared memory connections are used for clients:NETTYPE ipcshm,<numcpuvps>,<max/polls>,CPUShared memory poll threads should run in every CPU VP for best responsiveness during extreme loads. 23

  24. Network Configuration Parameters • NETTYPE soctcp/tlitcpNETTYPE soctcp,<num_needed>,300,NET • IBM estimates that a single poll thread can efficiently handle from 300-350 connections. • Calculate <num_needed> based on 300 to allow for unusual connection volume. • You can include a VPCLASS net... entry to permit you to configure noage and affinity for the NET VPs. 24

  25. Network Configuration Parameters • LISTEN_TIMEOUT <nsec> • Number of seconds after a client attempts to connect that Informix will wait for a valid protocol handshake before shutting down the attempt. • Keeping this short can limit the effects of DOS attacks. • Default: 60 25

  26. Network Configuration Parameters • NS_CACHE host=900,service=900,user=900,group=900 • Informix will now (11.70+) cache DNS, service/port, username, and user group database information to speed up repeated connections from the same sources • Improves performance for poorly written PC & Java applications that make a separate connection for each query and close it afterwards • Will refresh the cache after the specified number of seconds has expired since the last refresh. Default: 900 seconds 26

  27. Asynchronous IO Parameters • DIRECT_IO • 0 – Disabled • 1 – Enable O_DIRECT if supported • 2 – Enable O_DIRECT and O_CONCURRENT if supported (AIX) • AIX only supports Concurrent IO and Direct IO for filesystem files, not block devices! • You also have to enable the filesystem's 'noaccess' flag to prevent constantly updating the inode's modified time 27

  28. Scan Configuration Parameters BATCHEDREAD_TABLE 0 BATCHEDREAD_INDEX 0 BATCHEDREAD_KEYONLY 0 For now do not use except for testing ADTC's testing shows this feature is still a bit raw 28

  29. Update Statistics Parameters AUTO_STAT_MODE 1/0 Enables/disables the mode for selectively updating only stale or missing data distributions in UPDATE STATISTICS operations This is a good one. Turn it on. 29

  30. Update Statistics Parameters STATCHANGE 10 The percentage change that triggers updating statistics for a table when AUTO_STAT_CHANGE is enabled This replaces the -b/-B Browse option in dostats! It examines updates and deletes as well as inserts which dostats does not do (couldn't prior to 11.50). Yeah – one less thing I have to code for! 30

  31. Undocumented Environment Variables • IFX_NO_AIOVP_TUNING • Environment variable to turn off automatic AIO VP and Cleaner thread tuning. • Range of values: none -- oninit just checks for existence during startup • Prints this message in the online.log when enabled: "Turning off automatic AIO VP and Cleaner thread Tuning" 31

  32. Undocumented Environment Variables • IFX_SORT_POOLS Environment variable to avoid memory contention when too many sessions attempt a SORT at the same time. Informix preallocates SORT pools at the time the session starts. The basic parts of the pool (that is the first memory allocations) will be kept around during the session lifetime. Other parts will be freed at the time the SORT closes. However, if a SORT starts to consume large amounts of memory, the engine would spend too much time freeing the pool blocks one by one. Therefore, we simply drop the whole pool. Range of values: P:M P = Number of SORT pools to preallocate for a session. M = Maximum size of Pool before it gets dropped (In Kb). Setting: export IFX_SORT_POOLS=7:100 This would allocate 7 sort pools for a session. Any pools larger than 100 Kb will be dropped whole at the end of sorting instead of being freed piecemeal. 32

  33. Undocumented Environment Variables • NOTXSURVIVAL • An option in the CDR_FEATURES environment variable • If included, disables Transaction Survival for ER purposes 33

  34. Undocumented Environment Variables • IFX_LARGE_PAGES • 1 – use large memory pages if supported • Any other value – no large pages • If not supported a message is printed in the message log: WARNING: IFX_LARGE_PAGES not supported on this platform • Performance improvement on Linux, AIX, and Solaris when this is enabled 34

  35. Undocumented Environment Variables • IFX_NO_AUTO_CKPTS • If this variable exists in the server's environment it disables automatic checkpoint processing. Prints a message to the message log: • Turning off Automatic Checkpoints 35

  36. Undocumented Environment Variables • IFX_NO_LRU_TUNING • If this variable exists in the server's environment it disables automatic LRU tuning. Prints a message to the message log: • Turning off LRU Tuning 36

  37. Undocumented ONCONFIG Parameters • BTAPPENDERS • This one is considered “unsupported”. • Configures the number of threads used to append entries to the B+Tree during a parallel index build. • Values are unlimited and not validated, so you can get into trouble here – CAREFUL! • Some suggest limiting this to the number of CPU VPs • I would not configure it greater than 2x # CPU VPs to avoid internal contention and deadlocks • May help with indexing very large tables • Default: Dynamic based on the number of rows in the table or partition. 37

  38. Undocumented ONCONFIG Parameters • SORT_MERGE_SIZE • Range: 0 to 2147483647 • Default: unknown Allows you to specific the optimal number of rows for an in memory quick sort. Recently reduced to improve sort parallelism and so sorts start passes sooner. 38

  39. Undocumented ONCONFIG Parameters • CF_SDCLONE_TIMEOUT • Timeout if Shared Disk secondaries fall behind. The slowest secondary is killed if the timeout is reached. • Default: 10 seconds • Range: 2 to 2147483647 39

  40. Undocumented ONCONFIG Parameters • UDR_SAFE_MIGRATE • Default: 1 – enabled • 0 – disabled • Controls the new UDR migration feature • Enabling allows 'C' UDRs to migrate between CPU VPs permitting parallelism within UDRs and for sessions calling 'C' UDRs • Disabling binds each 'C' UDR to the first CPU VP in which it is run. Threads calling that UDR will have to migrate to that CPU VP in order to continue and may have to wait in the ready queue. (pre 11.70 behavior) 40

  41. Questions ?!? 41

  42. Everything They Never Told You About Your ONCONFIG File Art S. Kagel art.kagel@gmail.com

More Related