CPU efficiency

Posted in 2021

Summary

John Smith saw about 40% CPU efficiency in onstat -g glo on a busy virtual OLTP server. Mallik Damaraju reported a similar problem and fixed it by running update statistics high on heavily scanned system catalog tables, then onstat -z, noting it is not officially documented. Doug Lawry advised also resizing the DD_*, DS_* and PC_* schema cache parameters. Outcome not reported.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

John Smith — source: IBM Community (ConnectedCommunity.org) Informix forum

Hello seen with onstat -g glo on a heavy OLTP server , CPU efficiency is about 40% ? do i need to change some parameters (VPCLASS CPU ...other ?) Thanks ------------------------------ John Smith ------------------------------ #Informix

↪ replying to John Smith

Jon Ritson — source: IBM Community (ConnectedCommunity.org) Informix forum

Is this on a REAL machine or a Virtual machine? ------------------------------ Jon Ritson ------------------------------

↪ replying to Jon Ritson

John Smith — source: IBM Community (ConnectedCommunity.org) Informix forum

Virtual machine regards ------------------------------ John Smith ------------------------------

↪ replying to John Smith

Mallik Damaraju — source: IBM Community (ConnectedCommunity.org) Informix forum

Hi John, Is your Informix on cooked devices ? Are you using ver 12.10 and above ? Do you notice sequential scans on system catalog tables (like sysdistrib etc) ? Do those tables appear top of the sequential counts ? We have noticed similar CPU (degraded) efficiency on our high OLTP DB. Also noticed that system catalog tables were doing lot of sequential counts We implemented the following: a) update statistics high on offending system catalog tables b) zero profile counts (onstat -z) c) run find sequential scans query again It should now not show system catalog tables and your CPU efficiency will improve. Additional Note: If you are using AWS EC2 instances, you can definitely see a big drop in the graph for "EBS: Average I/O across all volumes" PS: There is no 'official' documentation from IBM/HCL about updating system catalog tables. Please be aware of this and any side effects. HTH Regards Mallik ------------------------------ Mallik Damaraju ------------------------------

↪ replying to Mallik Damaraju

Doug Lawry — source: IBM Community (ConnectedCommunity.org) Informix forum

| view attached Hi Mallik. It would be better also to resize schema caches so system catalog tables don't need continuous rereading. See DD_* DS_* PC_* in $ONCONFIG. The definitive IIUG presentation by Mark Jamison on this is attached. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Related threads

Glossary

onstat