Transactional Replication - Performance Problem

Last night, while I was sleeping I think I found the problem.

I think I was just looking for the problem at the wrong place.

Here's the environment:
- ServerA is the Publisher (OLTP)
- ServerB is the Subscriber and Distributor. (Reporting server)

ServerA' s hardware:
8 core, 12GB RAM, RAID5 for the data file, RAID1 for the Log, RAID1 for the
TEMPDB

ServerB' s hardware:
8 core, 12GB RAM, RAID5 for data file, log file, tempdb, distributor.

And I just was running the Performance Monitor against the ServerA and never
thought the problem could be about the ServerB. But last night I remembered
about the distributor. The workload on the ServerA' s physical disk would
occur to the disk where the distributor and replicated database are. I just
missed this point... So I think this is where the problem occurs. As the
arrays of ServerA is much more powerful than the ServerB' s, I'll put the
distributor database on ServerA' s disks.

Do you think I should separate the data and log files of distributor
database on the ServerA' s RAID5 and RAID1? What could be a better
implementation in terms of the distributor and according to the hardware
listed above?

Thanks indeed,
Joe


"Hilary Cotter" wrote:

> replication should have a very low impact on the instances it is running on.
> This really depends on your workload and hardware as you point out.
>
> What sort of performance degradation are you seeing and have you done any
> sort of bottle neck analysis to pinpoint the exact cause.
>
> Also you might want to look at a remote distributor if you are using
> transactional replication.
>
> "Joe" wrote:
>
> > Hello!
> >
> > Would you reckon with a performance problem when you set up a Transactional
> > Replication for your database?
> >
> > We had too much trouble with performance when we set it up. Is this
> > something expected? I know it depends on the hardware and workload from the
> > users, but we weren't expecting a huge impact like this.
> >
> > Do you have some tips to make Transactional Replication work
> > performance-free problems?
> >
> > Thank you,
> > Joe
 >> Stay informed about: Transactional Replication - Performance Problem 

I kind of doubt it.

I would run perf mon and study your disk times. If either are over 50-55%
you are in trouble. If the disk latencies are consistently high and over 2%
the number of disks you are possibly in trouble. Basically you are going to
disk a lot and you need to find out why. Then you need to check your disk
response times to ensure they are below 50 ms.

Also check you disk read to write % to ensure that they are both 20% writes
or less.

There's lots more counters you can look at for analysis, but I would start
there. If they are high check your memory counters (cache hit ration, page
life expectency) to ensure that you don't have a memory pressures which are
causing high disk io.

Pal is a good tool overall to give you an idea to see if your workload is
too much for your hardware. If it raises a lot of alerts you need to review
the alerts to see if they are significant.


"Joe" wrote in message

> Last night, while I was sleeping I think I found the problem.
>
> I think I was just looking for the problem at the wrong place.
>
> Here's the environment:
> - ServerA is the Publisher (OLTP)
> - ServerB is the Subscriber and Distributor. (Reporting server)
>
> ServerA' s hardware:
> 8 core, 12GB RAM, RAID5 for the data file, RAID1 for the Log, RAID1 for
> the
> TEMPDB
>
> ServerB' s hardware:
> 8 core, 12GB RAM, RAID5 for data file, log file, tempdb, distributor.
>
> And I just was running the Performance Monitor against the ServerA and
> never
> thought the problem could be about the ServerB. But last night I
> remembered
> about the distributor. The workload on the ServerA' s physical disk would
> occur to the disk where the distributor and replicated database are. I
> just
> missed this point... So I think this is where the problem occurs. As the
> arrays of ServerA is much more powerful than the ServerB' s, I'll put the
> distributor database on ServerA' s disks.
>
> Do you think I should separate the data and log files of distributor
> database on the ServerA' s RAID5 and RAID1? What could be a better
> implementation in terms of the distributor and according to the hardware
> listed above?
>
> Thanks indeed,
> Joe
>
>
> "Hilary Cotter" wrote:
>
>> replication should have a very low impact on the instances it is running
>> on.
>> This really depends on your workload and hardware as you point out.
>>
>> What sort of performance degradation are you seeing and have you done any
>> sort of bottle neck analysis to pinpoint the exact cause.
>>
>> Also you might want to look at a remote distributor if you are using
>> transactional replication.
>>
>> "Joe" wrote:
>>
>> > Hello!
>> >
>> > Would you reckon with a performance problem when you set up a
>> > Transactional
>> > Replication for your database?
>> >
>> > We had too much trouble with performance when we set it up. Is this
>> > something expected? I know it depends on the hardware and workload from
>> > the
>> > users, but we weren't expecting a huge impact like this.
>> >
>> > Do you have some tips to make Transactional Replication work
>> > performance-free problems?
>> >
>> > Thank you,
>> > Joe
 >> Stay informed about: Transactional Replication - Performance Problem 

Users of OLTP server complain about performance degradation and they say "we
simply can't use the application (an interface that use the OLTP Instance)".
When I stop the synchronization for the subscriber and stop the log reader
agent, then everything runs normal and nobody calls me.

During that slow performance period I closely watch server using Performance
Monitor and using counters such as "read\write\avg queue length", "disk
time", "page life expectancy", "cache hit ratio" etc. I don't see any
bottlenecks.

This is really about the Replication, otherwise users would still complain
about the performance when I drop it, but they don't. They say "performance
is good now".

So I think the Log Reader Agent which runs on the ServerB just can't
transfer log records from the ServerA to ServerB because ServerB has only one
RAID5 and every database related stuff runs on it despite ServerA has 3
separate RAID arrays.

Thanks,
Joe

"Hilary Cotter" wrote:

> I kind of doubt it.
>
> I would run perf mon and study your disk times. If either are over 50-55%
> you are in trouble. If the disk latencies are consistently high and over 2%
> the number of disks you are possibly in trouble. Basically you are going to
> disk a lot and you need to find out why. Then you need to check your disk
> response times to ensure they are below 50 ms.
>
> Also check you disk read to write % to ensure that they are both 20% writes
> or less.
>
> There's lots more counters you can look at for analysis, but I would start
> there. If they are high check your memory counters (cache hit ration, page
> life expectency) to ensure that you don't have a memory pressures which are
> causing high disk io.
>
> Pal is a good tool overall to give you an idea to see if your workload is
> too much for your hardware. If it raises a lot of alerts you need to review
> the alerts to see if they are significant.
>
>
> "Joe" wrote in message
>
> > Last night, while I was sleeping I think I found the problem.
> >
> > I think I was just looking for the problem at the wrong place.
> >
> > Here's the environment:
> > - ServerA is the Publisher (OLTP)
> > - ServerB is the Subscriber and Distributor. (Reporting server)
> >
> > ServerA' s hardware:
> > 8 core, 12GB RAM, RAID5 for the data file, RAID1 for the Log, RAID1 for
> > the
> > TEMPDB
> >
> > ServerB' s hardware:
> > 8 core, 12GB RAM, RAID5 for data file, log file, tempdb, distributor.
> >
> > And I just was running the Performance Monitor against the ServerA and
> > never
> > thought the problem could be about the ServerB. But last night I
> > remembered
> > about the distributor. The workload on the ServerA' s physical disk would
> > occur to the disk where the distributor and replicated database are. I
> > just
> > missed this point... So I think this is where the problem occurs. As the
> > arrays of ServerA is much more powerful than the ServerB' s, I'll put the
> > distributor database on ServerA' s disks.
> >
> > Do you think I should separate the data and log files of distributor
> > database on the ServerA' s RAID5 and RAID1? What could be a better
> > implementation in terms of the distributor and according to the hardware
> > listed above?
> >
> > Thanks indeed,
> > Joe
> >
> >
> > "Hilary Cotter" wrote:
> >
> >> replication should have a very low impact on the instances it is running
> >> on.
> >> This really depends on your workload and hardware as you point out.
> >>
> >> What sort of performance degradation are you seeing and have you done any
> >> sort of bottle neck analysis to pinpoint the exact cause.
> >>
> >> Also you might want to look at a remote distributor if you are using
> >> transactional replication.
> >>
> >> "Joe" wrote:
> >>
> >> > Hello!
> >> >
> >> > Would you reckon with a performance problem when you set up a
> >> > Transactional
> >> > Replication for your database?
> >> >
> >> > We had too much trouble with performance when we set it up. Is this
> >> > something expected? I know it depends on the hardware and workload from
> >> > the
> >> > users, but we weren't expecting a huge impact like this.
> >> >
> >> > Do you have some tips to make Transactional Replication work
> >> > performance-free problems?
> >> >
> >> > Thank you,
> >> > Joe
>
>
 >> Stay informed about: Transactional Replication - Performance Problem 

Joe, something does not seem right here. Contact me offline if you want me
to look at it with you.


"Joe" wrote in message

> Users of OLTP server complain about performance degradation and they say
> "we
> simply can't use the application (an interface that use the OLTP
> Instance)".
> When I stop the synchronization for the subscriber and stop the log reader
> agent, then everything runs normal and nobody calls me.
>
> During that slow performance period I closely watch server using
> Performance
> Monitor and using counters such as "read\write\avg queue length", "disk
> time", "page life expectancy", "cache hit ratio" etc. I don't see any
> bottlenecks.
>
> This is really about the Replication, otherwise users would still complain
> about the performance when I drop it, but they don't. They say
> "performance
> is good now".
>
> So I think the Log Reader Agent which runs on the ServerB just can't
> transfer log records from the ServerA to ServerB because ServerB has only
> one
> RAID5 and every database related stuff runs on it despite ServerA has 3
> separate RAID arrays.
>
> Thanks,
> Joe
>
> "Hilary Cotter" wrote:
>
>> I kind of doubt it.
>>
>> I would run perf mon and study your disk times. If either are over 50-55%
>> you are in trouble. If the disk latencies are consistently high and over
>> 2%
>> the number of disks you are possibly in trouble. Basically you are going
>> to
>> disk a lot and you need to find out why. Then you need to check your disk
>> response times to ensure they are below 50 ms.
>>
>> Also check you disk read to write % to ensure that they are both 20%
>> writes
>> or less.
>>
>> There's lots more counters you can look at for analysis, but I would
>> start
>> there. If they are high check your memory counters (cache hit ration,
>> page
>> life expectency) to ensure that you don't have a memory pressures which
>> are
>> causing high disk io.
>>
>> Pal is a good tool overall to give you an idea to see if your workload is
>> too much for your hardware. If it raises a lot of alerts you need to
>> review
>> the alerts to see if they are significant.
>>
>>
>> "Joe" wrote in message
>>
>> > Last night, while I was sleeping I think I found the problem.
>> >
>> > I think I was just looking for the problem at the wrong place.
>> >
>> > Here's the environment:
>> > - ServerA is the Publisher (OLTP)
>> > - ServerB is the Subscriber and Distributor. (Reporting server)
>> >
>> > ServerA' s hardware:
>> > 8 core, 12GB RAM, RAID5 for the data file, RAID1 for the Log, RAID1 for
>> > the
>> > TEMPDB
>> >
>> > ServerB' s hardware:
>> > 8 core, 12GB RAM, RAID5 for data file, log file, tempdb, distributor.
>> >
>> > And I just was running the Performance Monitor against the ServerA and
>> > never
>> > thought the problem could be about the ServerB. But last night I
>> > remembered
>> > about the distributor. The workload on the ServerA' s physical disk
>> > would
>> > occur to the disk where the distributor and replicated database are. I
>> > just
>> > missed this point... So I think this is where the problem occurs. As
>> > the
>> > arrays of ServerA is much more powerful than the ServerB' s, I'll put
>> > the
>> > distributor database on ServerA' s disks.
>> >
>> > Do you think I should separate the data and log files of distributor
>> > database on the ServerA' s RAID5 and RAID1? What could be a better
>> > implementation in terms of the distributor and according to the
>> > hardware
>> > listed above?
>> >
>> > Thanks indeed,
>> > Joe
>> >
>> >
>> > "Hilary Cotter" wrote:
>> >
>> >> replication should have a very low impact on the instances it is
>> >> running
>> >> on.
>> >> This really depends on your workload and hardware as you point out.
>> >>
>> >> What sort of performance degradation are you seeing and have you done
>> >> any
>> >> sort of bottle neck analysis to pinpoint the exact cause.
>> >>
>> >> Also you might want to look at a remote distributor if you are using
>> >> transactional replication.
>> >>
>> >> "Joe" wrote:
>> >>
>> >> > Hello!
>> >> >
>> >> > Would you reckon with a performance problem when you set up a
>> >> > Transactional
>> >> > Replication for your database?
>> >> >
>> >> > We had too much trouble with performance when we set it up. Is this
>> >> > something expected? I know it depends on the hardware and workload
>> >> > from
>> >> > the
>> >> > users, but we weren't expecting a huge impact like this.
>> >> >
>> >> > Do you have some tips to make Transactional Replication work
>> >> > performance-free problems?
>> >> >
>> >> > Thank you,
>> >> > Joe
>>
>>
 >> Stay informed about: Transactional Replication - Performance Problem 

Thank you for your interest Hilary, I appreciate it; however I'm too far away
from you and I guess my new thread may give you a clue about this performance
problem.

Joe

"Hilary Cotter" wrote:

> Joe, something does not seem right here. Contact me offline if you want me
> to look at it with you.
 >> Stay informed about: Transactional Replication - Performance Problem