Online LRU cleaning changes / mudanças na limpeza das LRUs online
This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português
English version:
Very quick post to document something which is not properly documented. While working at a customer I had the need to change the LRU cleaning parameters (min_dirty, max_dirty) without stopping the engine. The fine manual says this can be done by dynamically change the AUTO_LRU_TUNING parameter:
onmode -wm AUTO_LRU_TUNING=1,min=0.3,max=1
And this works fine... But there is a catch: If, like me yesterday, you have more than one BUFFERPOOL this will change the max/min for all of them. You may not want that... I had the solution somewhere in my personal notes, but as usual this was hard to find. Now that I did, I'm just sharing.... So, the solution would be:
Et voilá... the non documented option bpool allows us to define which of the BUFFERPOOL we want to change.
Um artigo muito rápido para deixar documentado algo que não está completament documentado. Enquanto estava a trabalhar num cliente surgiu a necessidade de alterar os parâmetros de limpeza das LRUs (min_dirty, max_dirty) sem parar o motor. O manual refere que tal pode ser feito dinamicamente mudando o parâmetro AUTO_LRU_TUNING:
onmode -wm AUTO_LRU_TUNING=1,min=0.3,max=1
E isto funciona como esperado. Mas há um problema: Se estiver como eu ontem, em que tinha mais que uma BUFFERPOOL isto mudaria os parâmetros da todas elas. Podemos não desejar isto. Tinha a solução algures nas minhas notas, mas como é habitual não foi fácil de encontrar. Agora que o fiz estou apenas a partilhar informação. Assim a solução seria:
Et voilá... a opção não documentada "bpool" permite-nos definir qual das pools queremos mudar
Posts
2 comments:
Hi Fernando,
I have tested that onmode -wm AUTO_LRU_TUNING=bpool=... command and first
got following error:
onmode: Please check the message log for any additional errors.
The problem is, that bpool= needs the index of the buffer pool as argument.
So first you need to know which bufferpool should has which id/index:
$ echo "select indx,bufsize,mindirty,maxdirty from sysbufpool"|dbaccess sysmaster -
indx bufsize mindirty maxdirty
0 2048 50.00000000000 60.00000000000
7 16384 50.00000000000 60.50000000000
Now you can change the lru_min/max dirty for each bufferpool online/without reboot:
$ onmode -wm AUTO_LRU_TUNING=bpool=0,min=0.5,max=2.1
14:54:09 Value of AUTO_LRU_TUNING has been changed to bpool=0,min=0.5,max=2.1.
$ echo "select indx,bufsize,mindirty,maxdirty from sysbufpool"|dbaccess sysmaster -
indx bufsize mindirty maxdirty
0 2048 0.5 2.100000000000
7 16384 50.00000000000 60.50000000000
$ onmode -wm AUTO_LRU_TUNING=bpool=7,min=0.6,max=2.2
14:54:50 Value of AUTO_LRU_TUNING has been changed to bpool=7,min=0.6,max=2.2.
$ echo "select indx,bufsize,mindirty,maxdirty from sysbufpool"|dbaccess sysmaster -
indx bufsize mindirty maxdirty
0 2048 0.5 2.100000000000
7 16384 0.6 2.200000000000
After reboot/shotdown all these changes are lost - so you need to change
bufferpool parameter additionally.
Cheers,
Markus Holzbauer
Hi Fernando,
that above works fine in 11.70 - but in 12.10 you will get the error:
AUTO_LRU_TUNING is already set to 0.
With 12.10 it works with:
$ onmode -wm AUTO_LRU_TUNING="1,bpool=0,min=0.5,max=2.1"
$ onmode -wm AUTO_LRU_TUNING="1,bpool=7,min=0.6,max=2.2"
$ onmode -wm AUTO_LRU_TUNING=0
The first 2 commands change the lru values online - the last
command turn off auto lru tuning..
Cheers,
Markus Holzbauer
Post a Comment