THE FAQ
Hey,
Its great to see the FAQ taking shape and I wish
to extend my thanks to Kerry and Jack for upping the tempo
on it. I would like to submit my tuppence worth on the
contentious subject of tuning/optimisation. My own net address
is not working and I'm off on vacation for the next two weeks
so I hope the following may be [discussed|edited|dismissed] by
the group in my absence.
Points from bottom up.
1. Check that the Unix system is performing properly
by using sar or other Unix version tools to monitor
CPU and Disk activity. Look for bottlenecks i.e
contention on a particular disk.
2. Think about reconfiguring your Informix installation
so that four logical units (rootdbs, physical log,
logical logs and your database dbspace reside on
physically seperate devices) BTW if you don't use
raw devices in your system (not always the best) then
check it out, RTFM, find out if your platform can
take advantage of raw devices. Further refinement is
possible by placing heavily used tables in a seperate
dbspace/device. The broad picture is to have all unix
resources working with you and not against each other.
3. Monitor the state of your data by checking your extents
from time to time, use tbcheck -ce to get warnings on
extents that have overreached the 8 extent recommendation.
Informix works very hard to run with tables spread over
a lot of extents (we used to call it data fragmentation).
Reorganise the data with unload/delete/load on the offending
table and calculate the next extent size to suit your
own systems needs.
4. Further to point 3, if the system catalog tables (systables,
syscolumns, etc.) have managed to run to more than 8 extents
then a dbexport of your database is advisable. When the export
runs it produces an .sql file to control the dbimport of the
data you may edit this file to include the ALTER TABLE command
(preferably near the beginning of the control file/.sql)
for these system catalog tables so that you MODIFY NEXT SIZE nnn.
i.e. ALTER TABLE systables MODIFY NEXT SIZE 100 ;
The value of nnn for each affected table may be ascertained by
running tbcheck -pT <dbname>:owner.<tabname> read how many pages
have been used in the current schema, change that to Kb and make
that the size of your next extent (minus the first extent ,
if you want to get picky).
5. Various releases of dbexport have their own canny charactaristics
and, in particular one feature (nudge,nudge) of my current version
is that it aborts because of a swapping problem. If you live with
such a version then su to root and change the obvious(?) ulimits
to unlimited (i.e. ulimit -v unlimited). su to informix and run
the dbexport from there.
6. Another feature(?) of dbexport in some versions is the ommision
of the NEXT EXTENT for tables and/or the LOCK MODE of tables,
these can be preserved by running the two scripts which follow
this posting.
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
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. Jack also notes a book Informix Online Performance Tuning
by Elizabeth Suto due for release in September 1994 and, of course,
Joe Lumbley's well publicised Survival Guide due out soon.
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.
Tom Hogarty Usual Disclaimers apply Online 5.01.U
SNI ----------------------- RM600, RM400, MXI
Paderborn Phone: 049-5251-8-11602 Sinix, Sco etc.
Germany Fax : 049-5251-8-11599 (Online 6.0 on desk)