Tom Hogarty (hogarty.pad@sni.de), whose address bounces, suggests:
Points from bottom up.
I know that tuning/optimisation is/can be a complex subject and would not hope to cover the subject in any sufficient completeness here, but there is always the Fine Manual to refer to along with
DATABASE TUNING... by Dennis E. Shasha ISBN 0-13-205246-6
SED & AWK by Dale Dougherty ISBN 0-937175-59-5
SYSTEM PERFORMANCE TUNING by Mike Loukides ISBN 0-937175-60-9
Informix OnLine Performance Tuning ISBN 0-13-124322-5
by Elizabeth Suto Published by Prentice Hall
The first is general db tuning while the second is an invaluable guide to sed/awk which I find a great in managing the various tbstat/tbcheck outputs. and the latter is a guide to Unix system tuning. Elizabeth Suto's book has been described as "both excellent and concise". Joe Lumbley's well publicised Survival Guide may also be worth a look
I hope some of you can further refine and correct my outpouring so that the message is clear.. optimise resources, remove botlenecks and keep on reading the manual.
The scripts mentioned in part 6:
# Name : lmode
# Author : Tom Hogarty
# Program : Integrated script/awk program to read/format/output
# the current (row level) lock modes in a database.
# Syntax : lmode >>modes.sql
# ( update the new db calling [isql|dbaccess] <dbname> modes )
# call as user informix
>/tmp/tom
echo " select 'ALTER TABLE ', owner, tabname
from systables
where locklevel = 'R' " >/tmp/tom
# edit the following line
[isql|dbaccess] <dbname> - </tmp/tom |
awk '
$0 ~ /ALTER TABLE/ { tabname=hoch1$3hoch2$4
printf " %12s %24s %34s \n",
"ALTER TABLE", tabname, " LOCK MODE (ROW) ; commit work ;"
} ' hoch1=\" hoch2=\".
# Name : nextx
# Author : Tom Hogarty
# Program : Integrated script/awk program to read/format/output
# the current next extent sizes in a database.
# Syntax : nextx >>extents.sql
# ( update the new db calling [isql|dbaccess] <dbname> extents )
# call as user informix
>/tmp/tom
echo " select 'EXTENTS',
owner,
tabname,
nextsize
from systables
where owner <> 'informix'
order by systables.owner,
systables.tabname;" >/tmp/tom
# edit the following line
[isql|dbaccess] <dbname> - </tmp/tom |
awk '
$0 ~ /EXTENTS/ { tabname=hoch1$2hoch2$3
nextsize=$4*2
printf "%12s %24s %18s %4s %15s\n",
"ALTER TABLE ",tabname," MODIFY NEXT SIZE ",
nextsize, "; COMMIT WORK ;"
} ' hoch1=\" hoch2=\".
On 11th Dec 1997 kagel@ns1.bloomberg.com (Art S. Kagel) wrote:-
BTW: I noticed your NETTYPE parameter for onipcshm in CPU VPs with 1 listener. If you increase this to 4 listeners there will be a listener in each CPU VP and you will 1) spread the load across the CPU VPs better and 2) be more responsive. The problem, I have found, is that the VPs with listeners take most of the work onto themselves which makes them too busy to listen but when they free up to listen they are not busy so they add the next job to their own queue instead of handing it off to another CPU VP. So 3 of your CPU VPs are in wait loops waiting for work most of the time while CPU VP1 is swampped. Check the CPU time distribution over the VPs in onstat -g glo with the current config and with 4 listeners and you can convince yourself. We started with 28 CPU VPs and 8 listeners as Informix consultants thought we should only need a few listeners. There were always some applications that were periodically paused waiting for service but 20 of the CPU VPs were never busy. We increased to 28 listeners and voila! No more delays and better VP utilization.
On 28th Oct 1997 kagel@ns1.bloomberg.com (Art S. Kagel) wrote:-
OK. Basically a NET VP (shown as class tli, soc, ipx, shm, str in the onstat -g glo report) is a Virtual Processor whose only job is to poll the various connections for new sessions and additional queries on existing sessions and pass the work off to CPU VPs as needed. The CPU VPs are doing actual query work for various sessions and in addition can handle exactly one poll thread to do listening itself. Basically it is best for networked connection types to have listeners in NET VPs and shared memory connection listeners in CPU VPs. Shared memory listeners in NET VPs will poll incessantly burning CPU time. Network listener threads in CPU VPs use more system call overhead. The difference has to do with the fact that the NET VPs have nothing better to do than monitor the connections and so can block waiting for network activity but they cannot block on shared memory operations and so have to poll in a loop. Inversely the CPU VPs cannot block waiting for network activity, they have better things to do with their time, and so have to poll network connections each time they have completed some user thread operation. Meanwhile the very fact that they are busy most of the time doing real work means that they can only poll shared memory connections occasionally which reduces the polling overhead for shared memory connections versus NET VPs.
Jack Parker has provided "a useful record of the sorts of problems you have to deal with when using DATETIME and INTERVAL types". It's a bit large to put in the main body of the FAQ, but here's a slightly edited copy of his introduction. The full, unabridged, document appears later as Appendix I.
A word about datetime/intervals:
As most of you know a DATETIME identifies a moment in time. For example: On Oct 11, 1987 at 11:05:32.32AM my wife and I exchanged wedding vows.
Say we have a recurring event - an appointment which occurs every month at a particular time - say on the 4th at 11:30. In this case we don't care about the year or the month, just the day and the hour.
A datetime datatype has the granularity to measure whatever you want. It can go from YEAR to FRACTIONs (5 decimal places) of a second - depending on your OS (syntax supports 5 places, but our OS only fills two of them).
There is a reasonable discussion of all this in Appendix J of TFM (4gl ref).
An Interval measures a time span. For example '2 hours ago'. While this is not a datetime concept, it uses much the same syntax and you can specify it in the same manner (YEARs through FRACTION). So 'how long have you been married' is an interval question. 'When did you get married' is a datetime question.
Who in their right mind cares? Anyone who wishes to mark a specific moment in time.
It makes no sense to 'add' two instants in time (or two datetimes) - what you want to do is take an instant in time (DATETIME) and add a time span (INTERVAL). "In two days I'm getting married". Sure this makes sense talking about it like this, but it's not 'intuitively obvious to the most casual observer' when working with the stuff. It's real easy to screw up what you put and where you put it. I probably wasted half of the time I spent on this trying to manipulate one datetime with respect to another datetime.
---
Like I said at the start, see Appendix I if you want to learn more.
From: lester@access.digex.net (Lester Knutsen)
Informix SE has a feature (this is not available in Informix Online) to create an audit trail of all adds, deletes and updates to a table. This can be used to trace which user is changing critical data. The procedures to create an audit file are simple and well documented. However, it is not well documented on how to read or use the audit file. The SQL syntax to create an audit trail is:
create audit for table_name in "pathname"
The full pathname is required for this command.
An Informix audit file is structured the same as the original data file (.dat file) with a header. One way to access an audit file is to convert it into an Informix database table. Then you can perform searches on the changes made to your data. The following steps will create an Informix database table out of an audit file. As an example, I will use the stores database and the orders table that come with Informix products. I recommend that you try this in a test environment first. To create an audit file on the orders table, type the following SQL command in dbaccess or isql.
create audit for orders in "/usr/lester/demo/orders.audit"
Every change to the orders table will be captured in this file. The next step is to create a way of loading this into a database and using the data.
dbschema -s stores -t orders a_orders.sql
a_type char(2) Type of record where aa = added, dd = deleted,
rr = before update image, ww = after update
image.
a_time integer Integer internal time value.
a_process_id smallint Process ID that changed the record.
a_usr_id smallint User ID that changed the record.
a_rowid integer Original rowid.
You will also need to change the table name in the SQL script produced by dbschema to the name you want to call the audit table. I like to use the old table name with an "a_" prefix. The Index statements will also need to be changed. There must be one index on this table for the next step with bcheck to work. The old unique indexes should be removed because in the audit file the same record could appear for multiple changes. Change the index statements to use the new table you are creating. The following example is the script for the orders table:
create table a_orders (
a_type char(2),
a_time integer,
a_process_id smallint,
a_usr_id smallint,
a_rowid integer,
order_num serial not null,
order_date date,
customer_num integer,
ship_instruct char(40),
backlog char(1),
po_num char(10),
ship_date date,
ship_weight decimal(8,2),
ship_charge money(6,2),
paid_date date );
create index a_order_num_idx on a_orders ( order_num );
select dirpath from systables where tabname = "a_orders"
On my system dirpath was "a_order007". Change to the directory where the database files are located and copy the audit file to replace the a_order007.dat file.
cd stores.dbs cp /usr/lester/demo/orders.audit a_order007.dat
bcheck a_order007
You now have an Informix table of your audit records and you can run SQL or build perform screens to see changes made to your data. Repeat steps 4 and 5 every time you need to update the table. The following is an example SQL statement. The results show an add (aa), change ( rr and ww) and a delete (dd) on the orders table.
select a_type, a_time, a_process_id, a_usr_id, a_rowid, order_num from a_orders
| a_table | a_time | a_process_id | a_usr_id | a_rowid | order_num |
| aa | 759109477 | 823 | 200 | 16 | 1016 |
| rr | 759109502 | 823 | 200 | 15 | 1015 |
| ww | 759109502 | 823 | 200 | 15 | 1015 |
| dd | 759109516 | 823 | 200 | 16 | 1016 |
When your table's .idx file magically disappears from your .dbs directory, firstly be assured that you're not alone in experiencing this phenomenon, and secondly here's how you can regain access to your data.
From Rudy Fernandes:
e.g cp junk___123.idx deleted105.idx
(N.B the idx file's prefix will be identical to that of its dat)
e.g bcheck -s deleted105
clema@informix.com (Clem Akins) writes:
It goes like this:
Data Replication, now called High-availability Data Replication, is the master/slave setup designed for disaster recovery.
Enterprise Replication (used to be Continuous Data Replication) is the far more powerful new capability. You can choose to replicate just columns, or selected rows, at different times, and deal with problems in any of several different ways.
On 8th Dec 1997 ddailey@informix.com (Doug Dailey) wrote:-
The problem is that you are wanting to do Enterprise Replication, not HDR. If your intention is to only replicate certain data sets from individual tables, then you best bet is to change your mode of thinking to ER.
There are skimpy release notes on this, SNMPDOC_7.2 and EREPDOC_7.2 in your $INFORMIXDIR/etc directory. But yes, you can replicate specific data from tables based on where filters setup. The hardware medium (platform) can be different and the disk layout and configuration of instance can be independant on one another. You will need Replication Manager 7.22.TC1 that runs on a NT 3.51 or greater Workstation in order to run replication although there is also an API that comes with 7.22.UC1 ESQL/C or by default with the 7.23.x engine and will be located in the $INFORMIXDIR/demo/esql directory.
Following is info on starting the SNMP and sub-agent for SCO:
SNMP Information
There are the following files that are used for the master agent on SCO. SCO is a PEER based protocol and INFORMIX does not ship a master agent with our product on this platform. These files are located in the /etc directory:
Snmpd {executable that starts the master agent}
lrwxrwxrwx 1 root root 32 May 22 1996 snmpd -> /opt/K/SCO/tcp/2.0.0Cl/etc/snmpd
snmpd.comm {needs the following entry}
hskgsk96 0.0.0.0 WRITE
snmpd.conf {needs the following entry}
descr=SCO TCP/IP Runtime Release 2.0.0 objid=SCO.1.2.0.0 contact= Doug Dailey {this is just what I used} location= Lenexa {same thing} smux_reserved=system smux_reserved=snmp smux_reserved=smux
snmpd.peers {needs the following entry}
"cdragt" 0.0 ""
snmpd.trap {not necessary, used for event trapping}
Notes on SCO and starting the agent:
It is not necessary to set SR_AGT_CONF_DIR if you are using the master agent of the OS. This is the basic rule for all platforms using SNMP master supplied by the OS vendor.
Start the master agent with the following syntax:
/etc/snmpd log_level 6 & {0-6, 6 being the most detail. Do a man on snmpd on SCO to get more detail}
The log file will be created in /usr/adm/snmpd.log.
Start the cdr sub-agent the same way you would on any other system:
$INFORMIXDIR/bin/cdragt -t 3 -f /tmp/cdr.out &
Here is also some info on a base test in getting this up and running:
Below are steps for setting up for replication on the UNIX side for the SUN Solaris platform. You will have to substitue the portions regarding the master and sub-agent with the above info regarding SCO:
I. Installation:
II. Configuration:
env for test1
setenv INFORMIXDIR /testing/ddailey
setenv INFORMIXSERVER test1tcp
setenv PATH ${INFORMIXDIR}/bin:/tsgoldopt/bin:${PATH}
setenv ONCONFIG onconfig.test1
setenv LD_LIBRARY_PATH $INFORMIXDIR/lib:$INFORMIXDIR/lib/esql
setenv SR_AGT_CONF_DIR $INFORMIXDIR/snmp/snmpr
env for test2
setenv INFORMIXDIR /testing/ddailey
setenv INFORMIXSERVER test2tcp
setenv PATH ${INFORMIXDIR}/bin:/tsgoldopt/bin:${PATH}
setenv ONCONFIG onconfig.test2
setenv LD_LIBRARY_PATH $INFORMIXDIR/lib:$INFORMIXDIR/lib/esql
setenv SR_AGT_CONF_DIR $INFORMIXDIR/snmp/snmpr
sqlhosts **************************************************************************** *** test1 group - - i=1 test1tcp ontlitcp sunset test1tcp g=test1 test2 group - - i=2 test2tcp ontlitcp sunset test2tcp g=test2
onconfig.test1 **************************************************************************** *** ROOTNAME rootdbs # Root dbspace name ROOTPATH /testing/ddailey/test1 ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 20000 # Size of root dbspace (Kbytes) SERVERNUM 60 # Unique id corresponding to a OnLine instance DBSERVERNAME test1tcp # Name of default database server DBSERVERALIASES # List of alternate dbservernames NETTYPE # Configure poll thread(s) for nettype RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
onconfig.test2 **************************************************************************** *** ROOTNAME rootdbs # Root dbspace name ROOTPATH /testing/ddailey/test2 ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 20000 # Size of root dbspace (Kbytes) SERVERNUM 61 # Unique id corresponding to a OnLine instance DBSERVERNAME test2tcp # Name of default database server DBSERVERALIASES # List of alternate dbservernames NETTYPE # Configure poll thread(s) for nettype RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
IV. Starting the Agents:
1. As root, go to $INFORMIXDIR/bin (in ksh)
./snmpdm -apall >/tmp/snmp.out 2>&1 &
2. As informix, go to $INFORMIXDIR/bin (in ksh)
./cdragt -t 5 -f /tmp/cdr.out 2>&1 &
V. Installing Rep Manager
1. Installed as Administrator and used at least the NT 3.51 version of 7.22.TC1 rep manager
2. REPHOSTS file:
test1 group - - i=1 test1tcp ontlitcp sunset test1tcp g=test1 test2 group - - i=2 test2tcp ontlitcp sunset test2tcp g=test2
VI. Test
1. create table on instance A : create table test (col1 char(1) primary key, col2 char(5)); create table on instnace B : create table test (col1 char(1) primary key, col2 char(5)); inserted 4 rows into table test on instance A prior to defining replicate 2. databases created in rootdbs with logging 3. synchronized the two servers through replication manager 4. defined a replicate with the following properties:
update anywhere transaction scope ris and ats spooling select * from test where test = "cc" ignore conflict resolution continuous replication test1 and test2 instances all participants defined the same? NO
5. update test set col2 = "cc"; {yes to all} /*updates not converted to inserts on instance B.*/ /*subsequent updates only propagated on newly inserted rows*/
: smb@oasis.icl.co.uk (Stuart Britt) writes: : Does anyone have any info on the following problem? We are using Informix : version 7.10.UC1 for Intel UnixWare 2.01 on single processor and SMP : machines. : : Using Informix on 1 processor we achieve good performance. If we switch to : 2 processors, then our performance actually degrades. We have been given : some advice on tuning the system for dual processor machines and we have : tried different settings for virtual processors and processor affinity. : We managed to get an improvement, but the best scaling we can get is : about 1.25. : : If we move to a quad processor system the problem goes away and we : approach expected performance levels. : : Has anyone else experienced this?
This conforms exactly to what Informix said at the user conference this summer. According to that you can't rely expect any performance increase until you have 4 processors. This is due to extra resources required to do the swiching between virtual processors takes up more time than you save by an adittional processor.
Not long ago someone also said that on SCO Unix even a 4 processor configuration didn't give that much increased performance. As we also use Intel hardware and plan to upgrade to multiple processors it would be very interesting for us to here what your performance increase is.