Performance tuning a database is a huge territory, so as others have said,
without details of your system and the performance problems you are
experiencing (if any), it's hard to help. A good first step is to read the
Performance Guide.
One major performance area I'll mention, because nobody else has so far,
is
the size of your bufferpool (onconfig "BUFFERPOOL" parameter). This is the
main memory cache of database data. Like all aspects of computing, the
memory cache needs to be large enough to hold the "working set" for your
workload to avoid thra****ng. In the case of a database, this means the
data
and index pages that are accessed frequently need to fit in the buffer
pool.
You can monitor buffer pool performance with "onstat -g buf" -- example
output below (this from a version 11.50, but it will be similar in 9.40).
You want the %cached for reads to be in the mid-to-upper 90's, and the
%cached for writes to at least be around 90. These are the percentages of
the time when data reads and data writes, respectively, found the page
they
needed already in the buffer pool, rather than having to read it in from
disk which is a "page fault" from the standpoint of the database. If they
are much below these, it means the buffer pool is too small for the
working
set and the database is thra****ng. It is many orders of magnitude slower
to
have to read the page in from disk than if it was already present in
memory
in the buffer pool.
You also want "Fg Writes" (meaning foreground writes) to be essentially
zero. This is the number of times a page fault could not find a clean page
in the buffer pool to use and had to write out a dirty page itself
("clean"
it) before it could service the page fault. If you have frequent
foreground
writes, performance will be a dog. Look up the onconfig parameters
LRU_MIN_DIRTY and LRU_MAX_DIRTY for how to tune page flusher behavior
here.
The page flu****ng should be tuned to be aggressive enough so that page
faults can always be serviced with existing clean pages, thus avoiding
foreground writes.
You can zero out all onstat statistics (not just onstat -g buf's stats)
via
"onstat -z" so if you change something you can start the stats over at
zero.
(Changing the size of the buffer pool, however, requires rebooting IDS.)
onstat -g buf output example (from 11.50):
IBM Informix Dynamic Server Version 11.50.F -- On-Line -- Up 7 days
01:30:04 -- 1067632 Kbytes
Profile
Buffer pool page size: 2048
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits
%cached
20226 22985 2292036 99.12 28202 92374 487634
94.22
bufwrits_sinceckpt bufwaits ovbuff flushes
81253 162 0 35
Fg Writes LRU Writes Avg. LRU Time Chunk Writes
0 0 nan 13818
Fast Cache Stats
gets hits %hits puts
602493 580721 96.39 347705
--
Kevin Cherkauer
Software Engineer
IBM Informix Dynamic Server -- Database Kernel
"raja" <tssr2001@[EMAIL PROTECTED]
> wrote in message
news:4c602408-b148-43f4-805f-79beb96878a6@[EMAIL PROTECTED]
> Hi 2 All,
>
> I am new to INFORMIX database.
>
> I am using INFORMIX 9.4 database in HP-UX System.
>
> I have few questions, Please clarify those questions for me :
>
> 1. What is in INFORMIX, similar to sqlplus in ORACLE ( to view the
> schema details - tables, indexes,... ) ?
> 2. What is dbaccess in INFORMIX ?
> 3. How to execute a sql script file in INFORMIX ?
> 4. What are the list of tables, that related to DBA, to tune the
> database ?
> 5. What are the tuning tools in INFORMIX ?
> 6. What is in INFORMIX, similar to statspack/AWR Re****t in ORACLE ?
> 7. What are the ways to tune a sql query in INFORMIX ?
> 8. What are the ways to tune a procedure in INFORMIX ?
>
> I would be very much thankful, if you Please explain me these
> questions separately with Examples.
> If you have any do***ents or URL, Please send me them.
>
> Thanks in Advance.
>
> With Regards,
> Raja.
|
