tuning HDR
Hi, A customer use HDR in IDS 10.They use async mode and DRINTERVAL is 30. database is unbuffered. a batch job run below two sql. 1, insert into A select * from B where condition; 2,delete from b where condition; they found those SQL become 10 times slow after using HDR. How to tuning it? Any suggestion would be appreciated. thanks.
Several suggestions, the most important of which is to have them upgrade to v12.10.xC5W1. HDR performance has gone through at least three phases of performance improvements since v10.00. In addition, v12 implements finer transaction control allowing a new "near synchronous" mode that has less effect on transaction times. Second, check the latency between the primary and secondary servers. If this is long, then perhaps the secondary should be an RSS secondary rather than HDR. If your client can live with 30 second asynchronous mode, then RSS should be OK for them as well. Of course, RSS secondary servers are not supported in v10.00, so this also requires upgrading to v11 or v12. Again, I recommend upgrading to the latest v12.10.xC5W1. Now, on the specific commands - if the insert ... select or the delete statements deal with very large numbers of rows, consider using either my dbcopy or dbmove utility for the data copy and my dbdelete utility for mass deletes. These are considerably faster than using a single SQL statement, avoids the risk of a long transaction rollback and huge numbers of locks causing contention with other applications, and tends to be friendlier to replication for these same reasons. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 18, 2015 at 11:09 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > A customer use HDR in IDS 10.They use async mode and DRINTERVAL is 30. > database is unbuffered. > a batch job run below two sql. > 1, insert into A select * from B where condition; > 2,delete from b where condition; > they found those SQL become 10 times slow after using HDR. > How to tuning it? > Any suggestion would be appreciated. > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01182f723c6e5105165ce925
It's been a long time since I've used HDR with Informix 10. It went out of continuing support in 2013. The BTScanner could be a factor in poor performance with the deletes. Was the database logged before HDR was switched on? If it was, then HDR should have minimal additional overhead and the most likely cause is slow performance on the HDR secondary or a poor network. Some questions: is it of the same specification as the primary server, working without issues etc.? Is there a heavy reporting workload on the secondary? Is the network between the two fast and, more importantly, reliable? Ben.
Newer version of the informix server support read ahead on the secondaries which should greatly improve the response time of data which is not in the cache. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/18/2015 08:56:30 AM: > From: "BENJAMIN THOMPSON" <benjamin.thompson@bskyb.com> > To: ids@iiug.org > Date: 05/18/2015 08:56 AM > Subject: Re: tuning HDR [35123] > Sent by: ids-bounces@iiug.org > > It's been a long time since I've used HDR with Informix 10. It went out of > continuing support in 2013. > > The BTScanner could be a factor in poor performance with the deletes. > > Was the database logged before HDR was switched on? If it was, then > HDR should > have minimal additional overhead and the most likely cause is slow > performance > on the HDR secondary or a poor network. Some questions: is it of the same > specification as the primary server, working without issues etc.? Is there a > heavy reporting workload on the secondary? Is the network between > the two fast > and, more importantly, reliable? > > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Lately we tested near sync instead of synchronous replication . There are little differences as regards to risks but performance gain is considerable . Sent from my iPhone > On 18 May 2015, at 18:30, John Miller iii <miller3@us.ibm.com> wrote: > > Newer version of the informix server support read ahead on the secondaries > which should greatly improve the response time of data which is not in > the cache. > > John F. Miller III > STSM, Lead Architect > miller3@us.ibm.com > 503-747-1366 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 05/18/2015 08:56:30 AM: > >> From: "BENJAMIN THOMPSON" <benjamin.thompson@bskyb.com> >> To: ids@iiug.org >> Date: 05/18/2015 08:56 AM >> Subject: Re: tuning HDR [35123] >> Sent by: ids-bounces@iiug.org >> >> It's been a long time since I've used HDR with Informix 10. It went out > of >> continuing support in 2013. >> >> The BTScanner could be a factor in poor performance with the deletes. >> >> Was the database logged before HDR was switched on? If it was, then >> HDR should >> have minimal additional overhead and the most likely cause is slow >> performance >> on the HDR secondary or a poor network. Some questions: is it of the same > >> specification as the primary server, working without issues etc.? Is > there a >> heavy reporting workload on the secondary? Is the network between >> the two fast >> and, more importantly, reliable? >> >> Ben. > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > Stefan Sammut Manager - Solutions & Innovation [PTL Ltd] Nineteen Twenty Three, Valletta Road Marsa, MRS 3000, MT T +35621445566 +356 99405444 stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>; [Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; [LinkedIn] <https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276 %2Ctas%3APTL%2Cidx%3A2-3-8> [Twitter] <https://twitter.com/PTL_Malta>; [Youtube] <https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as =public> [Google Plus] <https://plus.google.com/+PTLMalta>;