Reinder Menninga wrote:
Hi,
We tried to use the new database and at first it seemed to work. But
the performance is terrible. Some queries take over 200 seconds to
execute. All constraints and indexes seems to be imported oke from the
7.4 postgres database.
For example this query took more than 4000 seconds:
SELECT
contentelementen.notitie,contentelementen.owner,contentelementen.number,contentelementen.vrijetekst6,contentelementen.datum2,contentelementen.datum1,contentelementen.vrijetekst5,contentelementen.vrijetekst4,contentelementen.vrijetekst3,contentelementen.vrijetekst2,contentelementen.vrijetekst1,contentelementen.elementtype,contentelementen.titel,contentelementen.body,contentelementen.toontitel,contentelementen.getal2,contentelementen.getal1,contentelementen.boolean2,contentelementen.boolean1,contentelementen.otype,contentelementen.intro,root0.number,insrel.number,contentelementen.number
FROM cpb_contentelementen root0,cpb_insrel insrel,cpb_contentelementen
contentelementen WHERE root0.number=888 AND
((root0.number=insrel.dnumber AND
contentelementen.number=insrel.snumber AND insrel.dir<>1) OR
(root0.number=insrel.snumber AND
contentelementen.number=insrel.dnumber)) LIMIT 1
Does mmbase 1.7.1 support postgresql 8.0.13?
It should run fine on postgresql 8.
The reason of this query is most likey a call like
contentelement.getRelatedNodes("contentelementen");
How big is your insrel table, because this will always result in a full
table scan no matter which database or indices you use. An easy
explanation for this is that every database will try to minimize the
amount of io (disk) reads. Reads to an index will also mean, in worst
case, a disk read for every record from the index. A database will use
an index when it thinks it is faster then a full table.scan. This query
has two AND-groups which means that it has to uae two indices and merge
the results. After that it can read every record one by one. Many
databases think a full table scan will be faster. Some databases will
learn by their statistics that the two indices might be faster. Maybe
running postgresql for some time with a daily analyze will work, but I
doubt it,
A better way is to find the code snippets in the application which do OR
queries and rewrite them. It is always good to be as explicit as
possible to mmbase to get good performance. Try always to use
relationname, target nodemanager and the direction of the relation
(source, destination). Only leave it away when you don't know. Another
nice result is that the mmbase cache works better, because the queries
are less complicated. When you have done that then you can go and hunt
for inefficient calls on mmbase. Iterating over nodes and relations are
not always best performing.
You could use the below code in
org.mmbase.module.database.MultiConnection to find out which application
code does which sql statements. It is in the Logging class in mmbase 1.8.
/ /**
* @since MMBase-1.8
*//
*public* *static* String applicationStacktrace(Throwable e) {
StringBuffer buf = *new* StringBuffer(*"Application stacktrace"*);
/// Get the stack trace
/ StackTraceElement stackTrace[] = e.getStackTrace();
/// stackTrace[0] contains the method that created the exception.
/ /// stackTrace[stackTrace.length-1] contains the oldest method call.
/ /// Enumerate each stack element.
/
*boolean* mmbaseClassesFound = *false*;
*int* appended = 0;
*for* (*int* i = 0; i < stackTrace.length; i++) {
String className = stackTrace[i].getClassName();
*if* (className.indexOf(*"org.mmbase"*) > -1) {
mmbaseClassesFound = *true*;
/// show mmbase taglib
/ *if* (className.indexOf(*"bridge.jsp.taglib"*) > -1) {
buf.append(*"\n at "*).append(stackTrace[i]);
appended++;
}
} *else* {
*if* (mmbaseClassesFound) {
/// show none mmbase method which invoked an mmbase method.
/ buf.append(*"\n at "*).append(stackTrace[i]);
appended++;
*break*;
}
/// show compiled jsp lines
/ *if* (className.indexOf(*"_jsp"*) > -1) {
buf.append(*"\n at "*).append(stackTrace[i]);
appended++;
}
}
}
*if* (appended == 0) {
*for* (*int* i = 2; i < stackTrace.length; i++) {
buf.append(*"\n at "*).append(stackTrace[i]);
}
}
*return* buf.toString();
}
Nico
_______________________________________________
Developers mailing list
[email protected]
http://lists.mmbase.org/mailman/listinfo/developers
