WIXLIB

Advanced DataToolsWebcastfrom the IBM Informix ChampionsInformix TutorialBasic Informix ServerMonitoringby Lester KnutsenThursday, August 20, 20202:00pm EDT

Informix Tutorials Webcastsby Lester Knutsen, IBM Informix ChampionA step by step guide to using Informix Database ServersØGetting Started with Informix – January ReplayØConfiguring a New Informix Server – February ReplayØManaging Informix Disk Space – March ReplayØManaging Informix Logs – April ReplayØInformix Backup, Recovery, and High Availability – May ReplayØConnecting Users to Informix Servers – June ReplayØCreating Databases and Tables in Informix – July ReplayØBasic Informix Server Monitoring – August ReplaySee the Complete Webcasts Series -topics/tech-beginners/

Lester KnutsenLester Knutsen is President of Advanced DataToolsCorporation and has been building large datawarehouse and business systems using InformixDatabase software since 1983. Lester focuses onlarge database performance tuning, training, andconsulting. Lester is a member of the IBM GoldConsultant program and was presented with one ofthe Inaugural IBM Information Champion awards byIBM. Lester was one of the founders of theInternational Informix Users Group and theWashington Area Informix User ols.com703-256-0267 x102Informix Monitoring3

Webcast Guidelines The Webcast is pre-recorded. TheWebcast replay and slides will beavailable after the broadcast. Please Mute your line - backgroundsounds will distract everyone. Use the Chat Button in the upper rightto ask questions.Informix Monitoring4

AgendaBasic Informix Monitoring Onstat – Discovery OptionsOnstat – Performance RatiosOnstat – User Sessions and ThreadsOnstat – Measuring Disk IOOnstat – Monitoring LocksOther Onstat OptionsOncheck – Basic Dbspace ChecksOmode – How to Terminate a SessionInformixHQ ExamplesInformix Monitoring5

Informix Command Utilities ONSTAT - Shows shared memory andserver statistics ONCHECK - Checks and repairs diskspace ONMODE - Changes Server's operatingmode and terminates User SessionInformix Monitoring6

Onstat – Monitor InformixServer Operations Onstat utility reads shared-memory structures andprovides statistics about the database server at thetime that the command executes. The contents of shared memory might change as theonstat output displays. The onstat utility does not place any locks on sharedmemory, so running the utility does not affectperformance. Onstat is a key utility to monitor the performance ofyour Informix server.Informix Monitoring7

Informix Shared MemoryResident Memory Segment Control Tables BuffersOnstat DataLRU Page PageLRU Page PageLRU Page PageLRU Page PageVirtual Memory Segment Dictionary cache Working Storage Sort SpaceMessage Memory SegmentInformix Monitoring8

Discover Your InformixServerOnstatOptionPurposeonstat -Show version, status, and uptime of the serveronstat –g osiShow operation system and machine infoonstat –g disShow known Informix servers on machineonstat -cShow server configuration ONCONFIG Fileonstat -dShow Informix dbspaces and chunksonstat -lShow logical logs statusonstat –mShow Informix server message logonstat -g schShow Informix oninit processes and classesonstat –g segShow Informix memory segmentsInformix Monitoring9

Current status of Server:onstat Current status: onstat -Current status when Server is downlester@merlin onstat shared memory not initialized for INFORMIXSERVER 'merlindb'lester@merlin Informix Monitoring10

Onstat Header Information Product and Version Mode (and Type) (Optional: Reason when Server isBlocked) Time Server has been up Size of Shared Memory in KbytesInformix Monitoring11

Mode of Server Off-Line Mode (does not show inheader) Quiescent Mode On-Line Mode Read-Only Mode (DR Only) Recovery Mode Shutdown ModeInformix Monitoring12

Reason when Server isblocked CKPT - CheckpointLONGTX - Long transactionARCHIVE - Ongoing storage-space backupMEDIA FAILURE - Media failureHANG SYSTEM - Database server failureDBS DROP - Dropping a dbspaceDDR - Discrete data replication (Informix)LBU - Logs full high-watermarkInformix Monitoring13

Onstat –g osi : ShowOperation System InfoInformix Monitoring14

Onstat –g dis: ShowInformix ServersInformix Monitoring15

Onstat –c: Show ONCONFIGFileInformix Monitoring16

Onstat –d: Show DBSpacesand ChunksInformix Monitoring17

Onstat -d FlagsThe "flags" for Dbspaces are:Position 1M - Mirrored DbspaceN - Not Mirrored DbspacePosition 2X - Newly mirroredP - Physical recovery underwayL - Logical recovery underwayR - Recovery underwayD - DownPosition 3B – BlobspaceP – PlogdbsS – SbspaceT – Temporary DbspaceU – Temporary SBSpaceW – Temporary Dbspace on SD ServerPosition 4B – Chunk greater than 2GB EnabledPosition 5A Auto expandablePosition 6E - EncryptedInformix MonitoringThe "flags" for Chunks are:Position 1P - PrimaryM - MirrorPosition 2O - On-lineD - DownX - Newly mirroredI - InconsistentN – Renamed and Down or InconsistentPosition 3B - Blobspace DbspaceT - Temporary DbspacePosition 4B – Chunk greater than 2GB EnabledPosition 5E – Chunk is ExtendablePosition 6- Direct IO not enabledC – AIX Concurrent IO enabledD – Direct IO Enabled18

Onstat –l: Show LogsCurrent LogInformix Monitoring19

Onstat -l Flags A - New and ready to useB - Backed upC - Current logical-log fileD - Marked for deletionF - Free and available for reuseL - Contains the last checkpoint recordU - UsedInformix Monitoring20

Onstat –m: Show MessageLogsInformix Monitoring21

Using “tail -f” tocontinuously show the endof message log file Note: I like to have the OnLine log file always display in oneof my windows on screen. The trick to doing this is to usethe UNIX "tail" command with the "-f" option. Thiscontinually reads the last lines of a file as it is appended to.On my system I run the following command to continuallymonitor this log:tail -f INFORMIXDIR/online.logInformix Monitoring22

Onstat –g sch: Show OninitProcess and ClassesInformix Monitoring23

Oninit Process Classes CPU - Executes all user and session threads and some system threadsPIO - Handles physical log file when cooked disk space is usedLIO - Handles logical log file when cooked disk space is usedAIO - Handles disk I/OSHM - Performs shared memory communicationsTLI - Performs TLI network communicationsSOC - Performs socket network communicationsFIFO - Performs FIFO operationsOPT - Handles optical disk I/OADM - Executes administrative threadsADT - Executes auditing threadsMSC - Handles request for system callsInformix Introduction24

Onstat –g seg: ShowMemory SegmentsInformix Monitoring25

Informix Memory Classes R – Resident Memory Segment B – Buffer Pool Segment for data V – Virtual Memory Segment forWorking Storage M – Message Segment forcommunications between clientsInformix Introduction26

Onstat –p: Server ProfilePerformance Ratios1. Disk IO2. Actions3. CPU4. Waits5. Read AheadInformix Monitoring27

Key Elements of onstat -p Reads %cached - The goal is 95% Writes %cached - The goal is 85% The BUFFERS parameter in your ONCONFIG file will affect thisvalue. Be careful - if you make the BUFFERS too large this willtake memory away from other processes and may slowdown your whole system. bufwaits - This indicates the number of times a user thread haswaited for a BUFFER. lokwaits - This indicates the number of times a user thread haswaited for a LOCK. deadlks - This should be zero. This indicates the number oftimes a deadlock was detected and prevented. dltouts - This should be zero. This indicates the number oftimes a distributed deadlock was detected.Informix Monitoring28

Key Ratios Calculated fromOnstat -p Disk IO - KB read and written perminute/hour Buffer turnover ratio per minute/hour Buffer wait ratio Read Ahead UtilizationInformix Monitoring29

Key Ratios - Onstat -pInformix Monitoring30

User Sessions and ThreadsOnstat Option PurposeOnstat –uShow User Sessions StatusOnstat –xShow User Sessions TransactionsOnstat –g sqlShow Sessions and SQLOnstat –g sesShow Session DetailsInformix Monitoring31

Onstat –u: User StatusInformix Monitoring32

User status: onstat -u FlagsFlags inB C G L S T Y X Flags in* Flags inA B P X C R H -Informix Monitoringposition 1Waiting on a bufferWaiting on a checkpointWaiting on a logical log buffer writeWaiting on a lockWaiting on a mutexWaiting on a transactionWaiting on a conditionWaiting on a transaction rollbackposition 2Transaction active during I/O errorposition 3Dbspace backup threadBegin workPrepared for commit workTP/XA prepared for commit workCommitting workRolling back workHeuristically rolling back work33

User status: onstat -u FlagsFlags in position 4P - Primary thread for a sessionFlags in position 5R - Reading callX - Transaction is committingFlags in position 6NoneFlags in position 7B - Btree cleaner threadC - Cleanup of terminated userD - Daemon threadF - Page flusher threadM - ON-Monitor user threadInformix Monitoring34

Onstat –x: Show TransactionsUserLogStartLogNowCurrent LogInformix Monitoring35

Onstat –g sql: List SQLstatementsInformix Monitoring36

Onstat –g ses: List SQLstatements and more by SIDInformix Monitoring37

Onstat - Show ThreadsOnstat Optiononstat -g athonstat -g reaonstat -g waionstat -g actonstat -g bthInformix MonitoringPurposeShow all threadsShow threads ready to runShow threads waiting to runShow active threads runningShow blocking threads38

Onstat –g ath: Show threadsInformix Monitoring39

Onstat – Show Disk IOOnstat Optiononstat -Donstat -g iofonstat –g iovonstat –g iohonstat –g ckponstat -Fonstat -RInformix MonitoringPurposeShow Dbspaces and Chunk IO StaticsShow Disk IO Statistics by Chunk/fileShow Disk IO Statistics by Oninit VPShow Disk IO HistoryShow Checkpoint StatisticsShow Buffer Flush StatisticsShow LRU Queue Statistics40

Onstat –D: Disk IOInformix Monitoring41

Informix IO Path to DiskInformix SharedMemory BuffersLRU QUE ThreadCleaner ThreadsAIO or KAIODisk ChunkInformix Monitoring42

Onstat –R: LRU StatisticsInformix Monitoring43

Onstat –F: Flush to DiskInformix Monitoring44

Flush to Disk Foreground writes occur when the Server needs a buffer andmust interrupt processing to flush buffers to disk to free abuffer. These are the least desirable type of writes. Background writes (LRU Writes) occur when a set percent ofthe buffers are dirty. This is controlled by the LRU parametersin the ONCONFIG file. These do not interrupt user processingand are the best for interactive systems. Chunk writes occur at checkpoints, and all dirty buffer pagesare written to disk. The more dirty pages, the longer acheckpoint will take. Checkpoint writes are sorted andoptimized, but the longer a checkpoint is, the longer it will blockuser activity. Checkpoint writes are best for batch systems.Informix Monitoring45

Onstat –g iov: Show IO byOninit ProcessInformix Monitoring46

Onstat –g iof: Show IO byChunkInformix Monitoring47

Onstat –g ioh: Show IOHistory by ChunkInformix Monitoring48

Onstat –g ckp: ShowCheckpoint HistoryInformix Monitoring49

LOCKS Onstat –k to Show LocksHow many Lock Table overflows?What User Owns the Lock?What Table is Locked?What Type of Lock is it?Informix Monitoring50

Onstat –k: Show Locks WARNING: If you have a large number of LOCKS defined in your ONCONFIGfile and many users, you could see thousands of rows from this command.Lock TableOverflowNeed ToIncreaseLOCKSInformix MonitoringNew in14.1051

Who owns a lock The "owner" column lists the address in shared memoryof the user who owns a lock. Use this with "onstat -u" tosee all users and compare this with the "address" columnto identify username of the owner.Informix Monitoring52