1 / 35

IBM Informix Dynamic Server 9.40 Time to Upgrade!

Informix Chat with the Lab - Jerry Keesee, Director of the Informix Lab - Jonathan Leffler, Informix Database Engineering. IBM Informix Dynamic Server 9.40 Time to Upgrade!. Jonathan Leffler IBM Informix Database Engineering. Agenda. Key New Features Big Chunks ER and HDR coexisting

eagan-oneil
Download Presentation

IBM Informix Dynamic Server 9.40 Time to Upgrade!

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. Informix Chat with the Lab- Jerry Keesee, Director of the Informix Lab- Jonathan Leffler, Informix Database Engineering

  2. IBM Informix Dynamic Server 9.40Time to Upgrade! Jonathan Leffler IBM Informix Database Engineering

  3. Agenda • Key New Features • Big Chunks • ER and HDR coexisting • Encrypted communications • Performance • Dynamic Locks and Logs • Miscellaneous Features • Upgrade and Reversion Process • Application Features • Questions and Answers

  4. Database Servers Investment Areas... • Performance, Availability, Scalability • Manageability • Development Productivity • Integrated Information • Informix Compatibility & Enhancements • Business Intelligence • Stability Dynamic Server

  5. IBM Informix Dynamic Server 9.40 • IBM took over Informix databases • Completed July 2001. • IDS 9.30.UC1 • Released September 2001. • Current Fix Pack 9.30.UC7, released January 2004. • IDS 9.40.UC1 • Released February 2003. • Current Fix Pack 9.40.UC5, released September 2004. • IDS 9.50.UC1 • Expected release date, 1Q2005. • IDS 9.Next • Planning started, possible release 2H2006.

  6. IBM Dynamic Server 7.31 • IDS 7.30 introduced in April 1998. • IDS 7.31 introduced in February 1999. • IDS 7.31.UD8 released March 2004. • Continued support announced through 2009. • No new features. • Bug fixes and upgraded platforms as necessary.

  7. Informix Dynamic Server 9.40 • Feature Enhancements

  8. Major Features • Big Chunks • Coexistence of HDR and ER • Encrypted communications • Performance • Dynamic Locks and Logs

  9. Big Chunks • Prior to IDS 9.40, the maximum size of a chunk was 2 GB. • This limit was too painful, so we removed it. • In IDS 9.40, each chunk can be up to 4 TB. • And an IDS instance can now have up to 32,767 chunks. • The new upper limit on the size of an IDS instance is: • 128 petabytes (roughly 144 * 1015 bytes) • How big is 128 PB? • You would need more than 570,000 x 250 GB drives ($60M+). • At a load rate of 1 GB per minute, it would take over 250 years to fill it. • Very Big!

  10. Big Chunks • Chunk sizes and offsets now 64-bit values. • Utilities all compiled to handle large files. • dbimport, dbexport, dbload, dbaccess • onunload, onload • onspaces • oncheck • ontape • High Performance Loader

  11. ER and HDR Coexistence • In IDS versions prior to 9.40: • You can use HDR (High Availability Data Replication) • For automatic failover. • Between two substantially identical systems. • You can use ER (Enterprise Replication) • For controlled replication of subsets of data. • Between many heterogeneous systems. • But you cannot use both at once.

  12. ER and HDR Coexistence • In IDS 9.40 • HDR systems can participate in ER systems too. • Some root nodes in the ER system would be HDR pairs. • Systems communicating with such nodes must be running IDS 9.40. • Hence, all root nodes have to be running 9.40 before using HDR. • ER systems normally contact the HDR primary server: • Automatically contact the secondary if the primary fails.

  13. Other ER and HDR Improvements • HDR supports extensible data types: • Replicates logged smart blob spaces. • Logged smart-large-objects (BLOB and CLOB) • BOOLEAN • LVARCHAR • complex types: row types, collections (LIST, SET, etc.) • ER now supports extensible data types. • ER has faster queue recovery from IDS 9.30. • Using dynamic logs feature of IDS 9.30. • ER event class identifiers and messages for ALARMPROGRAM.

  14. Encrypted Communications • Prior to IDS 9.40, password encryption only: • Using CSS/CSM technology. • From IDS 9.40, encrypt all conversations from client to server: • Also using CSS/CSM technology. • Using cryptography libraries from OpenSSL. • Not using OpenSSL directly because of design of ASF. • Configurable via sqlhosts file. • Optionally encrypt ER traffic: • Often sent over long-haul networks. • Protects data sent over Internet. • Not available for HDR in initial release.

  15. Improved Performance • During development of IDS 9.40: • Performance testing of most nightly builds. • Aggressively attacking performance issues. • Internal benchmarking of IDS 9.40 shows: • 8-15% improvement over 7.3x. • IDS 9.30 performance roughly the same as IDS 7.31. • Using variety of benchmarks: • TPC-C (not fully scaled – results not auditable by TPPC). • Vendor benchmarks.

  16. Fuzzy Checkpoints and UPDATE STATISTICS • IDS 7.31 users sometimes have trouble with long checkpoints. • IDS 9.20 introduces Fuzzy Checkpoints: • Not all dirty pages are flushed at checkpoint. • Write-ahead logging ensures consistency in fast recovery. • Allows larger buffer pools without impacting performance. • IDS 9.40 does UPDATE STATISTICS faster: • Uses memory better (DBUPSPACE). • Uses light scans. • Can run in parallel on fragmented tables, indexes.

  17. Shared Statement Cache • Query plans shared between connections • Big benefits for sites with lots of users and often repeated SQL statements SELECT F.First, B.Second FROM Foo F, Bar B WHERE F.A = B.A AND F.C = :C1 AND B.C = :C2; CPU-VP Plan (C1, C2) { } SELECT F.First, B.Second FROM Foo F, Bar B WHERE F.A = B.A AND F.C = :C1 AND B.C = :C2; Informix Dynamic Server 9.2 and later

  18. Dynamic Locks and Logs • Dynamic Locks • Prior to IDS 9.20: • Running out of locks was bad news. • With IDS 9.20 and above: • Server can dynamically add locks if lock table overflows. • Prevents application hangs. • Dynamic Logs • Prior to IDS 9.30: • Running out of space in logical logs was catastrophic. • So LTXHWM and LTXEHWM parameters were set low. • With IDS 9.30 and above: • Server dynamically adds extra logical logs as necessary.

  19. Miscellaneous Features • Rewritten B-Tree cleaning algorithms • Revised buffer priority management • Fractional percentages for LRU_MIN_DIRTY and LRU_MAX_DIRTY • Use full size of tape devices • Rename chunk devices during restore • No libraries installed in /usr/lib by default • Add chunks when first chunk full • Restartable fast recovery

  20. Prior to IDS 9.40: Single-threaded B-Tree cleaner. Could get overwhelmed by amount of work. Replaced by B-Tree Scanner: Prioritized workload. Multiple scanner threads possible. Alternative algorithms available. Controlled by onmode –C. Monitored by onstat –C. B-Tree scanner is tunable: By default, B-Tree scanner threads run at low priority. Set priority high if they are falling behind. Don’t increase number of low priority cleaner threads. Rewritten B-Tree Cleaning

  21. Buffer Priority Management • Prior to IDS 9.40, buffer manager: • Gave priority to memory-resident tables and index pages. • Had four tier priority system. • Simplified and streamlined for 9.40. • Two priority categories instead of four: • HIGH - managed by FIFO queue. • LOW - managed by LRU queues. • Classification of buffer priority is dynamic: • Based solely on observed access frequencies. • Maximum size of HIGH priority band fixed: • HIGH priority buffers can no longer consume buffer pool.

  22. Fractional Percentages for LRU Dirtiness • IDS 9.40 allows fractional LRU_MIN_DIRTY, LRU_MAX_DIRTY. • Old: setting LRU max/min values to 2/1 on 300,000 buffers • LRU writes start at 6,000 dirty buffers, stop at 3,000 dirty buffers. • Checkpoint has maximum of 6,000 buffers to write. • New: setting LRU max/min values to 0.5/0.1 on 300,000 buffers • LRU writes start at 1,500 dirty buffers, stop at 300 dirty buffers. • Checkpoint has maximum of 1,500 buffers to write. • Helps minimize checkpoint times: • Increases writes between checkpoints. • These are ‘wasted’ if a crash occurs. • Decreases writes at checkpoint.

  23. Use Full Size of Tape Devices • Prior to IDS 9.40, must specify actual size of tape: • Difficult to estimate capacity of tapes with compression. • Error if specified size too big – wasted space when too small. • From IDS 9.40, tape size of zero means: • Write until no room left on tape (or disk). • No more guessing how much compression you’ll get. • Can use different size tapes during backup. • Changed utilities include: • dbexport, dbimport • onunload, onload • ontape

  24. Rename Chunks During Restore • Map chunk device names during cold restore: • Not available during warm restore. • All chunk device names. • Mapping on command line or in file. • Primary and mirror chunks can be renamed too. • Allows recovery on similar systems: • The sets of disk devices do not have to be identical. • Replacement disk devices after a disk crash. • Can fix mistakes in device naming: • Inappropriate use of version in pathname: • /ifmx/v713/sbspace03 • Direct use of device names instead of symbolic links. • Do a level 0 archive after recovery

  25. No Libraries Installed in /usr/lib • IDS 9.40 does not place any libraries in /usr/lib. • Except for Java libraries under /usr/lib/informix on HP-UX. • Optical library specified by new parameter: • OPTICAL_LIB_PATH in ONCONFIG file. • HPL library specified by new parameter: • HPL_DYNAMIC_LIB_PATH in PLCONFIG file. • Default: $INFORMIXDIR/lib/ipldd09a.so • ON-Bar library specified by new parameter: • XBSA_LIBRARY_PATH in ONCONFIG. • Removed support for Smart Disks (ismdd09a.so) • Simplifies un-install of IDS . • Simplifies installation of multiple versions of IDS. • Don’t forget to clean up old Informix libraries.

  26. Add Chunks When First Chunk Full • Prior to IDS 9.40 • All chunk information only in first chunk of root dbspace. • If first chunk full, not possible to add new disk space. • With IDS 9.40 • If there is no space left in first chunk of rootdbs. • Extra chunk information can be added to other chunks in root dbspace. • Helps prevent conversion failures. • Helps support 32,767 chunks.

  27. Restartable Fast Recovery • Prior to IDS 9.40: • Crashes during fast recovery could corrupt the system. • No fast recovery possible. • Restore from archive was necessary. • In IDS 9.40: • Physical logging is enabled during fast recovery: • Allows recovery to occur multiple times if necessary. • Physical log could overflow during recovery. • PLOG_OVERFLOW_PATH specifies where overflow goes. • Default: $INFORMIXDIR/tmp

  28. Upgrade Considerations • New reserved words have been added. • Direct migrations to IDS 9.40: • IDS 7.24, 7.30, 7.31, 9.14, 9.20, 9.21, and 9.30 upgrade directly. • Indirect migrations to IDS 9.40: • IDS 7.20 through 7.23 require an interim upgrade to IDS 7.31. • All other migrations will require dbexport, dbimport. • Migrating from 32-bit to 64-bit uses same process. • Migrating to a different operating system requires export and import.

  29. Space Required for Upgrade • Root Chunk (Chunk 0) needs 10% free. • 3000 free pages of logical log space to rebuild sysmaster database. • 2000 KB of free space per database is needed for each dbspace. • Partition headers should not be full: • Use oncheck –me to compress extents.

  30. Upgrade Process • Save copies of configuration files: • ONCONFIG, sqlhosts, etc. • Save copies of storage manager files. • Remove outstanding in-place alters. • Close all transactions. • Shutdown the source database server. • Check for any open transactions. • Verify the integrity of the data with oncheck.

  31. Upgrade Process • Verify the server is in quiescent mode. • Disable HDR. • Make a final backup of the source database server. • Verify that the source database server is offline. • On UNIX or Linux, modify kernel parameters. • Install IBM Informix Dynamic Server 9.40. • Set environment variables. • Customize configuration files. • Add any communications support modules.

  32. Upgrade Process • Install and configure any DataBlade modules. • Initialize IBM Informix Dynamic Server 9.40. • Monitor the conversion complete status. • For ON-Bar, rename the sm_versions.std file. • Run UPDATE STATISTICS. • Verify the integrity of the data. • Make an initial backup of IDS 9.40. • Tune IDS 9.40 for performance. • Enable HDR.

  33. Application Features • DESCRIBE INPUT • ORDER BY values not in select-list • Sequence objects • Triggers on views • SQL-99 RIGHT OUTER JOIN • UNION in sub-queries • Names for return values • Multiple OUT parameters • Iterator functions in FROM clause • Hold cursors and PDQ • Character storage • Relaxing limit on functional index parameters • Costing R-Tree index usage • Unicode support

  34. http://www.ibm.com/software/data/informix

  35. http://www.ibm.com/software/data/informix

More Related