Hello,

I have a strange(to me) case with a pair of HDR servers (14.10.FC11W1) on linux. The setup of BUFFERPOOL 2k is only memory=auto without other parameters. The other bufferpools are set the same way, but there is no such activity. The database has a number of CLOB columns which are extensively used for inserts and those are the only dbspaces on 2K (plus the "system" ones).

What I have noticed is, that the secondary server is extending the 2k bufferpool regularly until reaching the 24 segments limit and giving "Performance Advisory: Unable to extend bufferpool 2K", while the primary server extends it to some point and then settles on that value. Both databases have the same configuration with all auto parameters set to on, except AUTO_LLOG.

I know that I can set the bufferpools manually, but this behaviour is very strange to me, having in mind that this server is secondary and it does not have any other connections or load except the one to the primary. Am I missing some configuration, or just this is not the correct way to setup a secondary server and I should use different setup from the primary?

-------------------------------------------

Nayden:

The problem is that you have not set the cache_hit_ratio option to the 2K BUFFERPOOL setting. That option defaults to 90%. On the primary that works just fine because you are querying those CLOBs so the cache hit ratio is higher than 90%. However, in the secondary, you are only inserting those CLOBs and never querying them so the cache hit ratio for that cache is below 90% nearly all the time and that is triggering the buffer pool extension.

Run onstat -g buf several times during the day, especially at times where the message log reports a buffer pool extension, to determine what the "normal" read hit ratio is for that server and add the  cache_hit_ratio option with that value or  one a bit lower than "normal". I would do that on both the primary and secondary servers.

The only thing to note is that on the primary and if that secondary should be promoted to primary or standalone it may not be aggressive enough at extending the buffer pool size, so you may also want to add an initial minimum pool size that seems to be that settled level on the primary currently.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------