1 / 75

Server Side Performance Enhancements

Scott Pickett – WW IDS Technical Sales For questions about this presentation contact: spickett@us.ibm.com. Server Side Performance Enhancements. Agenda. C/UDR Pre-load Autonomic DBA Procedures Partition De-Fragmenter Connection Time Enhancements Unique Event Alarms

yannis
Download Presentation

Server Side Performance Enhancements

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. Scott Pickett – WW IDS Technical Sales For questions about this presentation contact: spickett@us.ibm.com Server Side Performance Enhancements

  2. Agenda • C/UDR Pre-load • Autonomic DBA Procedures • Partition De-Fragmenter • Connection Time Enhancements • Unique Event Alarms • Prevent Accidental Instance Re-Initialization • Dbimport/Dbexport/Dbschema Enhancements • Linux Large Page Support • Enterprise Replication Supports Online UTF8 Conversion • Dbaccess Packaged with CSDK • Define the Esc Character

  3. C/UDR – Library Pre-load

  4. What Does Pre-loading a C-UDR Shared Library Do? • Pre-loading a C-UDR shared library allows IDS threads to migrate from one cpuvp to another during the execution of the C-UDR. • Without this feature, the thread executing the UDR is bound to the cpuvp for the duration of the C-UDR execution. • Allowing thread migration during C-UDR execution can increase performance.

  5. How do you Pre-load a C-UDR Shared Library? • PRELOAD_DLL_FILE - onconfig parameter: • This parameter is repeatable, once for each shared library. • Directory location of the library is its value. • PRELOAD_DLL_FILE$INFORMIXDIR/extend/test.udr • PRELOAD_DLL_FILE/work4/jmcmahon/foo.so

  6. Verifying the C-UDR Shared Library is Preloaded • online.logduring server startup: • 14:23:41 Loading Module </work4/jmcmahon/test.udr> • 14:23:41 The C Language Module </work4/jmcmahon/test.udr> loaded • onstat –g dllnew flags: • ‘P’ represents preloaded. • ‘M’ represents thread can migrate. • Datablades: • addr slot vp baseaddr flags filename • 0x4b247310 15 1 0x2a985e3000 PM /work4/jmcmahon/test.udr • 0x4c2bc310 15 2 0x2a985e3000 PM • 0x4c2e5310 15 3 0x2a985e3000 PM

  7. Limitations • Know which shared libraries to preload at server startup. • You cannot dynamically change PRELOAD_DLL_FILE with onmode –wm command. • If preloaded, remains so until the server is brought offline. • Unix, Linux, and MacOSX platforms can benefit from this feature. • You can still preload shared libraries on Windows: • Threads executing UDRs are already unbound on the Windows platform. • The benefit to this feature is already realized on Windows.

  8. Questions?

  9. New Autonomic DBA Procedures

  10. Agenda • Idle User Time Out • Bad Index Alerts • Auto CPUVP Configuration • Auto Compress/Repack/Shrink & Defrag • Alerts for In Place Alters Needing Completion

  11. Idle User Time-out • Terminate database user sessions that have been idle for a configurable amount of time. • By default this task is disabled. To enable the task simply update the sysadmin:ph_task table and set the tk_enable column to ‘T'. • Example: • DATABASE sysadmin; • UPDATE ph_task SET tk_enable = ‘T'; • WHERE tk_name = 'idle_user_timeout'; • Or click the ‘Enable Task’ box for the task idle_user_timeout in the task scheduler wizard entry in the Open Admin Tool (OAT). • The default idle value is 60 minutes.

  12. Bad Index Alert • By default this task is scheduled to run once a day and looks for indexes which had been marked as unusable. • The task will create an entry in the sysadmin:ph_alerttable for each index it finds.

  13. Auto CPU VP Configuration • This task is run at system startup and will add additional CPU VPS based on the number of system cpus. • The task is disabled by default. • To turn on this functionality requires updating the table sysadmin:ph_task and setting the tk_enable column to 'T' • DATABASE sysadmin; • UPDATE ph_task SET tk_enable = 'T' • WHERE tk_name = 'auto_tune_cpu_vps'; • Or click the ‘Enable Task’ box for the task auto_tune_cpu_vps in the task scheduler wizard entry in the Open Admin Tool (OAT).

  14. Auto CPU VP Configuration • The number of CPU VPS to be added is determined by calculating the difference between the 'target' number of CPU VPS and the currently configured number of CPU VPS. The target number is calculated as 50% of the system cpus. • The target number of CPU VPS is limited to 8.

  15. Auto Compress/Repack/Shrink and Defrag (1) • The purpose of this task is to automatically compress, repack, shrink and defragment tables / fragments in the database server that meet qualifying criteria. • Each operation can be enabled, disabled and configured via specific entries in the sysadmin:ph_thresholdtable.

  16. Auto Compress/Repack/Shrink & Defrag (2) • By default this task is disabled. To enable the task, simply update the sysadmin:ph_task table and set the tk_enable column to ‘T'. • Example: • DATABASE sysadmin; • UPDATE ph_task SET tk_enable = ‘T' • WHERE tk_name = 'auto_crsd';

  17. Auto Compress/Repack/Shrink and Defrag (3) • Compress • To enable auto compression, update the table sysadmin:ph_thresholdand change the value to ‘T' for the AUTOCOMPRESS_ENABLED row. • DATABASE sysadmin; • UPDATE ph_threshold SET value = ‘T' WHERE name = 'AUTOCOMPRESS_ENABLED'; • Default value = ‘F’

  18. Auto Compress/Repack/Shrink and Defrag (4) • Compress (cont'd) • The criteria for auto compressing a table/fragment is the number of rows. The default is 50000. • The name of this sysadmin:ph_thresholdparameter is 'AUTOCOMPRESS_ROWS' • To change the value, updatesysadmin:ph_threshold • DATABASE sysadmin; • UPDATE ph_threshold SET value = '10000' WHERE name = 'AUTOCOMPRESS_ROWS';

  19. Auto Compress/Repack/Shrink and Defrag (5) • Repack • To enable auto repack, update the sysadmin:ph_threshold table and change the value to ‘T' for the AUTOREPACK_ENABLED row. • DATABASE sysadmin; • UPDATE ph_threshold SET value = ‘T' WHERE name = 'AUTOREPACK_ENABLED'; • Default value is ‘F’

  20. Auto Compress/Repack/Shrink and Defrag (6) • Repack (cont'd) • The criteria for auto repacking a table/fragment is when the percentage of non-contiguous storage exceeds the user defined threshold. The default is 50. • The name of this sysadmin:ph_thresholdparameter is 'AUTOREPACK_SPACE'. • To change the percentage value, update sysadmin:ph_threshold. • DATABASE sysadmin; • UPDATE ph_threshold SET value = '90' WHERE name = 'AUTOREPACK_SPACE';

  21. Auto Compress/Repack/Shrink and Defrag (7) • Shrink • To enable auto shrink, update the sysadmin:ph_threshold table and change the value to ‘T' for the AUTOSHRINK_ENABLED row. • DATABASE sysadmin; • UPDATE ph_threshold SET value = ‘T' WHERE name = 'AUTOSHRINK_ENABLED'; • Default is ‘F’

  22. Auto Compress/Repack/Shrink and Defrag (8) • Shrink (cont'd) • The criteria for auto shrinking a table/fragment is configured as the percentage of unused allocated space. The default is 50. • The name of this sysadmin:ph_thresholdparameter is 'AUTOSHRINK_UNUSED'. • To change the percentage value, update sysadmin:ph_threshold • DATABASE sysadmin; • UPDATE ph_threshold SET value = '90' WHERE name = 'AUTOSHRINK_UNUSED';

  23. Auto Compress/Repack/Shrink and Defrag (9) • Defrag • To enable auto defrag, update the sysadmin:ph_threshold table and change the value to ‘T' for the AUTODEFRAG_ENABLED row. • DATABASE sysadmin; • UPDATE ph_threshold SET value = ‘T' WHERE name = 'AUTODEFRAG_ENABLED'; • The default is ‘F’

  24. Auto Compress/Repack/Shrink and Defrag(10) • Defrag (cont'd) • The criteria for auto defragging a table/fragment is when the number of extents exceeds the user defined value for sysadmin:ph_threshold threshold. • The name of this sysadmin:ph_thresholdparameter is 'AUTODEFRAG_EXTENTS'. • To change the value update sysadmin:ph_threshold • DATABASE sysadmin; • UPDATE ph_threshold SET value = '100' WHERE name = 'AUTODEFRAG_EXTENTS'; • Default is 100.

  25. Outstanding In Place Alter Alerts • By default this task is scheduled to run every 7 days and looks for tables that have In Place Alters outstanding. • The task will create an entry in the sysadmin:ph_alerttable for each table it finds that has an outstanding in place alter. • The task is called check_for_ipa.

  26. Questions?

  27. Partition Defragmenter

  28. Purpose • After appending to partitions, we may end up with many extents – over 200 – mapping a logical page number to physical address becomes slow. • Chunk allocations (allocating space from a chunk) is much slower if we have a lot of small extents – this is a very common operation. • Customer perception: partitions with too many extents are inefficient – why can’t I have a utility like I have on my PC? • Customer wants to de-fragment a heavily used partition for higher efficiency: • In one step. • Similar to the defragmenter in Windows. • This is not a graphic utility.

  29. Partition Layout

  30. Defragmenter Syntax • EXECUTE FUNCTION task( • “defragment”, “<database>:[<owner>.]<table>” OR • “<defragment partnum>”, <partition number list>); • For a single table: • execute function task(“defragment”,”stores:scott.test”); • For a single partition number: • execute function task(“defragment partnum”, “2098850”); • For a list of partitions: • execute function task(“defragment partnum”,”2098850”,”6890890”); • You can use execute function admin as well for each of these. • Indexes can only be defragmented using their partnum.

  31. Defragmenter Considerations (1) • No locking – page locks are not used. • Has to rollback, always leave chunk and partition consistent. • Has to roll-forward for HDR and Mach11. • One transaction cannot contain entire defragment – have to break it up. • After crash if defragmenter was running, a cleanup thread will remove all left over chunk extents – may take up to a minute or so.

  32. Defragmenter Considerations (2) • Will try to work with extents that can’t cover entire partition. • Will always reduce the number of extents – or return a failure code. • Tries to reduce the maximum of number of extents given free extent of a certain size. • If there is nothing to do (already has 1 extent) – returns “ok” - no error.

  33. Defragmenter Side Effects • Tends to use up the large contiguous extents. • Tends to move extents to the end of the chunk – so you might have to defragment then dbspace pack afterwards. • Only slows down access a bit while it is running.

  34. Defragmenter Presumptions • Simply abort a utility or the defragmenter if a conflict is detected – don’t wait until the other utility finishes. • Conflicts with: • Compression • Truncate • Drop table • Pack table • Alter table • System catalog tables

  35. Defragmenter – Logging • Logs each page move: • Can create a lot of log records. • May need additional logical log space. • Breaks up large defragment operations into many transactions: • No long transaction issue will exist. • Rollforward / rollback work fine – “oncheck -ce” will indicate no “holes” are left in chunks.

  36. Defragmenter Log Record Types • Start defrag on partition. • End defrag on partition. • Add new extent to partition (target extent). • Remove old extent from partition. • Move page.

  37. Caveats • Currently will not defrag: • Temp dbspaces. • System catalog tables. • Attempts to do these generate -21539 ISAM errors presently. • Pseudo tables. • Any dbspace or chunk containing any of the above. • Tblspace tblspace does not de-fragment if need be.

  38. onstat –g defragment • Displays information about active running requests to defragment partition extents: • IBM Informix Dynamic Server Version 11.70.UC1B6TL -- On-Line -- Up 21:21:29 -- 509852 Kbytes • Defrag info • id table name tid dbsnum partnum status substatus errnum • 0 sysadmin:informix.sysprocbody 140 1 1048810 DEFRAG_FAILED 0 0 • id – the ID of defragment segment. • table name – fully qualified table name being fragmented. • tid – thread id running the operation. • dbsnum – dbspace number of the object being fragmented. • partnum – partition number of the object being fragmented. • status – status of the completed/in progress defragmentation operation, possible values: • SEARCHING_FOR_EXTENT • MERGING_EXTENTS • DEFRAG_COMPLETED • DEFRAG_FAILED • substatus - the detailed status number, if any. • errnum – the last error number returned from the defragmentation request.

  39. Questions?

  40. Connection Time Enhancements

  41. User Story • At an independent client/server testing company, there were some IDS issues with a connect/disconnect benchmark. • It is possible that the customer didn't do some O/S or IDS tuning. • Nonetheless, we found things that could be improved in IDS from that benchmark.

  42. ASF Performance Enhancement • Solution: • Use the Network Name Service Caching. • Use multiple listener threads for DBSERVERNAME/ALIASES

  43. New ONCONFIG Parameter • NS_CACHE • Used todefine an expiration time measured in seconds for the database server to search for and then get host, service, user, and group information from the O/S Name Service Cache. • Search the memory cache first before going to the operating system: • Its much quicker than disk …… and almost always there these days. • Example: • NS_CACHE host=900, service=900, user=900, group=900 (default) • onmode -wm/wf - redefine expire time and invalidate cache. • If each NS_CACHE argument is 0, or the expire time above is reached, then the server will go to the operating system for the data. • Monitor onstat –g glo, if the msc vp is high relative to the cpu vp, then you may need to set NS_CACHE.

  44. NS_CACHE host=60,service=60,user=60,group=60 • mt_cache mechanism • onstat -g cache • Name 'Network Name Service HostByAddr' • ID 'hstad' • Total Mem 20K • Used Mem 17K • Mem Cap 2048K • # buckets 128 • Avg Len 0 • # elements 3 • Hits 149996 100.0 • Misses 9 • Total Acc 150005 • Invalidates 0 • Frees 0 • Drains 2 • Alloc Misses 0 • Alloc Drains 2 • Drain Fails 2 • MaxChainLen 16 • Rehash 0 • Last Drain 04/26/2010.10:36

  45. NS_CACHE host=60, service=60, user=60, group=60 • mt_cache mechanism • onstat -g cache • Name 'Network Name Service pwnam' • ID 'pwnam' • Total Mem 12K • Used Mem 8K • Mem Cap 0K • # buckets 32 • Avg Len 0 • # elements 1 • Hits 149994 100.0 • Misses 2 • Total Acc 149996 • Invalidates 0 • Frees 0 • Drains 2 • Alloc Misses 0 • Alloc Drains 2 • Drain Fails 2 • MaxChainLen 16 • Rehash 0 • Last Drain 04/26/2010.10:36

  46. New ONCONFIG Parameters • To allocate new listener threads: • DBSERVERNAME name[-n] • DBSERVERALIASES alias[-n],... • Where 'n' is the number of FD threads allocated. • FD threads are the listener threads. • DBSERVERNAME ids-8 • NUMFDSERVERS n - turns on/off multiple FD server feature.

  47. Multiple Listener Threads • DBSERVERNAME ids-8 onstat -g ath 16 700000041c02b78 0 1 running 31soc* soctcppoll 17 700000041c72178 0 1 running 32soc* soctcppoll 18 700000041c72708 0 1 running 33soc* soctcppoll 19 700000041c72c98 0 2 sleeping forever 1cpu* soctcplst 20 700000041ce54c0 0 2 sleeping forever 3cpu* soctcplst 21 700000041d0ac40 0 2 sleeping forever 4cpu* soctcplst 22 700000041d34418 0 2 sleeping forever 5cpu* soctcplst 23 700000041d59b20 0 2 sleeping forever 6cpu* soctcplst 24 700000041d84370 0 2 sleeping forever 7cpu* soctcplst 25 700000041da8a78 0 2 sleeping forever 8cpu* soctcplst 26 700000041dd32c8 0 2 sleeping forever 9cpu* soctcplst

  48. ASF Performance Test Result (AIX) (1) • Each Application has 200 threads x 100 connections. • Server side connections from 1000/sec to 4800/sec.

  49. ASF Performance Test Result (AIX) (2) • Each application has 200 threads x 100 connections.

  50. ASF Performance Test Result (Linux64) • Each application has 200 threads x 250 connections. • Server side connections from 250/sec to 1800/sec.

More Related