Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Performance issue

2 posts in Replication Agent Last posting was on 2003-07-14 08:47:46.0Z
Orkan Genc Posted on 2003-07-14 08:25:13.0Z
From: "Orkan Genc" <ogenc@takasbank.com.tr>
Subject: Performance issue
Date: Mon, 14 Jul 2003 11:25:13 +0300
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <#tjkpLeSDHA.298@forums-2-dub>
Newsgroups: sybase.public.rep-agent
NNTP-Posting-Host: 212.15.15.65
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.rep-agent:584
Article PK: 862245

Hi All;

We are using warm standby replication for our major databases on disaster
recovery purposes. What we have observed so far is that during the day there
is no problem at all for the normal operation however when end-of-day
operations start (which includes continuous large transactions) problems may
start. The problems are reflected to operators as "log segment full" in some
databases, however to my observation the real problem is: the rep_agent is
not fast enough to read the transaction log, translate it to LTL, write it
to the stable device queue and re-locate the secondary truncation point on
the log segment. I think I can say that because although the process has
finished on the primary dataserver, the queue size contiunue to increase for
a long period of time. For large transactional operations we have handled
dumping the transaction log, but it is no use. I have overcome the problem
(not in a good fashion) by inserting a "waitfor delay xx:xx:xx after each
large batch. This just increase the operation duration, there must be some
better ways.

So, can you offer a better way of handling this bottleneck? What may be the
problems in terms of configuration or design? Could applying parallel dsi be
a solution, if so what is the proper way of application? Should it be
applied for the replicate side dsi threads or primary side? We made the
following configuration changes and did not see much of a difference.

ASEREP is the replicate dataserver..

suspend connection to ASEREP.tarihce
alter connection to ASEREP.tarihce set parallel_dsi to 'on'
resume connection to ASEREP.tarihce

suspend connection to ASEREP.tarihce
alter connection to ASEREP.tarihce set dsi_serialization_method to 'none'
--(it could be isolation 3, there is only a single user in end-of-day
operations)
resume connection to ASEREP.tarihce

suspend connection to ASEREP.tarihce
alter connection to ASEREP.tarihce set dsi_num_threads to '12'
resume connection to ASEREP.tarihce

after these changes still there is only "one active" dsi thread, while the
others remain in "Awaiting Command" status. Our configuration is as follows:

Adaptive Server Enterprise/12.0.0.6/P/EBF 10391 ROLLUP/HP9000-879/HP-UX
11.0/1891/64bit/FBO/Thu Aug 15 08:46:13 2002

Replication Server/12.5/P/HP9000/800/HP-UX 11.0/1/OPT/Mon Mar 25 01:52:47
PST 2002

TIA
Regards,
----------------------------------------------------------------------------
----

A.ORKAN GENC


Jason Posted on 2003-07-14 08:47:46.0Z
Date: Mon, 14 Jul 2003 18:47:46 +1000
From: Jason <jason@hotmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Performance issue
References: <#tjkpLeSDHA.298@forums-2-dub>
In-Reply-To: <#tjkpLeSDHA.298@forums-2-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <OowrFXeSDHA.298@forums-2-dub>
Newsgroups: sybase.public.rep-agent
NNTP-Posting-Host: m032-159.nv.iinet.net.au 203.217.32.159
Lines: 85
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.rep-agent:585
Article PK: 862248


Orkan Genc wrote:
> Hi All;
>
> We are using warm standby replication for our major databases on disaster
> recovery purposes. What we have observed so far is that during the day there
> is no problem at all for the normal operation however when end-of-day
> operations start (which includes continuous large transactions) problems may
> start. The problems are reflected to operators as "log segment full" in some
> databases, however to my observation the real problem is: the rep_agent is
> not fast enough to read the transaction log, translate it to LTL, write it
> to the stable device queue and re-locate the secondary truncation point on
> the log segment. I think I can say that because although the process has
> finished on the primary dataserver, the queue size contiunue to increase for
> a long period of time. For large transactional operations we have handled
> dumping the transaction log, but it is no use. I have overcome the problem
> (not in a good fashion) by inserting a "waitfor delay xx:xx:xx after each
> large batch. This just increase the operation duration, there must be some
> better ways.
>
> So, can you offer a better way of handling this bottleneck? What may be the
> problems in terms of configuration or design? Could applying parallel dsi be
> a solution, if so what is the proper way of application? Should it be
> applied for the replicate side dsi threads or primary side? We made the
> following configuration changes and did not see much of a difference.
>
> ASEREP is the replicate dataserver..
>
> suspend connection to ASEREP.tarihce
> alter connection to ASEREP.tarihce set parallel_dsi to 'on'
> resume connection to ASEREP.tarihce
>
> suspend connection to ASEREP.tarihce
> alter connection to ASEREP.tarihce set dsi_serialization_method to 'none'
> --(it could be isolation 3, there is only a single user in end-of-day
> operations)
> resume connection to ASEREP.tarihce
>
> suspend connection to ASEREP.tarihce
> alter connection to ASEREP.tarihce set dsi_num_threads to '12'
> resume connection to ASEREP.tarihce
>
> after these changes still there is only "one active" dsi thread, while the
> others remain in "Awaiting Command" status. Our configuration is as follows:
>
> Adaptive Server Enterprise/12.0.0.6/P/EBF 10391 ROLLUP/HP9000-879/HP-UX
> 11.0/1891/64bit/FBO/Thu Aug 15 08:46:13 2002
>
> Replication Server/12.5/P/HP9000/800/HP-UX 11.0/1/OPT/Mon Mar 25 01:52:47
> PST 2002
>
> TIA
> Regards,
> ----------------------------------------------------------------------------
> ----
>
> A.ORKAN GENC
>
>
>

For starters, you might get more help if you post this to the
replication server newsgroup. I think this one is for non-sybase
rep-agents. You might also want to include a rs_configure and a
rs_config_rep_agent for the database which is a problem.

If you have a look at the manual, one thing it suggest as a possible
solution is to define replication definitions. This has worked in the
past for me quite well. It can be a pain to keep the rep defs up to date.

I have also had some success in speeding up replication by putting all
the "temporary work tables" outside the replicated database. By
temporary work tables, I mean non-business critical data, and not #temp
tables.

Cheers,
Jason