Filip Sergeys wrote: > We have some pretty complex queries that need tuning of the > database. I > think we have done everything possible to tune the stored > procedure (It > is already a whole lot better, 9 times faster), but it still > needs to be > faster > My first thought was that we had not enough RAM and swapping was the > problem. But looking a little deeper revealed this: > (/dev/sdb1 is data disk, /dev/sda2 is swap disk) > > iostat -d -x /dev/sdb1 /dev/sda2 1 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s > avgrq-sz avgqu-sz await svctm %util > /dev/sdb1 0.00 111.20 0.00 167.30 0.00 2228.00 0.00 > 1114.00 13.32 4294688.98 0.41 0.60 10.00 > /dev/sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 > 0.00 0.00 0.00 0.00 0.00 0.00 > > Swapping is not the problem (0.00 during the whole query run), neither > is excesive reading from the datadisk, but writing to the datadisk. > I then noticed that while running these queries, the TEMP area is > growing massively: 400 / 500 MB and then going back again. > > What parameters can I tune to have more of what is going on in TEMP on > disk to be in memory? > I could not find straight answers in the documentation neither via > google, so hope somebody is more knowledgeable than either two.
Hi, with MaxDB version >= 7.5.0.21 you could enable new join implementation by setting database parameter JOIN_OPERATOR_IMPLEMENTATION to "YES" or "IMPROVED". This should reduce temp space consumption because the new implementation doesn't create intermediate results like the old one. Hope that helps. Best regards Holger -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
