All, we are testing 7.6.00.12 on Windows. We have a long running query and I suspect that it is trapped in some kind of endless loop. Here's why
- it's running since 14.8.2005 (three weeks if I'm not mistaken) - overall DB data size is 6GB with 1.2GB used - 7.5 needed much less time (73 seconds) - cache hit ratios are 100% (90% for catalog cache): Cache Accesses Successful Unsuccessful Hit Rate Data Cache 465.866.448.135 465.866.278.386 169.749 100,00% Some more figures: Activity: Selects and Fetches Rows Read 115.145.676.556 Qualified Rows 107.509.310.959 IO Activity Physical Reads 169.746 Logical Reads 465.876.191.543 Physical Writes 0 Logical Writes 38.729.260 Table Scans 96.036 It looks like MaxDB was cycling over the same data over and over again... Here's the query (I've also attached it as text file to prevent line breaks making it more unreadable than necessary): SELECT TA1.propvalue AS COL1, (SUM(TA3.avgbytecount * TA3.refcount)) / 1024.0 AS COL2, SUM(TA3.avgbytecount * TA3.refcount) * 100.0 / ( CASE WHEN MAX(TA4.COL1) = 0 THEN 1.0 ELSE MAX(TA4.COL1) END ) AS COL3, SUM(TA3.refcount) AS COL4, SUM(TA3.icp_refs * TA3.refcount) AS COL5, SUM(TA3.reload_refs * TA3.refcount) AS COL6, SUM(TA3.abort_refs * TA3.refcount) AS COL7, SUM(TA3.avgresponsetime * TA3.refcount) AS COL8, SUM(TA3.non_cacheable_object_refs * TA3.refcount) AS COL9, SUM(TA3.obj_hit_rate * TA3.refcount) * 100.0 / ( CASE WHEN SUM(TA3.refcount) = 0 THEN 1.0 ELSE SUM(TA3.refcount) END ) AS COL10, SUM(TA3.byte_hit_rate * TA3.refcount) * 100.0 / ( CASE WHEN SUM(TA3.avgbytecount * TA3.refcount) = 0 THEN 1.0 ELSE SUM(TA3.avgbytecount * TA3.refcount) END ) AS COL11 FROM ag_site TA1, ag_url TA2, v_ag_hourlymaster TA3, ( SELECT SUM(TA1.avgbytecount * TA1.refcount) AS COL1 FROM v_ag_hourlymaster TA1 WHERE ( TA1.applianceid=1 ) ) TA4 WHERE TA1.indexid=TA2.siteid AND TA2.urlid=TA3.urlid AND ( TA3.applianceid=1 ) GROUP BY TA1.propvalue ORDER BY COL4 DESC Note that there's a nested query (aka inline view) and several CASE expressions. Does this ring a bell with someone? Is this a known issue? What other information do you need? Thanks a lot Kind regards robert
SELECT TA1.propvalue AS COL1, (SUM(TA3.avgbytecount * TA3.refcount)) / 1024.0 AS COL2, SUM(TA3.avgbytecount * TA3.refcount) * 100.0 / ( CASE WHEN MAX(TA4.COL1) = 0 THEN 1.0 ELSE MAX(TA4.COL1) END ) AS COL3, SUM(TA3.refcount) AS COL4, SUM(TA3.icp_refs * TA3.refcount) AS COL5, SUM(TA3.reload_refs * TA3.refcount) AS COL6, SUM(TA3.abort_refs * TA3.refcount) AS COL7, SUM(TA3.avgresponsetime * TA3.refcount) AS COL8, SUM(TA3.non_cacheable_object_refs * TA3.refcount) AS COL9, SUM(TA3.obj_hit_rate * TA3.refcount) * 100.0 / ( CASE WHEN SUM(TA3.refcount) = 0 THEN 1.0 ELSE SUM(TA3.refcount) END ) AS COL10, SUM(TA3.byte_hit_rate * TA3.refcount) * 100.0 / ( CASE WHEN SUM(TA3.avgbytecount * TA3.refcount) = 0 THEN 1.0 ELSE SUM(TA3.avgbytecount * TA3.refcount) END ) AS COL11 FROM ag_site TA1, ag_url TA2, v_ag_hourlymaster TA3, ( SELECT SUM(TA1.avgbytecount * TA1.refcount) AS COL1 FROM v_ag_hourlymaster TA1 WHERE ( TA1.applianceid=1 ) ) TA4 WHERE TA1.indexid=TA2.siteid AND TA2.urlid=TA3.urlid AND ( TA3.applianceid=1 ) GROUP BY TA1.propvalue ORDER BY COL4 DESC
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
