[Info-Ingres] Invalidate cache
Cecil Westerhof
dummy at dummy.nl
Mon May 28 11:49:59 CDT 2007
Roy Hann wrote:
>>> You saspectacularning the same query repeatedly. Is it exactly the
>>> same
>>> query each time, with exactly the same constants in the WHERE clause
>>> each
>>> time? I have no explanation for why there would be a difference each
>>> time if it is the same query, except that perhaps the host machine is
>>> *very* busy doing other things as well.
>>
>> Yes, the script generates a SQL-statement and this is executed
>> repeatadly. The host machine is only executing VMware. And the virtual
>> machine is only executing the sql-query.
>>
>> I'll will make a simpler version of the script and post it here.
>
> I think you should do that, because I am either not understanding the
> description of the problem, or there is something really strange happening
> on your system. You should see very consistent response time unless
> there is a lot of lock-contention with sessions using exclusive locks on
> the same table/page.
I had the possibility to try it out on the real hardware. On this the
differences are a lot less spectacular.
The -relevant parts of the- used tables are:
################################################################################
Name: tableA
Location: ii_database
Type: user table
Version: II3.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 86
Row width: 86
Number of rows: 35018117
Storage structure: btree
Compression: none
Duplicate Rows: allowed
Number of pages: 2363217
Overflow data pages: 0
Journaling: enabled after the next checkpoint
Base table for view: yes
Optimizer statistics: none
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
id integer 4 no no
year integer 1 no no 1
month integer 1 no no 2
day integer 1 no no
hour integer 1 no no 4
fieldA char 20 no no
fieldB integer 4 no no
daysrt integer 1 no no 3
fieldC char 1 no no
fieldD integer 2 no no
fieldE integer 2 no no
fieldF integer 2 no no
fieldG decimal 4 no no
Secondary indexes:
Index Name Structure Keyed On
"$pk_uurgegevens_wpt" btree id, year, month, day, hour
Name: tableB
Location: ii_database
Type: user table
Version: II3.0
Page size: 16384
Cache priority: 0
Alter table version: 0
Alter table totwidth: 27
Row width: 27
Number of rows: 42132292
Storage structure: btree with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 354222
Overflow data pages: 0
Journaling: enabled after the next checkpoint
Base table for view: yes
Optimizer statistics: none
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
id integer 4 no no 2
fieldB integer 4 no no 1
fieldH char 2 no no 3
fieldI integer 2 no no
fieldJ integer 2 no no
fieldK integer 2 no no
fieldL decimal 5 no no
Secondary indexes: none
################################################################################
The used views are:
################################################################################
create view viewB as
select -id as id
, id as realid
, fieldB
, fieldH
, fieldI
, fieldJ
, fieldK
, fieldL
from tableB
\g
create view viewA as
select -id as id
, id as realid
, year
, month
, day
, hour
, fieldA
, fieldB
, fieldC
, fieldD
, fieldE
, fieldF
, fieldG
from tableA
\g
################################################################################
The -relevant parts of the- used script:
################################################################################
checkForError() {
error=`grep ^E_ ${TEMPOUTPUT}`
if [[ -n ${error} ]] ; then
echo "Er is een fout opgetreden bij het benaderen van de database:"
echo " ${error}"
exit -1
fi
}
clearCache() {
sql ${DATABASE} >${TEMPOUTPUT} <<EOT
SET TRACE POINT DM421
\g
EOT
checkForError
}
doExecuteSQL() {
/usr/bin/time --format=%e sql ${DATABASE} >${TEMPOUTPUT} <<EOT
${1}
${2}
${3}
${4}
\g
EOT
}
executeSQL() {
echo ${1}
for i in `seq ${REPEATS}` ; do
clearCache
usedSeconds=`doExecuteSQL "${3} ${4} ${5} ${ORDER}" 2>&1`
checkForError
echo " ${usedSeconds}"
if [[ ${i} -eq 1 ]] ; then
awk '/^(+|\||\()/ {print}' <${TEMPOUTPUT} >${2}
fi
done
rm ${TEMPOUTPUT}
sleep ${SLEEP_TIME}
}
################################################################################
I think it is evident that every time the same query is used.
The used query:
################################################################################
SELECT b.day
, b.hour
, a.fieldH
, a.fieldJ
, a.fieldK
, b.fieldD
, b.fieldE
FROM viewB a
, viewA b
WHERE b.realid = -4400
AND b.year = 5
AND b.month = 7
AND b.dag in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
AND b.fieldB = a.fieldB
AND a.realid = b.realid
ORDER BY day, hour, fieldH
################################################################################
The values the script gives are:
0.83
0.69
0.88
10.03
1.06
The difference between the fastest and slowest is almost 15 times.
Then there is also a problem with the way the client wants the query to be.
They are used with inputting positive id's and do not want to change this.
Because of this they want the where part to be:
################################################################################
WHERE b.id = 4400
AND b.year = 5
AND b.month = 7
AND b.dag in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
AND b.fieldB = a.fieldB
AND a.realid = b.realid
################################################################################
I would think that this would not give a significant difference, but the
used times become:
13.76
10.45
8.71
8.26
8.21
The good news is that the difference between the fastest and slowest is less
then 2 times. But the difference between the fastest of the first query and
the fastest of the second query is almost 12 times.
What is happening here? And is there anything I can do about this?
More information about the Info-Ingres
mailing list