Prefetch delay

Posted by Gareth Vincent on 16-Oct-2015 08:04

I'm curious in finding out if anyone has seen significant performance gains when working with the Prefetch parameters.  One of the articles I stumbled across suggested using the following values as a starting point.  

-prefetchFactor 100

-prefetchNumRecs 100

Our data center is running multiple DB's on Sun Solaris with version 11.5.  Clients are connecting via webclient.  The last thing we want to do is decrease the performance to our end users.  I suppose the question is how does one monitor the impact of these changes in a production environment.

Posted by ChUIMonster on 16-Oct-2015 08:36

Much depends on how your application is put together and how data flows between client and server.

As Libor says those parameters don't impact webclient connections.  They might matter to your appservers if the app server is connecting with -S.

The prefetch parameters *can* help performance and improve scalability.  I have not seen a case where it hurts and it is difficult to imagine how it might (but testing is of course strongly recommended).  

To actually see how well it works is not obvious and the end-user impact is somewhat indirect.  ProTop has a "servers"  display that shows the traffic between client and server and how full the data packets are and how much traffic you are pushing across  the network.  

All Replies

Posted by Libor Laubacher on 16-Oct-2015 08:17
Gareth,
 
I’ll let others comment on their experience with those 2 parameters, but one thing – you said you are using webclient. Webclient does not connect directly to a database, it does to an AppServer, and if the connection from the AppServer to a database is shared memory, these 2 parameters do not apply. –mc might be of help between webclient x appserver.
 
/LL
 
Posted by ChUIMonster on 16-Oct-2015 08:36

Much depends on how your application is put together and how data flows between client and server.

As Libor says those parameters don't impact webclient connections.  They might matter to your appservers if the app server is connecting with -S.

The prefetch parameters *can* help performance and improve scalability.  I have not seen a case where it hurts and it is difficult to imagine how it might (but testing is of course strongly recommended).  

To actually see how well it works is not obvious and the end-user impact is somewhat indirect.  ProTop has a "servers"  display that shows the traffic between client and server and how full the data packets are and how much traffic you are pushing across  the network.  

Posted by Gareth Vincent on 16-Oct-2015 08:44

Thanks Tom.  Our appservers connect via a shared connection.  Does this mean there is no real benefit in implementing these changes?

Posted by Gareth Vincent on 16-Oct-2015 08:48

You can ignore my last response.  I've just read Libor's message.  thanks Libor we have implemented the -mc with great success.  When monitoring the network traffic with Wireshark, bandwidth from the client to the appserver halved.

Posted by ChUIMonster on 16-Oct-2015 08:52

Ok.

FWIW -- it is very unlikely that widespread performance issues are going to be resolved by a silver bullet.

Posted by Gareth Vincent on 16-Oct-2015 09:00

if only :)

This thread is closed