|
|
Hi,
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.
Thanx in advance,
Filip
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be *
* Oostkaai 23 B-2170 Merksem *
* 03/6416673 - 0477/340942 *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
|
|