Proper Care and Feeding of your Ingres MDB

1. Proper Care and Feeding of your Ingres MDB Recommendations for General MDB Maintenance Read the notes on the foils! Revised 07/24/2006

2. Overview • Well thought-out MDB installation is only the beginning • Regular Maintenance Tasks • Optimization and Tuning Tips • Relocating Files • Troubleshooting • Backup/Recovery recommendations • List of Useful Commands

3. Before You Begin © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

4. Things you should do • Install Ingres on a disk with enough room available (a minimum of 100GB free space) – if possible, create Ingres Transaction Log(s) on separate disk(s) • Checkpoint, journal, and dump MUST be on a separate disk • Maintain the MDB often – optimizedb and ckpdb • Manage System Tables • Plan for MDB Backup/Recovery • Use appropriate MDB Settings – MEDIUM and LARGE

5. Logging Considerations • Ingres allows both the primary and dual transaction log (optional) files to exist on multiple devices (up to 16). Ingres improves logging system performance by allowing it to write to multiple disk devices. • When configuring multiple log partitions, keep the following in mind: • All log partitions must be the same size • The primary and dual transaction logs must use the same number of partitions. • Minimum recommended transaction log size is 3GB.

6. Logging Considerations • The Ingres log is a primary limiting factor for scalability • Log tuning is the first place to go if you cannot change a slow update query • Logs MUST be on safe DASD (protected by RAID) • If you lose the log you cannot recover the database

7. Transaction Log Location and Size • Some products may not give you a chance to specify where Ingres files should be stored – i.e. NSM r11 • You may need to increase the Transaction Log size depending upon data volume. In some cases 2 – 3GB may be more appropriate • Here is what should be done in order to configure Transaction Logs • Shutdown all products using the MDB as well as Ingres • Launch CBF from a Command Prompt, highlight Transaction Log and hit Shift+F1

8. Transaction Log Location and Size • Hit Shift+F4 (Destroy) and select Yes – destroy Transaction log

9. Transaction Log Location and Size • If you want to create the Transaction Log on a different disk, hit Shift+F3 (Delete), followed by Shift+F1 (Insert), and enter the new location

10. Transaction Log Location and Size • Now that a new location was picked, hit Shift+F1 (Create) and enter the new size of the transaction log in MB

11. Supplied MDB Settings - Medium • Default MDB settings • Supports Windows XP Pro – 4GB Virtual Memory required • Greatly improves Ingres performance by tuning key parameters dbms.*.connect_limit 500 gcc.*.inbound_limit 500 dbms.*.qef_hash_mem 49152000 gcc.*.outbound_limit 500 dbms.*.qef_sort_mem 8192000 rcp.log.archiver_interval 1 dbms.*.qef_sorthash_memory 675840000 ingstart.*.rmcmd 1 dbms.private.*.p8k.dmf_cache_size 15000 gcn.session_limit 64 dbms.private.*.p8k.dmf_group_count 4500 rcp.lock.list_limit 2048 dbms.private.*.p16k.dmf_cache_size 3000 rcp.lock.per_tx_limit 3000 dbms.private.*.p16k.dmf_group_count 0 rcp.log.database_limit 10 dbms.private.*.p32k.dmf_cache_size 2500 rcp.log.tx_limit 100 dbms.private.*.p32k.dmf_group_count 0 rcp.dmf_cache_size8k 200 dbms.*.log_writer 10 dbms.*.qef_qep_mem 25600 dbms.*.max_tuple_length 0 dbms.*.rep_qman_threads 0 dbms.*.default_page_size 8192 dbms.*.system_maxlocks 500 dbms.*.active_limit 256 dbms.*.opf_memory 36700160 dbms.*.system_readlock shared

12. Supplied MDB Settings - Large • setupmdb -II_MDB_SIZE=LARGE • Takes full advantage of Server class hardware: dbms.*.connect_limit 1000 rcp.lock.per_tx_limit 3000 dbms.*.log_writer 10 dbms.*.qef_hash_mem 65536000 dbms.*.max_tuple_length 0 dbms.*.qef_sort_mem 8192000 dbms.*.default_page_size 8192 dbms.*.qef_sorthash_memory 768000000 dbms.*.active_limit 1000 dbms.private.*.dmf_cache_size 15000 dbms.*.opf_memory 36700160 dbms.private.*.dmf_group_count 1500 gcc.*.inbound_limit 500 dbms.private.*.dmf_memory 55296000 gcc.*.outbound_limit 500 dbms.private.*.p16k.dmf_cache_size 5500 rcp.log.archiver_interval 1 dbms.private.*.p16k.dmf_group_count 0 ingstart.*.rmcmd 1 dbms.private.*.p16k.dmf_memory 90112000 gcn.session_limit 64 dbms.private.*.p32k.dmf_cache_size 5500 rcp.lock.list_limit 2048 dbms.private.*.p32k.dmf_group_count 0 rcp.log.database_limit 10 dbms.private.*.p32k.dmf_memory 180224000 rcp.log.tx_limit 100 dbms.private.*.p4k.dmf_cache_size 15000 rcp.dmf_cache_size8k 200 dbms.private.*.p4k.dmf_group_count 1000 dbms.*.qef_qep_mem 25600 dbms.private.*.p4k.dmf_memory 94208000 dbms.*.rep_qman_threads 0 dbms.private.*.p8k.dmf_cache_size 25000 dbms.*.system_maxlocks 500 dbms.private.*.p8k.dmf_group_count 5500 dbms.*.system_readlock shared dbms.private.*.p8k.dmf_memory 565248000

13. MDB Configuration • Supplied configurations have many tradeoffs – they were designed to support all products as we do not know which products are to be installed • One can do custom product specific configs with much better single product performance • NSM is the only product needing huge numbers of connections and a high locks per transaction value • You can reclaim much of the virtual storage when NSM is not present – this can be used for other purposes

14. MDB Custom Settings – NO NSM! • If the MDB is not being used by NSM, the parameters below can be custom set to the following values: gcn.session_limit 16 rcp.lock.per_tx_limit 750 dbms.*.active_limit 1000 dbms.private.*.p4k.dmf_cache_size 20000 dbms.private.*.p4k.dmf_group_count 1500 dbms.private.*.p8k.dmf_cache_size 55000 dbms.private.*.p8k.dmf_group_count 8500 dbms.private.*.p16k.dmf_cache_size 6500 dbms.private.*.p32k.dmf_cache_size 6500 • The values above increase performance across the board

15. MDB Custom Settings – Startup Count • Multiple CPUs – allow parallel transactions

16. MDB Custom Settings – Startup Count • Multiple CPUs – multiple transactions

17. MDB Custom Settings – Startup Count • Multiple NICs – default for MEDIUM/LARGE

18. CBF – Configuration By Forms • Ingres parameters can be configured via CBF - Configuration By Forms • Example: configuring dbms.private.*.p8k.dmf_cache_size • Open a Command Prompt Window and type in cbf.

19. CBF – Configuration By Forms • Highlight DBMS Server using the down arrow key and then press Shift+F1 (Configure)

20. CBF – Configuration By Forms • Press Shift+F3 to access to access Configure DBMS Caches • Highlight DMF Cache 8K using the down arrow key and then press Shift+F1 (Configure)

21. CBF – Configuration By Forms • Press Shift+F1 (Configure) to access Configure DBMS Cache Definition for 8K Buffers

22. CBF – Configuration By Forms • The parameter we want to configure is a derived one. So, press Shift+F2

23. CBF – Configuration By Forms • Then, highlight dmf_cache_size and press Shift+F1 (Edit) • Enter the new value: 55000 • Press Enter

24. CBF – Configuration By Forms • Keep pressing F10 until you get the following prompt: Save changes to DBMS Server Parameters? • Highlight Yes and then press Enter • Once you get back to the Configuration By Forms screen, press F6 to exit

25. Ingres Configuration Manager • Ingres parameters can also be configured via Configuration Manager UI • Example: configuring dbms.private.*.p8k.dmf_group_count • Launch the Ingres Configuration Manager utility in the Ingres [ EI ] program group

26. Ingres Configuration Manager • On the left panel expand the DBMS Servers tree and select (default) – all DBMS parameters will be displayed on the right panel

27. Ingres Configuration Manager • The parameter we want to configure is a derived Cache definition for 8K Buffers. So, click on the Cache tab on the right panel, highlight DMF Cache 8K, and then click on the Derived tab

28. Ingres Configuration Manager • Highlight dmf_group_count for 8K buffers and double-click on its value or Edit Value button. Then, type in the new value (8500 in this case) and press Enter • Click on Close when you are done

29. MDB Custom Settings –Navigating CBF and Ingres Configuration Manager • gcn.session_limit • Select Name Server – scroll down, highlight and edit session_limit • rcp.lock.per_tx_limit • Select Locking System – highlight and edit per_tx_limit • dbms.*.active_limit • Select DBMS Server – Derived tab/screen – highlight and edit active_limit • dbms.private.*.p4k.dmf_cache_size • Select DBMS Server – Cache tab/screen – highlight DMF Cache 4K – Derived tab/screen – highlight and edit dmf_cache_size • dbms.private.*.p4k.dmf_group_count • Select DBMS Server – Cache tab/screen – highlight DMF Cache 4K – Derived tab/screen – highlight and edit dmf_group_count

30. MDB Custom Settings –Navigating CBF and Ingres Configuration Manager • dbms.private.*.p8k.dmf_cache_size • Select DBMS Server – Cache tab/screen – highlight DMF Cache 8K – Derived tab/screen – highlight and edit dmf_cache_size • dbms.private.*.p8k.dmf_group_count • Select DBMS Server – Cache tab/screen – highlight DMF Cache 8K – Derived tab/screen – highlight and edit dmf_group_count • dbms.private.*.p16k.dmf_cache_size • Select DBMS Server – Cache tab/screen – highlight DMF Cache 16K – Derived tab/screen – highlight and edit dmf_cache_size • dbms.private.*.p32k.dmf_cache_size • Select DBMS Server – Cache tab/screen – highlight DMF Cache 32K – Derived tab/screen – highlight and edit dmf_cache_size

31. Suggested Regular Maintenance © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

32. Maintenance and Performance Tuning • MDB must be regularly maintained to produce good performance • Rebuild indexes, regenerate stats to reflect reality – usermod,optimizedb,ckpdb commands. • If you suddenly experience a performance issue or run out of disk space – think DB Maintenance! • You WILL run out of disk space if you forget to do maintenance

33. Optimizedb - daily • Can be run while db is active • Should be run whenever table is rebuilt or has major changes. Recommended – daily! • Syntax: optimizedb -zk -zw -umdbadmin mdb • If you add a lot of data, then run optimizedb before doing extensive queries – so, think about optimizedb after Argis reconciliation or a large Desktop Management replication finishes or after a large NSM discovery or after importing data into Service Desk (can make 3 orders of magnitude impact on performance)

34. Checkpoint - daily • Takes place online (while DB is in use) and is transparent to users • ckpdb command creates new checkpoint sequence, then copies log records of any DB changes that occurred during checkpoint procedure to dump file. • Permission required: System administrator, DBA, or an Ingres user with operator privilege. • Syntax: ckpdb -umdbadmin mdb • IMPORTANT: Free disk space must be monitored closely! Checkpoint and journal files will build up on the disk. However, always keep previous versions of checkpoint, dump, and journal files on tape! We will discuss that in greater detail later – Backup/Recovery Recommendations.

35. usermod • Usermod modifies the user defined tables only to their currently defined storage structure • Recreates any secondary indexes that are currently defined • Fixes overflow pages in a table – improves query processing performance • should be used on a regular (daily suggested) basis when: • Tables are in overflow • Need to reclaim space • Before optimizedb/sysmod • Can be run online where access to table is mostly permitted except at end when usermod takes exclusive access to table. • Syntax: usermod -umdbadmin -online mdb

36. Monitor DB Location Size • For 32-bit OS (W2K, RedHat ES 3.0), need to monitor size of files in the DB location. If > 2 GB need to : • Relocate table(file) from one location to another. E.g., modify employee to relocate With oldlocation = (loc1,loc2,loc3) newlocation = (loc4, loc5, loc6) • Reorganize table(file) across multiple locations. E.g., modify employee to reorganize with location = (loc1, loc2, loc3)

37. sysmod • sysmod reorganizes the system catalogs for DB to predetermined storage structures • If you create the MDB and have no (or limited) data in it and then use it or bulk load data -> run sysmod • If spread of data changes - > run sysmod • Sysmod requires exclusive access to the DB – and is not for regular live/online maintenance • Syntax: sysmod mdb

38. sysmod • You need a current backup (or two!) before running sysmod • The total time cost can be very high • Should not be done except on a carefully scheduled basis

39. Optimization © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

40. Statistics • Affect speed of query processing • Generating statistics for DB means effectively optimizing the DB • Run periodically on keys or index columns • Complete and accurate statistics result in greater and more effective query execution strategies • Inaccurate statistics can lead to wrong query execution plans to be chosen by optimizer and, therefore, to dramatic performance reduction

41. Statistics • Requires disk space because temporary tables are being created • Need to run this when significant change in column’s value • Stored in system catalogs (iistats and iihistograms) • Used by query optimizer to select an efficient query processing strategy • Syntax: optimizedb <options> dbname/tablenames(s) • Example 1: optimizedb -zv mdb –rai_fddef_prop • Example 2: optimizedb -zk mdb –rai_fddef_prop –rai_fsdef_prop -ahwuuid

42. System Catalogs • Also require maintenance as they are tables themselves and subject to data changes • sysmod will run the appropriate modify commands against the catalogs • sysmod takes an exclusive mode against the DB • Can be done on DB as well as individual tables

43. System Catalogs • symod requires exclusive access to DB (must be run while DB is “quiet”) • Can make this command wait till DB becomes free • Run whenever system catalogs are changes • Syntax: sysmod dbname/tablename(s) • Example 1: sysmod empdata • Example 2: sysmod -w empdata iirelation iiattribute

44. Optimization Recommendations • Ingres uses a Cost Based Query Optimizer to develop query execution plans – QEPs. • Optimizing the DB affects speed of query processing. More complete and accurate statistics -> more efficient query execution strategies - > faster system performance. • Without statistics, Query Optimizer can only guess what the data looks like, and “assumptions” are usually not valid for application SQL queries. • In VDBA, use Optimize Database dialog to generate database statistics for DB that is currently selected in Database Object Manager window. • At the command line, use optimizedb command: • Syntax: optimizedb -zk -zw -umdbadmin mdb

45. Frequency • How often should the MDB be optimized? • The simple answer is: often! Daily. • During roll-out it is expected that tables are getting populated. • Tables will keep growing – although values in some columns will change very little, others will change significantly. • optimizedb can be executed via shell scripts, batch files, etc. • Optimizing a database generally requires disk space, because temporary tables are created

46. Modify System Storage Tables • sysmod command modifies system tables (catalogs) of a database to predetermined storage structures. • sysmod should be run after optimizing the MDB. • System tables are modified to the most appropriate storage structure for accelerating query processing. • sysmod operation requires exclusive access to DB as well as DBA or system administrator privileges. • Syntax: symod mdb

47. Relocate Database Files © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

48. Types of Files in an Ingres Database • Data – User tables, indexes, and system catalogs • Checkpoint – Static copy of the entire database • Journal – Dynamic records of changes made to the journaled tables after the last checkpoint. Take regular checkpoints to minimize recovery time and avoid wasting disk space with journal files • Dump – Records of changes to the database that occurred during the dump process. Used to recover databases checkpointed online • Work – Used for system work, such as sorting and creating temporary tables

49. Why Relocate? • If data, work, checkpoint, dump and journal files are all currently located on the same disk, relocating them to separate disks will: • Improve fault tolerance and aid recovery from hardware failure (i.e., disk with data files). • Potentially improve performance • Gain disk space • HIGHLY RECOMMENDED! • IMPORTANT! Checkpoint, journal, and dump files can use only one location each.

50. Default Locations • Default Ingres locations are: • ii_checkpoint (checkpoint) • ii_database (data) • ii_dump (dump) • ii_journal (journal) • ii_work (work) • They are all set to the following: • Windows: C:\Program Files\CA\Ingres [EI] • UNIX\Linux: /opt/CA/IngresEI