Wednesday, October 31, 2012

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:

onmode -wm AUTO_LRU_TUNING=bpool=0,1,min=0.3,max=1

Et voilá... the non documented option bpool allows us to define which of the BUFFERPOOL we want to change.


Versão Portuguesa:

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:


onmode -wm AUTO_LRU_TUNING=bpool=0,1,min=0.3,max=1


Et voilá... a opção não documentada "bpool" permite-nos definir qual das pools queremos mudar

2 comments:

Anonymous said...

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





Anonymous said...

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