I have two identical databases, one of which is a copy made by using
the checkpoint of the first and rolling it forward. They are on
different machines but the archietecture and set up is almost
identical. The tables have the same structure and statistics and the
rollforward takes place on a nightly basis.

The problem I have is that a query runs on one of the machines almost
5 times faster than the other. The QEP for both is the same, yet when
I use qe90 the actual cpu time taken is over double on one of the
machines than the it is on the other. The ed and ad figures are about
the same. Can anybody give me a pointer on how to go about resolving
this?

Thanks

IAIN

On Aug 11, 2009, at 5:32 AM, iainm wrote:

The word ALMOST caught my eye ...

On both machines, compare

- the contents of ${II_SYSTEM}/ingres/version.rel
- the output of the "ingprenv" command
- the contents of ${II_SYSTEM}/ingres/files/config.dat

The last one is likely where you will find your cache sizes
are radically different. One is likely "tuned up" while the other
is likely a default configuration.

Comparing the config.dat files won't be trivial, but worthwhile, I
would guess.

Cheers,

Michael Leo
Kettle River Consulting

You can use the configuration differences analyser tool which comes with
Ingres 2006 on windows.

Copy the two config.dat files to windows (ascii conversion ok)
Change the names to machine1.ii_vcda and machine2.ii_vcda
Add this bit of text to the top of each file

[GENERAL PARAMETERS]
PLATFORM: AIX
[CONFIGURATION PARAMETERS]

Then run "Ingres Visual Configuration Differences Analyzer"


You might also like to check the shell environment settings also. Sometimes
there can be something there which overrides the default value in ingprenv.
For weeks our users experienced one machine on the network running much^10
slower than other identical installations until we discovered
II_EMBED_SET=printqry was in the environment. It recorded a detailed log of
every SQL on the box.


Paul

On 11 Áýã, 13:32, iainm <iain.mcne... (AT) sqa (DOT) org.uk> wrote:
There is an article called 'A Procedure to Identify and Fix
Longrunning Queries' at the North American Ingres Users Association
site http://naiua.org/longrunning.php that suggests that the CPU
statistics are not something to be concerened about;quoting :
"When you generate a QEP, you get estimates for Disk I/O (D) and CPU
statistics (C). The CPU statistics are basically useless - you are
concerned with the number of Disk I/O's required. The smaller the
number, the faster the query. It is important to compare the actual
disk I/O required versus the estimated disk I/O."
How did you do the benchmark? If it is 5 times faster then the
bottlneck could be somewhere else

nikosv wrote:

The above quoted comment is not wildly wrong but it refers to the QEP,
which shows the *predicted* CPU and disk cost before running the query.
The OP here is looking at the output of trace point QE90 which shows
what the query really cost, after running it.

I seem to remember Mike Leo asked for further information to verify that
the two systems really are identical, but I don't think the OP has
responded yet. Right now I like Mike's conjecture that the two
systems are tuned differently.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.